Case 6 · 3 levels
Slow movers in the catalogue
Merchandising wants a shortlist of active products that barely sell. Frame the question, sort out which products can be judged at all, rank them against their own category, and write the recommendation.
StartEvery catalogue collects products that nobody buys much. They take shelf space in the warehouse, photo slots on the site and a buyer’s attention. Once a year Merchandising reviews the range and asks the analyst for a shortlist: which active products barely sell? It sounds like a sort by sales, and that is exactly how it goes wrong: a sofa will always sell fewer units than a candle, and a product launched in October has had no time to sell at all.
This case is the whole job, the way you would do it at work:
- Frame the question: which products can be judged, over which period, by which measure, compared with what.
- Check the data: separate established, new and retired products, confirm what sold in the period, and find the products with no cost.
- Analyse, in SQL, pandas or Excel, whichever you prefer: each product’s share of its own category’s revenue, the weakest three in every category, and how much they add up to.
- Recommend: pick the paragraph you would send, with the numbers and the caveats.
The case is open from the start. It uses a LEFT JOIN, CASE labels and window functions (or merge,
np.select and groupby with transform in pandas, or COUNTIF and a share column in Excel); the
lessons walk through each.
Levels
- Frame the slow movers question
from Thomas Whitaker, Head of Merchandising
smart goal, problem framing, like for like, within group comparison
- Find the slow movers
from Thomas Whitaker, Head of Merchandising
data validation, left join, case when, window functions, row number, percent of total
- Recommend: which products to review?
from Thomas Whitaker, Head of Merchandising
recommendation, caveats, within group comparison, seasonality, data storytelling