RFM Analysis: Segment Customers by Behavior

WITH customer_rfm AS (
  SELECT
    customer_id,
    DATEDIFF('day', MAX(order_date), CURRENT_DATE) AS recency_days,
    COUNT(order_id) AS frequency,
    SUM(order_value) AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
  GROUP BY customer_id
)
SELECT
  customer_id,
  recency_days,
  frequency,
  monetary,
  NTILE(5) OVER (ORDER BY recency_days ASC) AS R_score,  -- Lower days = higher score
  NTILE(5) OVER (ORDER BY frequency DESC) AS F_score,
  NTILE(5) OVER (ORDER BY monetary DESC) AS M_score
FROM customer_rfm;

orders (order_id, customer_id, order_date, order_value)

WITH customer_metrics AS (
  SELECT
    customer_id,
    MAX(order_date) AS last_order_date,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_value) AS monetary_value
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'  -- 12-month window
  GROUP BY customer_id
),
rfm_calc AS (
  SELECT
    customer_id,
    DATEDIFF('day', last_order_date, CURRENT_DATE) AS recency_days,
    frequency,
    monetary_value
  FROM customer_metrics
)
SELECT * FROM rfm_calc;

WITH customer_metrics AS (
  SELECT
    customer_id,
    DATEDIFF('day', MAX(order_date), CURRENT_DATE) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_value) AS monetary_value
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary_value,
    NTILE(5) OVER (ORDER BY recency_days ASC) AS R_score,     -- Lower recency = better
    NTILE(5) OVER (ORDER BY frequency DESC) AS F_score,       -- Higher frequency = better
    NTILE(5) OVER (ORDER BY monetary_value DESC) AS M_score   -- Higher spend = better
  FROM customer_metrics
)
SELECT
  customer_id,
  recency_days,
  frequency,
  monetary_value,
  R_score,
  F_score,
  M_score,
  CONCAT(R_score, '-', F_score, '-', M_score) AS RFM_segment
FROM rfm_scores;

WITH rfm_scores AS (
  -- (Previous RFM scoring query)
  ...
),
rfm_segments AS (
  SELECT
    customer_id,
    R_score,
    F_score,
    M_score,
    CASE
      -- Champions (best on all dimensions)
      WHEN R_score >= 4 AND F_score >= 4 AND M_score >= 4 THEN 'Champions'

      -- Loyal (high frequency + monetary, any recency)
      WHEN F_score >= 4 AND M_score >= 4 THEN 'Loyal Customers'

      -- Potential Loyalists (recent + high spend, but low frequency)
      WHEN R_score >= 4 AND M_score >= 3 AND F_score <= 2 THEN 'Potential Loyalists'

      -- New Customers (very recent, low frequency)
      WHEN R_score >= 4 AND F_score <= 2 THEN 'New Customers'

      -- At Risk (low recency, but previously valuable)
      WHEN R_score <= 2 AND F_score >= 3 AND M_score >= 3 THEN 'At Risk'

      -- Hibernating (low recency + low frequency, but spent money)
      WHEN R_score <= 2 AND F_score <= 2 AND M_score >= 3 THEN 'Hibernating'

      -- Lost (low on all dimensions)
      WHEN R_score <= 2 AND F_score <= 2 AND M_score <= 2 THEN 'Lost'

      -- About to Sleep (recent, but low frequency + low spend)
      WHEN R_score >= 3 AND F_score <= 2 AND M_score <= 2 THEN 'About to Sleep'

      -- Need Attention (below average, not lost yet)
      ELSE 'Need Attention'
    END AS segment
  FROM rfm_scores
)
SELECT
  segment,
  COUNT(*) AS customer_count,
  ROUND(AVG(R_score), 1) AS avg_R,
  ROUND(AVG(F_score), 1) AS avg_F,
  ROUND(AVG(M_score), 1) AS avg_M
FROM rfm_segments
GROUP BY segment
ORDER BY customer_count DESC;

WITH rfm_scores AS (
  -- (RFM calculation)
  ...
),
segmented AS (
  SELECT
    r.customer_id,
    r.segment,
    u.city_tier,
    u.age_group,
    r.R_score,
    r.F_score,
    r.M_score
  FROM rfm_scores r
  JOIN users u ON r.customer_id = u.user_id
  WHERE r.segment = 'Champions'
)
SELECT
  city_tier,
  COUNT(*) AS champions_count,
  AVG(monetary_value) AS avg_spend
FROM segmented
GROUP BY city_tier;

WITH rfm_jan AS (
  SELECT customer_id, segment AS segment_jan
  FROM rfm_analysis
  WHERE analysis_date = '2026-01-31'
),
rfm_feb AS (
  SELECT customer_id, segment AS segment_feb
  FROM rfm_analysis
  WHERE analysis_date = '2026-02-28'
)
SELECT
  r_jan.segment_jan AS from_segment,
  r_feb.segment_feb AS to_segment,
  COUNT(*) AS customer_count
FROM rfm_jan r_jan
JOIN rfm_feb r_feb ON r_jan.customer_id = r_feb.customer_id
WHERE r_jan.segment_jan != r_feb.segment_feb  -- Only migrations
GROUP BY from_segment, to_segment
ORDER BY customer_count DESC;