The DTC Operator's Guide to Shopify LTV and Cohort Analysis (With Templates)

Your ad platforms report on clicks. Your Shopify dashboard reports on orders. But neither tells you whether the customer you acquired on Tuesday will still be worth money to you in 18 months. That is the LTV (Lifetime Value) question, and until you answer it, you are running a cash flow operation, not a compounding brand. We are going to show you the actual math, the SQL queries, and provide the exact spreadsheet templates we use to run cohort analysis across the Sentinel ecosystem.

By the end of this guide, you will leave with a working cohort analysis spreadsheet, and more importantly, the fluency to interpret what those numbers are screaming at you. No fluff, just the operator's reality of dealing with retention math.

Why LTV matters more than ROAS for DTC.

ROAS (Return on Ad Spend) lies because it is single-transaction. If you spend $50 to acquire a customer who buys a $60 item, your ROAS looks terrible. Most media buyers would kill that campaign on day three. But if that same customer returns four times over the next year to buy $200 worth of goods with zero additional acquisition cost, killing that campaign was the worst financial decision you could make.

This is the "negative early ROAS, positive 12-month LTV" pattern. Every subscription business and high-replenishment CPG brand lives or dies by this curve. If you manage the business purely by what Meta reports as a 7-day click ROAS, you will structurally underinvest in your best customers because the payback period is longer than Facebook's attribution window.

This is why cohort analysis is the only honest way to measure DTC growth. You group customers by the month you acquired them, and you watch how much revenue that specific group generates in month one, month two, month three, and beyond. This exposes the reality of your business. We recently audited a Shopify store that thought it was profitable based on a 2.5x ROAS, but cohort analysis revealed their 6-month retention had dropped to zero. They were running on a treadmill, churning every user they bought.

The 3 ways to calculate Shopify LTV.

If you search for "how to calculate LTV," you will find a dozen conflicting formulas. In reality, there are only three methods that matter, and you graduate through them as you scale.

Method 1: Historic LTV (simplest, fastest)

Historic LTV simply takes the total revenue generated by a group of customers and divides it by the number of customers. It looks backward and assumes nothing.

Historic LTV = Total Revenue from Cohort / Number of Customers in Cohort

If you are using a data warehouse, the SQL looks like this:

SELECT 
  DATE_TRUNC('month', first_order_date) AS acquisition_month,
  COUNT(DISTINCT customer_id) AS total_customers,
  SUM(net_revenue) AS total_historic_revenue,
  SUM(net_revenue) / COUNT(DISTINCT customer_id) AS historic_ltv
FROM customers
GROUP BY 1
ORDER BY 1 DESC;

When it's enough: If your store is less than a year old, or you just need a quick directional check, historic LTV is fine. Limitations: It punishes your newest customers. A customer you acquired last week has a terrible historic LTV compared to someone acquired three years ago. It tells you nothing about the future.

Method 2: Predictive LTV (probabilistic models)

Predictive LTV uses models (like the Buy Till You Die / BG-NBD model) to guess how much a customer will spend in the future based on their recency, frequency, and monetary value (RFM).

These models calculate the probability that a customer is still "alive" (hasn't churned) and multiplies it by their expected transaction value. When to use: When you are forecasting cash flow or calculating maximum allowable CAC for a mature brand with a deep historical dataset.

Method 3: Cohort-based LTV (the gold standard)

This is what we will build today. Instead of a single blended number, cohort LTV plots revenue over time. It shows you the curve. You can see that your Black Friday cohort spends aggressively in Month 0, but never returns, while your regular February cohort has steady 15% repeat purchase rates every month.

Building your first cohort analysis.

Here is the technical, step-by-step process to build a retention triangle. You can do this in Google Sheets, or pipe it through an integration into a BI tool.

Step 1: Export Shopify orders by month.

You need row-level order data. Go to Shopify Admin > Analytics > Reports > Sales over time. Export the raw data as a CSV. You must ensure you are exporting Net Sales (gross minus discounts and returns), not Gross Sales. Include the customer_id or email address, the order date, and the net sales amount.

Step 2: Define your cohorts.

In your spreadsheet or SQL query, identify the first_order_date for every unique customer. This defines their cohort. If John bought his first pair of shoes in March 2025, he belongs to the "2025-03 Cohort" forever, even when he buys again in November.

Step 3: Calculate revenue per cohort by month.

Now, calculate how many months have passed between the customer's first order and their subsequent orders. The first order happens in "Month 0." An order 35 days later happens in "Month 1." Sum the net revenue for each cohort, grouped by the "Months Since Acquisition" index.

Step 4: Build the triangle visualization.

Pivot the data. Put your Acquisition Cohorts down the Y-axis (rows) and the Months Since Acquisition across the X-axis (columns). Apply conditional formatting (a green color scale works best) to immediately visualize the decay.

Step 5: Interpret what you're seeing.

Read horizontally to see the lifecycle of a specific group. Read vertically to see how retention is trending over time (e.g., is Month 1 retention getting better or worse for newer cohorts?). Read diagonally to spot calendar seasonality (like the December bump that lifts all older cohorts simultaneously).

What cohort analysis reveals.

Which acquisition channel delivers compounding LTV.

If you slice your cohorts by acquisition source (UTM parameter), you might find that Google Search customers have an LTV of $250 over 6 months, while TikTok customers plateau at $90. Suddenly, paying a higher CAC on Google becomes entirely justified.

Which product mix predicts repeat purchase.

Slice cohorts by the first product purchased. Customers who buy the starter kit might have a 40% repeat rate in Month 1, while customers who buy a single accessory might have a 5% repeat rate. Your objective shifts from "get cheap traffic" to "force traffic to buy the starter kit."

When to panic vs when to ignore a dip.

A scary Month 3 drop for a specific cohort might just mean that cohort was acquired during a massive discounting event where you pulled forward future demand. Cohort analysis gives you the context to know whether a metric is broken, or just behaving according to seasonal physics.

Scaling beyond spreadsheets.

Eventually, Excel breaks. Signals you have outgrown Google Sheets include: spending 4 hours a week manually running exports, hitting row limits, or needing to slice cohorts by three variables at once (e.g., month + channel + first product).

When this happens, you have options. Tools like Lifetimely and Peel are excellent out-of-the-box SaaS products that visualize this for you automatically. Polar provides a deeper BI layer for operators comfortable with data models.

Or, you take the Sentinel approach. We believe that dashboards are where data goes to die. Sentinel plugs into Shopify, runs the cohort math natively, and simply emails you a plain-English summary every week. "Your March cohort is retaining 15% better than February. Keep spending on the creative that acquired them." No spreadsheets required.

Frequently Asked Questions.

How often should I rerun this analysis?

Monthly is the gold standard for most DTC brands. Weekly is too noisy and highly subject to variance. Quarterly is too slow to adjust your ad spend.

Can Shopify native analytics do this?

Not really. Shopify provides a basic "returning customer rate," but it lacks the depth of true cohort analysis where you track a specific month's acquisition group over time to see their exact revenue decay curve.

How do I factor in refunds and returns?

Your query or export must calculate Net Sales (Gross Sales - Discounts - Returns). Never run LTV on gross sales, or you will over-index on customers who buy 5 sizes and return 4.

What's a "good" LTV:CAC ratio?

A standard healthy DTC ratio is 3:1. However, if your payback period is fast (under 30 days), you can scale aggressively with a 2:1 ratio. If your payback period is 12 months, you need a 4:1 ratio to survive the cash flow gap.