HomeDashboardOnline retail

Analysis 01 of 10E-commerce

Which customers and products earn the money?

Which customers and products actually drive revenue, and where is money leaking to returns and one-time buyers?

The short answer A small share of products earns most of the money. The top 20% of products bring 78.5% of net revenue and the bottom 30% bring 0.9%. 27.6% of identified customers bought only once, and the cohorts that return settle near 23.3% at month three. Cancellations touch 15.8% of orders but only 3.6% of gross revenue, so the leak is handling cost more than lost revenue.

  • Public data, not my business
  • Last month partial: data ends Dec 9, 2011
Source
UCI Online Retail II, Chen, D. (2012), UCI Machine Learning Repository
Period
Dec 2009 to Dec 2011
Licence
CC BY 4.0
Charts
5, each with its data table and CSV

Key figures

  • £18.9MTotal net revenuenet of cancellations, Dec 2009 to Dec 2011
  • 15.8%Cancellation rateof orders (3.6% of gross revenue)
  • 27.6%One-time buyersof 5,852 identified customers bought only once
  • 78.5%Top 20% of productsshare of total net revenue

The charts

Chart 1Lead chart · trend

Monthly net revenue peaks in November, both years.

When does the money come in, and from where?

November is the strongest month in both 2010 and 2011, ahead of the holiday season, and the UK is most of the revenue in every month. The last month is partial: the data ends Dec 9, 2011.
Download CSV

UCI Online Retail II · public data, not my business

Monthly revenue: 25 rows. Net revenue after cancellations, by month and country group, as published in ecom.json (chart monthly_revenue). Total = UK + international.
MonthUK (£)International (£)Total (£)
2009-12709,523.8168,819.27778,343.08
2010-01476,901.60127,930.19604,831.79
2010-02438,359.5686,974.67525,334.23
2010-03660,101.6892,000.45752,102.13
2010-04546,198.4790,117.20636,315.67
2010-05515,043.8591,255.29606,299.14
2010-06575,530.0594,716.28670,246.33
2010-07535,657.6480,203.95615,861.59
2010-08568,740.2592,899.87661,640.12
2010-09716,277.26125,279.44841,556.70
2010-10925,064.91143,920.001,068,984.91
2010-111,220,153.09174,448.711,394,601.80
2010-12690,929.7567,160.75758,090.50
2011-01455,725.18123,130.01578,855.19
2011-02412,904.1186,560.24499,464.35
2011-03561,903.15117,426.06679,329.21
2011-04434,752.8547,309.08482,061.93
2011-05609,620.06121,384.71731,004.77
2011-06593,144.35130,700.79723,845.14
2011-07566,586.40110,225.68676,812.08
2011-08561,424.68139,865.25701,289.93
2011-09860,218.97151,120.771,011,339.74
2011-10876,926.44184,774.781,061,701.22
2011-111,258,907.88168,216.661,427,124.54
2011-12398,346.5442,053.02440,399.56

Chart 2Seasonality

Both years climb from September to a November peak.

Is the November peak a one-off?

The 2010 and 2011 lines follow the same shape: flat through summer, rising from September, highest in November. December 2011 is low only because the data stops on Dec 9.
Download CSV

UCI Online Retail II · public data, not my business

Month of year: 12 rows. The Total column of chart 1, regrouped by calendar year. December 2011 covers Dec 1 to 9 only.
Month2010 total (£)2011 total (£)
Jan604,831.79578,855.19
Feb525,334.23499,464.35
Mar752,102.13679,329.21
Apr636,315.67482,061.93
May606,299.14731,004.77
Jun670,246.33723,845.14
Jul615,861.59676,812.08
Aug661,640.12701,289.93
Sep841,556.701,011,339.74
Oct1,068,984.911,061,701.22
Nov1,394,601.801,427,124.54
Dec758,090.50440,399.56

Chart 3Breakdown by segment

Most new customers do not come back the next month.

Of the customers who first bought in a given month, how many keep buying?

At month 3, 9.5% to 42.5% of a cohort is still buying; the average across the 12 cohorts is 23.3% at month 3 and 19.6% at month 12. Customers without a Customer ID are left out of this view.
Download CSV

UCI Online Retail II · public data, not my business

Cohort retention: 12 rows. Share of each first-purchase cohort that bought again in each later month, percent, as published (chart cohort_retention). Month 0 is the first month, 100%.
First purchaseCohort sizeMonth 0Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12
2009-129511003533.342.537.93637.634.433.836.242.249.637.6
2010-0136810021.532.131.527.23126.923.428.532.63117.922.8
2010-0237510023.522.729.324.519.719.228.825.627.711.512.515.2
2010-034411001923.124.323.120.424.730.627.710.911.614.520
2010-0429410019191618.422.127.626.510.510.97.513.914.3
2010-0525510015.716.917.617.625.521.212.55.98.211.413.315.3
2010-0626710017.618.720.623.228.512.798.211.210.513.915
2010-0718510015.718.429.729.214.111.414.614.611.413.514.613
2010-0816310019.628.832.516.611.79.812.913.512.912.911.714.7
2010-092391002323.4138.810.513.89.6131312.11022.6
2010-1037510025.914.712.58.88.313.113.910.79.310.712.819.2
2010-1132610017.59.59.57.78.912.910.18.69.21114.725.2

Chart 4Ranking

The top 10% of products earn 62.3% of net revenue.

How concentrated is revenue across the catalog?

