Customer lifetime value : calculer la CLV à partir d’un export de commandes
Calculer la customer lifetime value dans Excel à partir d’un export de commandes, et pourquoi la formule habituelle donnait plus du triple de la valeur réelle.
- E-commerce
- Rétention
- Excel

Sur cette page
- Qu'est-ce que la customer lifetime value ?
- Comment calculer la customer lifetime value ?
- Comment calculer la CLV dans Excel à partir d'un export de commandes ?
- Que montre un vrai export de commandes ?
- Pourquoi la formule donne-t-elle une CLV plus élevée que l'export ?
- Pourquoi la CLV moyenne est-elle trompeuse ?
- Comment utiliser la CLV avec le coût d'acquisition client ?
- Où se situe Glow Reports ?
- Questions fréquentes
La customer lifetime value (CLV), ou valeur vie client, est le chiffre d'affaires, ou mieux la marge, qu'un client vous apporte sur toute la relation. Elle indique combien vous pouvez dépenser pour gagner un client tout en restant rentable. La plupart des guides donnent la formule, panier moyen fois fréquence d'achat fois durée de vie, et s'arrêtent là. Mettez cette formule à côté d'une mesure tirée de l'export de commandes, et l'une des réponses peut dépasser le triple de l'autre. Voici comment calculer les deux dans Excel à partir d'un export, ce qu'elles donnent sur les données réelles d'un commerçant, et laquelle croire pour quelle décision.
Qu'est-ce que la customer lifetime value ?
La customer lifetime value est le total qu'un client dépense chez vous de sa première à sa dernière commande. On la mesure de deux façons : par l'historique, en additionnant ce que les clients ont déjà dépensé sur une période donnée, ou par une formule qui multiplie panier moyen, fréquence d'achat et durée de vie attendue.
Shopify présente la même formule de base : valeur moyenne de commande × fréquence d'achat × durée de vie moyenne du client Shopify. L'historique est un fait. La formule est une estimation fondée sur des hypothèses, et ce sont les hypothèses qui font le résultat.
La CLV en chiffre d'affaires se tire facilement d'un export. La CLV en marge est celle qui sert. Un client qui dépense 1 000 € avec 30 % de marge brute vous laisse 300 € pour payer son acquisition et vos frais. Multipliez toute CLV en chiffre d'affaires par votre taux de marge brute avant de la comparer au coût d'acquisition.
Comment calculer la customer lifetime value ?
Multipliez le panier moyen par le nombre de commandes qu'un client passe par an, puis par le nombre d'années pendant lesquelles il reste client. Avec des chiffres annuels : CLV = panier moyen × fréquence d'achat annuelle × durée de vie en années.
Chaque élément vient de l'export :
- Le panier moyen est le chiffre d'affaires divisé par le nombre de commandes. Comptez les commandes, pas les lignes de produits. Calculer le panier moyen traite les exports à une ligne par produit.
- La fréquence d'achat est le nombre de commandes de l'année divisé par le nombre de clients qui ont commandé cette année-là.
- La durée de vie est la plus difficile. Sans plusieurs années d'historique, estimez-la en divisant 1 par le taux d'attrition annuel HubSpot. Si 36,4 % des clients de l'an dernier ne sont pas revenus, la durée de vie vaut 1 / 0,364, soit 2,75 ans.
Sur les données du commerçant présentées plus bas, l'année de décembre 2009 à novembre 2010 donne ces valeurs :
| Élément | Valeur |
|---|---|
| Clients ayant commandé | 4 266 |
| Panier moyen | 460,36 £ |
| Commandes par client sur l'année | 4,32 |
| Chiffre d'affaires par client sur l'année | 1 989,59 £ |
| Clients qui commandent encore l'année suivante | 63,6 % |
| Durée de vie, 1 / (1 - 63,6 %) | 2,75 ans |
| Customer lifetime value (formule) | 5 465 £ |
Les montants sont en livres sterling. Un abonnement suit la même logique au mois : revenu mensuel moyen par compte multiplié par la marge brute, divisé par le taux d'attrition mensuel Wikipedia.
Comment calculer la CLV dans Excel à partir d'un export de commandes ?
Construisez une ligne par client avec la date de sa première commande et le chiffre d'affaires des 12 mois suivants. Sa moyenne donne une CLV historique à 12 mois. Il faut trois fonctions : MIN.SI.ENS pour la première commande, MOIS.DECALER pour la date 12 mois plus tard, et SOMME.SI.ENS pour le chiffre d'affaires entre les deux.
Pas à pas, avec l'export mis sous forme de tableau nommé Commandes, avec les colonnes Facture, Date, Client et CA :
- Ajoutez une colonne CA si l'export ne donne que la quantité et le prix unitaire : . Retirez d'abord les commandes annulées et les lignes sans identifiant client.
- Sur une nouvelle feuille, listez chaque client une fois en A2 avec Microsoft Support.
- En B2, la date de première commande : . MIN.SI.ENS renvoie la plus petite valeur qui remplit les critères Microsoft Support.
- En C2, le chiffre d'affaires des 12 premiers mois : . MOIS.DECALER renvoie la date située un nombre de mois plus tard Microsoft Support, et SOMME.SI.ENS additionne les lignes qui remplissent toutes les conditions Microsoft Support.
- Ne gardez que les clients que vous pouvez suivre 12 mois complets, et écartez le premier mois de l'export. Les clients de ce premier mois achetaient déjà avant le début de l'export : ils ne sont pas nouveaux. Marquez les autres en D2 avec .
- La CLV à 12 mois vaut . Ajoutez la médiane à côté avec .
Sur un gros export, SOMME.SI.ENS sur chaque client devient lent. Les mêmes étapes en SQL, pour les clients dont la première commande tombe entre janvier et novembre 2010 dans les données ci-dessous :
WITH first_orders AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
GROUP BY customer_id
),
first_year AS (
SELECT f.customer_id, SUM(o.revenue) AS revenue_12m
FROM first_orders f
JOIN orders o
ON o.customer_id = f.customer_id
AND o.order_date < f.first_date + INTERVAL '12 months'
WHERE f.first_date >= TIMESTAMP '2010-01-01'
AND f.first_date < TIMESTAMP '2010-12-01'
GROUP BY f.customer_id
)
SELECT COUNT(*) AS customers,
ROUND(AVG(revenue_12m), 2) AS clv_12m
FROM first_year;Que montre un vrai export de commandes ?
Les nouveaux clients d'un commerçant britannique en ligne d'articles cadeaux ont dépensé 1 521 £ en moyenne sur leurs 12 premiers mois. Plus d'un quart de cette somme est arrivé avec la première commande. Les données viennent d'Online Retail II, un jeu de données public dont beaucoup de clients sont des grossistes UCI Machine Learning Repository.
Après retrait des annulations, des lignes en double, des lignes sans identifiant client et des lignes à quantité ou prix nul ou négatif, il reste 36 969 commandes de 5 878 clients, du 1er décembre 2009 au 9 décembre 2011. Ce sont les mêmes commandes que dans notre rapport de rétention par cohorte. Nous avons calculé chaque chiffre avec notre propre code, puis vérifié la CLV à 12 mois avec le SQL ci-dessus.
Les 3 311 clients dont la première commande tombe entre janvier et novembre 2010 peuvent tous être suivis 12 mois :
| Délai après la première commande | Chiffre d'affaires moyen par client |
|---|---|
| Première commande | 418 £ |
| 3 mois | 705 £ |
| 6 mois | 994 £ |
| 12 mois | 1 521 £ |
1 521 £, c'est la CLV à 12 mois d'un nouveau client. La formule plus haut donne 5 465 £.
Pourquoi la formule donne-t-elle une CLV plus élevée que l'export ?
Parce que ses données d'entrée viennent de toute votre base active, et que vos meilleurs clients y pèsent lourd. La formule plus haut utilise tous les clients ayant commandé entre décembre 2009 et novembre 2010. Plus d'un cinquième d'entre eux, 955, achetaient déjà au début des données, et des grossistes fidèles restent plus longtemps et achètent plus que les nouveaux.
Les données le montrent. Parmi les 955 clients ayant commandé en décembre 2009, 76,0 % ont recommandé entre décembre 2010 et novembre 2011. Parmi les 3 311 nouveaux clients de 2010, 60,0 %. Sur les deux années complètes, les premiers ont dépensé 8 917 £ chacun, les seconds 2 142 £.
La formule suppose aussi la même rétention chaque année, indéfiniment. En réalité, la rétention est au plus bas tout de suite après la première commande et plus élevée chez les clients qui restent. Nourrie de moyennes sur toute la base, la formule décrit un client existant typique, pas le nouveau client que vous payez pour acquérir.
Recalculée sur les seuls nouveaux clients, l'estimation baisse : 1 521 £ la première année, divisés par 1 - 60,0 %, donnent 3 804 £. Cela reste une estimation. Pour décider de votre budget d'acquisition, servez-vous de la CLV historique à 12 mois, car c'est un fait vérifiable. Traitez le résultat de la formule comme un plafond, et calculez ses éléments sur les nouveaux clients.
Pourquoi la CLV moyenne est-elle trompeuse ?
Parce que quelques clients la portent. Parmi les 3 311 nouveaux clients de 2010, le chiffre d'affaires médian de la première année est de 727 £, moins de la moitié de la moyenne de 1 521 £. Les 10 % de meilleurs clients apportent 48 % du chiffre d'affaires de la première année, et le plus gros a dépensé 208 296 £ à lui seul.
À l'autre bout, 28,7 % de ces clients n'ont commandé qu'une fois la première année. Une moyenne calculée sur les deux groupes ne décrit presque personne. Affichez la médiane à côté de la moyenne, et découpez la CLV par mois de première commande, canal d'acquisition ou premier produit acheté.
La rétention dit quels segments reviennent. Le guide du rapport d'attrition montre comment mesurer ceux qui partent.
Comment utiliser la CLV avec le coût d'acquisition client ?
Comparez les deux sur la même base : la marge, pas le chiffre d'affaires, et la même période. Si un client apporte 1 521 £ de chiffre d'affaires en 12 mois avec 30 % de marge, il reste 456 £ pour couvrir son acquisition. Au-delà de ce coût par nouveau client, la première année perd de l'argent.
Le ratio LTV/CAC compare la valeur vie client au coût d'acquisition. Calculez-le par canal d'acquisition, car un bon ratio global peut masquer un canal qui amène des clients peu rentables. Les données ci-dessus n'ont ni coûts ni canaux, leurs chiffres restent donc en chiffre d'affaires. Avec vos propres données, ajoutez le canal à chaque ligne client et calculez la CLV à 12 mois par canal.
Où se situe Glow Reports ?
Les étapes Excel ci-dessus fonctionnent sur un export. Elles ralentissent sur des centaines de milliers de lignes, et il faut les reconstruire chaque fois qu'on demande la CLV par mois de première commande ou par pays.
Avec Glow Reports, vous importez l'export de commandes en CSV ou Excel et vous demandez la CLV à 12 mois des nouveaux clients par mois de première commande, avec la médiane à côté de la moyenne. L'IA écrit le SQL, construit les graphiques et rédige le texte. Le SQL apparaît sous chaque chiffre : vous vérifiez quels clients comptent comme nouveaux et quelle période s'applique, et vous pouvez modifier la requête. Les rapports s'exportent en PDF et restent dans l'espace de travail. L'offre gratuite donne 300 crédits une fois, et un rapport coûte 100 crédits.
Questions fréquentes
Quelle différence entre CLV et LTV ?
Aucune. Customer lifetime value (CLV) et lifetime value (LTV) désignent la même mesure. LTV est plus courant dans le SaaS et dans le ratio LTV/CAC, CLV dans l'e-commerce et le marketing.
Qu'est-ce qu'une bonne customer lifetime value ?
Une CLV nettement supérieure à ce que vous payez pour acquérir un client, une fois les deux exprimés en marge. Seul, le chiffre dit peu de chose. Comparez-le au coût d'acquisition par canal, et au même chiffre un an plus tôt.
La CLV doit-elle se calculer sur le chiffre d'affaires ou sur la marge ?
Sur la marge, pour toute décision de dépense. La CLV en chiffre d'affaires se calcule plus facilement depuis un export : commencez par elle, puis multipliez par votre taux de marge brute. La formule pour les abonnements citée sur Wikipedia multiplie le revenu par la marge brute avant de diviser par l'attrition.
Sur quelle période calculer la CLV ?
Choisissez une période observable, comme 12 ou 24 mois après la première commande, et n'incluez que les clients que vous pouvez suivre sur toute la période. Une période courte est un fait. Une valeur à vie reste toujours une estimation.
Comment calculer la CLV d'une activité par abonnement ?
Prenez le revenu mensuel moyen par compte, multipliez-le par la marge brute et divisez par le taux d'attrition mensuel. Une attrition de 5 % par mois correspond à une durée de vie moyenne de 20 mois. La rétention nette des revenus montre comment l'expansion et la contraction changent le calcul.
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.
Plus d'articles de El Mehdi BenaneImportez votre export de commandes dans Glow Reports et demandez la CLV à 12 mois des nouveaux clients, avec le SQL derrière chaque chiffre.
À lire aussi
Net revenue retention (NRR) : la formule et le calcul dans Excel, avec la GRREl Mehdi Benane, 4 min de lecture
Calculer le panier moyen à partir d'un export de commandesEl Mehdi Benane, 4 min de lecture
Taux de rétention client : la formule et le calcul par cohorte depuis un export de commandesEl Mehdi Benane, 5 min de lecture