RFM Customer Segmentation

RFM stands for Recency, Frequency, and Monetary value — the three most predictive dimensions of customer value. Originally developed for direct mail marketing in the 1990s, RFM has become the standard framework for customer segmentation in e-commerce, subscription businesses, loyalty programs, and CRM systems.

The core insight: ec_customers who bought recently, buy frequently, and spend large amounts are your most valuable ec_customers. Customers who bought a long time ago, rarely, and for small amounts are at risk of churning permanently.

What Each Dimension Measures

Dimension	Question	Low Score Means	High Score Means
Recency	How recently did they buy?	Hasn't bought in a long time	Just bought recently
Frequency	How often do they buy?	Occasional buyer	Loyal repeat buyer
Monetary	How much do they spend?	Low-value purchases	High-value customer

RFM scoring typically assigns 1-5 scores to each dimension, then combines them to create segments like "Champions" (5-5-5), "At Risk" (low R, high F&M), or "Lost" (1-1-1).

Step 1 — Compute Raw RFM Values

The first step calculates raw values for each customer: days since last purchase (Recency), number of ec_orders (Frequency), and total spend (Monetary).

CURRENT_DATE - MAX(created_at)::date gives the number of days since the customer's most recent purchase. Smaller is better — a customer who bought yesterday scores better on Recency than one who last bought 6 months ago.

WITH rfm_base AS (
    SELECT
        customer_id,
        MAX(created_at)::date AS last_purchase_date,
        COUNT(order_id) AS order_count,
        SUM(total_amount) AS total_spend,
        MIN(created_at)::date AS first_purchase_date
    FROM ec_orders
    WHERE status NOT IN ('cancelled', 'refunded')
    GROUP BY customer_id
),
rfm_values AS (
    SELECT
        customer_id,
        last_purchase_date,
        order_count,
        ROUND(total_spend, 2) AS total_spend,
        CURRENT_DATE - last_purchase_date AS days_since_last_purchase,
        CURRENT_DATE - first_purchase_date AS customer_age_days
    FROM rfm_base
)
SELECT * FROM rfm_values ORDER BY days_since_last_purchase;

What This Returns

Raw RFM inputs for every customer. days_since_last_purchase is Recency (lower = better), order_count is Frequency (higher = better), total_spend is Monetary (higher = better). Before scoring, review the distribution of each metric to understand your customer base.

Purchase this course to unlock the full lesson.

← Previous Next →