Flagship analysis · E-commerce and inventory · Public data
Finding stock that stopped selling
Which products had no sale in the last 90 days, what did they bring in, and does the busy autumn hide more stops?
Autumn 2010 revenue from products with no sale in autumn 2011
£254K; 794 of 3,251 products, Sep 11 to Dec 9
Products with no sale in autumn 2011 brought in 7.3% of autumn 2010 revenue: 794 of 3,251 products. Against the prior 90 days instead, the share is 1.1%, because autumn is the busy season. Sales data, not stock levels.
Products with no sale in autumn 2011 brought in 7.3% of autumn 2010 revenue: 794 of 3,251 products. Against the prior 90 days instead, the share is 1.1%, because autumn is the busy season. Sales data, not stock levels.
1,567 of 4,725 products had no sale in the last 90 days.
All products4,725products, one block each; height = two-year net revenue, log scale
How to read this model
One block per product with a sale, grouped by what it did in the final 90 days. Height = two-year net revenue (Dec 2009 to Dec 2011), log scale; £10 or less, including net zero or below, is the floor. Choose a group to stack the blocks to scale. UCI Online Retail II · public data
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?
Share of the earlier window's revenue, by what happened next. Autumn here means Sep 11 to Dec 9. Source: UCI Online Retail II, Chen, D. (2012), UCI Machine Learning Repository · CC BY 4.0 · public data
Why this question
At Dasalla, data analysis identified $300,000 in non-performing inventory in a $2,000,000 product portfolio, and I presented it to leadership.
Drawn to scale.
This page answers the same kind of question, end to end, on public data. It uses none of that company's data and is not a record of that work.
The data
- Source
- UCI Online Retail IIChen, D. (2012), UCI Machine Learning Repository
- Licence
- CC BY 4.0Public data
- Period
- Dec 2009 to Dec 2011Data as of December 2011, fixed dataset
- Rows
- 1,067,3711,021,254 after cleaning
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.
1,067,371 rows in, 1,021,254 kept.
Cleaning steps and definitions
- 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 (5,931 rows).
- Dropped rows with price <= 0 (6,151 rows); nearly all of these have no Customer ID and are internal adjustments, not customer sales.
- Dropped negative-quantity rows on invoices that do not start with 'C' (none left after the step above); a true return in this dataset is a cancellation invoice (prefix 'C').
- Removed 34,035 exact duplicate line items (after the rules above).
- Counts are taken in this order, so each row is counted once. In the raw file the rules overlap: 6,207 rows have a price of zero or less and 3,457 have a negative quantity outside cancellations.
- 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.
- 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.
Method
Every product with a sale lands in one of four groups, by what it did in the final 90 days of the data.
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.
Stopped1,567
Sold earlier, no sale in the final 90 days.
Slowing347
Sold in both windows and final-90 revenue under half of prior-90 revenue (prior-90 revenue above zero).
Steady or growing2,602
The rest that sold in the final 90 days.
New209
First sale in the final 90 days.
Products in each group, of 4,725 products with a sale. 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.
The SQL: products by what happened in the last 90 days
-- 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 1The query runs on the Live SQL table (13 months, Dec 2010 to Dec 2011), so its counts differ from this page's 25-month figures. It returns:
Open in Live SQL| product_group | products | revenue_gbp | pct_of_revenue |
|---|---|---|---|
| 1 Stopped | 640 | 186,205 | 1.9 |
| 2 Slowing | 347 | 737,143 | 7.5 |
| 3 Steady or growing | 2583 | 8,542,619 | 87.4 |
| 4 New | 228 | 305,561 | 3.1 |
Findings
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?
4,725 products with at least one sale, £18.93M of net revenue in all, Dec 2009 to Dec 2011. UCI Online Retail II · public data
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. 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
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?
| Product | Stock code | Last sale | Revenue, two years (£) |
|---|---|---|---|
| Tea time cake stand in gift box | 37503 | Aug 1, 2010 | £24,913 |
| Red retrospot traditional teapot | 21527 | May 15, 2011 | £23,635 |
| English rose design quilted throw | 84467 | Jul 27, 2010 | £17,398 |
| Set of 3 coloured flying ducks | 35004C | Mar 7, 2011 | £15,791 |
| Set/3 russian doll stacking tins | 85232B | Feb 11, 2011 | £14,084 |
| Grow your own plant in a can | 22444 | May 19, 2011 | £13,714 |
| Tea time tea towels | 47556B | Apr 18, 2011 | £13,312 |
| Paper bunting coloured lace | 22088 | Jul 6, 2011 | £12,879 |
| Large popcorn holder | 22198 | Feb 21, 2011 | £12,412 |
| White cherry lights | 79323W | Sep 16, 2010 | £11,628 |
Revenue to the nearest pound. A product relaunched under another code would show here as stopped. UCI Online Retail II · public data
What I would do next with this
- Add stock levels and costs, so each stopped product shows what is on hand and what it cost, not only what it used to bring in.
- Match stopped codes against new codes with the same description, so a relaunch is not counted as a stop.
- Run the same-season comparison on a third autumn before calling a product gone.
- Refresh the workbook each month as a list of stopped and slowing products, ranked by past revenue, to go through one by one.
Limits
- 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.
- 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.
- 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.
- 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.
- 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.
Checks: 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.
My role
- Directed the project and made the product and design decisions.
- Approved design changes as mockups before they shipped.
- Checked the facts in the claims about me.
Downloads and links
Contact
Have channels or inventory to sort out? Let's talk.
Open to e-commerce, data and operations roles. Los Angeles or remote.