A diagnostic analytics project simulating a Zepto/Blinkit-style quick-commerce business, built to answer one question:
Where are the hidden operational gaps between what customers want and what the business actually delivers?
This project is not an analysis of real Zepto or Blinkit data. Since real company data isn't publicly available, a realistic 150,000-search / 78,936-order dataset was generated to simulate the operational reality of a quick-commerce platform — customers, products, inventory, dark stores, deliveries, fees, and support tickets.
The goal wasn't to build another KPI dashboard. It was to demonstrate a complete analyst workflow:
Simulated Data → Python Cleaning → BigQuery SQL Investigation → Root-Cause Analysis → Power BI Diagnosis → Business Recommendations
Overview of order volume, revenue, delivery speed, product availability, and which dark stores carry the most revenue at risk.
Where demand is being lost: zero-result searches, late deliveries, cancelled orders by area, and the categories most affected by inventory gaps.
Turns diagnosis into action: refund amounts, resolution times, peak-hour delay spikes by store, and specific resolution actions by support ticket.
54.28% of all customer searches resulted in either Product Unavailable or Limited Options — only 44.32% of searches found full availability. This is the single largest demand-supply gap in the dataset, and it's concentrated in specific SKUs: Cola (78.4%), Sanitary Pads (75.9%), and Power Bank (71.3%) availability-problem rates.
One dark store, DS009, stood out sharply from the rest of the network:
| Metric | DS009 | Network Average |
|---|---|---|
| Avg. Delivery Time | 31.69 min | 22.12 min |
| Avg. Delay | 12.03 min | 4.13 min |
| On-Time Rate | 6.13% | ~41-43% |
A full root-cause investigation was run across four possible explanations — peak-hour load, distance to customer, order workload, and picking/packing time — and all four were ruled out. DS009 remains an unexplained operational anomaly with the data available, flagged as a priority for manual, on-the-ground investigation.
Wrong Item and Missing Item complaints were analyzed by category. Women's Clothing had the highest complaint count (625), nearly 50% more than the next-highest category (Travel Essentials, 423).
Support tickets were evenly spread across six recurring issue types — Refund Requests and Wrong Item complaints tied as the most common (1,076 tickets each), followed by Missing Item, Late Delivery, Product Quality, and Fee Complaints. Cancellation rate was also tested as a potential driver but showed too little variation to be a major finding.
| Stage | Tool | Purpose |
|---|---|---|
| Data Generation & Cleaning | Python (Google Colab) | Generated realistic quick-commerce data, validated business rules, checked nulls/duplicates |
| Business Analysis | BigQuery (SQL) | 26 saved queries investigating order, delivery, revenue, product, and support patterns |
| Visualization | Power BI (Import mode) | 3-page interactive diagnostic dashboard |
├── notebooks/
│ └── Data_On_Demand.ipynb # Data generation, cleaning, validation, BigQuery upload
├── sql/
│ └── 02–27_*.sql # 26 BigQuery analyses, each with Purpose + Result comments
├── screenshots/
│ └── dashboard-*.png # Power BI dashboard pages
└── README.md
All 26 queries are organized in /sql, each file documented with its business purpose and result inline. A few worth reading first:
13_search_result_percentage.sql— the query behind the 54.28% availability-gap headline finding16_dark_store_performance.sql→24_ds009_vs_network.sql— the full DS009 root-cause investigation chain (queries 17, 21, 22, 23 rule out peak hours, distance, workload, and picking/packing in sequence)27_order_accuracy_by_category.sql— order accuracy complaints by product category
This project isn't really about Zepto or Blinkit — it's a demonstration of a complete analyst workflow: taking a messy, realistic business problem and working through Data → Investigation → Root-Cause Analysis → Business Insight → Visual Diagnosis → Actionable Recommendation.
That's the difference between a portfolio project and a plain EDA dashboard.
Chaitali Ranalkar LinkedIn