Analysis 03 of 28E-commerce

Which customers bring the money, and which are slipping away?

If every customer is scored on how recently they bought, how often and how much they spent, how many sit in each segment, how much revenue does each bring, and what would I do with each?

The short answer Champions and loyal customers, 31.7% of 5,852 identified customers, bring 79.3% of their net spend; champions alone (823 customers) bring 52.7%. The largest group is hibernating: 27.9% of customers and 4.1% of spend. 709 customers with 3 or more orders have not ordered in over six months (at risk, or cannot lose them) and spent £1.37M between them, the clearest win-back list. What I would do with each segment is below the charts.

Public data
Scored as of Dec 10, 2011
Identified customers only

Data as of Dec 2011 · fixed dataset

Customer retention When the same customers come back, and when they stop.

Source: UCI Online Retail II, Chen, D. (2012), UCI Machine Learning Repository
Period: Dec 2009 to Dec 2011, scored as of Dec 10, 2011
Licence: CC BY 4.0
Charts: 5, each with its data table and CSV
Data file: data/analyses/customer_segments.json

5,852Customers scoredidentified, as of Dec 10, 2011
79.3%Champions and loyal customersof net spend, from 31.7% of customers
823Champions14.1% of customers, 52.7% of net spend
£1.37MAt risk or cannot lose themnet spend of 709 customers with 3 or more orders and no order in over six months
27.9%Hibernatingof customers, 4.1% of net spend

Still of the 3D sculpture: a ring of ten wedges, one per customer segment. The warm champions wedge stands tallest, with the narrow cannot-lose-them wedge and the warm loyal customers wedge next; the wide hibernating wedge is almost flat. — Each wedge is one segment. Its width around the ring is its share of the 5,852 scored customers and its height is its average net spend per customer, so its volume is its share of net spend. Champions and loyal customers (warm) are 31.7% of customers and bring 79.3% of net spend; hibernating customers are the widest wedge, 27.9% of customers, and bring 4.1%.

Chart 1Customers against net spend

Champions and loyal customers are 31.7% of customers and bring 79.3% of net spend.

How many customers sit in each segment, and how much of the net spend does each bring?

Ten segments from the recency and frequency scores, ordered by net spend. Champions (823 customers, 14.1%) bring 52.7% on their own. Hibernating customers are the largest group, 27.9%, and bring 4.1%.

Download CSV

UCI Online Retail II · public data

Segments: 10 rows. 5,852 identified customers scored as of Dec 10, 2011; net spend £16,360,035.42 in all. Segment rules are under Method.
Segment	Share of customers (%)	Share of net spend (%)	Customers	Net spend (£)
Champions	14.1	52.7	823	8,624,952.28
Loyal customers	17.6	26.6	1,032	4,343,673.41
At risk	11.0	5.5	643	901,816.06
Potential loyalists	12.2	4.2	716	680,804.62
Hibernating	27.9	4.1	1,630	674,231.11
Cannot lose them	1.1	2.8	66	464,359.49
Need attention	4.8	2.2	282	367,766.92
About to sleep	7.2	1.4	421	223,723.47
Promising	2.8	0.3	161	52,898.75
New customers	1.3	0.2	78	25,809.31

Chart 2The 5 by 5 grid

Recent buyers are frequent buyers: 46.4% of the most recent fifth have 9 or more orders.

How do the recency and frequency scores combine?

Each bar is one recency fifth, split by frequency score. In the most recent fifth, 46.4% have 9 or more orders; in the least recent fifth, 61.1% ordered once. The segments are regions of this grid.

Download CSV

UCI Online Retail II · public data

Recency by frequency: 5 rows. Scores are fifths of the 5,852 customers by the nearest-rank rule, so tied values share a score; the fifths are close to, not exactly, 20% each. Recency counts days to Dec 10, 2011.
Recency score, days since the last purchase	Customers	F1, 1 order (%)	F2, 2 orders (%)	F3, 3 to 4 orders (%)	F4, 5 to 8 orders (%)	F5, 9 or more orders (%)
R5, 0 to 20 days	1,215	6.4	10.4	15.5	21.3	46.4
R4, 21 to 59 days	1,135	14.2	13.0	22.5	23.7	26.7
R3, 60 to 187 days	1,163	21.2	15.0	24.2	23.8	15.7
R2, 188 to 409 days	1,171	35.8	22.4	23.3	14.3	4.3
R1, over 409 days	1,168	61.1	20.1	12.8	4.5	1.4

