PROJECT 05Intermediate
RFM Customer Segmentation
Business scenario
Marketing sends the same email to all 4,000 customers. They want to target loyal customers differently from lapsing ones.
Problem statement
Segment customers by Recency, Frequency and Monetary value and recommend actions per segment.
Learning objectives
- Build a customer-level RFM table with a fixed analysis date
- Score R, F, M into quintiles and map to named segments
- Size segments and recommend differentiated actions
Instructions
Analysis date 2025-12-31. Completed orders only. Score each dimension 1–5 by quintile (recency: more recent = higher). Define 5–7 named segments (e.g. Champions, Loyal, At Risk, Hibernating) with explicit score rules.
Tasks
- Customer-level table: last order, order count, net revenue.
- Quintile scores (NTILE or Python qcut) — handle ties sensibly.
- Segment mapping rules and segment sizes (customers, revenue share).
- Profile segments (channel, city, segment).
- Recommended action and expected value per segment.
Deliverables
- SQL/Python
- Segment table
- 1-page targeting plan
Evaluation rubric
| Criterion | Weight | Excellent | Proficient | Developing | Insufficient |
|---|---|---|---|---|---|
| Data preparation & validation | 20% | Correct filters and grain; validation checks documented and totals reconciled. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| RFM computation | 25% | Correct metrics and scoring with tie handling explained. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Segment design | 15% | Rules explicit, segments meaningful and actionable. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Interpretation & recommendations | 25% | Clear, quantified conclusions tied to the business question, with limitations stated. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Presentation | 15% | Concise, well-labelled outputs a manager can act on without explanation. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
Guided solution
The guided solution unlocks after you submit your own attempt.