Data analytics project | Python · SQL · PostgreSQL
Analysed 3,731 SKUs from Zepto's e-commerce inventory dataset across 9 key attributes including pricing, stock levels, and product categories — performing data sanitisation, database schema design, and complex SQL queries to answer 14 business questions on inventory health, revenue estimation, and pricing strategy.
Inventory analytics directly impacts revenue. Every out-of-stock SKU is lost sales. Every over-discounted category is margin erosion. This project answers the questions a retail analyst or category manager would ask on day one.
- Project Overview
- Business Problem
- Dataset
- Project Workflow
- Key Findings
- Tools & Technologies
- Folder Structure
- How to Run
- Business Recommendations
- Skills Demonstrated
Zepto is a fast-growing Indian quick-commerce platform delivering groceries in under 10 minutes. This project analyses their product inventory to uncover pricing patterns, discount strategies, stock availability, and category-level performance using Python and PostgreSQL.
"How can Zepto optimise its inventory strategy, pricing, and product mix to improve revenue, reduce stockouts, and better serve customers across categories?"
| Attribute | Detail |
|---|---|
| Source | Kaggle — Zepto E-Commerce Inventory Dataset |
| Raw records | 3,732 SKUs |
| Clean records | 3,731 SKUs (1 zero-MRP product removed) |
| Categories | 14 |
| Attributes | 9 key fields |
Key features: Product Name · Category · MRP · Discounted Selling Price · Discount % · Available Quantity · Out of Stock · Weight (gms) · Quantity
- Loaded dataset and profiled structure — 3,732 rows, 9 columns
- Found 2 duplicate rows and 0 missing values
- Standardised all column names to snake_case
- Removed 1 zero-MRP product (invalid entry)
- Converted prices from Paise to Rupees (MRP mean was above 10,000)
- Added weight segmentation: Mini / Small / Medium / Large / Bulk
- Added price segmentation: Budget / Economy / Mid-Range / Premium / Luxury
- Exported clean dataset to CSV
- Connected Python to PostgreSQL using SQLAlchemy and psycopg2
- Loaded all 3,731 clean records directly into PostgreSQL database using to_sql()
- Verified data integrity in pgAdmin before running queries
Answered 14 business questions using advanced SQL:
| # | Business Question | Technique |
|---|---|---|
| 1 | Overall inventory summary | Multi-metric aggregation |
| 2 | Stock availability split | Window function · percentage |
| 3 | Top 10 categories by products | GROUP BY · AVG |
| 4 | Avg discount by category | GROUP BY · MAX · MIN |
| 5 | Revenue potential by category | SUM · proxy calculation |
| 6 | Product segmentation by weight | CASE WHEN · GROUP BY |
| 7 | Price range segmentation | CASE WHEN · GROUP BY |
| 8 | Top 5 discounted per category | CTE · ROW_NUMBER |
| 9 | Inventory health by category | CASE · OOS percentage |
| 10 | Best value categories | HAVING · availability rate |
| 11 | Revenue loss from stockouts | Conditional SUM |
| 12 | Best deals currently in stock | Multi-condition WHERE |
| 13 | Price variance by category | MIN · MAX · range calc |
| 14 | Zero discount products | CASE · percentage |
| Metric | Value |
|---|---|
| Total SKUs | 3,731 |
| Total categories | 14 |
| Avg MRP | Rs.156.84 |
| Avg discounted price | Rs.141.97 |
| Avg discount | 7.62% |
| In stock | 3,278 (87.86%) |
| Out of stock | 453 (12.14%) |
| Insight | Value | Business Action |
|---|---|---|
| Top category by products | Munchies & Cooking Essentials — 514 each | |
| Top revenue category | Cooking Essentials & Munchies — Rs.337,369 each | Prioritise restocking immediately |
| Highest avg discount | Fruits & Vegetables — 15.46% | Review margin sustainability |
| Most OOS products | Cooking Essentials & Munchies — 64 each | Reorder trigger needed |
| Insight | Value |
|---|---|
| Best deal in stock | Dukes Waffy Wafers — 51% discount |
| Best saving in stock | RRO Mascarpone Cheese — Rs.178 saving (50% off) |
| Widest price range | Cooking Essentials — Rs.10 to Rs.1,399 |
| Most zero-discount | Personal Care & Paan Corner — 38.95% products |
| Fully discounted | Fruits & Vegetables — 0% zero-discount products |
| Category | OOS Products | OOS % |
|---|---|---|
| Cooking Essentials | 64 | highest |
| Munchies | 64 | highest |
| Ice Cream & Desserts | 45 | high |
| Packaged Food | 45 | high |
| Chocolates & Candies | 45 | high |
| Category | Tools |
|---|---|
| Data Cleaning | Python · Pandas |
| Database Connection | SQLAlchemy · psycopg2 |
| Environment | VS Code |
| Database | PostgreSQL 18 |
| Querying | SQL (CTEs · Window Functions · CASE · HAVING · Aggregations) |
Zepto-Retail-Analytics/ │ ├── zepto_cleaning.py ├── zepto_analysis.sql ├── zepto_v2.csv ├── cleaned_data/ │ └── zepto_cleaned.csv ├── requirements.txt ├── .gitignore ├── LICENSE └── README.md
Step 1 — Clone the repository
git clone https://github.com/Khushi-Dhargawe/Zepto-Retail-Analytics.gitStep 2 — Install dependencies
pip install -r requirements.txtStep 3 — Run cleaning script
python zepto_cleaning.pyUpdate the DB credentials in the script before running:
DB_USER = "postgres"
DB_PASSWORD = "your_password_here"
DB_HOST = "localhost"
DB_PORT = "5432"
DB_NAME = "Zepto_db"Step 4 — Run SQL queries
Open zepto_analysis.sql in pgAdmin Query Tool and run queries one by one.
With sales velocity data I'd calculate days-of-inventory-remaining per SKU. With historical OOS timestamps I'd model stockout frequency and build a reorder point recommendation system.
- Restock Cooking Essentials and Munchies — 64 OOS products each, top revenue categories at Rs.337,369
- Increase discounts on Personal Care — 38.95% products have zero discount, below customer expectations
- Promote Fruits & Vegetables — highest avg discount 15.46%, strong value proposition for marketing
- Feature Dukes Waffy Wafers — best deal at 51% discount, ideal for promotional campaigns
- Review Biscuits pricing — max discount 51% but avg only 8.24%, inconsistent strategy needs review
- Leverage Economy segment — majority of products priced Rs.50-200, aligns with Zepto's target market
SQL PostgreSQL Python Pandas SQLAlchemy psycopg2
Database Design Data Cleaning Descriptive Analytics EDA
Inventory Analysis Product Segmentation Window Functions
CTEs CASE Statements HAVING Clause
Independent Project | April 2025
Connect with me on LinkedIn