Vous avez un export de commandes : 8 000 lignes, 1 200 clients, trois colonnes utiles qui, quand, combien.
Quelque part là-dedans se cachent vos 50 meilleurs clients, ceux qu’il faut chouchouter. Et vos 200 clients dormants, ceux qui achetaient il y a six mois et qui ont disparu sans bruit. Le problème : rien dans ce fichier ne vous dit lesquels.
C’est exactement ce que résout le scoring RFM : la méthode de segmentation la plus utilisée en marketing depuis quarante ans. Elle est simple sur le papier. Elle est étonnamment pénible à monter dans Excel.
Voici pourquoi, et comment la ramener à trois formules.
Ce qu’est le score RFM
RFM note chaque client sur trois dimensions, de 1 à 5 :
- R – Récence : depuis combien de temps a-t-il acheté ? Plus c’est récent, mieux c’est.
- F – Fréquence : combien de fois a-t-il commandé ? Plus c’est souvent, mieux c’est.
- M – Montant : combien a-t-il dépensé au total ? Plus c’est élevé, mieux c’est.
On concatène les trois notes en un score à trois chiffres. 555 est votre client idéal : il a acheté récemment, souvent, et beaucoup. 111 est un client perdu.
La force de la méthode tient à un détail : les notes sont relatives. Un client n’est pas noté 5 parce qu’il a dépensé plus de 1 000 € dans l’absolu, mais parce qu’il fait partie des 20 % qui dépensent le plus dans votre base. Le scoring s’adapte donc à votre activité, sans seuil arbitraire à définir.
C’est aussi ce qui le rend pénible à calculer à la main.
La méthode native : six colonnes et une table de correspondance
Partons d’un export de transactions : colonne A l’ID client, colonne B la date, colonne C le montant. Et, à côté, la liste unique de vos clients.
Étape 1 – Calculer les trois valeurs brutes
Récence – le nombre de jours depuis le dernier achat :
=AUJOURDHUI() - MAX.SI.ENS(Trans!B:B; Trans!A:A; A2)
Fréquence – le nombre de commandes :
=NB.SI(Trans!A:A; A2)
Montant – le total dépensé :
=SOMME.SI(Trans!A:A; A2; Trans!C:C)
Trois colonnes. Jusqu’ici, ça va. Notez au passage que MAX.SI.ENS n’existe qu’à partir d’Excel 2019, sur 2016, il faut une formule matricielle validée en Ctrl+Maj+Entrée.
Étape 2 – Déterminer les seuils de quintiles
Pour noter de 1 à 5, il faut découper chaque dimension en cinq tranches égales. Donc calculer quatre seuils, pour chacune des trois dimensions : douze seuils :
=CENTILE.INCLURE($D$2:$D$1200; 0,2)
=CENTILE.INCLURE($D$2:$D$1200; 0,4)
=CENTILE.INCLURE($D$2:$D$1200; 0,6)
=CENTILE.INCLURE($D$2:$D$1200; 0,8)
Et ainsi de suite pour la fréquence et le montant.
Étape 3 – Convertir chaque valeur en note
=SI(D2<=$K$1;5;SI(D2<=$K$2;4;SI(D2<=$K$3;3;SI(D2<=$K$4;2;1))))
Attention au piège : pour la récence, l’échelle est inversée peu de jours écoulés = bonne note. Pour la fréquence et le montant, c’est l’inverse. Trois formules différentes, une erreur de sens et tout le scoring est faux sans que rien ne le signale.
Étape 4 – Concaténer le score
=G2 & H2 & I2
→ "532"
Étape 5 – Traduire le score en segment
C’est l’étape que personne n’anticipe. 532 ne veut rien dire pour une équipe marketing. Il faut le traduire en « Potentiel fidèle », « À risque », « Champions »…
Avec 5 × 5 × 5, vous avez 125 combinaisons possibles à répartir dans une dizaine de segments. Deux options, aucune plaisante :
- une table de correspondance de 125 lignes à construire et maintenir à la main, puis un
RECHERCHEVdessus ; - une formule
SIimbriquée monstrueuse qui teste des dizaines de cas.
=RECHERCHEV(J2; Table_Segments!$A$2:$B$126; 2; FAUX)
Le bilan
Six colonnes de calcul, douze seuils, trois formules de notation différentes, une table de 125 lignes. Le montage fonctionne mais il est long à bâtir, fragile quand la base grandit, et à peu près impossible à faire relire.
Surtout : les seuils sont figés. Ajoutez trois mois de commandes et vos quintiles ne valent plus rien. Il faut tout recalculer.
La solution formula-based : trois formules
HexFormula couvre la chaîne complète avec trois fonctions qui se répondent.
1. Le score =PF.SCORE_RFM()
=PF.SCORE_RFM(id_client; plage_clients; plage_dates; plage_montants; [echelle])
La fonction parcourt toutes les transactions, calcule elle-même les percentiles sur les trois dimensions, et renvoie le score à trois chiffres :
=PF.SCORE_RFM(A2; Trans!$B:$B; Trans!$C:$C; Trans!$D:$D; 5)
→ 555
Le paramètre echelle accepte 5 (par défaut, notes de 1 à 5) ou 3 pour une segmentation plus grossière en 27 combinaisons : utile sur une base de petite taille.
Les seuils étant recalculés à chaque évaluation, l’ajout de nouvelles commandes ne casse rien.
2. Le segment =PF.SEGMENT_CLIENT()
=PF.SEGMENT_CLIENT(score_rfm; [format])
Elle remplace la table de 125 lignes :
=PF.SEGMENT_CLIENT(B2; "SIMPLE")
→ "Champions"
Le format "DETAILLE" renvoie le nom accompagné d’une description exploitable directement dans un rapport.
3. La valeur =PF.CLV()
=PF.CLV(id_client; plage_clients; plage_dates; plage_montants; [periode_mois])
La Customer Lifetime Value complète le tableau : le score dit qui est bon client, la CLV dit combien il vaut.
=PF.CLV(A2; Trans!$B:$B; Trans!$C:$C; Trans!$D:$D; 12)
→ 285
La chaîne complète
Trois colonnes, et votre base est segmentée :
| Colonne | Formule | Résultat |
|---|---|---|
| Score | =PF.SCORE_RFM(A2; ...) |
532 |
| Segment | =PF.SEGMENT_CLIENT(B2) |
Potentiel fidèle |
| Valeur 12 mois | =PF.CLV(A2; ...) |
285 € |
Les segments et les actions associées
PF.SEGMENT_CLIENT répartit les scores en onze segments. Les six qui déclenchent le plus d’actions concrètes :
| Segment | Profil | Action recommandée | Part attendue |
|---|---|---|---|
| Champions | Récents, fréquents, gros paniers | Programme VIP, accès anticipé | 5-10 % |
| Fidèles | Réguliers et fiables | Programme de fidélité, cross-sell | 10-20 % |
| Nouveaux clients | Premier achat récent | Série de bienvenue, guide produits | 5-15 % |
| À risque | N’ont pas acheté récemment | Code promo, enquête de départ | 10-15 % |
| Ne peuvent pas perdre | Gros clients historiques qui s’éloignent | Appel senior, offre VIP : urgent | 2-5 % |
| Perdus | Inactifs de longue date | Dernière relance, puis désabonnement | 15-25 % |
Les pourcentages attendus servent de contrôle de cohérence : si 60 % de votre base ressort en « Champions », vos données de transactions sont probablement incomplètes.
Trois usages concrets
Réactiver les dormants à forte valeur
Les clients dont la récence est faible mais la fréquence historique élevée sont les plus rentables à relancer : ils ont déjà prouvé qu’ils achètent.
=SI(ET(GAUCHE(B2;1)<=2; STXT(B2;2;1)>=4); "Email réactivation"; "")
On isole ici le premier chiffre (récence) et le deuxième (fréquence) du score.
Prioriser les appels commerciaux
500 leads, le temps d’en appeler 100. Ciblez les gros montants dont la récence commence à glisser : le signal d’un client important en train de partir :
=SI(ET(DROITE(B2;1)>=4; GAUCHE(B2;1)=3); "URGENT"; "")
Fixer le budget d’acquisition
La CLV donne le plafond de ce que vous pouvez dépenser pour acquérir un client. Avec une CLV moyenne de 285 € sur 12 mois et une marge de 40 %, le profit par client est de 114 €. En visant un ratio CLV/CAC de 3, votre coût d’acquisition maximum est de 38 € : le chiffre à ne pas dépasser sur Google Ads.
Comparatif des approches
| Critère | Méthode native | PF.SCORE_RFM + PF.SEGMENT_CLIENT |
|---|---|---|
| Colonnes de calcul | ❌ 6 et plus | ✅ 2 |
| Seuils de quintiles à gérer | ❌ 12, en dur | ✅ Aucun (recalculés) |
| Table de correspondance | ❌ 125 lignes à maintenir | ✅ Intégrée |
| Robuste à l’ajout de données | ❌ Seuils à refaire | ✅ Oui |
| Risque d’erreur silencieuse | ❌ Élevé (sens de la récence) | ✅ Faible |
| Sans add-in | ✅ Oui | ❌ Requiert HexFormula |
| Transparence du calcul | ✅ Chaque étape visible | ⚠️ Fonction « boîte » |
Le montage natif garde un mérite : il oblige à comprendre ce que l’on calcule. Pour un scoring ponctuel ou pédagogique, il fait le travail. Pour un fichier repris chaque mois par une équipe, la version en trois formules évite l’accumulation de colonnes fragiles.
FAQ
Combien de transactions faut-il pour un scoring RFM fiable ?
Comptez au minimum 300 à 500 clients avec plusieurs commandes chacun. En dessous, les quintiles deviennent instables : quelques clients suffisent à déplacer les seuils, et la segmentation change d’un mois à l’autre sans que le comportement réel ait bougé. Sur une petite base, l’échelle 3 (27 combinaisons) donne des résultats plus stables que l’échelle 5.
Sur quelle profondeur d’historique calculer le score ?
Douze à vingt-quatre mois selon votre cycle d’achat. Trop court, la fréquence perd son sens ; trop long, des clients partis depuis trois ans polluent les seuils. Pour un achat très récurrent (consommables), douze mois suffisent ; pour un cycle long (équipement), allongez.
Quelle différence entre le score RFM et la CLV ?
Le score RFM est un classement relatif : il situe un client par rapport aux autres, sur trois axes de comportement. La CLV est un montant absolu : ce que le client a rapporté ou rapportera. Les deux se complètent et le score priorise l’action, la CLV chiffre l’enjeu.
À quelle fréquence recalculer les scores ?
Mensuellement pour un e-commerce, trimestriellement en B2B. L’intérêt n’est pas tant le score à l’instant T que son évolution : un client qui passe de 544 à 344 est en train de décrocher. C’est ce mouvement qui doit déclencher une action, pas la valeur seule.
Peut-on utiliser ces scores dans un CRM ?
Oui. La colonne segment s’exporte en CSV et s’importe comme champ personnalisé dans la plupart des CRM et outils d’emailing. C’est l’usage le plus courant : calculer dans Excel, piloter les campagnes dans l’outil.
Conclusion
Le scoring RFM est une méthode simple qui souffre d’une mise en œuvre compliquée. Entre les quintiles à recalculer, l’échelle inversée de la récence et la table de 125 combinaisons, le montage natif accumule les points de rupture et devient faux en silence dès que la base évolue.
Pour comprendre la mécanique, montez-le une fois à la main. Pour l’exploiter mois après mois, =PF.SCORE_RFM(), =PF.SEGMENT_CLIENT() et =PF.CLV() ramènent la chaîne complète à trois colonnes qui se recalculent seules.
→ Essayez HexFormula gratuitement sur hexformula.com et segmentez votre base clients cet après-midi.