Shopify Unit Economics Spreadsheet: Build It Step by Step

Quick answer: A Shopify unit economics spreadsheet runs top to bottom: sessions and conversion rate give orders, average order value (AOV) and variable costs give contribution margin per order, and marketing cost divided by first-time customers gives customer acquisition cost (CAC). Net sales divided by marketing cost gives the marketing efficiency ratio (MER), a cohort curve gives payback and lifetime value, and a sensitivity table shows what a better conversion rate or order value is worth. Almost every cell comes from a Shopify report, an ad invoice or a supplier bill. This post builds the whole sheet on one illustrative store and names the source for each cell.

The hard part of a unit economics sheet is not the arithmetic. It is knowing which number goes in which box, where in Shopify it lives, and which version of it you can compare with last month’s.

This post is the sheet in text form: one imaginary store, seven steps, and a source for every cell, so you can rebuild it in Google Sheets or Excel with your own figures and see which lever matters most for your store.

Every dollar figure here is illustrative. The store does not exist. The numbers were chosen so the arithmetic is easy to follow. None is a benchmark, a target or a result from a client, and your own sheet will look different. Currency is Australian dollars.

A note on scope. Prism3 sells Shopify SEO, GEO and CRO. We do not run ads, email or SMS. This is a measurement guide for whoever owns the store’s numbers, and it works the same whoever runs your ads. For the wider picture of how paid, organic, conversion and email fit together, our growth-system guide comes first.

Before you start: four rules for every cell

One date window. Use whole calendar months, or a fixed 30 days, and use the same window for every cell. Shopify says its customer reports might not show the last 12 hours of activity, so do not pull the current month on the 30th and call it final.

One revenue basis. Use net sales. Shopify’s help centre defines net sales as gross sales minus discounts minus sales reversals, with shipping and taxes not included. Total sales adds taxes, duties, shipping charges and fees back in. If you mix the two, your margins will be wrong by the size of your shipping income and tax.

Know when refunds land. Shopify says sales reversals (returns, cancellations, edits and other negative adjustments) are dated by the day they were processed, not the original order date. A refund in November for an October order reduces November’s net sales. Gross sales also include pending, cancelled and unpaid orders, and exclude test and deleted orders. For a clean month, check the previous month’s late refunds before you trust a single month’s margin.

Label every input with its source and the date you pulled it. “A$41.72, from the Shopify sales report plus supplier invoices, October” is a decision you can repeat; “A$41.72” alone is useless in six months. All Shopify definitions in this post are from Shopify’s help centre and blog as at 30 October 2026.

GST and tax are your decision to make consistently. Decide whether your cost lines are GST-inclusive or exclusive, write it next to the number, and check the treatment with your accountant. This post is not accounting advice.

Step 1: Traffic, conversion and orders

These are your top-of-sheet inputs.

Check that sessions × conversion rate lands near your order count (40,000 × 2.0% = 800). If it does not, the gap tells you something: multiple orders in one session, a banner that hides sessions, or a date mismatch. Work out why before you build on it. GA4 versus Shopify Analytics explains why tools disagree.

The first-time customer count matters most. Shopify defines a returning customer as one whose order history already includes at least one order, and says customer report data is based on the entire order history, not only orders in the selected timeframe. So someone who first bought last year is returning, not new, which is what you want in a CAC denominator. For the rest of the denominator debate, what counts as CAC for a Shopify store goes line by line.

Step 2: Average order value and net sales per order

Here the store’s month looks like this.

Two values here look alike and are not: AOV is A$80.00, net sales per order is A$76.00. Shopify’s AOV excludes post-order adjustments, so refunds are not taken out of it. For unit economics, use the A$76.00. A refunded order is revenue you did not keep, and a margin built on the A$80.00 overstates revenue per order by A$4.00 in this example.

Step 3: Contribution margin per order

Contribution margin is what each order leaves after the costs that only exist because the order happened, and before marketing and before fixed costs such as staff, software and rent. It is the number that decides how much you can afford to spend to win a customer.

