DATA WAREHOUSING & ANALYTICS
Retail Events Data Warehouse
An end-to-end business intelligence solution transforming raw promotional data into strategic insights using the Medallion Architecture.
The Architecture Behind the Analysis
I implemented the industry-standard Medallion Architecture, creating a robust data pipeline that transforms raw CSV ingestions into business-ready intelligence.
Bronze Layer
Raw data ingestion. Captures unaltered data to preserve lineage and enable reprocessing.
Silver Layer
Data refinement. Validates against business rules, resolves inconsistencies, and creates reliable datasets.
Gold Layer
Business intelligence. Dimensional models (Star Schema) and aggregated views optimized for reporting.
Dimensional Data Modeling
The Gold Layer is built on a robust Star Schema designed for performance and usability.
- Fact Table: Centralized transactional records enabling granular analysis of sales events.
- Dimension Tables: Optimized lookups for Products, Campaigns, and Stores to support slicing and dicing.
- Performance: Indexed views and smart partitioning for efficient querying of large datasets.
Key Analytical Findings
BOGOF Dominance
"Buy One Get One Free" promotions dramatically outperformed others, generating over 200,000 units in sales. Interestingly, 50% OFF promotions showed lower effectiveness despite equivalent value, highlighting consumer psychology.
Category Leaders & Opportunities
Grocery & Staples account for 56.6% of sales. However, the Personal Care category, while smaller (7.2%), includes high-performers like "Atliq Lime Cool Bathing Bar", suggesting an untapped high-margin opportunity.
The "Festival Effect"
The Diwali campaign generated 153,338 units—over twice the volume of Sankranti (73,085 units). This 110% difference underscores the critical importance of aligning resources with cultural peaks.
Geographic Concentration
Top 3 cities (Bengaluru, Chennai, Hyderabad) drive 60% of sales. The steep drop-off to mid-tier cities signals a clear potential for targeted expansion strategies.
Strategic Recommendations
Promotion Optimization
Increase BOGOF promotions for high-velocity items. Reconsider 50% OFF strategies in favor of hybrid approaches that leverage the psychological appeal of "Free".
Category Expansion
Strategically expand Personal Care promotions. Create bundle offers pairing high-performing staples with higher-margin personal care items.
Resource Allocation
Implement a 2:1 budget ratio favoring Diwali over Sankranti. Develop specific product bundles for major festival peaks.
Geo-Targeting
Maintain presence in top metros while developing tailored expansion strategies for mid-tier cities showing growth signal.
Key Outcomes
"This project bridged the gap between raw data and business value, demonstrating the ability to architect scalable solutions and translate technical findings into concrete strategies."