Formulas

Contribution margin formula

Contribution margin is what is left from a sale after every cost that came with that sale: the goods, the shipping, the packaging, the payment fee.

About the Shopify connector

It is the money available to pay for advertising and for fixed costs. The mistakes are in deciding which costs are variable, and in using gross margin where contribution margin was needed.

The formula

Contribution margin = revenue - variable costs

Contribution margin per unit = price - variable cost per unit

Contribution margin ratio = contribution margin ÷ revenue

Variable costs are the ones that scale with each sale. Rent and salaries do not belong here. Cost of goods, shipping, packaging and payment processing fees do.

In Google Sheets, with units in column B, net revenue in C and variable costs in D, the ratio is:

=IF(C2=0, "", (C2-D2)/C2)

and the per-unit figure is:

=IF(B2=0, "", (C2-D2)/B2)

A worked example

The total row's ratio divides total contribution margin by total revenue:

=IF(SUM(C2:C4)=0, "", (SUM(C2:C4)-SUM(D2:D4))/SUM(C2:C4))

Gift sets bring in £6.00 a unit against £9.00 for a candle, despite the higher price. Packaging and shipping eat the difference.

What it means for ad spend

Every pound of ad spend has to come out of contribution margin, because nothing else is left to pay it from. At a 38.8% ratio, £1.00 of revenue brings £0.39 of margin, so revenue has to be 1 ÷ 0.3875 = £2.58 for every £1.00 spent before the ad has paid for itself. That is the break-even ROAS for this product mix. A campaign returning 2.20 looks healthy on the ad platform and loses money on the products.

The common mistake

Setting the ROAS target from gross margin instead of contribution margin.

The same products carry £3,900.00 of cost of goods, so gross margin is 51.3% and break-even ROAS on that basis is 1 ÷ 0.5125 = 1.95. The other variable costs add another £1,000.00, which takes the ratio down to 38.8% and break-even up to 2.58. A campaign at ROAS 2.20 passes the first test and fails the second. The £1,000.00 is real money; the ad platform just never sees it.

Keeping the inputs current

A scheduled Fresh Sheets report can keep your Shopify orders in their own tab, for example one called Shopify. Variable costs per unit (goods, shipping, packaging, fees) usually live elsewhere, so hold them on a Costs tab with one row per product.

Fresh Sheets replaces the Shopify tab's contents on every refresh, so keep your formulas on a separate tab. This version finds the sales column by its heading, so it still works if the columns move. Net payment is what the store received after refunds, with shipping and tax collected inside it, so it sits a little above goods revenue alone. It takes total variable cost from the Costs tab:

=(SUM(INDEX(Shopify!A:Z, 0, MATCH("Net payment", Shopify!1:1, 0))) - SUM(Costs!C:C)) / SUM(INDEX(Shopify!A:Z, 0, MATCH("Net payment", Shopify!1:1, 0)))

Divide 1 by that cell and you have the ROAS your campaigns need to clear this month.

Find your break-even point with the free calculator