Hypermarket stock analytics
Reporting for a construction and household goods hypermarket, where the catalogue was large enough that the reporting problem was mostly a master data problem.
The problem
Fifty thousand active SKUs across construction materials, plumbing, hardware and household goods, spread over several store formats. Stock reports existed but took two days to produce and were stale by the time anyone read them. Nobody could answer, on any given morning, which categories were overstocked and which were about to run out.
Approach
I rebuilt the extraction layer first — direct SQL against the 1C tables rather than manual exports — then shaped it in Power Query into a model the category teams could pivot themselves. The important design decision was to keep the model narrow: movements, product, store, date, and nothing else. Every request to add a field was answered by asking which decision it would change.
- Daily stock position by store and category, refreshed automatically
- Slow-mover flags based on days of cover rather than absolute volume
- Master data checks that surfaced duplicate and mis-categorised SKUs weekly
Outcome
Report assembly went from two days to a refresh. More usefully, the weekly master data check turned catalogue hygiene from an annual clean-up into something that happened continuously — which is the only version of it that holds at 50,000 SKUs.