Customer-retention system — Nordwind
An end-to-end customer-retention system for an e-commerce store (“Nordwind”). It cleans the raw sales, scores every customer, measures who is drifting away, shows it on a live Power BI dashboard, and re-runs itself every week on GitHub — opening an alert with an AI-written briefing whenever the revenue at risk climbs too high.
Turns a pile of raw sales into one number a manager can act on — £1.32M of revenue sitting with 1,708 customers who are slipping away — then refreshes it, alerts on it, and explains it in plain English every week, with no one touching it.
01The problem
- Winning a new customer costs far more than keeping one, so the quiet killer for any online store is churn — customers who buy once or twice and vanish.
- The business question: who is slipping away, how much money do they represent, and what should we do — answered automatically, not in a one-off spreadsheet.
02How the system works, end to end
Seven stages run as one automated pipeline — from raw data to an AI-written briefing.
- Clean 540,000+ raw transactions into trustworthy sales (Python / pandas).
- Score every customer by Recency, Frequency and Monetary value, and sort them into plain groups — Champions, Loyal, At Risk, Lost.
- Measure retention over time with a cohort analysis, and flag the revenue at risk (At Risk + Lost).
- Visualise it on a two-page Power BI dashboard — an executive overview and a retention deep-dive.
- Automate the whole run on GitHub Actions — a fresh cloud machine does it every week, with no clicks.
- Alert — when revenue at risk crosses a threshold, it opens an issue with the win-back customer list and emails it.
- Explain — an AI step (Google Gemini) writes a plain-language briefing of the week and the top 3 actions.
LIVEThe dashboard
Power BI · rebuilt here from the pipeline data| Cohort | M0 | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2010-12 n=885 | 100 | 37 | 32 | 38 | 36 | 40 | 36 | 35 | 35 | 40 | 37 | 50 | 27 |
| 2011-01 n=417 | 100 | 22 | 27 | 23 | 32 | 29 | 25 | 24 | 30 | 33 | 36 | 12 | |
| 2011-02 n=380 | 100 | 19 | 19 | 28 | 27 | 25 | 25 | 28 | 25 | 30 | 7 | ||
| 2011-03 n=452 | 100 | 15 | 25 | 20 | 22 | 17 | 27 | 23 | 28 | 9 | |||
| 2011-04 n=300 | 100 | 21 | 20 | 21 | 20 | 23 | 22 | 26 | 7 | ||||
| 2011-05 n=284 | 100 | 19 | 17 | 17 | 21 | 23 | 26 | 10 | |||||
| 2011-06 n=242 | 100 | 17 | 16 | 26 | 23 | 34 | 10 | ||||||
| 2011-07 n=188 | 100 | 18 | 21 | 22 | 27 | 11 | |||||||
| 2011-08 n=169 | 100 | 21 | 25 | 24 | 12 | ||||||||
| 2011-09 n=299 | 100 | 23 | 30 | 11 | |||||||||
| 2011-10 n=358 | 100 | 24 | 12 | ||||||||||
| 2011-11 n=323 | 100 | 11 | |||||||||||
| 2011-12 n=41 | 100 |
| Customer | Segment | Orders | Spend | Last seen |
|---|---|---|---|---|
| 12346 | Lost | 1 | £77,184 | 326 days |
| 15749 | At Risk | 3 | £44,534 | 235 days |
| 15098 | At Risk | 3 | £39,916 | 182 days |
| 12409 | At Risk | 3 | £11,073 | 79 days |
| 16180 | At Risk | 8 | £10,254 | 100 days |
| 12590 | Lost | 2 | £9,864 | 211 days |
| 13093 | At Risk | 8 | £7,832 | 276 days |
| 12435 | Lost | 2 | £7,830 | 80 days |
03The hands-off loop
- Once it is set up, no one touches it: the pipeline runs on a schedule, publishes fresh data the dashboard reads, and files an alert only when it is needed.
- A one-line fix in the pipeline flows straight through to the dashboard on the next refresh — one source of truth, no manual re-importing.
- The alert is not just a number: an AI briefing on top explains the week in plain words and names the exact customers to call first.
“We crossed our warning threshold this week with £1.32M in revenue at risk; 1,708 accounts have gone quiet. Top 3 actions: call top at-risk buyers 15749 (£44,534) and 15098 (£39,916); send a “we miss you” campaign to all 643 at-risk customers; investigate why the biggest lost buyer, 12346 (£77,184), stopped ordering.”