Level 5 · From net sales to net revenue

The last step of the waterfall

Net sales are what customers paid for. Some of it comes back. Refunds are the money returned, recorded per returned line in returns.refund_amount, and net revenue is what is left:

gross sales − discounts = net sales
net sales   − refunds   = net revenue

A refunded order contributes its full value to net sales and the same amount (or nearly, after a restocking fee) to refunds, so it nets close to zero. A completed order with one returned lamp nets the rest of the basket.

Joining returns without losing sales

returns has one row per returned line, and most lines are never returned. A left merge keeps every line and puts NaN in refund_amount where nothing came back:

lines = lines.merge(returns[["order_item_id", "refund_amount"]], on="order_item_id", how="left")

groupby(...).sum() skips NaN, so the refunds column adds only real refunds. An inner merge would keep only the 1,673 returned lines and destroy the net sales total.

Which year does a refund belong to?

A December order returned in January can be booked two ways: in the year of the order (the cohort view) or the year the money left (the cash view). Finance statements usually use the cash date. This track uses the order’s year, so each year’s refund rate describes that year’s orders. Both are valid; mixing them is not. Say which one you used.

The newest period is never finished

Returns arrive 4 to 40 days after the order. December 2025 orders have had only days to come back, so December shows $8,573.75 of refunds against $21,306.68 in November. The 2025 refund rate of 5.96% will creep up as January’s returns arrive. Reports that compare the newest month with older ones without that footnote make returns look like they are falling.

Common mistakes

• Inner merge. Only returned lines survive; net sales shrink to almost nothing.
• Counting returned lines as the rate. Units returned and dollars refunded give different rates; this level uses dollars, because the waterfall is in dollars.
• Subtracting refunds from gross sales. Refunds are paid on discounted prices, so they come off net sales.

Interview note

“Why would last month’s return rate look low?” Because returns lag orders. Saying “right-censoring” or simply “the window is not closed yet” shows you know the data is still arriving.