Chart 3Average net spend by segment

A champion has spent £10,479.89 on average; a cannot-lose customer £7,035.75.

How much is a customer in each segment worth?

Average net spend per customer over the whole history. Cannot-lose customers ordered as often as champions (median 12 orders) but last bought a median of more than 10 months ago.

Download CSV

UCI Online Retail II · public data

Spend per customer: 10 rows. Net spend counts cancellations as negative. A median of two middle values can end in .5.
Segment	Average net spend per customer (£)	Median orders	Median days since the last purchase	Customers
Champions	10,479.89	12.0	9.0	823
Loyal customers	4,208.99	8.0	53.5	1,032
At risk	1,402.51	4.0	369.0	643
Potential loyalists	950.84	3.0	24.0	716
Hibernating	413.64	1.0	431.0	1,630
Cannot lose them	7,035.75	12.0	315.5	66
Need attention	1,304.14	3.0	102.5	282
About to sleep	531.41	1.0	96.0	421
Promising	328.56	1.0	38.0	161
New customers	330.89	1.0	12.0	78

Chart 4Customers and spend by last purchase

27.8% of customers bought in the last month; they bring 63.3% of net spend.

How long ago did customers last buy?

Customers grouped by days since the last purchase, with their share of all customers and of all net spend. 27.2% have not bought for a year or more.

Download CSV

UCI Online Retail II · public data

Recency: 6 rows. Bands in days: under 1 month is 0 to 30 days, 1 to 3 months 31 to 90, 3 to 6 months 91 to 180, 6 to 12 months 181 to 365, 12 to 18 months 366 to 545.
Last purchase	Customers	Share of customers (%)	Share of net spend (%)
Under 1 month	1,627	27.8	63.3
1 to 3 months	1,258	21.5	17.7
3 to 6 months	586	10.0	6.0
6 to 12 months	790	13.5	5.8
12 to 18 months	1,052	18.0	5.2
Over 18 months	539	9.2	2.0

Chart 5Spend score mix by segment

63.3% of champions are top-fifth spenders; 82.7% of hibernating customers are in the bottom two.

Does the spend score line up with the segments?

The segments use recency and frequency only. The spend score is shown here, by segment, as a check that the names fit the money.

Download CSV

UCI Online Retail II · public data

Spend score: 10 rows. Spend score: the customer's fifth by net spend, nearest rank, in pounds and pence.
Segment	Customers	M1, up to £271.75 (%)	M2, £271.76 to £590.44 (%)	M3, £590.45 to £1,178.71 (%)	M4, £1,178.72 to £2,828.94 (%)	M5, over £2,828.94 (%)
Champions	823	0.0	0.9	9.0	26.9	63.3
Loyal customers	1,032	0.4	1.0	11.5	39.1	48.0
At risk	643	2.5	17.7	39.0	32.5	8.2
Potential loyalists	716	8.0	27.8	41.3	19.8	3.1
Hibernating	1,630	48.8	33.9	13.1	3.4	0.8
Cannot lose them	66	0.0	0.0	3.0	34.8	62.1
Need attention	282	5.0	20.6	37.9	29.8	6.7
About to sleep	421	35.9	35.6	20.9	6.4	1.2
Promising	161	57.1	29.8	10.6	2.5	0.0
New customers	78	53.8	39.7	5.1	0.0	1.3

Next stepsA plan for this data

What I would do with each segment.

PlanA plan for this retailer's data, written for this page. It is not a record of past work.

