Comment faire une analyse de cohorte sur Excel et suivre la rétention

Ton manager veut savoir si la fidélité des nouveaux clients s'améliore, mais ton export CRM te renvoie trois formats de date, des doublons et des lignes vides. Tu ouvres Excel, tu construis un TCD, puis tu réalises que la matrice raconte n'importe quoi dès qu'une même personne apparaît deux fois dans le mois. C'est exactement le genre de cas où une analyse de cohorte sur Excel peut être brillante, à condition de traiter les données sales avant de calculer quoi que ce soit.

Le vrai piège, ce n'est pas Excel. C'est l'idée qu'un simple tableau croisé dynamique suffit à produire une rétention fiable. Si tu veux un résultat exploitable, il faut une méthode solide, pas une recette fragile.

Comprendre pourquoi un nombre peut s'afficher comme du texte dans Excel aide aussi à repérer un symptôme fréquent dans les exports mal préparés, surtout quand les dates et les identifiants ne sont pas typés proprement.

Pourquoi ton analyse de rétention sur Excel est faussée

Tu pars souvent d'une demande très simple, un manager veut savoir si les clients recrutés le mois dernier reviennent encore au bout de quelques semaines. Sur le papier, l'idée semble limpide. En pratique, ton CRM mélange des dates au format FR, des doublons de contacts et des cellules vides qui perturbent toute lecture de rétention.

Le problème n'est pas la matrice, c'est l'entrée

Une matrice de cohorte n'a de valeur que si les lignes et les colonnes reposent sur des événements comparables. Quand une ligne correspond à un client inscrit en janvier, une autre à un premier achat en février et une troisième à un export incomplet, tu compares des objets différents. Le tableau devient visuellement propre, mais analytiquement faux.

Règle de terrain, si tu n'as pas défini la même logique d'entrée pour tous les clients, ta rétention n'est pas interprétable.

C'est aussi pour ça que les tutoriels trop rapides déçoivent en entreprise. Les cas réels ne sont pas propres, surtout avec des extractions venant de CRM, ERP ou d'outils RH où les formats de date et les doublons se mélangent. Si tu analyses des cohortes sans nettoyer la base, tu risques de lire une baisse de fidélité là où il s'agit juste d'un problème de saisie.

Ce que tu dois viser dès le départ

L'objectif n'est pas de faire “un joli tableau”. L'objectif est d'obtenir une base où chaque client est rattaché à une cohorte claire, avec une logique de mois stable. Dès que ce socle existe, Excel devient très lisible, et la rétention mensuelle prend une forme exploitable pour piloter une équipe commerciale, un service client ou un portefeuille RH.

La bonne nouvelle, c'est qu'une méthode simple suffit souvent pour remettre les choses d'équerre. La mauvaise, c'est qu'il faut accepter de nettoyer avant de résumer. Sans ça, tu fabriques une erreur élégante au lieu d'un indicateur fiable.

Préparer et nettoyer tes données pour l'analyse

Avant même de penser à un TCD, tu dois mettre ta base en état de travail. Les données doivent être nettoyées avant agrégation, avec suppression des doublons et des valeurs nulles, plus un format de dates homogène, sinon les cohortes mensuelles deviennent incohérentes et les taux de rétention sont faussés.

Standardise tes dates sans compromis

Commence par vérifier que la colonne de date est bien reconnue comme une date, pas comme du texte. En environnement francophone, les exports arrivent souvent au format jour/mois/année, parfois mélangés à des cellules importées depuis un outil qui utilise un autre ordre de lecture. Le risque n'est pas cosmétique, Excel peut interpréter deux lignes voisines différemment, et ton découpage mensuel part alors dans tous les sens.

Utilise une colonne de contrôle si nécessaire, puis force un format unique sur toute la table. Si certaines valeurs restent alignées à gauche dans la feuille, prends ça comme un signal d'alerte. Tu n'as pas besoin d'un système parfait, mais tu as besoin d'un format homogène.

Supprime les doublons avec méthode

Les doublons sont souvent invisibles au premier regard, surtout dans des exports CRM ou ERP. Un même client peut apparaître plusieurs fois dans la même période, et si tu comptes chaque ligne, tu surévalues l'activité réelle. Pour une analyse de cohorte, l'unité de mesure doit rester le client, pas la transaction brute.

La bonne méthode pour supprimer les doublons dans Excel t'évite de garder des lignes qui gonflent artificiellement la base.

• Vérifie d'abord la clé de déduplication, généralement l'identifiant client combiné à la date d'événement si besoin.
• Conserve la ligne qui porte l'information utile, pas celle qui existe juste parce qu'un export a été relancé.
• Contrôle ensuite la taille de la base, pour t'assurer que tu n'as pas supprimé des cas légitimes.

Gère les valeurs nulles proprement

Une cellule vide ne vaut pas une information. Si elle concerne l'identifiant ou la date d'entrée en cohorte, tu dois la traiter avant d'agréger. Dans certains cas, tu peux exclure la ligne. Dans d'autres, tu dois la corriger à la source avec le métier, surtout si la date manque parce que le processus de saisie est incomplet.