Products ranked by net revenue and cut into tenths. The top 10% bring 62.3% and the top 20% bring 78.5%. The bottom 30% together bring 0.9%.
Download CSV

UCI Online Retail II · public data, not my business

Product concentration: 10 rows. As published (chart product_pareto). The bottom 30% share, 0.9%, is 100 minus the cumulative share after the 7th tenth (99.1%).
Products (by revenue rank)Net revenue in this tenth (£)Cumulative share of revenue (%)
Top 10%11,792,519.4262.3
11 to 20%3,072,605.9578.5
21 to 30%1,640,235.6287.2
31 to 40%991,813.9092.4
41 to 50%623,835.7495.7
51 to 60%400,582.4097.9
61 to 70%240,647.6699.1
71 to 80%115,863.4499.7
81 to 90%42,385.52100
91 to 100%6,946.00100

Chart 5Breakdown by segment

Cancellations touch many orders but little revenue.

Is the money leaking out through cancellations?

In both groups, 15 to 20% of orders are cancellations but only 3 to 4% of gross revenue is cancelled. The leak is mostly handling cost, not lost revenue.
Download CSV

UCI Online Retail II · public data, not my business

Cancellations: 2 rows. As published (chart cancellation_impact): cancelled invoices as a percent of all invoices, and cancelled revenue as a percent of gross revenue, Dec 2009 to Dec 2011.
Country groupShare of orders cancelled (%)Share of gross revenue cancelled (%)
UK15.43.8
International19.63

01Findings

What the charts say.

  1. Revenue peaks in November in both years, and the shape repeats from one year to the next.

    Chart 1 Chart 2

  2. The top 20% of products bring 78.5% of net revenue (the top 10% alone, 62.3%); the bottom 30% bring 0.9%.

    Chart 4

  3. 27.6% of 5,852 identified customers bought only once. Across the 12 cohorts, an average of 23.3% are still buying at month three and 19.6% at month twelve.

    Chart 3

  4. Cancellations hit 15.8% of orders but only 3.6% of gross revenue, and the pattern holds for the UK and international groups.

    Chart 5

02Method

How the figures were built.

Steps

  1. 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.
  2. Dropped rows with price <= 0 (6,207 rows); nearly all of these have no Customer ID and are internal stock corrections, not customer sales.
  3. 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.
  4. Removed 34,035 exact duplicate line items (after the rules above).
  5. 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.
  6. Published table covers Dec 2010 to Dec 2011 (13 months) at invoice-line level to stay under the file size cap; the monthly revenue and cohort retention charts use the full Dec 2009 to Dec 2011 history, computed in the prep script and pre-aggregated into the chart data.

1,067,371 rows in the source file, 1,021,254 after cleaning. Every derived figure on this page (changes, indexes, averages, counts) is computed from the published rows, and the table under each chart says how.

Checks

  • Revenue nets out cancellations: a cancellation invoice (prefix C) carries negative revenue.
  • The charts use the full Dec 2009 to Dec 2011 history, aggregated in the prep script; the Live SQL table holds Dec 2010 to Dec 2011 at line level to stay under the file size cap, so queries there cover 13 months.
  • Each table on this page is the published chart data in ecom.json, shown row for row.

The queries

The query runs on the table retail_orders (531,205 order lines, Dec 2010 to Dec 2011). Open it in Live SQL, which runs DuckDB in your browser on the same public tables; the query loads ready to run.

Revenue by product quintile (the Pareto check)

Open in Live SQL 
WITH prod_rev AS (
    SELECT stock_code, sum(line_revenue) AS rev
    FROM retail_orders
    GROUP BY 1
),
ranked AS (
    SELECT rev, ntile(5) OVER (ORDER BY rev DESC) AS quintile
    FROM prod_rev
)
SELECT quintile, count(*) AS n_products, round(sum(rev), 2) AS revenue,
       round(100.0 * sum(rev) / sum(sum(rev)) OVER (), 1) AS pct_of_total_revenue
FROM ranked
GROUP BY 1
ORDER BY 1

Monthly net revenue

Open in Live SQL 
SELECT strftime(date_trunc('month', invoice_date), '%Y-%m') AS month, round(sum(line_revenue),2) AS net_revenue FROM retail_orders GROUP BY 1 ORDER BY 1

Top 10 products by net revenue

Open in Live SQL 
SELECT stock_code, round(sum(line_revenue),2) AS net_revenue, count(*) AS line_items FROM retail_orders GROUP BY 1 ORDER BY 2 DESC LIMIT 10

Order cancellation rate, top 10 countries

Open in Live SQL 
SELECT country, round(100.0*count(distinct CASE WHEN is_cancelled THEN invoice END)/count(distinct invoice),1) AS pct_orders_cancelled, count(distinct invoice) AS n_invoices FROM retail_orders GROUP BY 1 ORDER BY n_invoices DESC LIMIT 10

Caveats

  • This is a real UK-based online gift retailer selling mostly to wholesale customers, not a typical consumer DTC shop, so basket sizes and repeat-purchase patterns skew larger and more B2B than a typical online store.
  • The last month is partial: the data ends Dec 9, 2011.
  • Customers without a Customer ID (22.2% of clean rows) count toward revenue and products but not toward cohorts.

Releases and revisions

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

03Sources

Sources and licence.

Licence: CC BY 4.0. Public data, not my business.

Download the chart data

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

All ten 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