01 · The challenge
Nine units sold. Sixty-three reported.
The business measures how products perform under different retail events (planograms, bestseller placements, and promotions) by joining point-of-sale data to event records.
When a product-store-week fell under several events at once, POS units were counted once per event row. In one real example, 9 units actually sold showed up as 63 across 7 overlapping rows. About 30% of all POS units fall in overlapping weeks, which was enough to distort any performance comparison.
02 · What I did
What I owned and built.
- Started with SQL that spread POS evenly across overlapping events using CTEs and window functions, and built step-by-step queries to surface real overlap cases.
- Designed a lift-weighted proportional allocation in PySpark: a baseline from non-overlapping weeks, then chain-level median lift weights from clean single-event weeks (planogram ≈ 0.89, bestseller ≈ 0.83, promotion ≈ 1.10) used to split the true units across overlapping rows.
- Fixed a duplication bug by correcting the join keys to chain, store, UPC, and week, and stabilized results by materializing the view as a table.
- Built an executive presentation package that explains the method in plain language, grounded in a single real record.
- Kept methodology claims accurate, including correcting an early statement about how two event types compared.
03 · Architecture
How it fits together.
Product · store · week
From clean single-event weeks
Units tie back to actual sales
04 · Key decisions
The judgment behind the build.
Why lift-weighted instead of an even split?
Events don’t contribute equally. Weighting by observed lift attributes units in proportion to how each event type actually behaves.
Why median lift?
Outlier weeks would skew a mean. Chain-level medians from clean single-event weeks keep the weights robust.
Why materialize the view?
Recomputing a complex view on every read produced unstable results and slow queries. A table made the numbers reproducible.
Why anchor the story in one record?
“9 became 63” lands with executives in a way a methodology slide never will.
05 · Impact
What changed because of the work.
- Removes inflation affecting about 30% of POS units, so totals tie back to what was actually sold.
- Event performance comparisons now reflect true sales instead of double counting.
- Leadership has a statistically defensible method they can understand.
06 · What’s next
Where it goes from here.
Resolve open stakeholder questions on week-level baseline granularity, seasonality, and whether to anchor planogram lift at 1.0.
This case study is a sanitized reconstruction. Organization names, proprietary code, data, and internal schemas are intentionally left out.