Level 2 · Check and compare coupon orders in Excel

What the sheet holds

Priya’s export has one row per coupon and year, completed orders only:

column	holds
A, B	the coupon (none = no code) and the year
C	the number of orders
D	gross: what the baskets were worth before the discount
E	net: what customers paid

Twenty-one rows: seven codes, three years each. The labels of the summary table are already in I6:N14.

Step 1: does the discount match the code?

The data has the code and the money, so you can work out the discount customers really got and compare it with the one advertised:

F2   =1-E2/D2
G2   =D2-E2

Filled down to row 22, column F should show 25% on every BFCM25 row, 20% on HOLIDAY20 and so on. Two summary cells make the check quick to read:

J2   =MAX(F2:F22)
J3   =COUNTIF(F2:F22,0)

The highest rate should be the biggest code, 25%. The zero rows should be exactly the six with no discount on the lines: none and FREESHIP, which waives shipping and leaves the basket alone.

Step 2: one summary table

The summary repeats one pattern: a SUMIFS that reads its coupon from column I.

J7   =SUMIFS($C$2:$C$22,$A$2:$A$22,$I7)

The data ranges are locked so they stay put when you fill down, and $I7 lets the row move. Revenue and discount cost use the same shape on columns E and G. Then the derived columns:

L7   =K7/J7
N7   =K7/$K$14

with the totals in row 14. Fill everything down to row 13.

Reading the result

Coupons brought 12.8% of net revenue and cost $121,785 in discounts. Every code’s AOV is below the $554.36 of orders without one, from $386.27 on HOLIDAY20 to $618.69 on FREESHIP.

Be careful with that comparison. Seasonal codes run in weeks when everybody’s baskets differ, so “coupon AOV below no-coupon AOV” mixes the coupon with the calendar. The SQL and pandas versions of this step compare each code with orders on the same days; in a spreadsheet you would need the daily export to do the same.

Pitfalls

• AOV and share come from totals; never average a column of averages.
• #DIV/0! in the share column means the total reference was not locked.
• FREESHIP’s real cost is shipping, which is not in this sheet. Say so in the write-up.