Analysis 02 of 28E-commerce

When do customers come back, and when do they stop?

Of the customers whose first order was in a given month, what share bought again in each later month, how soon do the ones who return come back, and after how long without an order is a customer unlikely to return?

The short answer The biggest single drop comes after the first order: 27.6% of 5,852 identified customers bought once by Dec 9, 2011, while 77.7% of those with a second order placed a third. Customers new in 2010 or 2011 bought in the month after their first 21.0% of the time and between 13.5% and 21.5% in each later month to month 12; the December 2009 cohort, which includes established customers, ran between 33.3% and 49.6%. Returns come early: half of 2010's new customers who came back within a year did so within 71 days. After 120 days without an order the odds of another in the next six months are about even (51.5%), and after 180 days 40.3%.

Public data
Months observed to Nov 2011
Identified customers only

Data as of Dec 2011 · fixed dataset

Online retail breakdown Revenue, products and the first 12 cohorts, from the same orders.

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

72.4%Placed a second orderof 5,852 identified customers; 27.6% bought once
21.0%New customers who buy again the next monthcohorts Jan 2010 to Oct 2011, weighted; Dec 2009 cohort 35.0%
40.6%New customers back within 90 daysfirst purchase Jan 1 to Dec 9, 2010 (3,334 customers)
71 daysMedian days to come back2010's new customers who came back within a year
40.3%Order again within 6 months, after 180 quiet daysagainst 83.9% just after an order

Chart 1Lead chart · share still buying

New customers buy again far less often than the December 2009 cohort, which includes established customers: 21.0% in the next month, against 35.0%.

Of the customers who first bought in a given month, what share bought again k months later?

Each point is the share of a cohort that bought in that month after their first. Customers new from January 2010 (the cohorts of January 2010 to October 2011, 4,682 customers, weighted by size) bought in month 1 21.0% of the time and stayed between 13.5% and 21.5% to month 12. The December 2009 cohort (951 customers) includes customers already buying before the data starts, and runs between 33.3% and 49.6%.

Download CSV

UCI Online Retail II · public data

Retention curve: 12 rows. The weighted line adds up the buyers in that month across every cohort old enough to have reached it and divides by those cohorts' sizes, so later months rest on fewer, older cohorts (the last two columns say how many). Months 13 and later rest on 10 cohorts or fewer and are in chart 2. December 2011 is left out because it is partial.
Months since first purchase	Dec 2009 cohort (%)	New customers, cohorts Jan 2010 to Oct 2011, weighted (%)	Cohorts in the average	Customers in the average
Month 1	35.0	21.0	22	4,682
Month 2	33.3	21.5	21	4,461
Month 3	42.5	21.1	20	4,273
Month 4	37.9	19.4	19	4,166
Month 5	36.0	18.6	18	4,065
Month 6	37.6	18.4	17	3,957
Month 7	34.4	18.6	16	3,846
Month 8	33.8	16.4	15	3,740
Month 9	36.2	15.0	14	3,561
Month 10	42.2	13.5	13	3,436
Month 11	49.6	14.0	12	3,364
Month 12	37.6	18.4	11	3,288

Chart 2Retention by cohort, full history

Every cohort from December 2009 to October 2011, followed to November 2011.

How does each first-purchase month hold up, month by month?

Rows are first-purchase months, columns the months since. The Online retail page shows the first 12 cohorts to month 12; this is all 23 with every month observed. Among the new cohorts, month 1 ranges from 9.2% (December 2010) to 31.7% (October 2011).

Download CSV

UCI Online Retail II · public data

