Coderhouse goes global

🌍

Read more

What Is Customer Lifetime Value (CLV) and How Analysts Calculate It

AI Engineering

Build and ship real applications with LLMs, agents and automation.

8 weeks · Live online · View course →

Data Analytics

Go from zero to job-ready analyst with SQL, Python and BI tools.

10 weeks · Live online · View course →

Dan Patiño

AI Strategy & Innovation at Coderhouse

Data

What Is Customer Lifetime Value (CLV) and How Analysts Calculate It

What Is Customer Lifetime Value (CLV) and How Analysts Calculate It

Customer lifetime value (CLV or LTV) is an estimate of the total revenue, or profit, a business can expect from a customer over the entire relationship. Instead of judging a customer by a single purchase, CLV asks a longer question: how much is this relationship worth over time?

That question drives some of the biggest decisions in a company: how much to spend on acquiring customers, which segments to prioritize, and where retention efforts pay off. For data analysts, CLV is one of the most useful metrics to master because it connects transaction data directly to strategy. This guide explains what CLV is, how to calculate it, and how analysts use it in practice.

What is customer lifetime value?

As Wikipedia's entry on customer lifetime value explains, CLV is a prediction of the net value attributed to the entire future relationship with a customer. In practice, teams use two flavors:

  • Historical CLV: the revenue or margin a customer has generated so far. Easy to compute, but it only looks backward.

  • Predictive CLV: an estimate of future value based on purchase patterns, retention and margin. More useful for decisions, but it depends on good assumptions.

Why CLV matters

Keeping customers is usually cheaper than finding new ones. Harvard Business Review notes that acquiring a new customer can cost several times more than retaining an existing one, which is why understanding the long-term value of each customer matters so much. CLV helps teams:

  • Set acquisition budgets. Comparing CLV with customer acquisition cost (CAC) shows whether growth is profitable.

  • Prioritize segments. Some channels bring cheap customers who leave quickly; others bring fewer customers who stay for years.

  • Justify retention work. Small improvements in churn can raise lifetime value substantially.

  • Personalize offers. High-value customers may deserve different service, onboarding or loyalty programs.

How to calculate customer lifetime value

The simple formula

A common starting point multiplies three components:

CLV = Average order value × Purchase frequency × Customer lifespan

For example, if customers spend $50 per order, order 4 times a year, and stay for 3 years on average, CLV is $50 × 4 × 3 = $600 in revenue. Guides such as Shopify's CLV guide use this same structure for ecommerce.

Adding margin

Revenue is not profit. Multiply by gross margin to get a more honest number: with a 40% margin, the $600 above becomes $240 in gross profit per customer.

The subscription formula

For subscription businesses, a common shortcut is:

CLV = (Average revenue per account × Gross margin) ÷ Churn rate

If an account pays $100 per month, margin is 80% and monthly churn is 4%, CLV is ($100 × 0.8) ÷ 0.04 = $2,000. This shows why churn is so powerful: cutting churn from 4% to 3% raises CLV to about $2,667.

How analysts calculate CLV with real data

1. Prepare the transaction table

You need customer ID, order date and order value, ideally with cost or margin. Clean duplicates, refunds and test orders first.

2. Compute per-customer metrics in SQL

Group by customer to get first and last order date, number of orders and total revenue. These are the same building blocks used in RFM analysis, so the two techniques work well together.

3. Segment by cohort

Averages across all customers hide important differences. Group customers by acquisition month or channel and track cumulative revenue per customer over time; cohort analysis makes it easy to see which groups are becoming more or less valuable.

4. Estimate the future

For predictive CLV, analysts project retention curves forward or use statistical models (such as probabilistic “buy till you die” models) and machine learning regressors trained on early customer behavior. Start simple and validate predictions against what customers actually spend later.

5. Report it in context

CLV is most useful next to CAC, payback period and churn. Present it by segment in a dashboard so marketing, product and finance can act on it.

Common CLV mistakes

  • Using revenue instead of margin. High revenue customers can still be unprofitable if discounts, shipping or support costs are high.

  • Assuming customers stay forever. Lifespan assumptions should come from observed retention, not optimism.

  • One number for everyone. A single company-wide CLV hides the segments that create or destroy value.

  • Ignoring churn signals. CLV and churn analysis should be read together; a falling retention curve will lower future CLV before revenue shows it.

Where AI fits in

AI assistants can now draft the SQL for CLV calculations and explain cohort tables in plain language, while machine learning models predict the future value of new customers after only a few interactions. That speed is useful, but the fundamentals stay the same: clean data, honest assumptions about margin and churn, and validation against real outcomes. Teams like those described by Qualtrics also combine CLV with customer experience data to understand why valuable customers stay or leave.

FAQ

Is CLV the same as LTV?

Yes. CLV, CLTV and LTV are used interchangeably to describe customer lifetime value.

What is a good CLV to CAC ratio?

Many teams aim for CLV comfortably above CAC, often citing a benchmark around 3:1, but the right ratio depends on margins, payback period and growth stage. Use it as a guide, not a rule.

Can I calculate CLV in Excel?

Yes. An orders export and a pivot table give you average order value, frequency and lifespan per customer. SQL or Python become more practical as data grows.

Keep learning with Coderhouse

CLV is one of the clearest ways for an analyst to show business impact. If you want to calculate it confidently with SQL, Excel and dashboards, the Data Analytics Course is the natural next step. Explore all three paths:

Dan Patiño

I'm Dan Patiño, head of AI Strategy & Innovation at Coderhouse. My day-to-day work involves merging the tactical management of e-commerce (CRO, Email Marketing and SEO) with the development of disruptive solutions. I specialize in building internal AI-powered apps to automate tasks and boost innovation within the team. I firmly believe that technology is strategy's best ally. To dive deeper into my professional journey, I'll be waiting for you on my LinkedIn profile.