TL;DR Most LTV calculations are wrong because they use averages instead of cohorts. A proper LTV dashboard tracks retention curves, CAC payback periods, and repeat purchase rates by acquisition month. Let us build yours.
Why Most LTV Calculations Are Wrong
Here’s the formula everyone uses:
LTV = Average Order Value x Purchase Frequency x Customer Lifespan
It’s simple. It’s intuitive. And it’s dangerously misleading.
The problem: averages hide everything that matters. A customer acquired via Meta in January behaves completely differently from one acquired via Google in June. Bundling them together gives you a number that describes nobody.
Worse, most brands calculate LTV using all-time data, including customers acquired years ago when the business was different. That historical average has zero predictive power for customers you’re acquiring today.
What a Real LTV Dashboard Tracks
A proper LTV dashboard answers these questions:
- How much revenue does a cohort generate over 3, 6, 12 months?
- When does each cohort pay back its acquisition cost?
- Which channels produce the highest-LTV customers?
- Is LTV trending up or down over time?
- At what point do customers stop buying?
Here are the metrics that matter:
| Metric | What It Tells You | Update Frequency |
|---|---|---|
| Revenue per cohort (cumulative) | Total value generated over time | Weekly |
| CAC payback period | Months until a cohort becomes profitable | Monthly |
| Repeat purchase rate | % who buy again within 90 days | Weekly |
| Retention curve | When customers drop off | Monthly |
| LTV:CAC ratio by channel | Which channels are actually profitable | Monthly |
| Average orders per customer (by cohort) | Purchase velocity | Weekly |
The Cohort-Based Approach
Instead of one LTV number, you track LTV by acquisition cohort. A cohort is simply all customers who made their first purchase in a given month.
Here’s what a cohort LTV table looks like:
| Cohort | Month 0 | Month 3 | Month 6 | Month 12 |
|---|---|---|---|---|
| Jan 2025 | $52 | $74 | $98 | $131 |
| Feb 2025 | $48 | $69 | $89 | $118 |
| Mar 2025 | $55 | $81 | $107 | — |
| Apr 2025 | $51 | $72 | — | — |
This tells you immediately: January customers are your best cohort. March is close. February underperformed. You can then investigate why — what campaigns ran, what products launched, what changed.
Building the Dashboard: Technical Approach
Data Requirements
You need:
- Order data with customer ID, order date, revenue, and first-order flag
- Acquisition data — channel, campaign, date of first purchase
- Cost data — ad spend by channel and month
If you’re on Shopify, all of this lives in your orders and customers tables. Fivetran pulls it into BigQuery automatically.
The SQL Structure
Your transformation layer needs three core models:
- Customer spine — one row per customer with first order date, acquisition channel, and cohort month
- Cohort revenue — cumulative revenue per customer, grouped by months since acquisition
- Cohort summary — aggregated metrics per cohort (average revenue, retention rate, order count)
These models refresh daily and power your Looker Studio dashboard.
Visualisation Design
The dashboard should have four sections:
Section 1: Cohort revenue curves. Line chart showing cumulative revenue per cohort over months since acquisition. Each line is a cohort. Newer lines should trend above older ones if the business is improving.
Section 2: CAC payback. Bar chart showing months to payback by cohort and channel. Anything over 6 months is a warning sign.
Section 3: Retention heatmap. Classic cohort grid showing % of customers who purchased in each subsequent month. Dark green = high retention, red = drop-off.
Section 4: Channel comparison. Table showing LTV:CAC ratio by acquisition channel. This is where you decide budget allocation.
💡 This is what we do. We build LTV dashboards for ecommerce brands using BigQuery and Looker Studio. Cohort models, retention curves, CAC payback — all automated, refreshing daily. Delivered in ~3 weeks. Book a 20-minute discovery call — no pitch, just scoping.
Common Mistakes to Avoid
Mistake 1: Including refunds in LTV. Always use net revenue (after refunds and discounts). Gross LTV is vanity.
Mistake 2: Ignoring COGS. Revenue-based LTV is useful, but contribution-margin-based LTV is what actually matters for profitability decisions. If you have product cost data, use it.
Mistake 3: Projecting LTV too far out. A 24-month LTV projection for a brand that’s been around 18 months is fiction. Only project as far as you have data to validate.
Mistake 4: Not segmenting by channel. A blended LTV number hides the fact that your Meta customers might have 2x the LTV of your Google Shopping customers. Segment everything.
Mistake 5: Updating quarterly. LTV dashboards should refresh daily. Cohorts mature continuously. Monthly at minimum, daily if possible.
What Good LTV Numbers Look Like
Benchmarks vary wildly by category, but for DTC ecommerce:
| Metric | Healthy | Warning | Alarm |
|---|---|---|---|
| LTV:CAC ratio | >3:1 | 2-3:1 | <2:1 |
| CAC payback | <3 months | 3-6 months | >6 months |
| 90-day repeat rate | >25% | 15-25% | <15% |
| 12-month retention | >30% | 20-30% | <20% |
If your LTV:CAC is below 3:1, you’re either spending too much to acquire customers or not retaining them well enough. The dashboard will tell you which.
From Insight to Action
An LTV dashboard isn’t just for reporting. It should drive decisions:
- Budget allocation: Shift spend toward channels with highest LTV:CAC
- Retention investment: If payback is >4 months, invest in email/SMS flows
- Product development: If certain products lead to higher repeat rates, promote them to new customers
- Subscription offers: If churn spikes at month 3, introduce a subscription at month 2
The best ecommerce teams review LTV cohorts weekly and make allocation changes monthly.
Know someone drowning in spreadsheets? Share this guide with them.
If this sounds like more work than you want to take on, that’s what we do at Chartica. Book a 20-minute discovery call — we’ll scope it out, no pitch.