Every cohort: 23 rows. Share of each first-purchase cohort that bought in each later month, percent. Month 0 is the first month, 100%. Blank cells are months after November 2011, not yet observed. The first 12 rows to month 12 equal the published chart on the Online retail page, cell for cell.
First purchase	Cohort size	Month 0	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12	Month 13	Month 14	Month 15	Month 16	Month 17	Month 18	Month 19	Month 20	Month 21	Month 22	Month 23
2009-12	951	100.0	35.0	33.3	42.5	37.9	36.0	37.6	34.4	33.8	36.2	42.2	49.6	37.6	28.2	24.4	30.3	26.3	30.3	28.4	25.9	25.4	31.3	30.4	40.6
2010-01	368	100.0	21.5	32.1	31.5	27.2	31.0	26.9	23.4	28.5	32.6	31.0	17.9	22.8	17.9	19.6	15.2	24.5	20.4	19.3	20.1	25.3	19.8	25.3
2010-02	375	100.0	23.5	22.7	29.3	24.5	19.7	19.2	28.8	25.6	27.7	11.5	12.5	15.2	17.6	12.3	20.0	16.0	16.3	14.4	22.9	22.9	16.5
2010-03	441	100.0	19.0	23.1	24.3	23.1	20.4	24.7	30.6	27.7	10.9	11.6	14.5	20.0	16.3	20.0	17.0	17.5	15.6	17.7	20.4	21.3
2010-04	294	100.0	19.0	19.0	16.0	18.4	22.1	27.6	26.5	10.5	10.9	7.5	13.9	14.3	15.6	15.6	15.6	13.9	15.0	18.0	22.8
2010-05	255	100.0	15.7	16.9	17.6	17.6	25.5	21.2	12.5	5.9	8.2	11.4	13.3	15.3	15.3	9.8	12.5	13.7	16.5	15.3
2010-06	267	100.0	17.6	18.7	20.6	23.2	28.5	12.7	9.0	8.2	11.2	10.5	13.9	15.0	12.4	11.2	12.4	13.1	20.2
2010-07	185	100.0	15.7	18.4	29.7	29.2	14.1	11.4	14.6	14.6	11.4	13.5	14.6	13.0	13.5	19.5	17.3	23.8
2010-08	163	100.0	19.6	28.8	32.5	16.6	11.7	9.8	12.9	13.5	12.9	12.9	11.7	14.7	17.2	17.2	19.6
2010-09	239	100.0	23.0	23.4	13.0	8.8	10.5	13.8	9.6	13.0	13.0	12.1	10.0	22.6	16.7	16.7
2010-10	375	100.0	25.9	14.7	12.5	8.8	8.3	13.1	13.9	10.7	9.3	10.7	12.8	19.2	23.2
2010-11	326	100.0	17.5	9.5	9.5	7.7	8.9	12.9	10.1	8.6	9.2	11.0	14.7	25.2
2010-12	76	100.0	9.2	5.3	9.2	11.8	6.6	5.3	9.2	5.3	2.6	9.2	19.7
2011-01	72	100.0	16.7	22.2	19.4	22.2	15.3	15.3	12.5	11.1	20.8	25.0
2011-02	125	100.0	16.0	16.0	18.4	21.6	16.0	16.0	15.2	15.2	18.4
2011-03	179	100.0	17.9	21.8	20.1	22.3	15.1	20.7	20.1	24.0
2011-04	106	100.0	24.5	19.8	19.8	17.9	23.6	17.9	25.5
2011-05	111	100.0	23.4	24.3	16.2	21.6	20.7	26.1
2011-06	108	100.0	23.1	21.3	26.9	20.4	27.8
2011-07	101	100.0	20.8	29.7	27.7	34.7
2011-08	107	100.0	26.2	29.9	26.2
2011-09	188	100.0	27.1	37.8
2011-10	221	100.0	31.7

Chart 3Order by order

The biggest drop is after the first order: 72.4% place a second.

Of the customers who placed a given number of orders, what share placed one more?

27.6% of 5,852 identified customers ordered once. Of those with a second order, 77.7% placed a third, and every later step keeps 80.0% or more.

Download CSV

UCI Online Retail II · public data

Repeat ladder: 10 rows. Orders are distinct invoices that are not cancellations, over the whole history. Customers who first bought late in 2011 had less time to reach the next step, so the first steps read slightly low.
Step	Placed the next order (%)	Customers with this many orders	Customers with one more
1st to 2nd	72.4	5,852	4,234
2nd to 3rd	77.7	4,234	3,289
3rd to 4th	80.0	3,289	2,630
4th to 5th	81.4	2,630	2,141
5th to 6th	83.4	2,141	1,786
6th to 7th	84.4	1,786	1,508
7th to 8th	85.5	1,508	1,290
8th to 9th	86.5	1,290	1,116
9th to 10th	86.1	1,116	961
10th to 11th	89.9	961	864

Chart 4Days to the second purchase day

Returns come early: 40.6% of 2010's new customers were back within 90 days.

How soon after their first purchase do customers come back?

Share of each group with a second purchase day within so many days of the first. Customers whose first purchase fell from January 1 to December 9, 2010 (3,334): 16.0% within 30 days, 40.6% within 90, 69.7% within a year; half of those back within a year came within 71 days. The December 2009 cohort (951): 59.3% and 89.4%.

Download CSV

UCI Online Retail II · public data

Time to come back: 9 rows. Share of each group back by then. New in 2010: first purchase from Jan 1 to Dec 9, 2010. Purchase days, not invoices: several invoices on one day count as one. Every customer here first bought by Dec 9, 2010, so each had at least 365 days to come back.
Back within	Dec 2009 cohort (%)	New in 2010 (%)
7 days	12.9	3.2
14 days	23.6	7.0
30 days	29.1	16.0
60 days	49.6	30.8
90 days	59.3	40.6
120 days	68.5	47.3
180 days	77.5	57.2
270 days	83.5	64.7
365 days	89.4	69.7

Chart 5Odds of another order after a quiet spell

After 180 days without an order, 40.3% order again within six months.

Once a customer has gone quiet for a while, how likely is another order?

For every purchase day from Dec 1, 2009 to Aug 31, 2010: the share followed by another order within the next 180 days, once the customer has already gone so long without one. Just after an order it is 83.9%; after 90 quiet days 59.2%, after 120 days 51.5%, after 180 days 40.3% and after 270 days 28.3%.

Download CSV

UCI Online Retail II · public data

