Gross Margin by SKU From a Shopify Export

The formula is one line. The reason nobody has this number is that unit cost lives in a different system, and the failure when it is missing looks exactly like a very profitable product.

By Ed · Published August 20, 2026 · 8 min read

Twenty-five years starting companies, consulting, owning a multi-million-dollar P&L and leading business intelligence for large enterprises.

The formula for gross margin per product is one line, and almost nobody has the number. The obstacle is not arithmetic. It is that the order export tells you what the customer paid and says nothing at all about what the goods cost, and the two facts live in different systems maintained by different people.

The formula, and the two joins under it

Line revenue    line total − allocated discount
Line cost       quantity × unit cost
Gross profit    line revenue − line cost
Gross margin    gross profit / line revenue

Two joins are hiding in there. Unit cost has to come from a cost list, joined on SKU. Allocated discount has to come from the order, spread across that order lines in proportion to revenue. Skip the first and every product is pure profit; skip the second and promoted products keep their full-price margin on paper.

The cost list is the whole job

Shopify holds a cost per variant, and whether it is populated is a question with a genuinely uncomfortable answer at most stores: partly. New SKUs get entered, bundles do not, anything imported in bulk usually does not, and nobody notices, because the field is not shown on any report anyone reads.

Before calculating anything, count:

Lines with no cost match    =COUNTIF(Lines[Unit cost],"")
Revenue on those lines      =SUMIF(Lines[Unit cost],"",Lines[Line total])

Revenue is the number that matters, not the line count. Two hundred unmatched lines worth 0.4% of revenue is a rounding note. Nine unmatched lines carrying a fifth of revenue means the margin table cannot be shown to anyone yet.

Three sources, in descending order of maintenance

The variant export. Free, already joined to the SKU, and only as good as what was typed in.
A supplier price list. More accurate, needs mapping to SKUs, and goes stale on its own schedule.
A maintained cost sheet. Correct, and someone has to own it. In practice this is the only one that survives, and only where one person is accountable for it.

Whichever you pick, landed cost is a separate argument: freight, duty and payment fees are real costs of goods for an importer and are usually not in any of the three sources. Decide whether your unit cost is ex-works or landed, write it down, and do not mix the two in one list.

From building this reporting more than once

The zero-cost failure is the one I would put on a poster. A missing cost does not produce an error or an obviously silly number — it produces a product at 100% margin, sitting in a table of products at 40 to 60%, looking like the star of the range.

I have watched a range decision get made on a table like that. The best-performing SKU by margin was the one nobody had costed. Now the first row of any margin report I build is the count and the revenue of the unmatched lines, above the table rather than in a footnote, and it stays there even when it reads zero.

Doing it in Excel

Lines in Lines, costs in Costs, orders in Orders. Four helper columns:

Unit cost      =IFERROR(SUMIFS(Costs[Unit cost],Costs[SKU],[@SKU]),"")
Alloc discount =[@[Line total]]/SUMIFS(Lines[Line total],Lines[Order],[@Order])
               *SUMIFS(Orders[Discount],Orders[Order],[@Order])
Line revenue   =[@[Line total]]-[@[Alloc discount]]
Gross profit   =IF([@[Unit cost]]="","",[@[Line revenue]]-[@Qty]*[@[Unit cost]])

Note the last line returns blank rather than zero when cost is missing. That is deliberate: a blank propagates into the SUM and shows up as a gap, whereas a zero quietly becomes profit. Making the failure visible is worth more than making the table complete.

Then per SKU, with margin percentage and gross profit side by side:

Gross profit  =SUMIFS(Lines[Gross profit],Lines[SKU],$A2)
Revenue       =SUMIFS(Lines[Line revenue],Lines[SKU],$A2)
Margin %      =IFERROR(B2/C2,"")

Sort by margin percentage and you get products selling three units a month. Sort by gross profit and you get your volume lines. Both orderings are useful; neither alone is.

What gross margin does not tell you

It stops at the goods. A SKU at 58% gross margin that only sells through paid ads may be losing money once acquisition is counted, and gross margin will never show it — that is what contribution margin is for.

It also says nothing about capital. Two SKUs at identical margin, one turning over monthly and one sitting for a year, are not the same business — but that is an inventory question, and the honest answer about what a store export can tell you is in why inventory turns is not in your dashboard.

Doing it without maintaining the workbook

Quiriz for e-commerce takes the order-line export and a variant cost list and defines gross profit once, so gross margin by product category returns the same figure every time. In Excel that is =QUIRIZ.ASK("gross profit by product category last quarter", "table").

The honest boundary: we cannot supply the cost you have not recorded. No tool can. Where unit cost is missing, the answer is a smaller population and a note saying so, rather than a confident figure built on zeros.

Before reading any margin table, check what share of revenue sits on lines with no cost. A product at 100% margin is not a good product. It is an empty field.

Find out which products actually earn their shelf space

Upload your order-line export and a cost list, and ask for gross profit by category or by SKU in plain English. Free to start.

Try Quiriz free →

Frequently asked questions

How do you calculate gross margin per product?

Gross profit divided by revenue, per product. Gross profit is the line revenue after its share of any discount, minus quantity multiplied by unit cost. The arithmetic is trivial. Obtaining a reliable unit cost per SKU is the actual work, and it is why most stores do not have this number.

Where is the cost of goods in a Shopify export?

It is not in the order export at all. Shopify holds a cost per variant on the product record, so it can be exported separately if it has been entered, but the order file carries only what the customer paid. Cost has to come from the variant export, a supplier price list or a maintained sheet, and be joined on the SKU.

What happens if a SKU has no cost recorded?

In most spreadsheet setups the lookup returns zero, and the product is reported at 100% gross margin. That is the dangerous failure: it does not error, it does not look odd next to products at 55%, and it makes your worst-documented SKUs look like your best ones. Always count the unmatched lines and their revenue before reading any margin table.

Should gross margin be calculated before or after discounts?

After. A product sold at 40% off has not earned its list-price margin, and a margin table built on list prices will rank a heavily promoted line above one that sells at full price. This matters most when discounts are recorded at order level, because those have to be allocated down to the line before the per-SKU figure means anything.

Why is gross margin by SKU different from gross margin overall?

The overall figure is revenue-weighted, so it is dominated by whatever sells most. A store can have a healthy blended margin and a long tail of SKUs below cost. Ranking by margin percentage alone has the opposite problem — it surfaces high-margin products selling three units a month. Look at margin percentage and gross profit in currency side by side; the two orderings disagree, and the disagreement is the useful part.