RFM Segmentation

Methodology

What is RFM Segmentation?

RFM is a behavior-based framework for ranking and grouping customers by how recently they bought, how often they buy, and how much they spend. It turns raw transaction data into actionable segments — no machine learning required.

Dimension 1

Recency

Days since the customer's last purchase. The most predictive single metric — a customer who bought yesterday is far more likely to buy again than one who bought a year ago.

In this tool: HOT ≤ 30d · WARM ≤ 90d · COLD > 90d (adjustable)

Dimension 2

Frequency

Number of purchases per month. High-frequency customers have stronger habits, higher switching costs, and better LTV predictability. It signals loyalty even when recency dips.

In this tool: a binary threshold separates "frequent" from "infrequent" (adjustable)

Dimension 3

Monetary

Average monthly spend in €. Monetary value drives prioritization — a Champions customer spending €1,500/mo deserves a different retention investment than one spending €50/mo.

In this tool: LOW · MID · HIGH bands (adjustable) · dot size encodes spend visually

Why it matters in retail

Not all customers deserve the same marketing investment. RFM lets you concentrate budget where it generates the highest return — retaining Champions, reactivating At Risk customers before they churn, and converting Promising customers before they cool off. A company spending the same on a Lost customer as a Loyal one is burning margin.

The framework is deliberately simple — which is a feature, not a bug. It works with a single transactions table, runs in SQL in minutes, and produces segments that any team can understand and act on without a data science degree.

Segment reference

Segment	Definition	Recommended action
Champions	HOT · frequent · HIGH spend	Loyalty rewards, early access, referral programs. Don't over-discount — they already love you.
Loyal	HOT · frequent · MID or LOW spend	Upsell to higher-value products. Cross-sell adjacent categories. Increase basket size.
Potential	HOT · infrequent · MID or HIGH spend	Trigger repurchase with email sequences. Subscription offers. Show what they're missing.
New	First purchase ≤ 7 days ago	Onboarding is critical. First 30 days determine long-term retention. Welcome series + second-purchase incentive.
Promising	WARM recency · any F/M	Re-engage before going cold. Personalized nudge, limited-time offer, recommendation based on past behavior.
At Risk	WARM recency · frequent (historically)	Highest urgency. Were loyal, now drifting. Direct outreach, win-back campaign, ask why they stopped.
Lost	COLD recency (any F/M)	Low ROI. Test win-back with a strong offer on a random subset. If no response, suppress from active campaigns.

How to score in SQL

RFM scoring requires only a standard orders table with three columns: customer_id, order_date, order_value.

-- Step 1: compute raw RFM values per customer
SELECT customer_id,
  DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency_days,
  COUNT(*) / NULLIF(DATEDIFF(MAX(order_date), MIN(order_date)) / 30.0, 0) AS freq_per_month,
  SUM(order_value) / NULLIF(COUNT(DISTINCT DATE_FORMAT(order_date,'%Y-%m')), 0) AS spend_per_month
FROM orders GROUP BY customer_id;

-- Step 2: assign bands then map to segments
CASE
  WHEN recency_days > :cold_threshold THEN 'lost'
  WHEN recency_days <= 7 THEN 'new'
  WHEN recency_days <= :hot_threshold AND freq_per_month >= :freq_th AND spend_per_month > :m_high THEN 'champions'
  WHEN recency_days <= :hot_threshold AND freq_per_month >= :freq_th THEN 'loyal'
  WHEN recency_days <= :hot_threshold THEN 'potential'
  WHEN freq_per_month >= :freq_th THEN 'at_risk'
  ELSE 'promising'
END AS segment