When they stop: 7 rows. A purchase day stays in the base for a quiet spell if no later order came within it. Every purchase day used has at least 465 days of data after it, enough for the longest spell plus the window.
Days since the last order	Ordered in the next 180 days (%)	Purchase days still waiting	Followed by an order in the window
Just ordered	83.9	10,621	8,914
30 days	76.6	6,426	4,922
60 days	67.8	4,217	2,858
90 days	59.2	3,102	1,837
120 days	51.5	2,431	1,251
180 days	40.3	1,707	688
270 days	28.3	1,265	358

01Findings

What the charts say.

27.6% of 5,852 identified customers had not ordered again by Dec 9, 2011; once past the second order, 80.0% or more take each next step.
Chart 3
New customers buy in the month after their first 21.0% of the time, against 35.0% for the December 2009 cohort, which includes customers already buying before the data starts.
Chart 1 Chart 2
Returns come early: 40.6% of 2010's new customers were back within 90 days and 69.7% within a year; half of those back within a year came within 71 days.
Chart 4
The longer the silence, the lower the odds: 83.9% order within six months just after a purchase, 51.5% after 120 quiet days, 40.3% after 180.
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.
Customers: rows with a Customer ID, on invoices that are not cancellations. A month or a day counts once per customer however many invoices fall in it.
December 2011 (Dec 1 to 9) is left out as an observation month because it is partial; November 2011 is the last month observed.

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

Checks

The cleaning reproduces the live Online retail page: 1,067,371 source rows, 1,021,254 after cleaning, 5,852 identified customers, the 27.6% one-time share and all 156 cells of the published cohort chart.
Every number on this page was recomputed by separate code (verify_r4.py: plain Python reading the original UCI zip, with no DuckDB and no intermediate files) and matches.
The cohort and time-to-come-back queries below were run in DuckDB 1.5.5 and return the figures on this page.

The queries

This series is not in Live SQL. The data file behind every chart is data/analyses/customer_retention.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.

Cohort retention (DuckDB, the full cleaned history)

-- Cohort retention: share of each first-purchase month still buying k months later
WITH active AS (            -- one row per customer and month with a purchase
    SELECT DISTINCT customer_id, date_trunc('month', invoice_date) AS month
    FROM retail_lines
    WHERE customer_id IS NOT NULL AND NOT is_cancelled
      AND invoice_date < DATE '2011-12-01'        -- December 2011 is partial (to Dec 9)
),
cohort AS (
    SELECT customer_id, min(month) AS cohort_month FROM active GROUP BY 1
)
SELECT c.cohort_month,
       date_diff('month', c.cohort_month, a.month) AS months_since,
       count(*) AS customers,
       round(100.0 * count(*) / max(s.size), 1) AS retention_pct
FROM active a
JOIN cohort c USING (customer_id)
JOIN (SELECT cohort_month, count(*) AS size FROM cohort GROUP BY 1) s USING (cohort_month)
GROUP BY 1, 2
ORDER BY 1, 2

Time to come back (DuckDB)

-- Time to come back: days from a customer's first purchase day to the next one
WITH days AS (              -- several invoices on one day count as one purchase day
    SELECT DISTINCT customer_id, invoice_date::DATE AS d
    FROM retail_lines
    WHERE customer_id IS NOT NULL AND NOT is_cancelled
),
nexts AS (
    SELECT f.customer_id, f.first_day, min(d.d) AS next_day
    FROM (SELECT customer_id, min(d) AS first_day FROM days GROUP BY 1) f
    LEFT JOIN days d ON d.customer_id = f.customer_id AND d.d > f.first_day
    GROUP BY 1, 2
)
SELECT CASE WHEN first_day < DATE '2010-01-01' THEN 'Dec 2009 cohort'
            ELSE 'First purchase Jan 1 to Dec 9, 2010' END AS grp,
       count(*) AS customers,
       round(100.0 * count(*) FILTER (WHERE next_day - first_day <= 90) / count(*), 1) AS back_within_90_days_pct,
       median(next_day - first_day) FILTER (WHERE next_day - first_day <= 365) AS median_days_if_back_within_a_year
FROM nexts
WHERE first_day <= DATE '2010-12-09'          -- every customer here has at least 365 days to come back
GROUP BY 1
ORDER BY 1

Caveats

A UK online gift retailer selling mostly to wholesale customers, so repeat buying runs higher than at a typical consumer shop. Customers without a Customer ID are left out. The data starts on Dec 1, 2009, so the December 2009 cohort includes customers who first bought earlier.
The weighted curve's later months rest on fewer and older cohorts; the table under chart 1 gives the count for each month.
New customers here are customers first seen in the data from January 2010; some may have bought before December 2009 under the same Customer ID, which the data cannot show.
The repeat ladder covers the whole history, so customers who first bought in late 2011 had little time to reach a second order.
The December 2009 cohort is shown on its own because it mixes new and established customers; the live Online retail page includes it in its 12-cohort averages (23.3% at month 3, 19.6% at month 12).

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: Retention curve
Chart 2: Every cohort
Chart 3: Repeat ladder
Chart 4: Time to come back
Chart 5: When they stop

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

When do customers come back, and when do they stop?

Key figures

The charts

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?