BI over three years of sales: the pipeline that admits its limit
Reconstructing a retailer's sales history from exports accumulated over three years, and stating clearly which business questions that data can answer and which it cannot.
Tech stack
Context
A retailer had piled up sales exports from 2023 to 2026 in loose files, in formats that changed along the way. The brief was to turn that scattered history into a queryable model that could answer business questions.
What was reconstructed
- • 56,739 transactions and 107,314 sales lines reconstructed into a star schema on SQLite.
- • 2,378 canonical product keys, unifying the variants under which the same item was named across three years.
- • 3,688 anomalies detected and flagged for review instead of quietly corrected.
The uncomfortable finding
The pipeline itself reports that only 1.97% of sales value has an identified customer. In other words: any analysis of repeat purchase, retention or customer lifetime value would, with this data, be an invention. The system says so before anyone asks.
What I learned
That the best thing an analytics pipeline can do is delimit its own scope. A dashboard showing retention with two per cent of customers identified is not a dashboard: it is a hallucination with axes. Saying so costs one uncomfortable conversation and saves a series of wrong decisions.