Case 5 · 3 levels
Where do the returns come from?
Customer Support says returns feel high. Frame the question, check which orders the returns belong to, measure the return rate and refund value by category, read the reasons, and write the recommendation.
StartCustomer Support handles every return by hand, and lately the queue feels long. The Head of Support wants to know where returns come from and why, so she can take one or two fixes to the next operations review. “Returns feel high” is a feeling; your job is to turn it into a rate, find where it is highest, and say what is driving it.
This case is the whole job, the way you would do it at work:
- Frame the question: which orders count, which measure, which split, and what decision it feeds.
- Check the data: see which orders the returns belong to before you divide anything, because one order status behaves very differently from the rest.
- Analyse, in SQL, pandas or Excel, whichever you prefer: the return rate and the share of sales refunded by category, then the mix of return reasons.
- Recommend: pick the paragraph you would send, with the numbers and the caveats.
The case is open from the start. It uses a LEFT JOIN, conditional counts and percentages (or
merge, isin and crosstab in pandas, or SUMIF and SUMIFS on a prepared sheet in Excel); the
lessons walk through each.
Levels
- Frame the returns question
from Angela Rivera, Head of Customer Support
smart goal, problem framing, return rate, denominator
- Measure the return rate
from Angela Rivera, Head of Customer Support
data validation, left join, conditional aggregation, return rate, percent of total
- Recommend: where to push first?
from Angela Rivera, Head of Customer Support
recommendation, caveats, causation, denominator, data storytelling