Which customers actually drive chip sales, and did the new layout work?
Mainstream shoppers outspent Premium shoppers on chips, and two of three trial stores showed a real layout uplift. The third could not be separated from its own momentum.
THE PROBLEM
A supermarket chain sells chips in 272 stores and does not know who is buying them. Marketing spend is aimed at Premium shoppers because Premium sounds like where the money is. Meanwhile a new store layout has been trialled in three stores, and nobody can say whether the sales that followed were caused by the layout or would have happened anyway.
Both questions cost money to get wrong. One misdirects a marketing budget. The other risks a chain wide refit built on three stores and a hunch.
THE GOAL
- Identify which customer segments generate chip revenue, by total and by spend per customer, and say which deserve the marketing budget.
- Establish whether Premium shoppers really do spend more on chips.
- Determine whether the trial stores outperformed matched control stores by enough to justify a rollout.
DATA AND TOOLS
Transactions: 255,510 records, 272 stores, date, store number, loyalty card, product, quantity, total sales.
Customers: 72,637 unique, with lifestage and spending category (Budget, Mainstream, Premium).
Period: July 2018 to June 2019.
Public retail transaction dataset from an Australian supermarket chain. Independent analysis, not client work.
PROCESS
Cleaning
Excel, for initial preparation and joining the two datasets on loyalty card number.
Analysis
Google Sheets, for pivot tables and segmentation across lifestage and spending category. Excel proved unstable at this row count.
Trial testing
Trial stores 77, 86 and 88 compared against matched control stores 50, 106 and 165, chosen for similar pre trial sales and customer counts.
The build
A Power BI dashboard summarising segments and trial results. In progress.
KEY INSIGHTS
Older families spend the most per customer. Older singles and couples spend the most in total.
Average spend of $34.70 across 9,701 older families. Older singles and couples generated over $383,000 from a base of 14,501. Two different reasons to target two different groups.
Mainstream shoppers outspent Premium shoppers on chips.
$25.88 against $25.15 per customer, with Budget close behind at $25.25. The Premium label describes how people shop in general, not how they buy chips. The marketing budget has been pointed at the wrong shelf.
The layout worked in stores 77 and 86.
Both showed sustained sales increases against their matched controls, driven by higher spend per visit rather than by footfall alone.
Store 88 was already outperforming before the trial began.
Its pre trial results were strong and erratic. Nothing in the data separates the layout's effect from a trend that was there anyway. An inconclusive result is a result.
RECOMMENDATIONS
- Direct promotional spend at older families, older singles and couples, and young families. They combine spend per visit with scale.
- Move chip promotions away from Premium positioning and toward Mainstream and Budget shoppers, who are buying the category.
- Roll the new layout out to stores resembling 77 and 86.
- Extend the trial in store 88 by three months before deciding anything. Do not roll out on the strength of a store that was already winning.
WHAT THE NUMBERS SAID
Findings from the analysis. No recommendation from this project was implemented.
Highest average spend per customer, older families. A finding from the analysis, not a business outcome.
Highest total revenue, older singles and couples. A finding from the analysis, not a business outcome.
Mainstream average spend, above Premium at $25.15. A finding from the analysis, not a business outcome.
Trial stores with a clear layout uplift. A finding from the analysis, not a business outcome.
VISUALS
REFLECTION
Learned. The Premium result was the opposite of what everyone assumed, including me. A segment label that predicts general behaviour does not predict behaviour in a single category.
Differently. I would run the analysis in SQL rather than Google Sheets. 255,510 rows is past the point where a spreadsheet is the right instrument, and it made the trial comparison harder to reproduce than it should have been. Limitation. The store 88 result is not interpretable. Three trial stores is a small sample, the control stores were matched on pre trial sales and customer count but not on seasonality, and nothing here controls for what else was happening in those stores during the trial window.