Level 2 · Three return rates, one dataset

A rate is a numerator over a denominator

“Return rate” sounds like one number. It is at least three, depending on what you divide by:

• Per order: orders with at least one returned line ÷ booked orders. What support feels: how often a customer comes back unhappy.
• Per line: returned lines ÷ all lines. What the warehouse feels: how many parcels come back.
• Per dollar: refunds ÷ net sales. What finance feels: how much revenue goes back out.

All three are honest. Mixing them on one slide, or switching between them from month to month, is not.

Harbor & Pine’s three return rates

year  orders  per order  per line  per dollar
2023   2,972     11.68%     6.62%       6.67%
2024   3,772     10.05%     5.84%       5.89%
2025   4,409     10.00%     5.55%       5.96%

The per-order rate is almost twice the others: an order has 2.5 lines on average, so one returned line makes the whole order count as “returned”. And look at 2024 to 2025: by lines, returns fell; by dollars, they rose. A manager who wants good news can pick the denominator that gives it. A KPI definition exists to take that choice away.

Counting after a join

The lines CTE has one row per order line, so COUNT(*) counts lines, not orders. For the order rate you need COUNT(DISTINCT order_id), and for “orders with a return” the same count restricted to returned lines with FILTER (WHERE return_id IS NOT NULL). COUNT(return_id) counts only lines that were returned, because COUNT(column) skips NULLs, and SUM(refund_amount) does the same.

Which one goes on the KPI sheet

Choose by the decision the number feeds. Finance planning revenue needs refunds per dollar. The warehouse planning staff needs lines. Customer experience needs orders. Pick one per audience, write the denominator into the name (“returns, % of net sales”), and never change it silently.

Common mistakes

• Counting rows after a join as orders. Use COUNT(DISTINCT order_id).
• Dividing by all orders. Cancelled and pending orders were never paid, so they cannot be returned; including them lowers the rate for no reason.
• Comparing a per-order rate with last year’s per-dollar rate. The jump from 6% to 10% is the definition, not the customers.