Champions 823 customers · 52.7% of net spendKeep them first in line: stock on what they reorder, early access to new lines, a named contact. Keep discounts for the groups that need a nudge.
Loyal customers 1,032 customers · 26.6% of net spendMove them toward champion: volume tiers or bundles on the lines they already buy, and a reorder reminder at their usual interval.
At risk 643 customers · 5.5% of net spendWin them back now, person to person: ask what changed and make an offer on their usual lines. They average £1,402.51 each.
Potential loyalists 716 customers · 4.2% of net spendEarn the next order: a replenishment reminder and related products from their first orders.
Hibernating 1,630 customers · 4.1% of net spendSpend little: one reactivation message with the new season's range, then stop.
Cannot lose them 66 customers · 2.8% of net spendCall each of the 66 by name. They average £7,035.75 and last bought a median of more than 10 months ago; find out why they stopped before offering anything.
Need attention 282 customers · 2.2% of net spendA time-limited offer on their past categories, before they slide into at risk.
About to sleep 421 customers · 1.4% of net spendA reminder with best sellers, and a check that nothing went wrong with their last order.
Promising 161 customers · 0.3% of net spendShow them the range beyond the first order, and make the second order easy: the retention page finds that half of 2010's new customers who came back within a year did so within 71 days.
New customers 78 customers · 0.2% of net spendA welcome sequence timed inside those first 71 days; across all customers, after about 120 days without an order the odds of another in the next six months fall to about even (51.5%).

01Findings

What the charts say.

Champions and loyal customers: 31.7% of customers, 79.3% of net spend. Champions alone: 14.1% and 52.7%.
Chart 1
Recency and frequency move together: 46.4% of the most recent fifth have 9 or more orders, while 61.1% of the least recent fifth ordered once.
Chart 2
709 customers with 3 or more orders have not ordered in over six months. The 66 cannot-lose customers average £7,035.75 each, more than a loyal customer (£4,208.99).
Chart 3 Chart 1
27.8% of customers bought in the last month and bring 63.3% of net spend; 27.2% have not bought for a year or more.
Chart 4
The names fit the money: 63.3% of champions are top-fifth spenders, though spend plays no part in assigning a segment.
Chart 5

02Method

How the figures were built.

Steps

Dropped non-product stock codes (POST, DOT, M, m, D, BANK CHARGES, AMAZONFEE, C2, PADS, CRUK, ADJUST, ADJUST2, S, B, TEST001/002, gift card vouchers) that represent postage, fees, manual adjustments, and test rows rather than real merchandise.
Dropped rows with price <= 0 (6,207 rows); nearly all of these have no Customer ID and are internal stock corrections, not customer sales.
Dropped negative-quantity rows on invoices that do not start with 'C' (3,457 rows); a true return in this dataset is a cancellation invoice (prefix 'C'), and these unlabeled negative-quantity rows all have null Customer ID and mostly null description, so they read as inventory write-offs rather than customer returns.
Removed 34,035 exact duplicate line items (after the rules above).
Kept rows with missing Customer ID (22.2% of clean rows) for revenue and product analysis since they are real sales; excluded them from the cohort retention analysis, which requires a known customer.
One row per identified customer with at least one invoice that is not a cancellation (5,852). Recency: days from the last purchase day to Dec 10, 2011, the day after the last invoice. Frequency: distinct invoices that are not cancellations. Spend: net revenue, with cancellations counted as negative.
Each score is the customer's fifth of this base on that measure, by the nearest-rank rule, so tied values share a score and the fifths are not exactly equal in size.
Cut points: recency 20, 59, 187 and 409 days; orders 1, 2, 4 and 8; net spend £271.75, £590.44, £1,178.71 and £2,828.94.
Segments (R = recency score, F = frequency score): Champions R 5, F 4 or 5; Loyal customers R 3 or 4, F 4 or 5; Potential loyalists R 4 or 5, F 2 or 3; New customers R 5, F 1; Promising R 4, F 1; Need attention R 3, F 3; About to sleep R 3, F 1 or 2; At risk R 1 or 2, F 3 or 4; Cannot lose them R 1 or 2, F 5; Hibernating R 1 or 2, F 1 or 2.

1,067,371 rows in the source file, 1,021,254 after cleaning; 5,852 identified customers scored. Every figure on this page comes from the chart tables in data/analyses/customer_segments.json, built from the full cleaned history; the table under each chart says how.

Checks

The SQL below, run in DuckDB 1.5.5, returns the same customers, net spend and share per segment as the scoring code, for all ten segments; every cell of the 5 by 5 recency and frequency grid falls in exactly one segment.
Every number on this page was recomputed by separate code (verify_r4.py: plain Python reading the original UCI zip) and matches.
The segments add up to all 5,852 customers and to their total net spend, £16.36M.

The queries

This series is not in Live SQL. The data file behind every chart is data/analyses/customer_segments.json, built by data/analyses/scripts/build_r4.py (prep, DuckDB) and data/analyses/scripts/verify_r4.py (independent check, plain Python) from the source downloads.

