Level 2 · Contribution margin by channel

From gross margin to contribution

Gross margin takes one cost off sales: the goods. Contribution margin takes off every cost that grows with each sale, so it answers “what does one more order leave to pay the fixed costs?”. Which costs count depends on what the business can measure. Harbor & Pine’s data has two:

• COGS: quantity × unit cost.
• Refunds: money paid back on returned lines (returns.refund_amount).

contribution = SUM(net) - SUM(cogs) - SUM(refund)

In a real company you would also take off payment fees, packaging, outbound shipping cost and marketplace commissions. The method is the same: one more column per variable cost.

Why refunds belong here

A refunded line was sold, so it is in net sales, and then its money went back. Gross margin still counts it as a sale. Contribution removes the refund, which is closer to the cash the store kept.

A LEFT JOIN returns gives every line its refund, or NULL when it was not returned. COALESCE(r.refund_amount, 0) turns that NULL into 0 so the sum works. With a plain JOIN you would keep only returned lines and lose almost all the sales.

Per order: count orders, not lines

The CTE has one row per order line. To divide by orders, count distinct order ids:

SUM(net - cogs - refund) / COUNT(DISTINCT order_id)

COUNT(*) would count about 2.5 lines per order and shrink the result by the same factor.

Reading the result

The three channels end up within half a point of each other on contribution %, so none of them is cheaper to serve on goods and returns. The per-order figure differs more, because web orders are a little bigger. That is the number to set against what each channel costs to win an order, which this table cannot see yet.

Common mistakes

• Joining returns with an inner join. Only returned lines survive, and the channel totals collapse.
• Dividing by lines instead of orders. Per-order figures come out far too low.
• Calling gross margin “profit”. Refunds alone move it by six points here.