Retail analytics

Customer Segmentation & Lifetime Value

1,067,371 transactions from 2009-12-01 to 2011-12-09, cleaned to 776,827 line items across 5,853 identified customers — then segmented, clustered and costed.

Open the dashboard Source code Selvi Patel
5,853Customers analysed
£17.1MRevenue
77.2%Revenue from the top 20%
9.5%Still buying at month 12

The business is carried by a small group it cannot afford to lose — and most of the people it acquires never come back.

Revenue is concentrated

Ranking all 5,853 customers by spend and accumulating their revenue gives the curve below. It bends hard and early: the first fifth of customers carry 77.2% of everything.

Cumulative revenue share

0%25%50%75%100% top 20% of customers → 77.2% of revenue 0%20%40%60%80%100% customers, ranked by spend
Every customer, ranked highest spend first.

Cohort retention

0%25%50%75%100% 9.5% at month 12 036912 months since first purchase
Mean across every monthly acquisition cohort.

Interactive dashboard

The same analysis published to Tableau Public: revenue and customer counts by segment, the cluster scatter, and a ranked contact list of the 829 high-value customers who have stopped buying.

Open on Tableau Public ↗ Download the workbook (.twbx)

Who to contact first

829 customers sit in At Risk or Can't Lose Them: real historical spend, no recent order. Ranked by what they have actually spent, the top 25 are where a retention budget goes first. This is the list the dashboard is built to produce — a team can work from it on Monday.

CustomerSegmentCountryLast order OrdersLifetime spend Modelled CLV
12346 At Risk United Kingdom 326 days 3 £77,353 £13,073
16754 Can't Lose Them United Kingdom 372 days 29 £65,500 £11,173
13093 Can't Lose Them United Kingdom 276 days 55 £54,144 £8,057
17850 Can't Lose Them United Kingdom 372 days 155 £51,209 £7,629
15749 At Risk United Kingdom 235 days 3 £44,534 £14,696
13902 Can't Lose Them Denmark 632 days 5 £34,023 £5,144
12482 Can't Lose Them Sweden 576 days 27 £21,942 £3,674
15808 Can't Lose Them United Kingdom 306 days 21 £17,180 £2,550
13564 Can't Lose Them United Kingdom 354 days 36 £15,880 £2,356
17448 Can't Lose Them United Kingdom 529 days 41 £14,449 £2,453
18087 Can't Lose Them United Kingdom 291 days 16 £13,909 £2,060
13952 Can't Lose Them United Kingdom 218 days 14 £12,824 £1,940
14134 Can't Lose Them United Kingdom 383 days 15 £11,123 £1,669
15032 Can't Lose Them United Kingdom 256 days 8 £11,010 £1,656
14028 At Risk United Kingdom 550 days 3 £10,396 £2,069
14828 Can't Lose Them United Kingdom 197 days 13 £9,839 £1,469
17504 Can't Lose Them United Kingdom 206 days 18 £9,490 £1,552
14063 Can't Lose Them United Kingdom 687 days 7 £9,472 £1,421
14439 Can't Lose Them Greece 320 days 5 £9,443 £1,507
17230 Can't Lose Them United Kingdom 264 days 18 £9,388 £1,407
14160 Can't Lose Them United Kingdom 610 days 7 £8,421 £1,321
13206 Can't Lose Them United Kingdom 400 days 13 £8,308 £1,242
13715 Can't Lose Them United Kingdom 281 days 17 £8,153 £1,400
18231 Can't Lose Them United Kingdom 192 days 28 £6,848 £1,073
15413 Can't Lose Them United Kingdom 692 days 5 £6,799 £1,008

Segments

Seven groups from RFM quintile scoring — recency, frequency and monetary value, each scored 1 to 5, then mapped to named segments a team can actually act on.

