22.3 E-commerce
This is a fictional marketplace seller, not the quick-commerce data used in the earlier modules. Interviewers ask conversion and returns. They rarely ask for sessions.
| KPI | What the number means | Who uses it | Action it drives |
|---|---|---|---|
| Conversion | Orders placed divided by sessions. | Seller, category lead | A low rate means the listing, the price, or the photo. Not "buy more traffic". |
| Return % | Returned orders divided by orders placed. | Seller operations | A high rate means size, damage, or a wrong promise. Fix the SKU before the next ad. |
| AOV | Delivered sales divided by delivered orders. | Finance and the seller | Used with returns, not instead of returns. |
Fictional sample rows. One month. Delivered sales exclude returns. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblEcom (Table Design › Table Name).
| City | Sessions | Orders placed | Delivered | Returned | Delivered sales |
|---|---|---|---|---|---|
| Pune | 1,000 | 50 | 40 | 10 | ₹20,000 |
| Nashik | 500 | 40 | 36 | 4 | ₹18,000 |
| Kolhapur | 800 | 20 | 16 | 4 | ₹8,000 |
| Nagpur | 400 | 40 | 32 | 8 | ₹16,000 |
| Solapur | 200 | 20 | 18 | 2 | ₹9,000 |
| Sambhaji Nagar | 600 | 10 | 8 | 2 | ₹4,000 |
Excel formulas for the whole file (type them in empty cells below or beside the table):
| KPI | Excel formula | Result | Number format |
|---|---|---|---|
| Conversion | =SUM(tblEcom[Orders placed])/SUM(tblEcom[Sessions]) |
5.1% | 0.0% |
| Return % | =SUM(tblEcom[Returned])/SUM(tblEcom[Orders placed]) |
16.7% | 0.0% |
| AOV | =SUM(tblEcom[Delivered sales])/SUM(tblEcom[Delivered]) |
₹500 | ₹#,##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 |
|---|---|
| Conversion | =[@[Orders placed]]/[@Sessions] |
| Return % | =[@Returned]/[@[Orders placed]] |
| AOV | =[@[Delivered sales]]/[@Delivered] |
Expected result per row:
| City | Conversion | Return % | AOV |
|---|---|---|---|
| Pune | 5.0% | 20.0% | ₹500 |
| Nashik | 8.0% | 10.0% | ₹500 |
| Kolhapur | 2.5% | 20.0% | ₹500 |
| Nagpur | 10.0% | 20.0% | ₹500 |
| Solapur | 10.0% | 10.0% | ₹500 |
| Sambhaji Nagar | 1.7% | 20.0% | ₹500 |
One city with SUMIFS. Conversion for Pune only:
=SUMIFS(tblEcom[Orders placed],tblEcom[City],"Pune")/SUMIFS(tblEcom[Sessions],tblEcom[City],"Pune")
Result for Pune: 5.0%. 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 | Nashik conversion 8.0% and return 10.0% | Pune 1,000 sessions |
| Why | Nashik turns more of the visit into an order, and fewer come back. | Pune looks biggest on traffic. One in five Pune orders was returned. Traffic did not pay the return. |
What the manager does. The Pune seller opens the 10 returned orders (size, damage, or late) before buying another 1,000 sessions. The Nashik seller keeps the listing that converted and does not copy Pune's traffic plan. Kolhapur is the warning: 800 sessions and only 20 orders (2.5%). That is a listing problem, not a city problem.
Ravindra Bagale's Tip
Return % is returned orders divided by orders placed. Sessions are never the denominator. Write the denominator in the column header.
Ravindra Bagale's Tip – मराठी
Return % म्हणजे returned orders भागिले orders placed. Sessions कधीच denominator नसतात. Denominator column header मध्ये लिहा.
Ravindra Bagale's Tip – हिंदी
Return % यानी returned orders भाग orders placed. Sessions कभी denominator नहीं होते. Denominator को column header में लिखें.