RFM scores and segments (DuckDB, the full cleaned history)

-- RFM: score each customer 1 to 5 on recency, frequency and spend, then name the segment
-- The cut points are the customers' own fifths (nearest rank), computed once and written in.
WITH customers AS (
    SELECT customer_id,
           DATE '2011-12-10' - max(invoice_date::DATE) FILTER (WHERE NOT is_cancelled) AS recency_days,
           count(DISTINCT invoice) FILTER (WHERE NOT is_cancelled) AS orders,
           round(sum(line_revenue), 2) AS net_spend -- in pounds and pence; cancellations count as negative
    FROM retail_lines
    WHERE customer_id IS NOT NULL
    GROUP BY 1
    HAVING count(*) FILTER (WHERE NOT is_cancelled) > 0
),
scored AS (
    SELECT *,
           CASE WHEN recency_days <= 20 THEN 5 WHEN recency_days <= 59 THEN 4 WHEN recency_days <= 187 THEN 3
                WHEN recency_days <= 409 THEN 2 ELSE 1 END AS r,
           CASE WHEN orders <= 1 THEN 1 WHEN orders <= 2 THEN 2 WHEN orders <= 4 THEN 3
                WHEN orders <= 8 THEN 4 ELSE 5 END AS f,
           CASE WHEN net_spend <= 271.75 THEN 1 WHEN net_spend <= 590.44 THEN 2 WHEN net_spend <= 1178.71 THEN 3
                WHEN net_spend <= 2828.94 THEN 4 ELSE 5 END AS m
    FROM customers
)
SELECT CASE
           WHEN r = 5 AND f >= 4 THEN 'Champions'
           WHEN r BETWEEN 3 AND 4 AND f >= 4 THEN 'Loyal customers'
           WHEN r >= 4 AND f BETWEEN 2 AND 3 THEN 'Potential loyalists'
           WHEN r = 5 AND f = 1 THEN 'New customers'
           WHEN r = 4 AND f = 1 THEN 'Promising'
           WHEN r = 3 AND f = 3 THEN 'Need attention'
           WHEN r = 3 AND f <= 2 THEN 'About to sleep'
           WHEN r <= 2 AND f BETWEEN 3 AND 4 THEN 'At risk'
           WHEN r <= 2 AND f = 5 THEN 'Cannot lose them'
           WHEN r <= 2 AND f <= 2 THEN 'Hibernating'
       END AS segment,
       count(*) AS customers,
       round(sum(net_spend), 2) AS net_spend,
       round(100.0 * sum(net_spend) / sum(sum(net_spend)) OVER (), 1) AS share_of_spend_pct
FROM scored
GROUP BY 1
ORDER BY net_spend DESC

Caveats

Scores are fifths of this retailer's own customers over the full Dec 2009 to Dec 2011 history, scored as of Dec 10, 2011. Mostly wholesale buyers, so order counts and spend run high. Customers without a Customer ID (22.2% of clean rows) are in no segment.
The segment names are a common convention for a recency by frequency grid, not the retailer's own.
Frequency and spend count the whole history, which favours customers who have been around longer. 20 customers have net spend of zero or less (cancellations cancel their orders) and score 1 on spend; 23 identified customers with only cancellations are not scored.
This is a snapshot: a customer moves between segments as time passes without an order.

Releases and revisions

This is a fixed historical dataset (as of December 2011); it is not revised.

03Sources

Sources and licence.

UCI Online Retail II Chen, D. (2012), UCI Machine Learning Repository. Licence CC BY 4.0.

Licence: CC BY 4.0. Public data.

Download the chart data

Each CSV is made in your browser from the rows in that chart's table.

Chart 1: Segments
Chart 2: Recency by frequency
Chart 3: Spend per customer
Chart 4: Recency
Chart 5: Spend score

All twenty-eight analyses on the Dashboard

04Contact

Want this kind of breakdown on your own data?

Open to e-commerce, data and operations roles. Los Angeles or remote.

zacharybhenley@gmail.com Book a 30-minute call

Which customers bring the money, and which are slipping away?

Key figures

The charts

What I would do with each segment.

What the charts say.

How the figures were built.

Steps

Checks

The queries

Caveats

Releases and revisions

Sources and licence.

Download the chart data

Want this kind of breakdown on your own data?