Calculating Customer Lifetime Value (CLV) in SQL

Customer Lifetime Value (CLV or LTV) is arguably the most important metric in business. It answers: "How much can we afford to spend to acquire a customer?"

If your CLV is $500, you can profitably spend $100 on ads to get them. If your CLV is $50, spending $100 will bankrupt you.

A Real Business Scenario: Paid Acquisition Budgeting

CLV becomes operational when a growth or finance team needs to decide whether a channel is scalable.

Imagine a SaaS company with three acquisition channels:

• paid search brings in high-intent customers but costs more
• affiliates bring in cheaper users with lower retention
• outbound sales brings in large accounts with slower payback

The team does not just want "average revenue per customer." They want to know whether a newly acquired customer is likely to repay acquisition cost fast enough and profitably enough. That is where CLV becomes useful. It turns raw order or subscription history into a planning input for CAC targets, retention work, and pricing discussions.

1. Historical CLV (The Easy Way)

The simplest version is to just look at how much customers have already spent.

SELECT
    customer_id,
    SUM(amount) as lifetime_revenue
FROM orders
GROUP BY customer_id;

This is accurate but backward-looking. It doesn't tell you what a new customer is worth today.

2. Simple Predictive CLV (ARPU / Churn)

A common formula for subscription businesses (SaaS) is:

Formula:

CLV = (Average Monthly Revenue per User (ARPU)) / (Monthly Churn Rate)

If average users pay $10/month and 5% cancel every month, expected lifetime is $1 / 0.05 = 20$ months. $20 \times $10 = $200$ CLV.

A Common Mistake: Treating Revenue as Value

The formula looks simple enough that teams often compute a number quickly and assume it is decision-ready. The most common mistake is using top-line revenue as if it were actual economic value.

That causes several problems:

• refunds, credits, and chargebacks are ignored
• one-time setup fees get blended with recurring revenue
• gross revenue is treated like gross margin or contribution margin
• churn is assumed to be stable when early-month retention is still moving

If the business is using CLV to set acquisition budgets, margin-adjusted CLV is usually more useful than raw revenue CLV. Otherwise the number can look healthy while the channel is still unprofitable.

Interactive Playground: Calculating ARPU and Churn

Let's calculate the inputs for this formula from raw subscription logs.

Cohort-Based CLV

The most accurate way to measure CLV is via Cohorts. You group users by the month they joined (e.g., "Jan 2024 Cohort") and track their cumulative spend over time.

This requires pivot tables or complex self-joins, but it reveals trends like "Newer cohorts are spending less than older cohorts," which a simple average would hide.

It also helps you separate product changes from customer-mix changes. If your blended CLV drops, you do not yet know whether retention worsened or whether a new acquisition channel simply brought in a different customer type. Cohorts make that visible.

Historical vs Predictive CLV

These two versions answer different business questions:

• Historical CLV asks: how much value has this customer already generated?
• Predictive CLV asks: what is a customer likely worth going forward if current behavior continues?

That distinction matters because teams often compare them as if they were the same metric.

Historical CLV is safer and easier to compute. Predictive CLV is more useful for acquisition and budgeting decisions, but it depends heavily on assumptions about churn, retention, and revenue stability.

What Makes CLV Hard in Practice

CLV looks simple in slide decks because the formulas are short. In real data work, the complexity comes from definitions:

• Which revenue should count?
• Do refunds and discounts reduce value?
• Is CLV gross revenue or contribution margin?
• Are one-time buyers and subscription customers handled the same way?
• Do you calculate at customer level, cohort level, or segment level?

If those definitions are fuzzy, the resulting CLV number may look precise while being strategically misleading.

A Practical Workflow for CLV SQL

Use this sequence to keep CLV work grounded:

1. Start with historical revenue by customer.
2. Segment or cohort the customer base.
3. Measure retention or churn separately.
4. Add the predictive assumptions only after the underlying inputs are trustworthy.
5. Compare new cohorts against older ones instead of relying on one global average.

That progression is important because predictive CLV is only as good as the retention and revenue assumptions underneath it.

Boundary and Performance Notes

CLV logic tends to start as one query and then grow into a small analytics pipeline. A few practical constraints show up quickly:

• event-level billing tables are often too granular, so it is usually better to aggregate to customer-month before calculating ARPU or retention
• churn definitions vary across products, especially when pauses, reactivations, and annual plans exist
• joins against refunds, invoice adjustments, or plan metadata can duplicate revenue unless the grain is controlled carefully
• cohort calculations often involve repeated scans over the same customer history, so pre-aggregated monthly facts can be easier to maintain than recomputing from raw events every time

The important habit is to lock the business definition before optimizing the SQL. A fast CLV query with the wrong grain is still the wrong answer.

When NOT to Use One Blended CLV Number

Avoid leaning on a single site-wide CLV number when:

• the business has multiple customer segments with radically different retention patterns
• acquisition channels bring in meaningfully different customer quality
• the product mixes one-time purchases and subscriptions
• the finance decision depends on payback period rather than lifetime revenue

In those cases, segmented CLV or cohort-level retention tables are more useful than a single blended metric.

Official References

• Google Analytics lifecycle and retention concepts for how product teams often frame retention and lifecycle value analysis.
• PostgreSQL window function documentation for the cohort and cumulative-value calculations that usually support advanced CLV work.
• Shopify on customer lifetime value for a practical business framing of CLV assumptions and use cases.

Related Articles

• Cohort Analysis with SQL for the retention structure that makes cohort-based CLV much more informative.
• Customer Segmentation with RFM Analysis in SQL for slicing the customer base before comparing value across groups.
• Time Series Analysis with SQL for tracking growth, trends, and rolling metrics around the revenue inputs feeding CLV.

Conclusion

CLV is only useful when the underlying revenue, churn, and customer definitions are defensible. SQL gives you a practical way to compute those pieces, compare cohorts, and turn customer history into a planning input. Start with historical revenue, make the retention logic explicit, and only then trust the predictive number enough to guide acquisition or pricing decisions.