Segmentation RFM dans SFMC : requêtes SQL, scores et segments

Vous avez l’historique d’achats dans Salesforce Marketing Cloud, et vos meilleurs clients reçoivent aujourd’hui le même e-mail que quelqu’un qui a acheté une seule fois il y a deux ans. La segmentation RFM dans SFMC règle ce problème, avec deux requêtes SQL et non un projet de data science.

Comment faire une segmentation RFM dans SFMC ? Avec une activité de requête SQL, vous notez chaque client sur la récence (jours depuis la dernière commande), la fréquence (nombre de commandes) et le montant (dépenses cumulées), puis vous associez ces scores à des segments nommés comme Champions ou À risque. Le résultat est écrit dans une data extension, utilisable dans Email Studio ou Journey Builder.

‍

Ce guide donne les deux requêtes, la configuration des data extensions cibles, une checklist à copier et les pièges qui faussent le plus souvent les audiences RFM. Si vous préférez ne pas écrire le code, vous pouvez aussi générer du SQL SFMC à partir de langage naturel et relire le résultat avant exécution.

‍

Ce que recouvre la segmentation RFM dans SFMC

‍

Le RFM est un modèle de scoring issu du marketing direct. Il classe les clients selon la date de leur dernier achat, la régularité de leurs achats et leurs dépenses. Le principe est simple : un client récent, fidèle et dépensier mérite un autre message qu’un client qui s’est éloigné.

‍

Les trois indicateurs

La récence est le nombre de jours depuis la dernière commande. La fréquence est le nombre de commandes sur une fenêtre fixe, par exemple les 24 derniers mois. Le montant est la somme des commandes sur cette même fenêtre.

Chaque indicateur reçoit une note de 1 à 5, 5 étant la meilleure. Les trois notes forment ensuite un segment. Vous fixez vous-même les seuils, car un horloger et une enseigne alimentaire n’ont pas du tout le même cycle d’achat.

‍

Pourquoi le SQL plutôt qu’un filtre

Les Filtered Data Extensions filtrent des lignes sur des champs qui existent déjà. Le RFM demande un comptage et une somme sur plusieurs lignes de commande par client, donc il faut d’abord agréger. Une activité de requête SQL dans Automation Studio le fait avec GROUP BY, COUNT(), SUM() et MAX().

‍

Préparer les data extensions de commandes et de clients

‍

Avant d’écrire du SQL, il vous faut quatre data extensions : une source de clients, une source de commandes et deux cibles pour les résultats.

‍

Les data extensions sources

Les exemples utilisent des noms génériques. Remplacez-les par les vôtres.

• Customers_Master : une ligne par client, avec SubscriberKey et EmailAddress.
• Orders : une ligne par commande, avec OrderID, SubscriberKey, OrderDate (date) et OrderTotal (nombre ou décimal).

Si vos commandes contiennent des remboursements, des annulations ou des commandes de test, décidez dès maintenant comment les exclure. Un RFM construit sur des données sales donne des segments assurés mais faux.

‍

Les data extensions cibles

Créez les deux cibles avant de monter les activités de requête, car la cible ne peut pas être une data extension utilisée dans la requête elle-même, comme l’indique la documentation Salesforce sur l’activité de requête SQL.

• RFM_Scores : SubscriberKey (clé primaire), EmailAddress, RecencyDays, Frequency, Monetary, RScore, FScore, MScore.
• RFM_Segments : SubscriberKey (clé primaire), EmailAddress, RScore, FScore, MScore, Segment.

Les deux utilisent Overwrite comme type de mise à jour, car les scores changent à chaque exécution et vous voulez un instantané propre, sans lignes périmées. Pour revoir les trois options, lisez Overwrite, Update ou Append dans une requête SFMC.

‍

Requête SQL de segmentation RFM, étape 1 : calculer et noter R, F et M

‍

La première requête agrège les commandes par client dans une sous-requête, puis note chaque indicateur avec CASE dans la requête externe. SFMC accepte les requêtes imbriquées et les instructions CASE, mais pas les expressions de table communes (CTE) : la sous-requête se place donc dans la clause FROM.

‍

La requête

SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.RecencyDays,
  s.Frequency,
  s.Monetary,
  CASE
    WHEN s.RecencyDays <= 30 THEN 5
    WHEN s.RecencyDays <= 90 THEN 4
    WHEN s.RecencyDays <= 180 THEN 3
    WHEN s.RecencyDays <= 365 THEN 2
    ELSE 1
  END AS RScore,
  CASE
    WHEN s.Frequency >= 10 THEN 5
    WHEN s.Frequency >= 6 THEN 4
    WHEN s.Frequency >= 3 THEN 3
    WHEN s.Frequency = 2 THEN 2
    ELSE 1
  END AS FScore,
  CASE
    WHEN s.Monetary >= 1000 THEN 5
    WHEN s.Monetary >= 500 THEN 4
    WHEN s.Monetary >= 250 THEN 3
    WHEN s.Monetary >= 100 THEN 2
    ELSE 1
  END AS MScore
FROM (
  SELECT
    c.SubscriberKey,
    c.EmailAddress,
    DATEDIFF(day, MAX(o.OrderDate), GETDATE()) AS RecencyDays,
    COUNT(o.OrderID) AS Frequency,
    SUM(o.OrderTotal) AS Monetary
  FROM Customers_Master c
  INNER JOIN Orders o ON o.SubscriberKey = c.SubscriberKey
  WHERE o.OrderDate >= DATEADD(month, -24, GETDATE())
  GROUP BY c.SubscriberKey, c.EmailAddress
) s

Data extension cible : RFM_Scores. Clé primaire : SubscriberKey. Type de mise à jour : Overwrite.

‍

Lecture ligne à ligne

• La sous-requête joint Customers_Master à Orders sur SubscriberKey et ne garde que les 24 derniers mois de commandes.
• MAX de OrderDate donne la dernière commande, et DATEDIFF la convertit en jours écoulés. C’est la récence.
• COUNT de OrderID donne la fréquence, et SUM de OrderTotal donne le montant.
• GROUP BY regroupe toutes les commandes d’un client sur une seule ligne.
• La requête externe reprend ce résultat sous l’alias s et note chaque indicateur de 1 à 5 avec CASE. Chaque CASE s’arrête à la première condition vraie : classez donc les conditions de la meilleure à la moins bonne.

Les seuils sont des exemples. Observez d’abord votre propre distribution, par exemple en lançant seule la sous-requête, puis déplacez les seuils pour que chaque note regroupe une part significative de clients. Pour en savoir plus sur DATEADD() et DATEDIFF(), voyez les fonctions de date SQL dans SFMC.

‍

Étape 2 : transformer les scores RFM en segments nommés

‍

Les scores servent à l’analyse, mais les marketeurs ont besoin d’audiences qui portent un nom. La seconde requête lit RFM_Scores, une data extension différente de sa cible, et applique un libellé de segment.

‍

La requête de segmentation

SELECT
  r.SubscriberKey,
  r.EmailAddress,
  r.RScore,
  r.FScore,
  r.MScore,
  CASE
    WHEN r.RScore >= 4 AND r.FScore >= 4 AND r.MScore >= 4 THEN 'Champions'
    WHEN r.RScore >= 3 AND r.FScore >= 3 THEN 'Loyal'
    WHEN r.RScore <= 2 AND r.FScore >= 4 THEN 'At risk'
    WHEN r.RScore >= 4 AND r.FScore <= 2 THEN 'New or promising'
    WHEN r.RScore <= 2 AND r.FScore <= 2 THEN 'Lapsed'
    ELSE 'Needs attention'
  END AS Segment
FROM RFM_Scores r

Data extension cible : RFM_Segments. Clé primaire : SubscriberKey. Type de mise à jour : Overwrite.

‍

Comment lire les segments

• Champions : récents, fréquents, gros paniers. Récompensez-les, demandez des avis, offrez un accès anticipé.
• Loyal : solides sur la récence et la fréquence. Entretenez la relation avec des contenus pertinents.
• At risk : ils ont acheté souvent, mais plus récemment. C’est votre priorité de réactivation.
• New or promising : achat récent, mais seulement une ou deux fois. Un parcours de bienvenue convient bien.
• Lapsed : récence et fréquence faibles. Tentez une seule relance, puis réduisez la pression.
• Needs attention : tous ceux qui se situent entre les deux.

