Taux de rétention client : la formule et le calcul par cohorte depuis un export de commandes

La formule du taux de rétention, les trois taux qu'on confond, un tableau de cohortes dans Excel ou Sheets et un vrai rapport sur 36 969 commandes.

Fondateur, Glow Reports. , 5 min de lecture
  • Rétention
  • Excel
  • Exemples de rapports
Des tickets de commande rangés dans des dossiers de cohortes mensuelles, moins nombreux chaque mois
Sur cette page
  1. Comment calculer le taux de rétention client ?
  2. Quel taux de rétention choisir ?
  3. Comment construire un tableau de cohortes dans Excel ou Google Sheets ?
  4. Que montre un vrai rapport de cohortes ?
  5. Pourquoi la première cohorte semble-t-elle si bonne ?
  6. Que dit encore le tableau ?
  7. Quelle place pour Glow Reports ?
  8. Questions fréquentes

Le taux de rétention client mesure la part de clients qui reviennent. La formule tient en trois nombres, mais le mot « rétention » recouvre trois mesures différentes. Avec un export de commandes, celle qui vous renseigne le plus est la rétention par cohorte. Parmi les clients qui ont commandé pour la première fois un mois donné, combien recommandent 1, 2 ou 12 mois plus tard ? Voici la formule, les trois mesures, la méthode dans Excel ou Google Sheets, et un rapport réel sur 36 969 commandes d'un grossiste britannique en articles cadeaux, dont chaque chiffre a été vérifié trois fois.

Comment calculer le taux de rétention client ?

Prenez les clients actifs en fin de période, retirez les nouveaux clients gagnés pendant la période, puis divisez par les clients du début de période. Multipliez par 100 pour obtenir un pourcentage.

Taux de rétention client
Taux de rétention = (clients en fin de période - nouveaux clients) / clients en début de période x 100

HubSpot donne la même formule et conseille de choisir d'abord la période : mois, trimestre ou année HubSpot. Pour une boutique, le client n'a pas de contrat. On le définit donc comme une personne qui a passé au moins une commande sur la période.

Sur les données du grossiste, 4 266 clients ont commandé entre décembre 2009 et novembre 2010. Sur les 12 mois suivants, 4 297 clients ont commandé, dont 1 584 nouveaux. 2 713 clients de la première année sont donc revenus.

63,6 % des clients de la première année ont recommandé la deuxième année.

Ce chiffre fait un bon titre. Mais il ne dit pas quand les clients reviennent, ni si les nouveaux se comportent comme les anciens. C'est le rôle des cohortes.

Quel taux de rétention choisir ?

Trois mesures portent ce nom et répondent à des questions différentes. Choisissez celle qui correspond à votre question avant de construire quoi que ce soit.

Ce qu'il compteSur les données du grossiste
Rétention sur périodeLes clients d'une période qui recommandent la période suivante63,6 % de l'année 1 à l'année 2
Rétention par cohorteLes clients vus pour la première fois le mois M qui commandent à M + N9,5 % à 42,5 % au mois 3
Taux de réachatLes clients avec plus d'une commande sur toute la période71,8 % (4 200 sur 5 850)

La rétention sur période convient à une présentation au comité de direction. Le taux de réachat grimpe avec la durée des données : comparez-le seulement sur des fenêtres de même longueur. La rétention par cohorte compare des clients au même âge. C'est la question que posent les utilisateurs d'Excel-Downloads qui cherchent à séparer clients fidèles et nouveaux clients Excel-Downloads.

Comment construire un tableau de cohortes dans Excel ou Google Sheets ?

Donnez à chaque commande un mois, une cohorte et un âge en mois, gardez une ligne par client et par mois, puis comptez les clients par cohorte et par âge dans un tableau croisé dynamique. Quatre colonnes d'aide et un tableau croisé suffisent.

Partez d'un export avec une ligne par commande : un numéro de commande, une date et un identifiant client. Un export par ligne d'article fonctionne aussi, grâce au dédoublonnage de l'étape 4. Disons que la date de commande est en colonne B et l'identifiant client en colonne C.

  1. Mois de commande. En G2, , puis recopiez vers le bas. Chaque commande porte le premier jour de son mois.
  2. Mois de cohorte. En H2, . La fonction renvoie le premier mois de commande du client Support Microsoft. Dans Google Sheets, elle s'appelle et prend les mêmes arguments Aide Éditeurs Google Docs.
  3. Âge. En I2, . Elle donne 0 pour le premier mois, 1 pour le suivant, et ainsi de suite Support Microsoft.
  4. Une ligne par client et par mois. Copiez les colonnes C, H et I dans une nouvelle feuille en valeurs, puis lancez DonnéesSupprimer les doublons sur les trois colonnes Support Microsoft. Dans Google Sheets, passez par DonnéesNettoyage des donnéesSupprimer les doublons Aide Éditeurs Google Docs. Sans cette étape, un client qui commande deux fois dans le mois compte double.
  5. Tableau croisé dynamique. Insérez un tableau croisé sur cette feuille, avec la cohorte en Lignes, l'âge en Colonnes et l'identifiant client en Valeurs, en Nombre Support Microsoft. La colonne de l'âge 0 donne la taille de chaque cohorte. Si les dates s'affichent par jour, groupez le champ Cohorte par Mois et Années, comme dans le guide du tableau croisé dynamique par mois.
  6. Pourcentages. À côté du tableau croisé, divisez chaque cellule par la cellule de l'âge 0 de sa ligne. Si l'âge 0 est en colonne B et l'âge 1 en colonne C, la première cellule vaut , recopiée vers la droite et vers le bas.

