5 sales files
Daily, city-level from three quick-commerce apps; monthly from one marketplace; a 12-line summary from the fifth.
Case study
From 9 messy platform files to one MIS the sales team can act on
Online sales and purchase-order fill-rate reporting for an FMCG confectionery brand selling on five quick-commerce and marketplace platforms.
Built on a real client's data. Client, brand, staff and distributor names are removed, products are named by type, and money and unit figures are rounded.
- online sales in one month, across 5 platforms
- ~₹9 Cr
- raw rows in 9 files and 45 tabs
- 1.5 lakh
- of ordered units actually supplied, once POs were matched
- 52%
- decisions the first month’s report pointed to
- 4
The engagement
Five platforms, and no single view of the month.
A confectionery brand with four sub-brands — crackers, chocolate sticks, coffee candy and biscuits — sold on Blinkit, Zepto, Swiggy Instamart, Flipkart and BigBasket.
Distributors supply the platforms’ warehouses: 27 of them, under 7 sales territories. Each platform raises purchase orders (POs) and reports sales its own way.
Nobody had one view of the month: what sold, where, and how much of what the platforms ordered was actually supplied.
01 · What we received
Nine files, about 1.5 lakh rows — and yes, one platform’s name was misspelt in its own file name.
4 PO files
Every purchase order the platforms raised in the month, and how much of it was received.
45 tabs
Too many rows for anyone to check by eye, and no two files built the same way.
Each platform sends the same numbers its own way
| Platform | Rows | Units column | Value column | Date column | City column |
|---|---|---|---|---|---|
| Blinkit | 55,772 | qty_sold | mrp (MRP value) | date | city_name |
| Zepto | 26,658 | Quantity | GMV | Sales Date | City |
| Swiggy Instamart | 58,654 | UNITS_SOLD | GMV | ORDERED_DATE | CITY, lower case |
| Flipkart | 3,447 | Units | Values | Month, as text | City or state |
| BigBasket | 12 | Sum of Total_quantity | Sum of Total_sales | none | none |
The four PO files added 6,240 more lines, with 21 different status words between them: COMPLETED, Expired, PENDING_GRN, approved…
02 · What they needed
Four questions the sales team wanted answered.
Sales head
How much did we sell, and where?
One number across five platforms, split by sub-brand, platform and city.
Territory managers and distributors
Are we supplying what they order?
Fill rate by platform, territory, distributor and SKU.
Supply chain
Where are orders being lost?
Unfilled PO value, expired POs, and the SKUs and distributors behind them.
Everyone
Can we trust it next month too?
Same rules every month, one refresh, and totals that match the platforms’ own files.
03 · Problems in the data
What was wrong with the files.
Problem 1
Half of one platform’s rows had no sales in them
- 12,866 of 26,658 rows (48%) had a city and a SKU but no quantity and no value.
- Pack size written twice in one name: “1 pack (45 g) 72.0 GRAM”.
- The SKU ID was a 36-character code that no other platform uses.
Problem 2
PO dates arrived as text
- Dates came as “YYYY-MM-DD 00:00:00 +0000 UTC” — text, not a date, so it cannot be sorted, filtered or aged.
- The platform’s own legal name came in four spellings — quotes, capitals, mixed case.
- 373 PO lines had no appointment date, so deliveries could not be planned from the file.
Problem 3
A pivot table quietly dropped a whole month
- Months sorted A to Z, not by calendar: Apr, August, Dec, Feb…
- One month, two spellings: “Apr’25” but “April’26”.
- The pivot’s yearly total was about ₹52 L short of the data underneath, because the latest month never reached it.
Always tie a report’s totals back to the raw sheet.
Problem 4 · The same thing had many names
The best-selling product, a pack of cheese crackers, appeared under 10 different names across the sales and PO files. Six of them, brand removed:
| Where | Name as sent |
|---|---|
| Platform 1 · sales | Cheese Flavoured Cracker Biscuit - Family Pack 150 g |
| Platform 1 · PO | Cheese Flavoured Cracker Biscuit - Family Pack(Pouch) |
| Platform 2 · sales | Cheese Crunchy Layered Crackers 144.0 GRAM |
| Platform 2 · PO | Cheese Crunchy Layered Crackers - 1 pack (144 g or 150 g) |
| Platform 3 · sales | Cheese Cream Cracker Biscuit 144 g |
| Platform 4 · sales | Cheese Crunchy Layered Crackers |
- 96 became 12product names became 12 sub-brands
- 46 became 27distributor spellings became 27 distributors
- 500 became 399city spellings became 399 cities
- 21 became 2PO status words became Open or Closed
04 · What we fixed
What we fixed, and how we checked it.
| Problem | What we did | Check |
|---|---|---|
| 12,866 blank rows | Removed, after confirming they carry no quantity or value | Totals tie to the platforms’ own summary sheets |
| Text dates and month labels | One calendar table: date, month and trade week | Every file filters and sorts by real dates |
| 96 product names | SKU master: each platform name mapped to 12 sub-brands | One brand view across all five platforms |
| 46 distributor spellings | Distributor master: 27 distributors under 7 territories | POs with no territory column now have an owner |
| 21 PO status words | A rule for Open vs Closed; fill rate on closed POs only | One platform reads 64.5% filled, not 47.5% with open POs mixed in |
| 500 city spellings | City master: bangalore to Bengaluru, Gurgaon to Gurugram | 399 clean cities for the city ranking |
| Stale pivots | One data model feeds every report | The missing ~₹52 L month is back in the total |
One pipeline instead of nine spreadsheets
- Collect9 files from 5 platforms: sales and POs, as they arrive.
- CleanBlank rows out, text dates fixed, spacing and case tidied.
- MapMasters for SKU, distributor to territory, city and calendar.
- ModelA sales table of 1.28 lakh rows and a PO table of 6,240 lines.
- ReportOne dashboard, a fill-rate report and a weekly PDF pack.
The masters are the real work. A name mapped once stays mapped, so next month's refresh only deals with what is new.
05 · The report
Nine files in, one page out.
- sales
- ~₹9 Cr
- units
- ~15.6 L
- PO fill rate
- 52.4%
- unfilled
- ~₹4.7 Cr
- still open
- ~₹2.6 Cr
One page, three filters
Platform, sub-brand and sales territory, so each manager sees their own slice of the same numbers.
Fill-rate heat map
Territory by platform. The red cells are where to follow up first.
City ranking
The top 10 cities bring 58.5% of sales.
Behind it
A monthly shortfall report — ordered vs supplied, and what it cost — and a weekly PO pack by territory, distributor and SKU.
06 · What to do next
Four decisions the month's data pointed to.
Decision 1
Fix the chocolate-stick supply
Platforms ordered about 4.7 lakh units; only 6.8% were supplied. Around ₹2 Cr of orders missed, on all four platforms that raise POs.
Next. Supply chain: check production and allocation before more POs expire.
Decision 2
Protect the hero product
The cheese crackers are 51% of sales, yet 39% of their ordered quantity was not supplied — about ₹1.6 Cr, most of it on one platform.
Next. Sales head: priority stock for the two biggest quick-commerce warehouses.
Decision 3
Close the territory gaps
One territory filled 45% and carried the biggest unfilled value. Another had no territory manager at all, and one platform there filled 8%.
Next. Regional sales: name an owner for the uncovered territory; a weekly review with the weakest territory’s top 3 distributors.
Decision 4
Stop POs from expiring
486 POs expired in the month with about 7 lakh units never supplied — roughly ₹3 Cr.
Next. Territory managers: a daily open-PO list, sorted by expiry date.
How it runs from next month
Less time cleaning data, more time acting on it.
- Files inSales and PO files from the five platforms, in whatever shape they arrive.
- Clean and mapSame rules, same masters. A new SKU or distributor is mapped once.
- RefreshDashboard, fill-rate report and PDF pack come from the same model.
- ReviewThe four decisions tracked month on month, each with an owner.
This case study uses a real client's data with their identity removed: no client, brand, staff or distributor names, products named by type, the month left out and money and unit figures rounded.
Send us one month of your platform files.
We will show you your own before and after — what is wrong with the files, what we would fix, and the report your team would open every month.