Who are the best customers, who is leaving, and what are they worth?
A UK-based online retailer of gifts and homewares: 1.07 million invoice lines from December 2009 to December 2011. Every answer here was checked against what actually happened next.
Four questions a CRM team asks
Who are the best customers?
Who is leaving?
Is retention improving?
What is each customer worth?
Product sales by month: the peak repeats every September to November
About 13% of sales has no customer ID: real revenue, but it cannot be attributed to anyone.
Measured before cleaning
The file has 34,335 lines identical to another line. The reflex is drop_duplicates(). Measuring first showed two different things.
Removed: an export artefact
The two Excel sheets overlap from 1 to 9 Dec 2010, identical line for line. Kept once, December stops counting twice.
Kept: they look like real purchases
86% sit on non-adjacent lines of the same invoice, and the same product often comes back with another quantity: an item added twice to an order. Deleting them would remove real sales.
The SQL does the maths, Python orchestrates
SQL migrations
Every rule is versioned SQL. Functions take a reference date, so validation cannot peek at the future.
quality checks
If one fails, the whole run rolls back: a wrong number never reaches the report.
automated tests
Hostile inputs, four session time zones and an end-to-end run on the real file.
A quarter of the customers bring 69% of revenue
Recency, frequency and value scored 1 to 5 in SQL, with ties always on the same score, then mapped to ten segments, each with an action for the CRM team.
Segments and suggested actions
A simple rule, tested at a past date
“More than 90 days without buying”, applied on a past date with only what was known that day, then compared with who actually came back in the next six months.
Did not come back, by days without buying at the cut-off
Separate “at risk” from “already gone”
Just past 90 days, customers did not come back about as often as the average customer. The rule mostly separates the active from those who left long ago.
at risk · 91–365 days
– of revenue in the last 12 months
inactive · over a year
Already gone: no recent revenue.
About one in five customers buys again the next month
Whether it is improving, two years of data cannot say: early cohorts mix in old customers who came back, and the season moves month 1. A first draft claimed new customers fell 36%; with the same history window for both years it is 979 against 940, so the claim was withdrawn.
expected in the next 6 months
Active probability by segment (same season a year earlier) × average order × purchases per month (smoothed) × months. A forecast made on 10 June 2011 was checked against the real revenue to 10 December.
It beats the fair, seasonal baseline on all three measures. The error on the total is partly luck: by segment the bias runs from −17% to +199%, and errors offset.
Forecast vs actual, 6 months after 10 June 2011
Three independent reviews
QA
Adding six months to a timestamp depended on the session's time zone. Fixed inside every function and tested in four zones.
Data methodology
A false acquisition drop, a churn headline mixing “at risk” with “gone”, and a weak baseline. All rewritten.
Engineering
Failed checks could still be saved and exported. Now the run rolls back and the export refuses.
Thanks for reading.
Churn per customer
A threshold from each customer's own buying rhythm, or a model that sees the season.
Probabilistic CLV
BG/NBD and Gamma-Gamma, compared with this model and the seasonal baseline.
Basket analysis
Which products sell together, to feed each segment's action.
Diego Freitas Santiago · Data Analyst · PostgreSQL, SQL, Python, Power BI
Data: Chen, D. (2012). Online Retail II [Dataset]. UCI Machine Learning Repository, CC BY 4.0.