Formulas

Gross margin formula

Gross margin tells you what share of each pound of revenue is left after paying for the goods themselves.

About the Shopify connector

Gross profit is the pound figure; gross margin is that figure as a percentage of revenue. Most of the mistakes are in which revenue figure goes into it. What counts as a good margin depends on the category you sell in, so compare against your own history rather than a published number.

The formula

Gross margin = (revenue - cost of goods sold) ÷ revenue

Revenue means net revenue: after discounts and refunds. Cost of goods sold is what the products cost you, including inbound freight if you can allocate it.

In Google Sheets, with net revenue in column B and cost of goods in column C:

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

Format the column as a percentage. The IF stops a product with no sales returning a #DIV/0! error.

A worked example

The total row divides total gross profit by total revenue:

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

Averaging the three margins would give 46.7%. The blended figure is 51.3%, because candles sell far more than gift sets and an average ignores that.

The common mistake

Using gross sales, the figure before discounts, as revenue.

Gift sets sold £1,800.00 at list price and £300.00 of that went in discounts, so net revenue is £1,500.00. Measured on gross sales, the margin is (1,800.00 - 1,050.00) ÷ 1,800.00 = 41.7%. The real margin is 30.0%. A discount reduces what you were paid; it does not reduce what the goods cost you.

Shopify makes this easy to get wrong because it records discounts in two places: discounts applied to a line item, and discounts applied to the whole order (a code entered at checkout, say). A column of line-item discounts leaves order-level discounts sitting in your revenue. Before you subtract, check which kind the column holds, and subtract both.

Refunds are the same story. A refunded order is revenue you no longer have, and the goods may or may not have come back.

Keeping the inputs current

A scheduled Fresh Sheets report can keep your Shopify orders in their own tab, for example one called Shopify. Cost of goods usually lives somewhere else (a supplier price list, or your inventory system), so keep that on a tab of its own 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 reads total cost of goods from a Costs tab:

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

Contribution margin takes shipping and payment fees off as well. It is the figure your ROAS has to clear.

Work out your margin with the free margin calculator

Convert between markup and margin with the free markup calculator