Level 1 · Frame the returns question

Why framing comes first

“Returns feel high” is a feeling from someone who sees the queue every day. It may well be right, but before you write a query you need to agree what “high” would mean. The checklist most analysts use is SMART:

• Specific: which orders and which split. Here: completed orders, by product category, with the reason for each return.
• Measurable: which number. The return rate is returned order lines divided by sold order lines. Because a returned sofa costs more than a returned candle, add the refund share: refunds divided by net sales.
• Achievable: can the data answer it? Yes: every row in returns points to one order line through order_item_id, and it carries a reason and a refund amount.
• Relevant: what decision does it feed? Angela will take one or two fixes to the operations review, so the analysis has to point at causes, not just at categories.
• Time-bound: all three years of orders, answer before the review next month.

What the rate divides by

The denominator decides what the rate means. Three choices matter here:

• Lines, not orders. An order with three items and one return is not “a returned order”. Counting lines matches what Support handles: one return per line.
• Completed orders only. A cancelled order never shipped, so it cannot be returned. A refunded order is a different process: the whole order is refunded and every line gets a return row. Those belong in the report, but as their own number, not inside the product return rate.
• Real sales. A handful of order lines have a quantity of zero or below. They are data-entry errors, not sales, so they stay out of the denominator.

What the plan looks like

Step	What you do	Output
Check	Count lines and returned lines by order status	a 4-row table, one per status
Analyse	Return rate and refund share by category, completed orders	a 6-row table
Explain	Mix of reasons within each category	a 6-row table of shares
Show	One chart: return rate by category	a column chart
Recommend	Where to push first, with caveats	three or four sentences

Interview note

“How would you investigate a rise in returns?” comes up in e-commerce and operations interviews. A strong answer defines the rate and its denominator, splits it by something actionable (category, product, carrier, reason), separates whole-order refunds from item returns, and ends with a fix someone can own: packaging, sizing guides, product photos, a carrier conversation.