Where the cells come from:

• Product cost. Shopify’s profit reports use the cost per item you recorded at the time of sale, and only for products and variants that had a cost recorded. Shopify describes the profit data as static, relevant only to a specific point in time. Products without a cost are left out, which means net sales in your profit reports can differ from net sales in your sales reports. Gross profit in Shopify is net sales minus cost, which here is A$76.00 − A$24.00 = A$52.00. That is higher than contribution margin because it stops after product cost.
• Shipping. Shopify’s sales report shows the shipping you charged customers (shipping charges minus shipping discounts minus refunded shipping). The cost side comes from your carrier invoices or label spend.
• Payment fees. They come from your payment provider’s statements. I have used a flat 3% of net sales for the illustration. Use your real figure.
• Anything else that varies with each order: pick and pack, a third-party logistics fee, returns handling. If it moves with orders, it belongs here.

Shopify’s CAC payback article defines gross profit as revenue minus cost of goods sold and other direct costs, which is close to contribution margin.

Step 4: CAC, calculated two ways

Marketing cost for the month, illustrative:

Now divide by the 520 first-time customers from Step 1:

• Blended media CAC: A$20,000 ÷ 520 = A$38.46
• Fully loaded CAC: A$26,000 ÷ 520 = A$50.00

Use fully loaded CAC as the headline and write the scope next to it. Same store, same month, two honest answers, and neither is a mistake. The mistake is quoting one without saying which. The CAC definition post has the in-or-out table for each cost line.

Shopify’s Performance by marketing channel report also shows ad spend, return on ad spend (ROAS), cost per acquisition and CAC, and it is a good cross-check, with three details worth knowing from the help page:

• Spend-based columns only appear for channels that report cost data to Shopify, such as a connected ad platform. A dash means no cost data available, which is not the same as zero.
• By default the report shows the last 30 days and the last non-direct click attribution model. It includes cancelled, pending and unpaid orders, and shows the value of goods sold, not money received.
• Shopify says the Referring channel, Referring medium and Traffic type fields use updated values from 18 October 2026. If you compare channel splits across that date, check that you are comparing like with like.

Step 5: MER and your break-even MER

The marketing efficiency ratio is total revenue divided by total marketing spend. Shopify’s MER article (published 18 July 2026) says the same, says it is also called blended ROAS, and says to take revenue from the same date range as spend and to pick one definition of revenue, such as total sales or net sales, and stick to it. It also notes that brands differ on what counts as marketing spend: some count only media, others add tools, contractors, production and salaries.

In the sheet:

• MER on media only: net sales ÷ media spend = A$60,800 ÷ A$20,000 = 3.04
• MER on all acquisition costs: A$60,800 ÷ A$26,000 = 2.34

On its own, 2.34 means nothing, because it depends on the margin behind it. The number to compare it with is break-even MER: 1 ÷ contribution margin percentage. Here that is 1 ÷ 0.549 = 1.82. This is my own derivation, not a Shopify or industry standard, and it only works against the all-costs MER (2.34), because that one counts the same marketing cost the contribution has to cover. Below 1.82, the contribution from orders does not cover marketing cost. Above it, the store earns something before its fixed costs.

The illustrative store sits at 2.34, so:

• Contribution from all 800 orders: 800 × A$41.72 = A$33,376
• Less marketing cost: A$26,000
• Contribution after marketing: A$7,376, or 12.1% of net sales

That A$7,376 still has to pay for staff, software, rent and everything else the business carries. It is not profit.

Why use MER instead of platform ROAS

Meta’s Business Help Centre says purchase ROAS is calculated as purchase conversion value divided by amount spent, based on approximate shop sales on Meta technologies and information from connected Meta Business Tools attributed to your ads. It adds that where events cannot be counted directly because of partial or missing data, statistical modelling may be used. Under Meta’s standard attribution settings, each ad set credits conversions using click-through (1 or 7 days after a link click), view-through (1 day after an impression) or engage-through (1 day after a non-link click), and Meta says results cannot be compared in the campaign overview table across ad sets with different attribution models. Its own advice is that if you use external analytics tools, you should evaluate performance in those tools.

