Customer LTV & Cohort Analysis in Excel: No Code Required

Updated February 2025 14 min read Advanced

Shopify

TL;DR

Cohort = customers acquired in same month, tracked over time
LTV = average revenue per customer over their lifetime with your brand
Best cohorts: high month-1 AOV + strong repeat rate + low churn
Use insights to focus marketing on channels that drive valuable cohorts

Prerequisites

Customer order history with first purchase date
At least 12 months of business history
Order-level data including customer ID and order date
Basic pivot table knowledge

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

2

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.
2

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.
2

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.
2

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.
2

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.
2

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.

,,,,,,

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

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.

Get automated cohort analysis showing which marketing channels drive the most valuable customers.

Analyze Customer LTV

Make smarter marketing decisions

Common Mistakes to Avoid

Learn from these common pitfalls to ensure success

❌

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.

❌

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.

❌

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.

❌

Not segmenting cohorts by acquisition source

Solution: Facebook January and Google January should be separate cohorts. Source matters more than month.

❌

Giving up on cohorts after Month-3

Solution: Some products have 12-18 month repurchase cycles. Track cohorts for at least 18 months to see full picture.

How to Verify Your Numbers

Ensure accuracy with these verification steps

✓

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

✓

You can predict 12-month LTV for new cohorts using historical patterns

Frequently Asked Questions

How many customers do I need in a cohort for it to be meaningful?

Minimum 100 customers for statistical relevance. Under 50, variance is too high. Under 20, essentially useless.

Should I track revenue or profit in cohorts?

Start with revenue (simpler). Advanced: track contribution margin (revenue minus COGS and direct variable costs). Never use gross revenue without accounting for refunds.

What if my business is too new for 12-month cohorts?

Start with 3-month analysis. Use industry benchmarks to project 12-month. Update projections as real data comes in.

How often should I review cohort analysis?

Monthly for tactical decisions (which channels are working this quarter). Quarterly for strategic decisions (should we shift budget allocation).

Can I use cohort analysis for B2B or high-ticket items?

Yes, but with modifications. B2B cohorts might be tracked by quarter not month, and measured by contract value not transaction count.

Ready to Transform Your Reporting?

Start with a free report and see exactly what you'll get every time.

Get 1 Report Free - No Card View Pricing