SegmentCustomers% of base Revenue% of revenueChurned
Champions 1,476 25.2%
£11,826,035		 69.2% 0%
Loyal 1,207 20.6%
£2,422,417		 14.2% 2%
Can't Lose Them 358 6.1%
£1,034,451		 6.1% 100%
Hibernating / Lost 1,508 25.8%
£620,117		 3.6% 100%
At Risk 471 8.0%
£560,087		 3.3% 100%
New / Promising 451 7.7%
£416,565		 2.4% 0%
Needs Attention 382 6.5%
£201,457		 1.2% 4%

K-Means clusters

Run independently of the rule-based segments above, on log-scaled recency, frequency and monetary value. k came from the elbow method rather than the silhouette: silhouette peaked at k=2, which splits customers into active and inactive — true, but nothing a team can do anything with.

ClusterCustomersMedian recency Median ordersMedian spend % of revenue
VIPs 1,179 16 days 13 £4,910 73.3%
Lapsing regulars 1,456 182 days 5 £1,460 16.9%
Active light buyers 1,246 24 days 3 £720 6.1%
One-and-done 1,972 401 days 1 £278 3.7%

Cohort retention

Each row is one month's new customers; each column is months since their first purchase. Retention falls from 20.1% at month one to 9.5% at month twelve.

Cohort01234567891011
2009-121003533423836383434364250
2010-011002132322731272329333118
2010-021002323292520192926281113
2010-031001923242320253128111215
2010-04100191916182228271111714
2010-0510016171818252113681113
2010-0610018192123281398111014
2010-071001618302914111515111315
2010-081002029331712101314131312
2010-09100232313910141013131210
2010-101002615139813141191113
2010-11100171010891310991115
2010-121009591275953920
2011-011001722192215151211212511
2011-0210016161822161615151860
2011-0310018222022152120241000
2011-041002520201824182511000
2011-05100232416222126120000
2011-061002321272028800000
2011-071002130283516000000
2011-08100263026170000000
2011-0910027381500000000
2011-101003216000000000
2011-11100140000000000
2011-1210000000000000

What to do about it

Three costed actions. Each states the assumption it rests on, because a number without its assumption is a guess wearing a suit.

1

Win back 'Can't Lose Them' before they are gone for good

358 customers (6.1% of the base) have bought 6 times on average but have not ordered in 338 days. They carry GBP 1,034,451 of historical revenue (6.1% of the total).

ActionTargeted win-back offer, sequenced email plus phone for the top decile.

£37,240gross margin · 12% response, 30% gross margin

2

Protect Champions: the revenue base is dangerously concentrated

1,476 customers (25.2%) generate GBP 11,826,035 — 69.2% of all revenue. Losing just 5% of them costs GBP 591,302 in revenue.

ActionNamed account care for the top 200, early-warning alert when a Champion's gap between orders exceeds twice their own norm.

£177,391gross margin · Protecting 5% of Champion revenue at 30% margin

3

Convert one-time buyers to a second purchase

1,619 customers (27.7%) ordered exactly once, at a median of GBP 229. They account for only 3.3% of revenue — the single largest untapped group.

ActionPost-purchase onboarding sequence with a second-order incentive, triggered 30 days after first delivery.

£5,560gross margin · 5% conversion to a second order at median AOV

£220,191 total identified gross margin, on the assumptions stated above

Method & limitations

Cleaning1,067,371 raw rows reduced to 776,827 (27.2% dropped): cancelled invoices, non-product lines such as postage and bank charges, exact duplicates, and 243,007 rows with no customer ID.
Known biasThose missing-ID rows are guest checkouts — real sales that cannot be attributed to a person. They skew toward one-time buyers, so every segment here over-represents repeat customers. Stated rather than hidden.
Lifetime valueAOV × orders per year × 30% assumed gross margin, where orders per year is measured over how long each customer has been observed, not over their active span — dividing by active span sends one-time buyers to infinity.
ChurnNo purchase within 180 days of the 2011-12-10 snapshot. Four segments show 0% or 100% by construction, because the segments are themselves defined on recency.