15. Final Project: Blinkit Maharashtra Monthly Report
15.1 Project Brief and Data
Brief. Blinkit and Amazon Now (fictional data) want one monthly report for Ganeshotsav month, September 2026, covering the six cities. The report is for Ravindra Bagale (regional lead) and the six city managers. It must answer:
- What were Net Sales, Delivered Orders, AOV and Cancellation Rate for the month?
- Which city and which area contributed most? Which is below target?
- How did daily sales move during Ganeshotsav (festival spike expected from 14-09-2026, the start of the festival in this fictional data)?
- Which categories (Dairy, Fruits, Sweets, Pooja Items, Snacks…) sold most per city?
- Are deliveries within the 12-minute promise in every city?
Input files (you create them with fictional rows):
| File / sheet | Rows (suggested) | Columns |
|---|---|---|
Raw_Orders |
300–500 | Order ID, Order Date, City, Area, Store ID, Product, Qty, Amount, Delivery Mins, Status, Customer, Phone |
Stores |
12 (2 per city) | Store ID, Platform, City, Area, City Manager, Manager Email |
Products |
25 | Product, Category, Unit Price |
Targets |
6 | City, Monthly Target |
Use only these people: Ravindra Bagale, Shraddha Bagale, Ruhi Bagale, Shahrukh, Amir, Salman, Zoya, Ravina, Raja, Rani, with @example.com e-mails. Areas: Pune (Kothrud, Hinjewadi, Baner, Hadapsar, Wakad), Nashik (College Road, Gangapur Road), Nagpur (Dharampeth, Sitabuldi), Kolhapur (Rajarampuri, Tarabai Park), Solapur (Hotgi Road, Murarji Peth), Sambhaji Nagar (CIDCO, Nirala Bazar).
Steps to prepare the raw data (deliberately messy)
- Type or generate the rows; then damage about 10% of them on purpose: extra spaces, "pune/PUNE", "Aurangabad" for Sambhaji Nagar, text amounts like "Rs. 450", US-style dates, 5–8 duplicate rows, a few blank Delivery Mins, phone numbers with +91 and spaces.
- Keep a copy of this untouched sheet as
Raw_Ordersand never edit it. - Write the five questions above on a
Notessheet with KPI definitions (Module 13.1).
Worked example – a few raw rows.
| Order ID | Order Date | City | Amount | Status |
|---|---|---|---|---|
| blk-9001 | 14-09-2026 | pune | Rs. 450 | delivered |
| BLK-9002 | 09/14/2026 | Aurangabad | 1,299 | Delivered |
| BLK-9001 | 14-09-2026 | pune | Rs. 450 | delivered |
Ravindra Bagale's Tip
Khup students project sathi aadhich clean data ghetat – mag interview madhe "what challenges did you face?" la kahich uttar nasta. Muddam messy data banva aani pratyek problem aani tyacha fix Notes sheet var lihun theva. Hech tumchya project cha khara "story" asto.
Practice task
Create the four input sheets with fictional data for September 2026 and introduce at least eight different data-quality problems into Raw_Orders. List each problem on the Notes sheet.