Growth 14 min read Advanced

Customer LTV & Cohort Analysis in Excel: No Code Required

Stop looking at total revenue. Cohort analysis reveals which customer acquisition months drive the most lifetime value. Learn to build cohort tables and LTV calculations in Excel for smarter marketing spend.

Platforms: Shopify
Customer LTV & Cohort Analysis in Excel: No Code Required

The Question That Changes Your Marketing Strategy

You spend $50K on Facebook ads in March and $50K on Google ads in April. Both drive 1,000 customers. Which was the better investment? You can't tell from first-month revenue alone. You need to track these cohorts over time: March Cohort (Facebook):
  • Month 1: $40K revenue (40 customers)
  • Month 2: $12K (120 repeat orders)
  • Month 3: $8K (80 repeat orders)
  • 3-Month LTV: $60K / 1,000 = $60 per customer
April Cohort (Google):
  • Month 1: $50K revenue (50 per customer)
  • Month 2: $4K (40 repeat orders)
  • Month 3: $2K (20 repeat orders)
  • 3-Month LTV: $56K / 1,000 = $56 per customer
Facebook looked worse initially but has better repeat behavior. Over 12 months, the gap widens. This is why cohort analysis matters.

Building Cohort Analysis in Excel

1

Structure Your Customer Data

Need columns: Customer ID, Order Date, Order Value, First Purchase Date. Export from Shopify: Customers > Export. Use formula =MIN(OrderDate) to find each customer's first purchase date.

Structure Your Customer Data
2

Create Cohort Assignment

Add column Cohort Month =TEXT(FirstPurchaseDate,"YYYY-MM"). This groups customers by acquisition month. All January 2024 customers = one cohort.

Create Cohort Assignment
3

Calculate Months Since First Purchase

Add column: MonthsSince =DATEDIF(FirstPurchaseDate, OrderDate, "M"). Order from cohort's Month 0 = 0, Month 1 = 1, etc. This lets you track behavior over time.

Calculate Months Since First Purchase
4

Build Cohort Pivot Table

Pivot: Rows=Cohort Month, Columns=Months Since, Values=Sum of Order Value. This creates the classic cohort table showing revenue by cohort by month.

Build Cohort Pivot Table
5

Calculate Cumulative LTV

For each cohort, cumulative LTV = sum across all months. Formula: =SUM(Month0:Month12)/CohortSize. Shows total value generated per customer over 12 months.

Calculate Cumulative LTV
6

Calculate Repeat Purchase Rate

By cohort by month: =COUNTIFS(Cohort,ThisCohort,MonthsSince,ThisMonth)/CohortSize. Shows % of cohort still buying. Strong cohorts stay above 15% by Month 6.

Calculate Repeat Purchase Rate
7

Build LTV Projection Model

Use historical repeat rate to project future LTV. If 20% buy in Month 1, 15% in Month 2, project 12-month LTV even for new cohorts with only 3 months data.

Build LTV Projection Model

The Key Metrics in Cohort Analysis

1. Month-0 AOV (First Purchase Value)

Higher is better, but not the whole story. Good: $75+ Concerning: Under $30

2. Month-1 Retention Rate

% of customers who buy again in Month 1. Excellent: 25%+ Good: 15-25% Average: 8-15% Poor: <8%

3. Month-6 Cumulative LTV

Total revenue per customer after 6 months. Calculation: =SUM(M0:M6 revenue) / Cohort Size Target: 2-3x Month-0 AOV

4. Cohort Half-Life

Months until 50% of repeat revenue is generated. Fast: 3-4 months (consumables, subscriptions) Slow: 8-12 months (durable goods, fashion)

5. LTV:CAC Ratio

Lifetime value vs. customer acquisition cost. Healthy: 3:1 or better Workable: 2:1 Unsustainable: <2:1
Cohort Quality M0 AOV M1 Retention M12 LTV Characteristics
Exceptional $100+ 30%+ $300+ Brand advocates, high repeat, profitable at scale
Strong $75-100 20-30% $200-300 Solid repeat behavior, sustainable growth
Average $50-75 12-20% $120-200 Decent but needs optimization
Weak $30-50 8-12% $60-120 Marginal, high CAC risk
Poor <$30 <8% <$60 One-and-done customers, unprofitable

The Cohort Insight That Changes Everything

December cohorts often have lower LTV than March cohorts. Holiday shoppers buy gifts and never return. Don't judge channel performance by holiday acquisition costs alone.

Common Mistakes to Avoid

Mistake: Comparing cohorts before they have equal time to mature
Solution: Only compare cohorts at the same age. Compare January at Month-6 to February at Month-6, not January at Month-12 to February at Month-1.
Mistake: Not accounting for seasonal cohort differences
Solution: Q4 cohorts naturally differ from Q2. Compare Q4-2024 to Q4-2023, not Q4-2024 to Q2-2024.
Mistake: Focusing only on total LTV without looking at repeat rate curves
Solution: A high LTV with 5% Month-1 retention is worse than moderate LTV with 25% retention. The second compounds.
Mistake: Not segmenting cohorts by acquisition source
Solution: Facebook January and Google January should be separate cohorts. Source matters more than month.

Verification Checklist

  • You have at least 6 cohorts with at least 6 months of tracking data
  • You can identify your best-performing acquisition month and channel
  • You know your average Month-1 and Month-6 retention rates
  • You've stopped or reduced spend on channels that drive low-LTV cohorts

Frequently Asked Questions

Minimum 100 customers for statistical relevance. Under 50, variance is too high. Under 20, essentially useless.
Start with revenue (simpler). Advanced: track contribution margin (revenue minus COGS and direct variable costs). Never use gross revenue without accounting for refunds.
Start with 3-month analysis. Use industry benchmarks to project 12-month. Update projections as real data comes in.
Monthly for tactical decisions (which channels are working this quarter). Quarterly for strategic decisions (should we shift budget allocation).
Yes, but with modifications. B2B cohorts might be tracked by quarter not month, and measured by contract value not transaction count.