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), qcut can 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.