Rfm customer segmentation

RFM analysis scores every customer on three dimensions: Recency (how recently they ordered), Frequency (how often), and Monetary (how much they spent). Each dimension gets a 1-5 score using NTILE, and the combination classifies customers into segments like Champions, Loyal, At Risk, and Lost.

Rfm scoring: three dimensions

rfm_analysis.sql
sql
-- Complete RFM analysis
WITH rfm_base AS (
  SELECT
    o.customer_id,
    MAX(o.order_purchase_timestamp) AS last_order,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(p.payment_value) AS total_spent
  FROM orders o
  JOIN order_payments p ON o.order_id = p.order_id
  GROUP BY o.customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    last_order,
    order_count,
    total_spent,
    NTILE(5) OVER (ORDER BY last_order DESC) AS recency_score,
    NTILE(5) OVER (ORDER BY order_count ASC) AS frequency_score,
    NTILE(5) OVER (ORDER BY total_spent ASC) AS monetary_score
  FROM rfm_base
)
SELECT
  customer_id,
  recency_score,
  frequency_score,
  monetary_score,
  CASE
    WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 THEN 'Champion'
    WHEN recency_score >= 3 AND frequency_score >= 3 THEN 'Loyal'
    WHEN recency_score >= 4 AND frequency_score <= 2 THEN 'New Customer'
    WHEN recency_score <= 2 AND frequency_score >= 3 THEN 'At Risk'
    WHEN recency_score <= 2 AND frequency_score <= 2 THEN 'Lost'
    ELSE 'Regular'
  END AS segment
FROM rfm_scores
LIMIT 20;

Three-CTE pipeline: calculate raw metrics, score with NTILE(5), classify with CASE. NTILE adapts to your data distribution automatically.

With segments defined, let us check the distribution to see how many customers fall into each category. This tells the business where to focus their efforts.

rfm_distribution.sql
sql
-- Segment distribution: how many customers in each segment?
WITH rfm_base AS (
  SELECT o.customer_id,
    MAX(o.order_purchase_timestamp) AS last_order,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(p.payment_value) AS total_spent
  FROM orders o
  JOIN order_payments p ON o.order_id = p.order_id
  GROUP BY o.customer_id
),
rfm_scores AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY last_order DESC) AS r,
    NTILE(5) OVER (ORDER BY order_count ASC) AS f,
    NTILE(5) OVER (ORDER BY total_spent ASC) AS m
  FROM rfm_base
),
segmented AS (
  SELECT *,
    CASE
      WHEN r >= 4 AND f >= 4 AND m >= 4 THEN 'Champion'
      WHEN r >= 3 AND f >= 3 THEN 'Loyal'
      WHEN r >= 4 AND f <= 2 THEN 'New Customer'
      WHEN r <= 2 AND f >= 3 THEN 'At Risk'
      WHEN r <= 2 AND f <= 2 THEN 'Lost'
      ELSE 'Regular'
    END AS segment
  FROM rfm_scores
)
SELECT segment, COUNT(*) AS customer_count,
  ROUND(AVG(total_spent)::NUMERIC, 2) AS avg_spent
FROM segmented
GROUP BY segment
ORDER BY avg_spent DESC;

This reveals actionable business insights: how many Champions, how many Lost customers, and their average spending.

NTILE adapts to your data distribution automatically. If most customers spent under $50, fixed thresholds might put 90% in the bottom tier. NTILE always creates equal-sized groups, giving you a balanced distribution regardless of how skewed your data is.

Fill in the blanks: Rfm scoring

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Build full rfm

Loading practice…