Part 178: Customer LTV & RFM Segmentation in SQL

Classifying customers into Champions, Loyalists, and At-Risk groups using NTILE quintiles.

1. What is RFM Analysis?

• Recency (R): How recently did the customer purchase? (Lower days = Better score)
• Frequency (F): How often do they purchase? (Higher count = Better score)
• Monetary (M): How much total revenue did they generate? (Higher spend = Better score)

2. Calculating RFM Scores with NTILE(5)

WITH customer_metrics AS (
    SELECT 
        customer_id,
        CURRENT_DATE - MAX(DATE(order_date)) AS recency_days,
        COUNT(order_id) AS frequency,
        SUM(order_total) AS monetary_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT 
        customer_id,
        recency_days,
        frequency,
        monetary_value,
        -- R Score: 5 is most recent (lowest recency_days)
        NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
        -- F Score: 5 is highest frequency
        NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
        -- M Score: 5 is highest spend
        NTILE(5) OVER (ORDER BY monetary_value ASC) AS m_score
    FROM customer_metrics
)
SELECT 
    customer_id,
    r_score, f_score, m_score,
    CASE 
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
        WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk (High Value, Slipping)'
        WHEN r_score <= 1 AND f_score <= 2 THEN 'Lost Customers'
        ELSE 'Potential Loyalist'
    END AS customer_segment
FROM rfm_scores;

Key Takeaways

• NTILE(5) divides the distribution into exact 20% quintiles, normalizing across different company scales.

In Part 179, we explore Market Basket Analysis: Cross-Selling & Co-Occurrence in SQL!