So platform ROAS answers “how many purchases does this ad account claim?” and MER answers “how much did the whole store sell for each dollar of marketing?” Shopify’s MER article makes the same split: ROAS for campaign decisions, MER for overall efficiency. You need both, and your own order count underneath them. If the two disagree, why attribution is broken on Shopify and diagnosing a ROAS drop are the next reads.

I am not giving you a “good MER” target. Shopify’s MER article quotes one from a vendor study I have not read, so I will not repeat it. Your break-even MER comes from your own margin, and that is the number that matters.

Step 6: Payback and lifetime value from a cohort curve

Everything so far is one month. Payback asks a different question: how long until a group of new customers has returned what they cost?

Shopify’s Customer cohort analysis report is built for this. It groups customers into cohorts by the date of their first order, and the columns show a metric over the weeks, months or quarters since that first order. The metrics you can choose include number of customers, customer retention rate, gross sales, net sales, average order value and amount spent per customer. Clicking a cell shows details including orders per customer, and you can filter cohorts by first-order attributes such as marketing channel, so you can build one curve per channel. One catch from the help page: the “period 0” column holds repeat orders made in the same period as the first order, and you should check whether the cell you are reading shows that period or a running total.

Here is an illustrative cohort, using cumulative orders per customer, and turning orders into contribution at the A$41.72 from Step 3 (a simplification: repeat orders are assumed to earn the same margin as first orders):

Fully loaded CAC is A$50.00. The first order leaves each customer A$8.28 behind (50.00 − 41.72). Cumulative contribution passes A$50.00 during month 3 (A$49.23 at month 2, A$52.15 at month 3). That is payback: about three months.

Check the cash effect. The month’s 520 first-time customers return 520 × A$41.72 = A$21,694 on their first orders against A$26,000 of marketing cost, a gap of about A$4,306. The 280 orders from returning customers (280 × A$41.72 = A$11,682) fill it. A store like this funds new customers from repeat customers, which is fine until repeat buying slows.

The simple payback formula, and why it can mislead

Shopify’s CAC payback article gives the formula as customer acquisition cost divided by average monthly gross profit per customer, giving months. Its example is a CAC of $100 and $25 of average monthly gross profit per customer, giving 4 months.

Apply that to our cohort, using contribution where Shopify’s formula says gross profit, and averaging the 12-month figure evenly: A$64.67 ÷ 12 = A$5.39 a month, and A$50.00 ÷ A$5.39 = 9.3 months. The cohort curve says 3 months. The gap is timing. The formula takes one average monthly figure per customer, so it treats profit as arriving evenly, while in this cohort most of the money (A$41.72 of the A$64.67) arrives with the first order. Build the curve from your cohort report and use the simple formula as a quick check. The curve is closer to when your cash actually comes back.

Lifetime value, on three different bases

Shopify’s customer lifetime value (CLV) article, updated 8 October 2026, gives the core formula as average order value × purchase frequency × average customer lifespan, and a margin-adjusted version that multiplies by gross margin. It separates historical CLV, which measures purchases already made and leaves out future ones, from predicted CLV, which uses forecasts. For a 12-month sheet, historical is what you can defend.

Take the same cohort over 12 months (1.55 orders per customer) against the same CAC of A$50.00, and the ratio depends on which basis you use:

Same customers, same cost, three ratios from 1.29 to 2.36. Shopify’s article treats a 3:1 ratio of lifetime value to CAC as the benchmark, names no original source for it, and says the right target depends on your margins and operating costs. So never compare your ratio with a figure you saw online unless you know which basis it was built on. I use contribution, because it is the one that cash actually follows, and I read it next to the payback period, not instead of it.

Shopify’s cohort report can also show projections, but only for amount spent per customer, based on the previous 24 months of your data, and the help page says they are not a guarantee.