L'important, c'est d'éviter les faux mois de cohorte et les clients fantômes. Une base propre n'est pas parfaite, mais elle est cohérente. Et en analyse de rétention, la cohérence vaut plus qu'une feuille spectaculaire.

Créer ta matrice de cohortes avec un tableau croisé dynamique

La méthode classique reste redoutablement efficace quand tu veux sortir vite un premier résultat. Elle s'appuie sur une logique simple, normaliser les dates au mois de cohorte, calculer un âge de cohorte en mois, puis agréger dans un TCD. L'idée n'est pas de compliquer Excel, mais de lui donner des repères stables.

Calcule le mois de cohorte

Le mois de cohorte représente le mois d'entrée du client, ou le premier mois d'activité que tu retiens comme point de départ. Si tu travailles sur des abonnements, c'est souvent la date d'inscription. Si ton analyse porte sur des ventes, ça peut être la première commande.

Tu peux normaliser la date au premier jour du mois avec une formule comme celle-ci, en adaptant la cellule :

=DATE(ANNEE(A2);MOIS(A2);1)

Cette étape regroupe tous les événements d'un même mois dans une cohorte commune. Sans ça, janvier 3 et janvier 28 deviennent deux points différents alors qu'ils doivent appartenir au même groupe.

Calcule l'âge de cohorte

L’âge de cohorte mesure le nombre de mois écoulés entre le mois de cohorte et le mois de transaction. La logique de type DATEDIF reste la plus propre pour ça. Si ton mois de cohorte est en B2 et ton mois de transaction en C2, la formule ressemble à ceci :

=DATEDIF(B2;C2;"M")

Le résultat est simple. 0 pour le mois d'entrée, 1 pour le mois suivant, puis 2, 3, et ainsi de suite. Tu obtiens une chronologie uniforme, facile à comparer d'une cohorte à l'autre.

Construis le TCD sans te tromper d'axe

Une fois les deux colonnes prêtes, insère un tableau croisé dynamique et place :

• Mois de cohorte en lignes.
• Âge de cohorte en colonnes.
• L'identifiant client en valeurs.

Privilégie le comptage distinct des identifiants clients. C'est plus fiable, parce qu'un même client peut apparaître plusieurs fois dans une période donnée. Coche Ajouter ces données au modèle de données en créant le TCD, puis choisis la synthèse Total distinct. Sans le modèle de données, la synthèse Nombre compte chaque ligne et surestime vite la cohorte.

Convertis les comptes en taux de rétention

Les comptes bruts sont utiles pour contrôler le volume, pas pour comparer les cohortes entre elles. Pour obtenir une rétention lisible, divise chaque cellule par la taille de la cohorte en période 0, puis applique un format en pourcentage. Si la période 0 est en colonne B, =C5/$B5 recopiée sur toute la grille donne le taux de chaque case. Excel te donne alors une grille comparable, même si les cohortes n'ont pas toutes la même taille.

Pratique simple, fige la sortie du TCD en valeurs avant de bâtir les pourcentages si tu sais que le pivot va bouger souvent. Tu évites des recalculs instables et des écarts difficiles à expliquer.

Le TCD dans Excel reste donc le meilleur point de départ quand tu veux aller vite, tester une hypothèse ou présenter un premier diagnostic à un manager.

Si le tableau croisé te freine encore, l’exercice Analyser des ventes avec un tableau croisé dynamique en reprend la construction depuis zéro.

Automatiser l'analyse de cohorte avec Power Query

Quand tu dois refaire l'exercice chaque mois, le TCD manuel devient vite pénible. Tu corriges une date, tu supprimes un doublon, tu rafraîchis le pivot, puis tu découvres qu'un filtre a sauté. Power Query change la logique, tu transformes la donnée en amont et tu laisses Excel travailler sur une base déjà nettoyée.

Pourquoi Power Query est souvent supérieur

Le TCD est rapide, mais il reste fragile si la source évolue. Power Query est plus fiable parce qu'il centralise les étapes de préparation, suppression des doublons, normalisation des dates, création des colonnes calculées, puis chargement dans Excel. Quand le fichier source change, tu actualises la requête au lieu de refaire le nettoyage à la main.

Critère	Tableau Croisé Dynamique (TCD)	Power Query
Vitesse de mise en place	Très rapide	Un peu plus long au départ
Robustesse sur des données sales	Moyenne	Forte
Gestion des mises à jour	Manuelle	Automatisée
Risque d'erreur humaine	Plus élevé	Plus faible
Adapté aux fichiers récurrents	Correct	Excellent

Mets en place le flux de transformation

Commence par importer la source dans Power Query, puis vérifie les types de colonnes. Les dates doivent être reconnues comme dates, les identifiants comme texte ou clé stable, et les colonnes inutiles retirées tôt pour garder un modèle lisible. Ensuite, nettoie les lignes vides et les doublons avant d'ajouter les colonnes de cohorte.