Deux choix changent tous les chiffres : notez-les au-dessus du tableau. D'abord, la rétention au mois N signifie ici « a commandé pendant ce mois calendaire ». Un client qui saute le mois 2 et revient au mois 3 compte au mois 3 seulement. Ensuite, retirez le dernier mois si l'export s'arrête en cours de mois, sinon la dernière colonne ressemble à un effondrement. Les données du grossiste s'arrêtent le 9 décembre 2011, donc le rapport laisse décembre 2011 de côté.

Nettoyez l'export avant l'étape 1. Toujours. Les dates stockées en texte cassent , et une espace en trop dans un identifiant coupe un client en deux. Les corrections sont dans comment nettoyer des données dans Excel.

Que montre un vrai rapport de cohortes ?

Parmi les clients arrivés à partir de janvier 2010, environ un sur cinq recommande dans un mois donné, du mois 1 au mois 12. La première ligne demande une lecture à part.

Nous avons suivi ces étapes dans Glow Reports sur Online Retail II, un jeu de données public d'un commerçant britannique en ligne d'articles cadeaux, dont beaucoup de clients sont des grossistes UCI Machine Learning Repository.

Après retrait des annulations, des doublons et des lignes sans identifiant client, il compte 36 969 commandes de 5 878 clients, du 1er décembre 2009 au 9 décembre 2011. Nous avons recalculé chaque chiffre ci-dessous avec notre propre code et relancé le SQL du rapport dans Postgres : les trois concordent.

Télécharger le rapport complet (en anglais)PDF

Voici les douze cohortes aux mois 1, 3, 6 et 12 :

CohorteClientsMois 1Mois 3Mois 6Mois 12
déc. 200995535,3 %42,5 %37,7 %37,6 %
janv. 201038320,6 %30,5 %25,8 %22,2 %
févr. 201037423,8 %29,1 %19,3 %15,2 %
mars 201044319,0 %24,2 %24,6 %20,1 %
avr. 201029419,4 %16,3 %27,6 %13,9 %
mai 201025415,7 %17,3 %21,3 %15,4 %
juin 201027017,4 %20,4 %12,6 %14,8 %
juil. 201018615,6 %29,6 %11,3 %13,4 %
août 201016220,4 %32,1 %9,9 %15,4 %
sept. 201024322,6 %12,3 %13,6 %21,8 %
oct. 201037725,7 %12,5 %13,0 %19,1 %
nov. 201032517,5 %9,5 %12,9 %25,5 %

Le SQL des cohortes, avec le nom de table interne remplacé par , réduit au mois 3 :

Rétention au mois 3, 42,5 % pour la cohorte de décembre 2009
WITH orders_f AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS order_month
  FROM orders
  WHERE order_date < TIMESTAMP '2011-12-01'
),
cohorts AS (
  SELECT customer_id, MIN(order_month) AS cohort_month
  FROM orders_f
  GROUP BY customer_id
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.customer_id) AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT o.customer_id) / COUNT(DISTINCT c.customer_id), 1) AS month_3
FROM cohorts c
LEFT JOIN orders_f o
  ON o.customer_id = c.customer_id
  AND o.order_month = c.cohort_month + INTERVAL '3 months'
WHERE c.cohort_month < TIMESTAMP '2010-12-01'
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Pourquoi la première cohorte semble-t-elle si bonne ?

Parce que ce n'est pas une cohorte. Le piège est classique. Les données commencent le 1er décembre 2009 : tous les clients qui ont commandé ce mois-là tombent dans la « cohorte de décembre 2009 », y compris ceux qui achetaient depuis des années. Le premier mois d'un export contient toute votre base active, et non vos nouveaux clients.

