Level 1 · Frame the coupon question

Why framing comes first

“Do coupons pay for themselves?” is a fair question with a budget behind it. Jordan in Marketing thinks yes; Laura in Finance wants to see it. Before any query, agree what an answer would look like. SMART is the usual checklist:

• Specific: which codes and which orders. Here: the six codes (WELCOME10, SPRING15, SUMMER15, BFCM25, HOLIDAY20, FREESHIP), completed orders, 2023 to 2025.
• Measurable: orders, net revenue, average order value (AOV) and discount cost per code.
• Achievable: orders.coupon_code holds the code, and every line in order_items holds the discount actually applied, so both the cost and the value are in the data.
• Relevant: Laura will keep, change or test each code in next year’s budget.
• Time-bound: three full years of data, an answer within two weeks.

Compared with what?

Coupons do not appear at random, and that shapes the comparison:

• The calendar. SPRING15 runs in March and April, SUMMER15 in June and July, HOLIDAY20 from December 1 to 20, BFCM25 over Black Friday weekend. Baskets differ by season, so comparing coupon orders with all other orders mixes the code with the calendar. The fair comparison is orders on the same days without a code.
• The customer. WELCOME10 only goes to first orders. First orders are different from repeat orders whatever the code, so comparing WELCOME10 with everything else is a selection problem.

What the data cannot tell you

A coupon pays for itself only if it brings orders that would not have happened otherwise. That missing world, the same customers on the same days with no code, is the counterfactual, and no table holds it. The analysis can show what a coupon order is worth next to a normal one and what the code gave away. It cannot show how many coupon orders were extra. Say that in the framing, so nobody expects the analysis to prove more than it can.

What the plan looks like

Step	What you do	Output
Check	Compare each code with the discount on its lines	one row per code, min and max discount
Analyse	Orders, revenue, AOV and discount cost by code	a 7-row cost sheet
Compare	Seasonal codes against same-day orders without a code	a 4-row table
Recommend	What it means for the budget, with caveats	three or four sentences

Interview note

“How would you measure whether a promotion worked?” is a common analyst interview question. A strong answer names the cost (discount given), the value (orders and revenue), a fair comparison (same period, similar customers), and the limit: without a hold-out group that never saw the code, you cannot tell how many orders the promotion created.