RFM Segmentation: Turn Transaction History into Customer Strategy
Imagine you run an online store with thousands of customers. Some buy every week, some bought once and vanished, some spent a fortune months ago and never returned. Who do you email a loyalty reward? Who needs a win-back discount? Who is about to churn? RFM answers all three — using only three numbers computed from your existing orders table. No machine learning, no extra data — just Recency, Frequency, and Monetary.
1. The Business Problem RFM Solves
Ranking customers by total spend alone is misleading. A customer who spent $2,000 two years ago and never returned is less valuable than one who spent $400 last week and buys monthly. RFM fixes this by scoring when they last bought, how often they buy, and how much they spend — then combining the three into a single actionable segment like Champions or At-Risk.
2. Your Starting Point: The Raw Orders Table
In Project 35 (and the notebook assignment) you already have this table as df_orders after ingesting from SQLite. Here is a miniature version with 8 rows across 3 customers — everything RFM needs is already here:
| transaction_id | customer_id | transaction_date | quantity | unit_price | total_amount |
|---|---|---|---|---|---|
| 1001 | C001 | 2024-06-10 | 2 | 120.00 | 240.00 |
| 1002 | C001 | 2024-06-22 | 1 | 899.00 | 899.00 |
| 1003 | C001 | 2024-06-28 | 3 | 45.00 | 135.00 |
| 1004 | C002 | 2024-02-12 | 5 | 80.00 | 400.00 |
| 1005 | C002 | 2024-03-18 | 2 | 250.00 | 500.00 |
| 1006 | C002 | 2024-04-01 | 4 | 120.00 | 480.00 |
| 1007 | C003 | 2024-06-25 | 1 | 45.00 | 45.00 |
| 1008 | C003 | 2024-04-10 | 1 | 45.00 | 45.00 |
You have seen total_amount = quantity * unit_price since Module 3. RFM simply rolls this table up to one row per customer.
3. The Three Pillars — Plain English + Formula
| Pillar | Plain English | How to compute (per customer) | Higher is better? |
|---|---|---|---|
| R — Recency | How many days ago was their last purchase? | (snapshot_date − max(transaction_date)).days — pick a single snapshot (we use 2024-07-01) and subtract the customer's most recent date |
Lower days = better (3 days ago beats 140 days ago) |
| F — Frequency | How many separate orders did they place? | count(transaction_id) |
Higher count = more loyal |
| M — Monetary | How much money did they spend in total? | sum(total_amount) |
Higher spend = more valuable |
4. Worked Example — From 8 Rows to 3 Customer Summaries
Using snapshot_date = 2024-07-01, roll up the table above. Do it by hand once — then the code does it for 50 customers automatically:
| customer_id | Last purchase | Recency (days) | Frequency | Monetary | Intuition |
|---|---|---|---|---|---|
| C001 | 2024-06-28 | 3 = 2024-07-01 − 2024-06-28 | 3 orders | $1,274.00 | Bought 3 days ago, repeatedly, high spend → likely Champion |
| C002 | 2024-04-01 | 91 = 2024-07-01 − 2024-04-01 | 3 orders | $1,380.00 | High spend but 3 months silent → At-Risk |
| C003 | 2024-06-25 | 6 | 2 orders | $90.00 | Very recent but low count/spend → New / Promising |
Notice C002 spent the most overall but is the riskiest — this is exactly why RFM beats "sort by total spend".
5. Scoring: Turning Raw Numbers into 1–5 (So You Can Compare)
Raw R/F/M values have different units (days vs counts vs dollars) and can't be compared directly. We convert each into a quintile score 1–5 so 5 always means "top 20% best" and 1 means "bottom 20%".
- How quintiles work:
pd.qcut(series, q=5, labels=[1,2,3,4,5])sorts the values and cuts them into 5 equal-sized buckets (20% of customers each). The lowest 20% get 1, the highest 20% get 5. - Why Recency is inverted: For Frequency and Monetary, a larger number is better — so the highest spend gets 5. For Recency, a smaller number of days is better (3 days ago is better than 90). So we flip the labels:
labels=[5,4,3,2,1]— the most recent 20% get 5. - Tie handling: If many customers share the same Frequency (e.g. dozens with exactly 1 order),
qcutcan fail with duplicate edges. We add.rank(method='first')to break ties deterministically before cutting.
What the same 3 customers look like after scoring (illustrative 5-customer population):
| customer_id | R (days) | R score | F | F score | M | M score | RFM |
|---|---|---|---|---|---|---|---|
| C001 | 3 | 5 | 3 | 5 | $1,274 | 5 | 5-5-5 Champion |
| C002 | 91 | 1 | 3 | 4 | $1,380 | 5 | 1-4-5 Can't Lose |
| C003 | 6 | 4 | 2 | 2 | $90 | 1 | 4-2-1 Promising |
6. The Exact Pandas Code (Same Logic, Now Readable)
import pandas as pd
# Step 1 — pick the snapshot (the day you run the analysis)
snapshot_date = pd.to_datetime('2024-07-01')
# Step 2 — one row per customer: recency / frequency / monetary
rfm = df_orders.groupby('customer_id').agg({
'transaction_date': lambda d: (snapshot_date - pd.to_datetime(d.max())).days, # R
'transaction_id': 'count', # F
'total_amount': 'sum' # M
}).reset_index()
rfm.columns = ['customer_id', 'recency', 'frequency', 'monetary']
# Step 3 — quintile scores (R is inverted: recent = 5)
rfm['r_score'] = pd.qcut(rfm['recency'], q=5, labels=[5, 4, 3, 2, 1])
rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), q=5, labels=[1, 2, 3, 4, 5])
rfm['m_score'] = pd.qcut(rfm['monetary'], q=5, labels=[1, 2, 3, 4, 5])
# Optional: combine into a 3-digit segment key
rfm['rfm_segment'] = rfm['r_score'].astype(str) + rfm['f_score'].astype(str) + rfm['m_score'].astype(str)
print(rfm.head())
# customer_id recency frequency monetary r_score f_score m_score rfm_segment
# 0 C001 3 3 1274.0 5 5 5 555
# 1 C002 91 3 1380.0 1 4 5 145
This is exactly the code you will run in Task 5 of the notebook and in Project 35 — now every line maps to a row in the tables above.
7. From Scores to Business Action — The Segment Matrix
A 3-digit score like 5-5-5 or 1-4-5 maps to a playbook. Learn these six — they cover 90% of stakeholder conversations:
| Segment | Typical RFM | What it means | What you recommend |
|---|---|---|---|
| Champions | 5-5-5 | Bought recently, often, big spend | Loyalty perks, early access, referral ask |
| Loyal | 4-4-4 / 5-4-5 | Consistent high value | Upsell, subscription nudge |
| Promising / New | 4-2-1 / 5-1-1 | Very recent but low F/M | Onboarding series, second-purchase coupon |
| At-Risk | 2-4-4 / 2-3-3 | Used to buy often, now fading | Re-engagement email, survey "what changed?" |
| Can't Lose Them | 1-4-5 / 1-5-5 | Highest spenders, now silent (C002) | High-value win-back, personal outreach, discount |
| Lost / Hibernating | 1-1-1 / 1-2-2 | Long ago, few & small orders | Low-cost reactivation or suppress from spend |
Interview tip: When asked "Why not just rank by total spend?", answer with C002 above — top spend but 91 days silent. RFM catches timing + cadence that a single spend rank misses. That distinction is Quiz 4 Q09 and a frequent interview follow-up.