Ravindra BagaleCourses & study guides मराठी Track your progress

22. KPI in All Industries

22.2 Retail and Quick Commerce

By Last updated:

Dark-store and kirana work in Pune and Nashik is judged on cancelled baskets and late deliveries, not on how many orders the app created. You will meet these in a quick-commerce interview and on an operations page (the dashboard build is Module 13).

KPI What the number means Who uses it Action it drives
AOV Delivered sales divided by delivered orders. One basket, in ₹. City manager If AOV falls, check mix (small top-up orders) before celebrating more orders.
Cancellation rate Cancelled orders divided by all orders. Dark-store lead A high rate means stock, rider, or a wrong pin. Open those orders.
% delivered under 10 min Delivered orders that took 10 minutes or less, divided by delivered orders. Operations head Below the promise, add a rider or cut the SKU list. Do not add more orders.

Fictional sample rows. One week. Pending orders are orders that are neither delivered nor cancelled. Sales are delivered sales only. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblRetail (Table Design › Table Name).

Store City Orders Delivered Under 10 min Cancelled Delivered sales
Kothrud Pune 40 28 18 6 ₹12,600
Baner Pune 30 24 20 2 ₹10,800
Hadapsar Pune 20 14 8 4 ₹5,600
Gangapur Road Nashik 30 26 22 1 ₹13,000
College Road Nashik 24 16 12 2 ₹6,400
Satpur Nashik 16 12 10 1 ₹5,600

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI Excel formula Result Number format
AOV =SUM(tblRetail[Delivered sales])/SUM(tblRetail[Delivered]) ₹450 ₹#,##0
Cancellation rate =SUM(tblRetail[Cancelled])/SUM(tblRetail[Orders]) 10.0% 0.0%
% under 10 min =SUM(tblRetail[Under 10 min])/SUM(tblRetail[Delivered]) 75.0% 0.0%

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column Formula in the first row
AOV =[@[Delivered sales]]/[@Delivered]
Cancellation rate =[@Cancelled]/[@Orders]
% under 10 min =[@[Under 10 min]]/[@Delivered]

Expected result per row:

Store AOV Cancellation rate % under 10 min
Kothrud ₹450 15.0% 64.3%
Baner ₹450 6.7% 83.3%
Hadapsar ₹400 20.0% 57.1%
Gangapur Road ₹500 3.3% 84.6%
College Road ₹400 8.3% 75.0%
Satpur ₹467 6.2% 83.3%

One city with SUMIFS. AOV for Pune only:

=SUMIFS(tblRetail[Delivered sales],tblRetail[City],"Pune")/SUMIFS(tblRetail[Delivered],tblRetail[City],"Pune")

Result for Pune: ₹439. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

Good KPI Vanity metric
From the same rows Cancellation 10.0%, and 75.0% under 10 min 160 orders
Why Hadapsar is 4 cancelled out of 20 (20.0%) and only 8 of 14 delivered orders under 10 min (57.1%). Gangapur Road is 1 of 30 cancelled (3.3%) and 22 of 26 under 10 min (84.6%). 160 looks busy. It hides Hadapsar. A manager who reports only orders will add riders to the wrong store.

What the manager does. The Pune city manager opens Hadapsar first: which SKUs were missing, and which hour the 6 slow deliveries sat in. The Nashik manager does not copy Hadapsar's order count. Gangapur Road is the pattern in this file. Satpur is fine on speed (10 of 12) but small. Do not close it because the order card looks low.

Practice workbook and guide: Retail dashboard workbook (Excel) · How to build the Retail dashboard in Excel. The workbook data is also fictional.

Ravindra Bagale's Tip

For AOV, divide delivered sales by delivered orders. If cancelled amounts sit in the same Amount column, use SUMIFS with Status = "Delivered" and label the card.