Step 7: The sensitivity table

Now change the two inputs a store can most directly work on after the click: conversion rate and AOV. Hold everything else still:

• sessions stay at 40,000;
• marketing cost stays at A$26,000;
• 65% of orders come from first-time customers, as in the base month (520 of 800);
• sales reversals stay at 5% of order value, product cost stays at 30% of AOV, payment fees stay at 3% of net sales, and shipping and packaging stay at A$8.00 per order. That means an AOV change is modelled as extra product at the same cost ratio.

Contribution margin per order moves with AOV: A$35.51 at A$70, A$41.72 at A$80 and A$47.94 at A$90.

Three things to read from it.

Conversion rate moves CAC, AOV does not. With spend fixed, more visitors buying means more first-time customers for the same money. At 2.4% the fully loaded CAC is A$41.67, roughly the first-order contribution of A$41.72, so the first order about pays for the customer. At 1.6% the CAC is A$62.50, and on the illustrative curve (at A$80 AOV) payback slips past month 6 and lands before month 12. This is the arithmetic behind a point I make often: part of what you pay per customer is how many visitors buy.

Both levers together beat either alone. Moving from the base to 2.4% and A$90 takes monthly contribution after marketing from A$7,376 to A$20,018. Moving the other way, to 1.6% and A$70, turns it into a loss.

The table is a model, not a forecast. It assumes more orders come with the same average margin and that extra customers need no extra spend. Real changes rarely behave that neatly, and a lift in a test is a result for your store and that period, not a promise for the next one.

Which lever matters most, per 1%

Because the sizes of the changes above are arbitrary, compare the levers per 1% relative improvement, one at a time:

In this example, 1% on AOV is worth more than 1% on conversion rate. The reason is the A$8.00 of shipping and packaging, which does not rise when the basket does, so more of each extra dollar of AOV drops to contribution (a 1% conversion lift just sells 1% more orders at the average margin). That ranking depends on this store’s cost structure, so recompute it with yours.

Per 1% is only half the question. The other half is how far you can realistically move each lever. The table tells you where a point of improvement is worth the most, not where it is easiest to find. For that you need data from your own store: session recordings, funnel drop-off by device, tracking you trust, and a test where traffic allows. Our guide to Shopify conversion rate explains how to measure the base, and grow Shopify revenue without more ad spend covers the levers after the click.

What to do with the answer

A few decision rules the sheet supports. These are my rules of thumb, not standards.

1. If contribution after marketing is negative at your current numbers, do not scale spend. Fix the first-order gap (CAC minus first-order contribution) first, through conversion rate, AOV or the offer.
2. If the first order roughly covers CAC, you are in a strong position on cash, and the question becomes how much more traffic you can buy at that CAC before it rises.
3. If payback is longer than your cash can wait, shorten it. The levers are first-order margin, early repeat purchase and cost per customer, in that order of speed. What to do first when CAC is high and lifetime value is low sets out the sequence.
4. If MER is falling while platform ROAS looks stable, look at attribution and at how much of your revenue the ad platforms are claiming before you cut spend.

Where Prism3 fits: the conversion-rate and AOV rows of this table are what we work on, through CRO, plus the tracking check that comes first so the inputs to this sheet can be trusted. SEO and GEO work on organic and AI-search visibility, which is one way new customers can arrive without a media cost, though we promise no particular number. We do not run your ads, email or SMS, so the media and retention rows belong to whoever manages those for you.

Your own sheet: build order

1. Pick the window (whole months) and the revenue basis (net sales). Write both at the top.
2. Pull sessions, conversion rate, orders and first-time customers (Step 1).
3. Pull gross sales, discounts and reversals; calculate AOV and net sales per order (Step 2).
4. Fill product cost and the other variable costs; calculate contribution margin per order and as a percentage (Step 3).
5. Fill marketing cost lines by your written CAC scope; calculate both CACs (Step 4).
6. Calculate MER on media only and on all acquisition costs, plus break-even MER (Step 5).
7. Build the cumulative contribution curve from the cohort report and find the month it passes CAC (Step 6).
8. Copy the base column, change conversion rate and AOV, and let the formulas recalculate (Step 7).
9. Add a “last checked” date to every input block, and repeat monthly. Compare only against months built the same way.