Ajoute ensuite, par Ajouter une colonne > Colonne personnalisée, la colonne de mois de cohorte avec Date.StartOfMonth([Inscription]), puis la colonne d'âge de cohorte avec (Date.Year([Transaction]) - Date.Year([Mois de cohorte])) * 12 + Date.Month([Transaction]) - Date.Month([Mois de cohorte]). L'intérêt, c'est que ces calculs vivent dans la requête et non dans des cellules dispersées dans plusieurs onglets. Tu réduis ainsi les risques de casse quand quelqu'un colle de nouvelles données au mauvais endroit.

Ce que tu gagnes en pratique

Dans un environnement comptable, RH ou conseil, cette automatisation change vraiment la vie. Tu peux réutiliser la même logique sur un fichier mensuel de ventes, un suivi d'absences ou une base d'onboarding sans reconstruire tout le classeur. L'analyse devient un process, pas un bricolage.

Si tu veux approfondir ce type de chaîne de traitement, une formation Excel structurée aide à gagner du temps et à éviter les mauvais réflexes sur les requêtes, les pivots et les formules. Le plus gros avantage n'est pas la vitesse brute, c'est la confiance dans le chiffre final.

Visualiser et interpréter tes résultats de rétention

Une matrice de cohorte sans lecture visuelle reste froide. Dès que tu appliques une mise en forme conditionnelle en échelle de couleurs, la structure saute aux yeux et les décrochages deviennent visibles beaucoup plus vite. Cette lecture en carte de chaleur est aussi la bonne manière d'éviter les comparaisons trompeuses entre cohortes de tailles différentes, puisque tu travailles sur des taux plutôt que sur des volumes bruts.

Lis la couleur comme un signal, pas comme une décoration

Dans une heatmap, les cellules les plus fortes doivent ressortir clairement. Une zone qui reste verte plus longtemps suggère une rétention plus solide, alors qu'une colonne qui vire rapidement vers des teintes faibles signale un décrochage précoce. C'est la raison pour laquelle la mise en forme conditionnelle est si utile, elle t'aide à lire la décroissance mensuelle sans forcer ton cerveau à décoder chaque case une par une.

La carte de chaleur dans Excel fonctionne particulièrement bien quand tu dois présenter un diagnostic à un responsable métier qui ne veut pas lire dix colonnes de chiffres.

Regarde les formes avant les chiffres

Une diagonale qui s'assombrit vite indique souvent un churn précoce. À l'inverse, une courbe qui se tasse sans s'effondrer suggère une base plus stable. Ce que tu cherches, ce n'est pas seulement la meilleure cohorte, c'est aussi le moment où la rétention casse.

Tu peux aussi tracer une courbe pour une cohorte précise si tu veux suivre son comportement dans le temps. Cette approche aide à comparer plusieurs groupes sans perdre la lecture générale de la matrice. Elle est pratique pour un directeur commercial qui veut savoir si un canal d'acquisition récent tient mieux que l'ancien.

Traduis les visuels en action

Une rétention faible dès le départ appelle souvent un travail sur l'onboarding ou le premier usage. Une baisse plus tardive pointe plutôt vers la valeur perçue, la fréquence d'usage ou le suivi relationnel. Dans les RH, la même logique peut servir à suivre la tenue d'un vivier, d'une promotion ou d'un programme d'intégration.

Le bon réflexe, c'est de relier la forme visuelle à un levier opérationnel concret. Tu ne regardes pas juste une couleur, tu détectes un problème de parcours, d'offre ou de process.

Questions fréquentes et prochaines étapes

Faut-il toujours partir d'une cohorte d'acquisition

Non. Une cohorte d'acquisition repose sur la date d'inscription ou de première commande, mais une cohorte comportementale peut partir de la première utilisation d'une fonctionnalité, d'un achat répété ou d'un jalon métier. Le bon choix dépend de la question posée, pas de la facilité de calcul.

Quelle méthode choisir entre TCD et Power Query

Le TCD est parfait quand tu veux aller vite, tester un cas ou présenter une lecture ponctuelle. Power Query devient indispensable dès que la source est sale, récurrente ou sujette à mises à jour fréquentes. Si tu dois refaire le même fichier tous les mois, l'automatisation te fera gagner en fiabilité.

Une cohorte trop petite vaut-elle quelque chose

Une petite cohorte peut servir de signal exploratoire, mais elle reste fragile. Si tu vois des écarts extrêmes sur peu de clients, traite-les comme un indice, pas comme une conclusion. La cohérence de la tendance compte davantage qu'un point isolé spectaculaire.

Bon réflexe, compare toujours des cohortes construites avec la même définition d'entrée, la même granularité mensuelle et la même règle d'activité. Sinon, tu mélanges des pommes, des poires et des périodes incomplètes.

En résumé

• Nettoie d'abord, surtout les dates, les doublons et les cellules vides.
• Normalise au mois de cohorte, puis calcule l'âge avec une logique DATEDIF.
• Utilise un TCD pour démarrer vite, puis Power Query si la source doit être automatisée.
• Travaille en taux de rétention, pas seulement en volumes.
• Lis la matrice comme un outil de décision, avec une heatmap et des courbes simples.