HomeDashboardSlow movers

Analysis 05 of 31 · E-commerce

Which products stopped selling, and how much did they used to bring in?

The short answer

7.3% Autumn 2010 revenue from products with no sale in autumn 2011

Share of the earlier revenue from products with no sale in the final 90 days

Autumn here means Sep 11 to Dec 9.

794 of 3,251 products that sold in autumn 2010 sold nothing in autumn 2011. In all, 1,567 had no sale in the last 90 days; over two years they brought in 8.3% of revenue. Sales data, not stock levels.

Which products had no sale in the last 90 days, what did they bring in, and does the busy autumn hide more stops?

  • Public data
  • Final 90 days: Sep 11 to Dec 9, 2011
  • Sales only, no stock levels

Data as of · fixed dataset

Key figures

  • 7.3%Autumn 2010 revenue from products with no sale in autumn 2011£254K; 794 of 3,251 products, Sep 11 to Dec 9
  • 8.3%Revenue from products that stopped selling£1.58M of £18.93M, Dec 2009 to Dec 2011
  • 1,567Products that stopped sellingof 4,725 products with a sale; none in the last 90 days
  • 1.1%Prior-90 revenue from products that then stopped206 products sold in the prior 90 days and not in the final 90

Chart 1 of 5

Lead chart · two ways to measure the same stop

Against the same weeks a year earlier, products that stopped held 7.3% of that season's revenue; against the prior 90 days, 1.1%.

Does comparing the final 90 days with the 90 days before them flatter the products?

It does. The final 90 days (Sep 11 to Dec 9) are this retailer's busiest season: they brought in 1.81 times the revenue of the prior 90 days, against 1.06 times the revenue of the same weeks of 2010. Against the prior 90 days, 206 products stopped (1.1% of that revenue). Against the same weeks of 2010, 794 of 3,251 products stopped (7.3% of that season's revenue) and 745 slowed (29.0%). Put another way, 794 of 3,251 products (24.4%) that sold in autumn 2010 sold nothing in autumn 2011. Autumn here means Sep 11 to Dec 9.

UCI Online Retail II · public data

Download CSV
Same season: 3 rows. Each column is the share of the earlier window's revenue, from products that sold in that window (3,006 products for the prior 90 days, 3,251 for Sep 11 to Dec 9, 2010). The same rule applies: stopped is no sale in the final 90 days; slowing is final-90 revenue under half. Autumn means Sep 11 to Dec 9.
GroupAgainst prior 90 days (% of revenue)Against same weeks 2010 (% of revenue)Products, prior 90 daysProducts, same weeks 2010
Stopped1.17.3206794
Slowing12.029.0347745
Steady or growing86.963.82,4531,712

Chart 2 of 5

Products against revenue

33.2% of products stopped selling, and they brought in 8.3% of two-year revenue.

How many products stopped selling, and how much did they bring in?

Each product with a sale lands in one of four groups. Stopped: sold earlier, no sale in the final 90 days (Sep 11 to Dec 9, 2011). Slowing: sold in both the prior 90 days and the final 90, and final-90 revenue is under half of prior-90 revenue. New: first sale in the final 90 days. Steady or growing: the rest that sold in the final 90 days. 1,567 products stopped, with £1.58M of revenue over two years; 347 are slowing and 209 are new.

UCI Online Retail II · public data

Download CSV
Four groups: 4 rows. 4,725 products with at least one sale, £18.93M of net revenue in all, Dec 2009 to Dec 2011. Revenue is net of cancellations. A product is a stock code, upper-cased.
GroupShare of products (%)Share of revenue (%)ProductsRevenue, two years (£)Revenue, prior 90 days (£)Revenue, final 90 days (£)
Stopped33.28.31,5671,575,750.8422,672.63-23.09
Slowing7.37.03471,331,743.44244,085.7373,264.55
Steady or growing55.183.02,60215,714,923.341,774,614.513,318,414.61
New4.41.6209305,281.910.00305,281.91

Chart 3 of 5

Stopped products by month of last sale

50.6% of the revenue from stopped products came from ones that last sold in 2011.

When did the stopped products last sell, and what did they bring in?

Each bar is the two-year revenue of the stopped products whose last sale fell in that month. 1,009 stopped products last sold before 2011 (£778,363). 558 last sold in 2011 (£797,387). The data starts in December 2009, so for products that last sold early on, it shows only the end of their sales.