L’ordre des lignes WHEN compte. Un client qui correspond à Champions n’est jamais testé sur les règles suivantes. Pour affiner ensuite le ciblage selon l’engagement, lisez la requête SQL pour cibler les contacts qui ouvrent sans cliquer.

‍

Réponse rapide : checklist de segmentation RFM

‍

Copiez cette liste lors de la première mise en place du modèle.

1. Créez Customers_Master et Orders, ou vérifiez qu’elles existent avec SubscriberKey des deux côtés.
2. Créez RFM_Scores et RFM_Segments avec SubscriberKey comme clé primaire.
3. Montez l’activité de requête 1 avec la requête de scoring, cible RFM_Scores, Overwrite.
4. Montez l’activité de requête 2 avec la requête de segments, cible RFM_Segments, Overwrite.
5. Placez les deux dans une même automation, dans cet ordre, et planifiez-la.
6. Utilisez RFM_Segments comme source d’entrée ou comme audience d’envoi.

‍

Les pièges qui faussent les audiences RFM

‍

La plupart des problèmes viennent des données, pas du SQL. Voici ce qu’il faut vérifier avant le premier envoi.

‍

Données et logique

• Les clients sans commande disparaissent à cause de l’INNER JOIN. Utilisez un LEFT JOIN si vous voulez un segment « jamais acheté ».
• Les retours et les commandes annulées gonflent la fréquence et le montant si vous ne les filtrez pas dans la clause WHERE.
• Avec plusieurs devises, les notes de montant n’ont plus de sens. Convertissez d’abord dans une seule devise.
• SubscriberKey et adresse e-mail ne sont pas la même chose. Joignez sur la clé que portent réellement vos commandes.

‍

Performance et sortie

Les requêtes SFMC ont un délai maximal de 30 minutes : limitez la fenêtre de commandes au lieu de parcourir tout l’historique. Si une exécution échoue malgré tout, lisez timeout de requête SQL SFMC : causes et solutions.

Salesforce précise aussi que les champs décimaux utilisés dans une requête ne renvoient pas de sortie décimale. Testez donc la colonne Monetary et envisagez de travailler en unités monétaires entières. Consultez la référence SQL pour la liste à jour des limites.

‍

Questions fréquentes

‍

Comment faire une segmentation RFM dans SFMC ?

Agrégez les commandes par client avec une activité de requête SQL pour obtenir récence, fréquence et montant. Notez chacun de 1 à 5 avec CASE, écrivez les scores dans une data extension, puis lancez une seconde requête qui associe les combinaisons de notes à des segments nommés.

‍

Peut-on utiliser NTILE pour noter le RFM en SQL SFMC ?

La référence SQL de Salesforce ne mentionne pas NTILE et précise que la prise en charge du SQL ne correspond pas exactement à SQL Server. Des seuils fixes avec CASE restent le choix sûr. Pour un scoring par percentiles, testez avec Validate Syntax avant de vous y fier.

‍

À quelle fréquence rafraîchir les scores RFM ?

Alignez-vous sur votre cycle d’achat. Une fois par semaine convient à la plupart des e-commerçants, une fois par jour aux boutiques à fort volume. Comme les deux requêtes utilisent Overwrite, chaque exécution remplace l’instantané précédent.

‍

Une filtered data extension peut-elle faire du RFM ?

Pas seule. Une filtered data extension filtre des lignes existantes et ne compte ni ne somme plusieurs lignes de commande par client. C’est pourquoi l’agrégation doit d’abord passer par le SQL.

‍

Créez cette audience en quelques secondes avec QAiry

‍

Écrire et déboguer des requêtes RFM est un bon exercice, mais c’est lent quand les marketeurs réclament un nouveau segment chaque semaine. QAiry transforme une demande en langage naturel en SQL SFMC prêt pour la production et en data extension dans votre propre compte, et vous relisez la requête avant son exécution.

Vous pouvez essayer QAiry gratuitement ou regarder une démo produit pour voir comment une demande comme clients ayant acheté trois fois dans l’année mais pas depuis 60 jours devient une audience.