Key findings
Dataset: UCI Online Retail, 1 Dec 2010 to 9 Dec 2011. Customers are scored on the day after the last order (10 Dec 2011).
Who drives the revenue
Each segment's share of customers next to its share of sales. When the second bar is much longer than the first, a small group is carrying the business.
Share of customers vs share of sales, by segment
10 segments · every customer is assigned to exactly one
Show the segment table
The RFM map
Customers placed by how recently they ordered (rows) and how often (columns). Top-right is where you want customers to be; the bottom-left corner is the dormant tail.
Customers by recency and frequency score
Darker = more customers · hover a cell for sales
How customers move between segments
The same scoring applied at the end of each quarter, using only the orders placed up to that date. This shows whether segments are growing or draining, which a one-off RFM snapshot cannot.
Segment size at each quarter end
Customers in four key segments
What the movement says
Between 1 Oct and 10 Dec 2011
Where Q3's customers ended up
Row = segment on 1 Oct 2011 · cell = share of that row in each segment on 10 Dec 2011 · hover for counts
Do new customers come back?
Customers grouped by the month of their first order. Each cell is the share of that group who ordered again N months later.
Monthly cohort retention
The Dec 2010 group includes customers who were already buying before the data starts, which is why it retains far better · Dec 2011 is a partial month (9 days)
Monthly sales
GBP · Dec 2011 is a partial month
Active customers per month
Customers with at least one order · Dec 2011 is partial
What I would do next
Each segment needs a different conversation. Ordered by the revenue at stake.
How it was built
Plain SQL in three layers, run end to end with one command (python run.py). The same models port to Snowflake or BigQuery with minor syntax changes.
Bronze · raw
invoice lines loaded exactly as published.
Silver · cleaned
Gold · analysis
- One row per customer with R, F and M scores
- Segment summary and R × F grid
- Quarterly snapshots and migration
- Monthly cohorts and trends
Scoring rules
Fixed business thresholds, so a score means the same thing at every snapshot and the 1,500 one-time buyers are never split across scores by an arbitrary tie-break
| Score | Recency (days since last order) | Frequency (orders) | Monetary (sales, GBP) |
|---|---|---|---|
| 5 | 0–14 | 6 or more | 2,000+ |
| 4 | 15–30 | 4–5 | 1,000–1,999 |
| 3 | 31–90 | 3 | 500–999 |
| 2 | 91–180 | 2 | 250–499 |
| 1 | over 180 | 1 | under 250 |
Segments come from the standard R × F grid (for example, R 5 and F 4–5 = Champions; R 1–2 and F 5 = Can't Lose). All 25 combinations map to a segment, so no customer lands in "other". The monetary score is kept for context and sorting.