UCI Online Retail II · public data

Download CSV
When they last sold: 22 rows. The 1,567 stopped products are counted once, in the month of their last sale. Sales between Sep 11 and Dec 9, 2011 would make a product not stopped, so the last bar is September.
Month of last saleRevenue, two years (£)Stopped products
2009-128,235.19130
2010-0118,728.9679
2010-0216,854.6257
2010-0364,274.66115
2010-0431,216.8561
2010-0542,591.8756
2010-0645,239.5458
2010-0751,643.7543
2010-0888,755.6673
2010-0989,404.1065
2010-1083,273.0678
2010-11120,304.40112
2010-12117,840.8082
2011-0179,933.7541
2011-0263,090.4036
2011-03108,607.2768
2011-04115,010.64100
2011-05153,288.6776
2011-0656,506.5064
2011-0778,474.2478
2011-0899,616.6172
2011-0942,859.3023

Chart 4 of 5

Top stopped products by past revenue

The ten stopped products with the most past revenue brought in £159,766; the biggest was the tea time cake stand in gift box.

Which stopped products used to bring in the most?

The ten stopped products with the most two-year revenue, with the description the data gives them most often. Together they brought in £159,766, 10.1% of the stopped group's revenue. The first is tea time cake stand in gift box (stock code 37503), £24,913, last sold Aug 1, 2010.

UCI Online Retail II · public data

Download CSV
The biggest ones: 10 rows. Revenue to the nearest pound. First sale is the first sale in the data, which starts on Dec 1, 2009, not the product's launch. A product relaunched under another code would show here as stopped.
ProductRevenue, two years (£)Stock codeLast saleUnits soldFirst sale
Tea time cake stand in gift box24,913375032010-08-012,4652009-12-01
Red retrospot traditional teapot23,635215272011-05-153,3452009-12-01
English rose design quilted throw17,398844672010-07-278162009-12-06
Set of 3 coloured flying ducks15,79135004C2011-03-073,0622009-12-01
Set/3 russian doll stacking tins14,08485232B2011-02-113,0862009-12-01
Grow your own plant in a can13,714224442011-05-1910,1322010-03-23
Tea time tea towels13,31247556B2011-04-185,2052009-12-01
Paper bunting coloured lace12,879220882011-07-064,3992009-12-01
Large popcorn holder12,412221982011-02-217,3312009-12-01
White cherry lights11,62879323W2010-09-163,0042009-12-01

Chart 5 of 5

The biggest slowdowns, even in the busy season

347 products slowed even in the busy season; the ten that fell most sold £64,374 less.

Which products are selling much less than they were?

Slowing products sold in both windows and brought in under half as much in the final 90 days as in the prior 90, in a season when the shop as a whole took 1.81 times as much. The 347 slowing products brought in £244,086 in the prior 90 days and £73,265 in the final 90. The chart shows the ten with the biggest fall.

UCI Online Retail II · public data

Download CSV
Slowing: 10 rows. Pounds to the nearest pound. Revenue is net of cancellations, so a product with many cancellations in a window can show a low figure for it.
ProductPrior 90 daysFinal 90 daysStock codeFall (£)
Party bunting34,18212,6204756621,563
Regency teapot roses14,5445,532231739,012
Large zinc heart wall organiser4,8338231344,825
Set 40 heart shape petit four cases5,773977232974,796
Regency sugar bowl green6,9032,459231744,444
Picnic basket wicker large5,132821225014,311
3 hook photo shelf antique white5,5971,327221714,270
Small zinc heart wall organiser4,317259231354,058
Assorted colours silk fan5,4831,788150363,695
Paper bunting retrospot6,2632,864220903,399

Limits · Read with care

What this data can't show.

ContextAt Dasalla Trading Company I asked the same kind of question: I identified $300,000 in non-performing inventory through data analysis and presented it to leadership. This page asks it of public data. It uses none of that company's data and is not a record of that work.

  1. How much was on hand. This data has sales, not stock levels. It cannot say what was on a shelf, what was bought in, or what a product cost.
  2. Why a product stopped. A stop can be a choice, a supplier problem, the end of a season or a new code for the same thing. Nothing here records which.
  3. Whether it is one product or two. One product can carry more than one stock code, and a relaunch under a new code looks like a stop.
  4. Costs, margins and returns. Revenue is what customers paid, net of cancellations. There are no costs, no margins and no returns to a supplier, so the pounds are not profit.
  5. A season or an ending. Two years cannot tell a product that rests every winter from one that is gone. Chart 1 compares the same weeks a year earlier, which helps and does not settle it.

