Segmentation RFM

🏗️Exemple concret

Sur 50 000 clients : R = nombre de jours depuis le dernier achat (noté de 1 à 5), F = nombre de commandes sur 12 mois (1 à 5), M = CA total sur 12 mois (1 à 5). Segment « Champions » (5-5-5) = 3 200 clients qui génèrent 42% du CA total.

WITH rfm AS (
  SELECT
    customer_id,
    DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency,
    COUNT(DISTINCT order_id)                AS frequency,
    SUM(amount)                             AS monetary
  FROM orders
  WHERE order_date >= DATEADD('year', -2, CURRENT_DATE)
  GROUP BY customer_id
),
scored AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency)    AS f_score,
    NTILE(5) OVER (ORDER BY monetary)     AS m_score
  FROM rfm
)
SELECT *,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 THEN 'Champion'
    WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'Nouveau client'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'Client en risque'
    ELSE 'Inactif'
  END AS segment
FROM scored

∑ Concept clé

Score RFM = (Score_R w_R) + (Score_F w_F) + (Score_M w_M). Souvent on note R, F, M de 1 à 5 indépendamment puis on combine.

🎯Quand l'utiliser ?

🛠️ Outils principaux