Les chiffres le montrent bien. Les 955 clients de décembre 2009 ont passé en moyenne 15,8 commandes sur les deux ans. Les 3 311 clients de janvier à novembre 2010 en ont passé 5,2. Le texte du rapport présente décembre 2009 comme la meilleure cohorte et dit que les données n'expliquent pas son niveau. L'explication, c'est le début des données. Lisez le tableau à partir de janvier 2010 et traitez la première ligne comme votre base existante.

Le même effet de bord explique une autre ligne du rapport : tout le chiffre d'affaires de décembre 2009 compte comme « nouveaux clients », faute de mois antérieur pour qualifier quiconque de client fidèle.

20 % des clients arrivés de janvier à novembre 2010 ont recommandé le mois suivant leur première commande, et 18 % au mois 12.

Que dit encore le tableau ?

Le calendrier pèse autant que l'âge de la cohorte. La saison compte. Le grossiste vend des cadeaux, et septembre à novembre sont ses mois forts.

Les cohortes de juillet et août 2010 culminent au mois 3, qui tombe en octobre et novembre. Les cohortes de septembre à novembre descendent à 9,5 % à 12,5 % au mois 3, qui tombe dans les mois creux de décembre à février.

Une seule colonne masque donc la saison, et c'est le piège le plus courant quand on lit un tableau de cohortes pour la première fois. Une cohorte de fin d'automne paraît faible au mois 3 et forte au mois 12. Novembre 2010 est à 9,5 % au mois 3 et à 25,5 % au mois 12, deuxième de cette colonne. Comparez les cohortes au même âge et, si l'activité est saisonnière, à la même période de l'année.

Le rapport sépare aussi le chiffre d'affaires. Sur les 24 mois, les clients fidèles apportent 13 858 895 sur 16 857 596, soit 82 %. Les montants sont en livres sterling, même si l'axe du graphique du PDF affiche un symbole dollar.

Quelle place pour Glow Reports ?

La méthode du tableur fonctionne et vous apprend ce que mesure chaque chiffre. Elle prend du temps sur un gros export, et les colonnes d'aide sont à refaire à chaque nouveau fichier.

Avec Glow Reports, vous importez l'export CSV ou Excel et demandez le rapport en français courant, avec vos propres définitions : ce qui compte comme cohorte, les mois à écarter. L'IA écrit le SQL, construit les graphiques et rédige le texte. Le SQL apparaît sous chaque chiffre, pour vérifier une définition ou la modifier. Le rapport ci-dessus vient d'une seule demande de ce type, et son texte a tout de même demandé une relecture humaine sur la première cohorte. L'offre gratuite donne 300 crédits une fois, et un rapport coûte 100 crédits. Pour un abonnement, le calcul du vrai churn de MRR traite le côté revenu.

Questions fréquentes

Qu'est-ce qu'un bon taux de rétention client ?

Tout dépend de la fréquence à laquelle on a besoin de ce que vous vendez. Une boutique de cadeaux et un abonnement de café n'auront jamais le même taux. Comparez vos cohortes entre elles, au même âge et à la même saison, plutôt qu'avec une moyenne extérieure.

Quelle différence entre taux de rétention et taux de réachat ?

Le taux de rétention compte les clients qui reviennent sur une période ou un mois donné. Le taux de réachat compte les clients qui ont passé au moins deux commandes sur toutes les données. Chez le grossiste, le taux de réachat atteint 71,8 %, alors que 107 des 132 cellules mensuelles des cohortes de janvier à novembre 2010 se situent entre 10 % et 30 %.

Pourquoi la dernière colonne de mon tableau de cohortes chute-t-elle ?

L'export s'arrête sans doute en cours de mois, et ce mois ne compte que quelques jours de commandes. Retirez le mois incomplet, comme le rapport le fait pour décembre 2011.

Peut-on calculer le taux de rétention dans Excel sans tableau croisé dynamique ?

Oui. Nommez Visites la feuille dédoublonnée de l'étape 4, avec la cohorte en colonne B et l'âge en colonne C. Sur une nouvelle feuille, placez les cohortes en colonne A et les âges 0 à 12 sur la ligne 4. La formule compte alors les clients d'une cohorte à un âge donné. Recopiez-la sur la grille, puis divisez par la colonne de l'âge 0.

El Mehdi Benane
Fondateur, Glow Reports

Je suis ingénieur produit et fondateur de Glow Reports. J'ai passé plus de 6 ans à construire des produits dans la tech, les données et la cybersécurité, pour des grands groupes, des startups et des PME. Dans la plupart des organisations, j'ai vu les données traitées comme un sujet technique, alors qu'elles guident les décisions. J'ai construit l'outil de reporting que je cherchais, pour que les dirigeants des petites et moyennes structures lisent leurs chiffres sans équipe data.

Importez votre export CSV ou Excel dans Glow Reports et obtenez un rapport de rétention avec le SQL derrière chaque chiffre.

À lire aussi