Store-Level Loss & Sales ETL

Context

Our store lacked visibility into security incidents, theft hot-spots, and boycott-driven sales swings.

Role

• Owned the analysis end-to-end: SQL data modeling/ETL, anomaly detection, and reporting.

Approach

• Merged incident, sales & HR tables in SQL; automated KPIs via views and stored procedures.
• Built Python dashboards mapping theft vs. sales, tagged by format, state and boycott timeline.
• Applied anomaly detection to spotlight outlier stores and employees.

Impact

• Identified an outlier store cluster averaging 14 incidents/store (~4–5× higher than peers).
• Flagged multiple regions as top theft hot spots (up to ~$991/store/day).
• Quantified boycott hit: –28.7% (May ’23), –11.6% (Jun ’23), –60.2% (Jul ’23) YoY sales.
• Surfaced a small set of high-risk associates (anonymized) for empty-package reports; one outlier averaged $249/item on just 2 items.

Links

GitHub PDF Notebook

Notes

Store, state, and employee identifiers are anonymized in the case study.