Case 7 · 3 levels
Do coupons pay for themselves?
Finance wants to know what Harbor & Pine's six coupon codes really cost. Frame the question, check the discounts against the codes, compare coupon orders with orders on the same days, and write the recommendation.
StartThis case unlocks when you finish any one of these chapters. The levels are readable now.
Harbor & Pine runs six coupon codes: a welcome discount on first orders, a spring and a summer sale, Black Friday weekend, a December holiday code and free shipping. Laura Bennett in Finance is setting next year’s promotion budget and wants a straight answer to a question every retailer asks: do these codes earn back what they give away?
It is a harder question than it looks. Coupon orders come in particular weeks, from particular customers, and nobody can see the orders that would have happened without the code. This case is the whole job:
- Frame the question so the data can answer it, and say up front what it cannot answer.
- Check the data: prove the discount on every order line matches the code on the order.
- Analyse, in SQL, pandas or Excel, whichever you prefer: what each code cost, what its orders were worth, and how they compare with orders placed on the same days without a code.
- Recommend: pick the paragraph you would send Laura, with the numbers and the caveats.
The case opens as soon as you finish grouping and aggregates in any track. It uses GROUP BY,
conditional sums and a CTE (or groupby and merge in pandas, or SUMIFS in Excel); the lessons
walk through each.
Levels
- Frame the coupon question
from Laura Bennett, Head of Finance
smart goal, problem framing, like for like, counterfactual, selection bias
- Check and compare coupon orders
from Laura Bennett, Head of Finance
data validation, coalesce, group by, conditional aggregation, cte, like for like
- Recommend: keep, change or test the coupons?
from Laura Bennett, Head of Finance
recommendation, caveats, counterfactual, selection bias, holdout test, data storytelling