RFM Segmentation

9 min read

Learn

Quick Reading

Estimated 9 mins

Prereq

Intermediate

Basic ML concepts helpful

Interactive

Static Playbook

Static guide & reference tables

Three numbers, no machine learning, still remarkably effective

RFM segmentation scores every customer on three behavioral dimensions: Recency (how long since their last purchase/interaction), Frequency (how often they purchase/interact), and Monetary value (how much they've spent). Each dimension is typically scored 1-5 (often via quintile buckets against the full customer base), producing a three-digit RFM score per customer — a customer scoring 5-5-5 is recent, frequent, and high-spending; a 1-1-1 customer hasn't purchased in a long time, rarely did, and spent little. Despite its simplicity (no model training, no ML infrastructure required), RFM remains a staple in retail and e-commerce CRM precisely because it's transparent, fast to compute, and directly actionable.

rfm-quintile-scoring.sql

sql

WITH customer_metrics AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS days_since_last_order,
    COUNT(*) AS order_count,
    SUM(net_revenue) AS total_spend
  FROM analytics.orders
  GROUP BY customer_id
),
scored AS (
  SELECT
    customer_id,
    -- Recency: lower days_since = better score, so invert the quintile
    6 - NTILE(5) OVER (ORDER BY days_since_last_order) AS recency_score,
    NTILE(5) OVER (ORDER BY order_count) AS frequency_score,
    NTILE(5) OVER (ORDER BY total_spend) AS monetary_score
  FROM customer_metrics
)
SELECT
  customer_id,
  recency_score,
  frequency_score,
  monetary_score,
  CONCAT(recency_score, frequency_score, monetary_score) AS rfm_code
FROM scored

Naming the segments: from raw codes to actionable groups

A raw RFM code like '541' is useful for computation but meaningless to a marketer planning a campaign, so RFM codes are typically rolled up into named segments with clear behavioral definitions and recommended treatments.

Common RFM-derived segments

Segment name	Typical RFM pattern	Recommended treatment
Champions	High R, high F, high M (5-5-5, 5-4-5)	Reward loyalty, early access to new products, referral asks
Loyal customers	High F, high M, moderate R	Upsell/cross-sell, maintain engagement cadence
At-risk	Low R, but historically high F/M	Win-back campaigns, reactivation offers, before full churn
Hibernating	Low R, low F, low M across the board	Low-cost reactivation attempts only; low expected ROI on heavy spend
New customers	High R, low F (one recent purchase)	Onboarding sequences, second-purchase incentives

RFM works because it's a cheap proxy for both loyalty and churn risk

A customer who was once a Champion but has drifted into the At-risk segment (recency score dropping while frequency/monetary history stays high) is exhibiting almost exactly the leading-indicator pattern a formal churn model would flag — RFM gives you a lightweight, no-infrastructure way to catch that drift early and route it into a win-back flow, without needing a trained churn prediction model.

Quintile boundaries shift as your customer base grows — recompute regularly

Because RFM scores are typically computed via quintiles against the current customer base, a customer's score is relative, not absolute — the same literal purchase behavior could score differently as the overall base's distribution shifts (e.g. after acquiring a large cohort of low-frequency customers through a new channel). Recompute RFM scores on a regular cadence (monthly is common) rather than treating a score as a permanent label, and be aware that segment membership will drift for reasons unrelated to any individual customer's own behavior change.

What's Next?

RFM is a hand-built heuristic segmentation; the natural next step for teams with enough transaction history is letting a machine learning model learn the equivalent segmentation and predictive power automatically, directly from the raw behavioral data rather than three hand-picked dimensions.

I build these systems professionally.

Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.

Work With Me →