Featured case study
Rossmann Performance Insights
844,338 sales records across 1,115 German drugstore locations, structured around 8 business questions a retail analytics team would actually get asked - not a Kaggle leaderboard exercise.
Business problem
Regional leadership wanted to know which stores were quietly underperforming, whether promotions were worth the spend, whether nearby competitors were actually hurting revenue, and which stores deserved the next round of investment. Four separate questions, one dataset.
Dataset
1,017,209 raw daily store records, January 2013 through July 2015, across 1,115 stores. 844,338 remained after removing closed-store days. Public on Kaggle, but I treated it as a live business problem rather than a leaderboard submission.
My role
Solo, start to finish - data cleaning in Python, 15 analytical SQL queries in PostgreSQL, and a 4-page Power BI dashboard with 14 DAX measures.
Methodology
Window functions carried most of the analysis: NTILE for quartile ranking, LAG/LEAD for period-over-period comparisons, PERCENT_RANK and PERCENTILE_CONT for benchmarking, STDDEV for Z-score anomaly detection. Expansion candidates were scored on a weighted composite - 30% revenue, 35% YoY growth, 20% promo responsiveness, 15% basket size.
Two mistakes I caught before presenting anything
The Sunday problem
Raw day-of-week averages made Sunday look like the worst day in the chain, around €2,100. Only 33 of 1,115 stores actually trade on Sundays - comparing 33 stores to the full chain on every other day isn't a fair comparison. Once isolated, those 33 stores average €8,224, almost exactly matching Monday's chain-wide €8,216. Monday is the real best day.
The 2015 partial-year problem
Comparing raw annual totals, 2015 looked like a 36% collapse from 2014 - €1.39B vs €2.18B. But the 2015 data stops in July: seven months against twelve. Switched the comparison to average daily sales per store instead, and 2015 was actually up 9.7% year over year.
WITH store_stats AS (
SELECT store, AVG(sales) AS mean_sales, STDDEV(sales) AS std_sales
FROM rossmann_sales
WHERE open = 1
GROUP BY store
),
daily_zscore AS (
SELECT s.store, s.date, s.sales,
ROUND((s.sales - st.mean_sales) / NULLIF(st.std_sales, 0), 3) AS z_score
FROM rossmann_sales s
JOIN store_stats st ON s.store = st.store
WHERE s.open = 1
)
SELECT store, COUNT(*) FILTER (WHERE ABS(z_score) > 3) AS extreme_anomaly_days
FROM daily_zscore
GROUP BY store
HAVING COUNT(*) FILTER (WHERE ABS(z_score) > 3) > 5
ORDER BY extreme_anomaly_days DESC;Key findings
- Promotions lift daily sales 39% on average, but Type a stores respond at 43% vs. 18% for Type b - even promo budget spread across store types is leaving money on the table.
- Stores within 500m of a competitor average €7,611/day vs. €6,677 for stores 500m–1km out. High-competition zones are usually high-footfall zones too.
- December runs about 30% above the annual average every year - staffing and inventory planned off annual averages will miss both the peak and the trough.
Business impact / recommendations
- Reallocate promo spend toward Type a and assortment-c stores, which respond hardest to promotions.
- Prioritize Type b store openings - highest basket size (€11.3) and fastest YoY growth (6.48%).
- Target high-footfall competitive zones for new sites instead of avoiding them.
- Review the bottom 630 stores for assortment upgrades before committing CapEx.
Challenges
The promo lift figure doesn't control for seasonality - a December promo isn't the same as a July one, and the 39% figure is a portfolio average across all months. Type b's numbers rest on only 17 stores, a real signal but not a statistically confident one for a capital decision.
Lessons learned
Running a query and reporting the output isn't the job - checking whether the output makes sense is. Both corrections above came from asking "does this number make sense given what I know about the business" before it went anywhere near a dashboard.