Key takeaways

• A unit economics sheet is a chain: sessions and conversion rate give orders, order value and variable costs give contribution margin, marketing cost and first-time customers give CAC, and a cohort curve gives payback and lifetime value.
• Use net sales, not AOV, for unit economics: AOV excludes post-order adjustments such as refunds. In the illustrative store, AOV is A$80.00 and net sales per order are A$76.00.
• Contribution margin per order (A$41.72 here) is the number that decides how much you can pay to win a customer. Break-even MER is 1 ÷ contribution margin percentage, 1.82 in this example.
• Build payback from a cohort curve. In the example it is about three months from the curve and 9.3 months from the simple formula, because repeat orders arrive early.
• The same customers give an LTV:CAC ratio of 2.36, 1.61 or 1.29 depending on whether you use net sales, gross profit or contribution. State the basis.
• With ad spend fixed, a better conversion rate lowers CAC and a higher AOV does not. Per 1% in this example, AOV was worth slightly more than conversion rate, but the ranking depends on your cost structure and on how far each lever can really move.
• Every number in this post is illustrative. Use the structure, replace the inputs with yours, and label each cell’s source.

FAQs

What should a Shopify unit economics spreadsheet include?

Sessions, conversion rate, orders and first-time customers; gross sales, discounts, refunds and AOV; product cost and the other variable costs per order; marketing cost; and a cohort view of repeat orders. From those it calculates contribution margin per order, CAC, MER, payback and lifetime value. Keep the date window and revenue basis the same in every cell.

What is contribution margin, and how is it different from gross margin?

Shopify defines gross profit as net sales minus cost of goods sold, and gross margin as gross profit divided by net sales. Contribution margin goes further and also subtracts the other costs that vary with each order, such as shipping not recovered and payment fees, before marketing. In the illustrative store, gross profit is A$52.00 per order and contribution is A$41.72.

What is MER, and how is it different from ROAS?

Per Shopify’s MER article, it is total revenue divided by total marketing spend over the same date range, also called blended ROAS. Platform ROAS counts only the revenue the platform attributes to its ads. MER uses your store’s revenue, but it cannot say which campaign drove which sale.

How do I calculate CAC payback for a Shopify store?

Shopify’s formula is CAC divided by average monthly gross profit per customer, which treats profit as arriving evenly. A cohort curve is closer to your cash: add up cumulative contribution per customer by month and find the month it passes your CAC. Shopify’s customer cohort analysis report provides the orders-per-customer data.

What is a good LTV to CAC ratio for a Shopify store?

Shopify’s lifetime value article treats 3:1 as the benchmark, names no original source for it, and says the right target depends on your margins and operating costs. The ratio also depends on the basis: in the illustrative example the same customers give 2.36 on net sales, 1.61 on gross profit and 1.29 on contribution. Use your own history and payback period instead of a quoted number.

Which matters more for profit, conversion rate or AOV?

It depends on your cost structure and on how far each can realistically move. In the illustrative store, 1% on AOV was worth slightly more than 1% on conversion rate, because shipping cost per order does not rise with the basket. Build the sensitivity table with your own numbers, then ask where a real gain is easiest to find.

Keep reading

• What counts as CAC for a Shopify store
• What to do first when CAC is high and lifetime value is low
• How paid, organic, CRO and email work as one system

Is your cost per customer higher than your sheet says it should be?

Spending more to get the same orders? Part of your cost per customer is how many visitors buy. Book a free 30-minute diagnostic: we look at where your store loses buyers after the click, using your own numbers, and tell you what to fix first. We do not run ads or email. No pitch. No email gate.

If you want to see how we approach conversion and tracking, read about our Shopify CRO service.