Comment Créer une analyse de cohorte dans Excel - Guide Pratique

Apprenez à construire des tableaux d'analyse de cohorte pour suivre le comportement des utilisateurs au fil du temps, essentiel pour comprendre la rétention et les métriques de cycle de vie. Cette technique avancée révèle comment différents groupes de clients performent sur leur durée de vie.

Pourquoi c'est important

L'analyse de cohorte identifie les tendances de rétention et les modèles de comportement critiques pour les stratégies SaaS, e-commerce et applications mobiles. Elle sépare les nouveaux utilisateurs des utilisateurs établis pour révéler les vraies performances du produit.

Prérequis

•Compétences Excel intermédiaires : formules VLOOKUP, INDEX/MATCH
•Compréhension des tableaux croisés dynamiques et des fonctions de date
•Données de transaction propres avec identifiants utilisateur et dates

Instructions étape par étape

Préparer les données de transaction brutes

Assurez-vous que votre ensemble de données contient les colonnes ID utilisateur, Date de transaction et Chiffre d'affaires. Nettoyez les doublons via Données > Outils de données > Supprimer les doublons.

Créer la colonne d'attribution de cohorte

Ajoutez une colonne 'Mois de cohorte' avec la formule =TEXT(MIN(SI($A$2:$A$100=A2,$B$2:$B$100)),"YYYY-MM") en tant que formule matricielle (Ctrl+Maj+Entrée).

Calculer l'âge de la cohorte en périodes

Créez la colonne 'Âge de cohorte' avec =DATEDIF(DATE(YEAR(D2),MONTH(D2),1),DATE(YEAR(B2),MONTH(B2),1),"M") pour compter les mois entre le début et la transaction.

Construire un tableau croisé dynamique pour la matrice de cohorte

Insérer > Tableau croisé dynamique, placez Mois de cohorte en Lignes, Âge de cohorte en Colonnes, et sommez le Chiffre d'affaires en Valeurs.

Formater et analyser les tendances de rétention

Appliquez la mise en forme conditionnelle (Accueil > Mise en forme conditionnelle > Dégradés de couleur) pour visualiser la performance et analysez les tendances diagonales.

Méthodes alternatives

Utiliser des tableaux croisés avec champs calculés

Construisez d'abord le tableau croisé, puis ajoutez des champs calculés pour calculer les pourcentages de rétention directement sans formules manuelles.

Approche basée sur Power Query

Utilisez Power Query (Données > Obtenir et transformer > À partir d'un tableau) pour regrouper et agréger les données avant l'importation dans Excel.

Cohorte de rétention avec nombre d'utilisateurs

Remplacez Revenue par COUNTA pour suivre le nombre d'utilisateurs actifs par mois de cohorte, révélant la rétention d'utilisateurs plutôt que la valeur monétaire.

Astuces et conseils

✓Utilisez toujours la date de première transaction de l'utilisateur comme identifiant de cohorte pour la cohérence.
✓Formatez les âges de cohorte comme 0, 1, 2, 3+ mois pour une interprétation plus facile des courbes de rétention.
✓Utilisez des références relatives dans les tableaux croisés pour permettre des ajustements faciles de la plage de dates.
✓Mettez en surbrillance les cellules diagonales pour suivre visuellement la progression de chaque cohorte.

Astuces avancées

★Créez un tableau de cohorte secondaire montrant les taux de rétention (divisez chaque cellule par la taille de cohorte Mois 0) pour normaliser les cohortes de tailles différentes.
★Utilisez INDEX/MATCH avec IFERROR pour gérer les combinaisons cohorte-âge manquantes, évitant les valeurs zéro trompeuses.
★Automatisez l'attribution de cohorte avec des colonnes d'aide utilisant la fonction MINIFS pour identifier dynamiquement la date de premier achat.

Résolution de problèmes

Le tableau croisé affiche des erreurs #VALUE! dans la colonne d'âge de cohorte

Assurez-vous que toutes les dates sont au format Date, pas texte. Utilisez Données > Convertir en colonnes et sélectionnez le format Date.

La matrice de cohorte contient de nombreuses cellules vides

C'est normal—les cohortes futures n'ont pas vieilli suffisamment. Utilisez la mise en forme conditionnelle pour masquer les cellules vides.

Les pourcentages de rétention dépassent 100% dans les mois suivants

Vérifiez les ID utilisateur dupliqués ou plusieurs transactions par utilisateur le même jour ; dédupliquez les données avant de recalculer.

Le tableau croisé prend trop de temps à actualiser avec de gros volumes de données

Utilisez Power Query pour pré-agréger les données par cohorte et âge avant de créer le tableau croisé.

Formules Excel associées

DATEDIF MINIFS TEXT INDEX/MATCH VLOOKUP DATE YEAR MONTH

Questions fréquentes

Quelle est la différence entre une cohorte de rétention et une cohorte de chiffre d'affaires ?

Une cohorte de rétention suit le pourcentage d'utilisateurs revenant chaque mois, tandis qu'une cohorte de chiffre d'affaires suit les dépenses totales par cohorte. Les deux révèlent des modèles mais répondent à des questions commerciales différentes.

Puis-je créer une analyse de cohorte avec des données hebdomadaires ou quotidiennes ?

Oui, remplacez simplement la logique MOIS par des fonctions SEMAINE ou JOUR dans votre formule d'attribution de cohorte. Les cohortes quotidiennes conviennent aux événements haute fréquence ; hebdomadaires pour la plupart des applications SaaS.

Comment interpréter les lignes diagonales déclinantes dans ma matrice de cohorte ?

Les valeurs déclinantes de gauche à droite indiquent une rétention déclinante—typique et attendue. Les chutes plus abruptes suggèrent une mauvaise adéquation ou des problèmes d'intégration.

Dois-je inclure les utilisateurs du Mois 0 qui ne reviennent jamais ?

Oui, incluez tous les utilisateurs du Mois 0 comme dénominateur pour les calculs de taux de rétention ; les exclure gonfle les pourcentages et masque les problèmes de désabonnement.

Quel est le meilleur moyen de comparer plusieurs cohortes de tailles différentes ?

Créez un tableau de taux de rétention en divisant les valeurs de chaque cohorte par la taille de son Mois 0, normalisant toutes les cohortes à 100% à la création.