Download

The product table, as a workbook.

All 4,725 products with a sale: first and last sale, revenue in each window, units, and the group as a formula, so you can change the dates or the half-revenue threshold and watch the groups move. Built with Claude under Zachary's direction. Public data: UCI Online Retail II (CC BY 4.0).

Download slow-movers-products.xlsx

Findings

What the charts say.

  1. Against the same weeks a year earlier, 794 of 3,251 products (24.4%) that sold in autumn 2010 sold nothing in autumn 2011; they had brought in 7.3% of that season's revenue, and 745 more slowed. Autumn here is Sep 11 to Dec 9.

    Chart 1

  2. Comparing the final 90 days with the 90 before them flatters the products, because the final 90 are the busy season (1.81 times the revenue): it finds 206 stops holding 1.1% of the prior 90 days' revenue.

    Chart 1

  3. 1,567 of 4,725 products (33.2%) had no sale in the final 90 days. They brought in £1,575,751 over two years, 8.3% of revenue.

    Chart 2

  4. Most are old stops: 1,009 last sold before 2011 (£778,363). The 558 that last sold in 2011 brought in £797,387, 50.6% of the stopped group's revenue.

    Chart 3

  5. The ten stopped products with the most past revenue brought in £159,766; the biggest, the tea time cake stand in gift box, brought in £24,913 and last sold Aug 1, 2010.

    Chart 4

  6. 347 products slowed even in the busy season: £244,086 in the prior 90 days, £73,265 in the final 90.

    Chart 5

Method

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 adjustments, 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').
  4. Removed 34,035 exact duplicate line items (after the rules above).
  5. A product is a stock code, upper-cased (so 85123a and 85123A are one product). One product can carry more than one code, or be relaunched under a new code, and the data cannot tell.
  6. A sale is a line on an invoice that is not a cancellation. Revenue is net of cancellations: cancellation lines (invoices starting with C) are negative and count against the date of the cancellation.
  7. Windows: final 90 days Sep 11 to Dec 9, 2011 (the last invoice is Dec 9 at 12:50); prior 90 days Jun 13 to Sep 10, 2011; the same weeks a year earlier Sep 11 to Dec 9, 2010.
  8. Stopped: sold earlier, no sale in the final 90 days. Slowing: sold in both windows and final-90 revenue under half of prior-90 revenue (prior-90 revenue above zero). New: first sale in the final 90 days. Steady or growing: the rest that sold in the final 90 days.
  9. 14 stock codes have only cancellation lines (net £263.88 below zero): they have no sale, so they are in no group and not in the totals.

1,067,371 rows in the source file, 1,021,254 after cleaning; 4,739 product codes after upper-casing, 4,725 with a sale. Every figure on this page comes from the chart tables in data/analyses/slow_movers.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 and 1,021,254 after cleaning; the last invoice is Dec 9, 2011 at 12:50.
  • Every number on this page was recomputed by separate code (verify_slow.py: plain Python reading the original UCI zip, with no DuckDB and no intermediate files) and matches, product by product.
  • The two queries below were run on the Live SQL table (13 months) in DuckDB 1.5.5 and return the figures shown under them; the full-history SQL was run on the cleaned table and returns the group counts and revenue on this page.
  • Query 1 on the Live SQL table (13 months) returns stopped: 640 products, £186,205, 1.9%; slowing: 347 products, £737,143, 7.5%; steady or growing: 2,583 products, £8,542,619, 87.4%; new: 228 products, £305,561, 3.1%. These differ from the page's 25-month figures because the table starts in December 2010.
  • Query 2 returns ten stock codes, the first three 47556B £6,045, 22783 £5,961, 22088 £5,512; the table has no product descriptions, so it shows codes.

The queries

The query runs on the table retail_orders (531,205 order lines, Dec 2010 to Dec 2011; it holds 13 months, so the counts these queries return differ from the page, which uses all 25). Open it in Live SQL, which runs DuckDB in your browser on the same public tables; the query loads ready to run.

