How to use Building Contribution Margin-Adjusted LTV From Shopify Order Data

Most Shopify stores in the €1M–€15M range are still running LTV off gross revenue, usually because that's what the admin dashboard hands them for free. The problem shows up the moment you divide it by CAC: the ratio flatters everything, and paid channels look far healthier than they are.

This guide walks through the four blocks of work it takes to fix that on Shopify data specifically: pulling the right fields, attaching variant-level COGS, subtracting the costs Shopify doesn't compute for you, and rolling everything into a cohort curve you can chart. Each block has a dedicated spoke page if you want to go deeper on the mechanics.

Step 1: Pulling the right fields from Shopify

Before you touch a spreadsheet, pick your extraction method. The three realistic options are the Shopify Admin CSV export, the Bulk Operations API, or a warehouse sync (Fivetran, Hightouch reverse, a native connector). The right choice depends on order volume and how often you need to refresh the model.

For a store doing under ~50k orders/year, the Admin export is fine for a quarterly refresh. Above that, or if you need weekly cohort updates, jump straight to Bulk Operations or a warehouse sync — the CSV export truncates line-item detail and loses refund timing. There's a dedicated comparison of Shopify Admin export vs Bulk Operations API vs warehouse sync if you're on the fence.

Whichever route you pick, you need these fields at minimum: order_id, customer_id, created_at, financial_status, currency, subtotal_price, total_discounts, total_shipping_price_set, total_tax, and — critically — line_items with variant_id, quantity, price, and discount_allocations. On the customer side, first_order_date (or the derived earliest created_at per customer_id) becomes your cohort key.

Step 2: Attaching COGS at the variant level

Shopify has a `cost` field on the variant, but most stores either don't populate it, populate it once and never update it, or use landed cost inconsistently across SKUs. That's the single biggest source of error in a CM-LTV model.

Build a variant-cost table externally — a Google Sheet keyed on variant_id or SKU is fine to start — and left-join it onto every line item at query time. Update it monthly if your COGS moves with FX or supplier pricing. If you're missing the variant.cost field entirely, there's a spoke on mapping variant-level COGS to Shopify line items without a cost field that covers the SKU-parsing fallbacks.

The gap in that chart — roughly 3.1× at month 24 — is exactly what a gross-revenue LTV hides. If your paid-media team is bidding against the top line, they're overpaying by that same multiple. This is the mechanism behind why gross-revenue LTV overstates LTV:CAC by 2-3× and why finance quietly disagrees with growth on channel ROI.

Step 3: Subtracting real shipping, payment fees, and discounts

The Shopify order object gives you `total_shipping_price_set` — that's what the customer paid, not what shipping actually cost you. Real outbound cost lives in your carrier invoices (Sendcloud, ShipStation, DHL portal) and needs to be joined in by shipping zone and weight tier. Netting the customer-paid amount against the invoiced amount is what the spoke on real shipping cost vs customer-paid shipping in Shopify exports walks through in detail.

Payment fees are more mechanical: Shopify Payments takes a percentage plus a fixed fee per transaction, and third-party gateways (PayPal, Klarna, Mollie) each have their own schedule. Allocate the fee on the settled amount, per order. Discounts are already in `total_discounts` — the tricky part is attributing automatic vs code-based discounts to the right CM line, which matters when you later slice LTV by acquisition offer.

Two more line items complete the stack: refunds and multi-currency. Refunds need to hit the cohort of the original order, not the month they were processed — otherwise your recent cohorts look artificially healthy. And if you sell in more than one currency, convert every order to a base currency at the order's created_at date, not today's rate; there's a spoke on multi-currency Shopify stores in a single CM-LTV model that covers the FX policy choices.

Step 4: Rolling per-order CM into a cohort LTV curve

Once every row in your orders table has a defensible `contribution_margin_eur` column, the cohort math is straightforward. Assign each customer to their first-order month, then for every order compute `months_since_first_order = date_diff(order.created_at, customer.first_order_month, 'month')`. Group by cohort_month and months_since, sum CM, and divide by the cohort's headcount.

The output is a triangle: rows are acquisition cohorts, columns are months of tenure, cells are cumulative CM per customer. Reading down the first column gives you first-order CM by cohort (a proxy for cohort quality). Reading across a row gives you the LTV curve for that cohort. The mechanics of translating that triangle into a smoothed curve are covered in rolling per-order CM into a monthly cohort LTV curve.