Every grocery store runs promotions. Fewer check whether they made money. I took two years of store data, scored all 317 promotions against what each line would have sold anyway, and followed the margin all the way through.
The answer: shoppers bought 47% more on promotion, but only 5% of promos paid their way. Over two years they cost $10,154 of margin, and the biggest cost was not the extra units: it was the $8,934 discount given on units that would have sold at full price anyway.
Python · pandasbaseline modellingcannibalisationprice responsebasket analysisCRISP-DMsynthetic data
1 · The question behind the question
A store owner asks "how did the promo go?" and usually hears "we sold heaps". Units are the wrong scoreboard. The real question is: did this promotion leave the store with more margin than not running it?
Who needs the answer
The owner: which promos to keep, change or stop.
The category manager: how deep to discount, and which lines sit together.
The supplier conversation: how much funding a promo needs before the store agrees to it.
What success looks like
A keep, change or stop call for every line, a funding ask for the supplier, and a weekly short list of actions, all traceable to the numbers.
2 · What the data could and couldn't say
68,829daily sales rows by line and channel
50lines in 11 categories
317in-store promos, ~3 days at ~20% off
What I checked first
Some lines had more than one row per day: aggregated before anything else.
Days with no sales had no row at all: I built the full grid of every line on every day, so a zero is a zero, not a gap.
The data has daily totals, not till receipts. That limits what can be said about baskets, and the study says so.
3 · The baseline: what would have sold anyway
Everything hinges on the counterfactual. For each promo day, the baseline is the average units on the same weekday over the four weeks before, with promo days left out, so one promotion can't inflate the next one's baseline.
baseline = units.where(promo == 0) # promo days don't count
.shift(1) # only the weeks before
.rolling(4, min_periods=2) # four same weekdays
.mean() # per line, per weekday
Both actual and baseline margin use the same list unit cost, so the comparison is like for like.
4 · Following the margin all the way through
Each promotion's result splits into four parts. Added up across all 317:
+$4,103
−$8,934
−$3,551
−$1,773
−$10,154
Margin on the extra unitsDiscount on units that would have sold anywayTaken from other lines in the categoryThe dip in the 3 days afterNet
Why it loses
A 20% discount on a line with about 27% margin leaves about 9% margin per unit. To stand still, the promo needs roughly three times the usual sales. It got 1.47 times.
Two more models
Discount depth: each line's promo response from its own history, the margin a day at 5% to 30% off, and the share of the discount a supplier must fund to break even (for Craft Beer 6pk, 88%).
What sells together: support, confidence and lift on baskets. Because the data has no receipts, baskets were simulated from shopping missions and labelled that way: the method is real, the pairs are illustrative.
5 · How I know the numbers hold
Every promo nets out exactly: the four parts add to its net, checked by an automated test on all 317.
Totals tie: line, category and store totals agree to the dollar.
The depth model behaves: deeper discounts always sell more units, never fewer.
Privacy: no basket links to a person, card or loyalty number, also checked by a test.
What it can't tell you yet
No supplier funding data: a funded promo can be worth it even when this says it isn't.
Promo response mixes price and display; a clean price elasticity needs price tests without the end-cap.
Real till receipts would replace the simulated baskets.
6 · From analysis to Monday morning
The model runs inside Shelly, my open-source analytics agent, so the answer is a working tool, not a slide:
A Commercial lab page: the margin bridge, every line's keep, shallower or stop call, a discount slider and the supplier funding ask.
The Store Floor: tap a shelf to see its lines' promo verdicts and what they sell with.
Ask Shelly "are our promos worth it?" and get the number with a link to the evidence.
This week's actions it produced
Craft Beer 6pk: 7 promos lost $648. Go shallower, or ask the supplier to fund 88% of the discount.
Put Salsa beside Taco Kit: they sell together 11.75 times more than chance (simulated baskets).
Dinner-tonight shoppers are 18.5% of baskets but 36.9% of sales: make the meal bay the first thing they see after 4 pm.
With real data, next
Add supplier funding, run promotions as a simple test (alternate weeks or stores) to measure lift cleanly, and use real receipts for baskets.