Products by what happened in the last 90 days

Open in Live SQL 
-- Products by what happened in the last 90 days (Live SQL table: Dec 2010 to Dec 2011)
WITH p AS (
    SELECT upper(stock_code) AS code,
           min(invoice_date) FILTER (WHERE NOT is_cancelled) AS first_sale,
           count(*) FILTER (WHERE NOT is_cancelled AND invoice_date BETWEEN DATE '2011-06-13' AND DATE '2011-09-10') AS sales_prior,
           count(*) FILTER (WHERE NOT is_cancelled AND invoice_date >= DATE '2011-09-11') AS sales_final,
           sum(line_revenue) FILTER (WHERE invoice_date BETWEEN DATE '2011-06-13' AND DATE '2011-09-10') AS rev_prior,
           sum(line_revenue) FILTER (WHERE invoice_date >= DATE '2011-09-11') AS rev_final,
           sum(line_revenue) AS rev_total
    FROM retail_orders
    GROUP BY 1
    HAVING count(*) FILTER (WHERE NOT is_cancelled) > 0          -- at least one sale
), g AS (
    SELECT *,
           CASE WHEN sales_final = 0 THEN '1 Stopped'
                WHEN first_sale >= DATE '2011-09-11' THEN '4 New'
                WHEN sales_prior > 0 AND rev_prior > 0 AND rev_final < 0.5 * rev_prior THEN '2 Slowing'
                ELSE '3 Steady or growing' END AS grp
    FROM p
)
SELECT grp AS product_group,
       count(*) AS products,
       round(sum(rev_total)) AS revenue_gbp,
       round(100.0 * sum(rev_total) / sum(sum(rev_total)) OVER (), 1) AS pct_of_revenue
FROM g
GROUP BY 1
ORDER BY 1

Top ten products that stopped selling, by past revenue

Open in Live SQL 
-- The ten products that stopped selling and brought in the most (Live SQL table: Dec 2010 to Dec 2011)
SELECT upper(stock_code) AS code,
       max(invoice_date) FILTER (WHERE NOT is_cancelled) AS last_sale,
       round(sum(line_revenue)) AS revenue_gbp
FROM retail_orders
GROUP BY 1
HAVING count(*) FILTER (WHERE NOT is_cancelled) > 0
   AND count(*) FILTER (WHERE NOT is_cancelled AND invoice_date >= DATE '2011-09-11') = 0
ORDER BY sum(line_revenue) DESC
LIMIT 10

Caveats

  • A UK online gift retailer selling mostly to wholesale customers. The data has sales, not stock levels, and the shop's busiest season falls in the final 90 days.
  • The unit is the stock code. One product can carry more than one code, or be relaunched under a new code, and the data cannot tell, so some products here counted as stopped may be selling under another code.
  • The data starts on Dec 1, 2009. A product's first sale here is its first sale in the data, not its launch, and 3,009 of 4,725 products have a first sale in December 2009.
  • A gap of 90 days is not always an ending. Some products sell a few times a year, to a few wholesale customers, and a quiet quarter does not mean they are gone.
  • The final 90 days (Sep 11 to Dec 9) are the shop's busiest season, so the prior-90 comparison flatters the products. Chart 1 shows the same-season comparison; data covering two years cannot fully separate a season from an ending.
  • Slowing needs prior-90 revenue above zero, compared in whole pence (8 products sold in both windows but had prior-90 revenue of zero or less after cancellations, and sit in steady or growing). 149 products in steady or growing had no sale in the prior 90 days, only in the final 90.
  • 14 stock codes have only cancellation lines, so they have no sale and are in no group. The last day of data, Dec 9, 2011, ends at 12:50.

Releases and revisions

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

Sources

Data and licence

Source
UCI Online Retail II, Chen, D. (2012), UCI Machine Learning Repository
Period
Dec 2009 to Dec 2011 (final 90 days Sep 11 to Dec 9, 2011)
Licence
CC BY 4.0
Charts
5, each with its data table and CSV

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.

Related analysis · 01 of 31

Online retail analysis

Revenue and products over the same two years, from the same orders.

Open the analysis

All thirty-one analyses on the Dashboard

04Contact

Want this kind of analysis on your own data?

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

zacharybhenley@gmail.com Book a 30-minute call