Calculating LTV in SQL — Patterns from Real SaaS

Three flavours of LTV you'll be asked to compute, and the SQL that gets each one right. FIND_VIDEO: search 'customer lifetime value SQL tutorial' — recommended channel: Data with Danny. Aim for 11 min or under.

15 minutesVideo LessonPDF notes

Watch Calculating LTV in SQL — Patterns from Real SaaS on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

CLV Overview — The instructor outlines customer segmentation goals and defines core metrics using AdventureWorks 2019.
Schema Examination — Key columns across the Customer and SalesOrderHeader tables are reviewed for joins and aggregations.
Aggregating Metrics — A query is built to group orders by customer ID, calculating order counts, AOV, and lifespan.
Applying CLV Formula — Base metrics inside a CTE are multiplied together to calculate projected customer lifetime value.
Profitability Segmentation — A CASE expression segments customers into non-profitable, profitable, and very profitable tiers.

PDF notes

Bottom line: You will calculate Customer Lifetime Value (CLV/LTV) and segment customer accounts using transactional sales data in SQL Server. By aggregating order frequency, average order value, and account lifespan into structured CTEs, you can classify users into distinct profitability tiers for targeted retention.

This lesson on Calculating LTV in SQL — Patterns from Real SaaS is hands-on and example-driven. You will calculate Customer Lifetime Value (CLV/LTV) and segment customer accounts using transactional sales data in SQL Server. By aggregating order frequency, average order value, and account lifespan into structured CTEs, you can classify users into distinct profitability tiers for targeted retention.

What You'll Be Able To Do

Join customer dimension tables with transaction order headers to aggregate purchase history per user.
Calculate core LTV components including Average Order Value (AOV), purchase frequency, and account lifespan in days and years.
Derive estimated Customer Lifetime Value using CTE-based mathematical modeling across transactional tables.
Implement conditional tiering logic with CASE statements to segment customers into actionable profitability buckets.

Detailed Concept Walkthrough

1. Customer Transaction Aggregation via CTEs

Before calculating advanced metrics like CLV, raw transactional order lines must be aggregated to the customer level. A Common Table Expression (CTE) consolidates individual order amounts, order counts, and earliest purchase dates per customer.

Mechanism: Join Sales.Customer to Sales.SalesOrderHeader on CustomerID to aggregate individual orders per account. This ensures every transaction is mapped back to an identified entity.
Aggregations Applied: COUNT(SalesOrderID) yields purchase frequency, AVG(TotalDue) produces Average Order Value (AOV), SUM(TotalDue) provides historical spend, and MIN(OrderDate) captures the customer's acquisition date.
Lifespan Calculation: Compute raw account lifespan using DATEDIFF(day, MIN(OrderDate), GETDATE()). This represents elapsed days since the customer's first purchase.
Execution Flow: Encapsulating this initial aggregation inside a CTE isolates grouped logic from subsequent mathematical derivations and segmentation rules.

WITH CustomerBaseMetrics AS (
    SELECT 
        c.CustomerID,
        COUNT(soh.SalesOrderID) AS PurchaseFrequency,
        SUM(soh.TotalDue) AS TotalSales,
        AVG(soh.TotalDue) AS AverageOrderValue,
        DATEDIFF(day, MIN(soh.OrderDate), GETDATE()) AS DaysSinceFirstPurchase
    FROM Sales.Customer c
    INNER JOIN Sales.SalesOrderHeader soh 
        ON c.CustomerID = soh.CustomerID
    GROUP BY c.CustomerID
)
SELECT * FROM CustomerBaseMetrics;

Key Takeaway: Group transaction headers by customer ID in a CTE to construct baseline metrics before attempting compound CLV calculations.

2. Calculating Customer Lifetime Value (CLV)

CLV models the total projected revenue an organization expects from a customer over their entire relationship. It combines spending size, transaction frequency, and relationship duration.

Mathematical Formula: The standard formula applied is CLV = Average Order Value * Purchase Frequency * Lifespan in Years. Lifespan in years is derived by dividing elapsed days by 365.0.
Precision Nuance: When converting days to years, divide by a float (e.g., 365.0) rather than an integer to prevent integer truncation during calculation.
Business Interpretation: This metric estimates long-term revenue contribution, allowing teams to determine how much budget can be allocated toward acquisition and retention.

WITH CustomerBaseMetrics AS (
    SELECT 
        c.CustomerID,
        COUNT(soh.SalesOrderID) AS PurchaseFrequency,
        SUM(soh.TotalDue) AS TotalSales,
        AVG(soh.TotalDue) AS AverageOrderValue,
        DATEDIFF(day, MIN(soh.OrderDate), GETDATE()) AS DaysSinceFirstPurchase
    FROM Sales.Customer c
    INNER JOIN Sales.SalesOrderHeader soh 
        ON c.CustomerID = soh.CustomerID
    GROUP BY c.CustomerID
)
SELECT 
    CustomerID,
    PurchaseFrequency,
    TotalSales,
    AverageOrderValue,
    DaysSinceFirstPurchase,
    -- CLV = AOV * Frequency * (Days / 365.0)
    (AverageOrderValue * PurchaseFrequency * (DaysSinceFirstPurchase / 365.0)) AS CLV
FROM CustomerBaseMetrics;

Key Takeaway: Derive CLV by scaling AOV and purchase frequency against normalized account lifespan in years.

3. Conditional Customer Segmentation

Segmenting customers by CLV boundaries categorizes accounts based on whether their lifetime value offsets acquisition and servicing costs. This guides where marketing and retention teams focus budget.

Threshold Evaluation: Apply a CASE expression against the computed CLV metric to classify accounts into non-profitable, profitable, and highly profitable buckets.
Segment Definitions: CLV under 1,000 indicates non-profitable accounts failing to cover acquisition/service costs; 1,000 to 5,000 marks profitable moderate accounts; greater than 5,000 defines highly profitable high-value accounts.
Strategic Action: High-value tiers receive dedicated retention efforts, while lower tiers trigger automated or low-cost marketing campaigns.

WITH CustomerCLV AS (
    SELECT 
        c.CustomerID,
        COUNT(soh.SalesOrderID) AS PurchaseFrequency,
        SUM(soh.TotalDue) AS TotalSales,
        AVG(soh.TotalDue) AS AverageOrderValue,
        DATEDIFF(day, MIN(soh.OrderDate), GETDATE()) AS DaysSinceFirstPurchase,
        (AVG(soh.TotalDue) * COUNT(soh.SalesOrderID) * (DATEDIFF(day, MIN(soh.OrderDate), GETDATE()) / 365.0)) AS CLV
    FROM Sales.Customer c
    INNER JOIN Sales.SalesOrderHeader soh 
        ON c.CustomerID = soh.CustomerID
    GROUP BY c.CustomerID
)
SELECT 
    CustomerID,
    CLV,
    CASE 
        WHEN CLV < 1000 THEN 'Non-Profitable'
        WHEN CLV BETWEEN 1000 AND 5000 THEN 'Profitable'
        WHEN CLV > 5000 THEN 'Very Profitable'
    END AS CustomerSegment
FROM CustomerCLV;

Key Takeaway: Use CASE expressions on CLV thresholds to bucket customer accounts into distinct profitability tiers.

Topics Covered in Calculating LTV in SQL — Patterns from Real SaaS

CLV Overview (0:00 - 0:45) — The instructor outlines customer segmentation goals and defines core metrics using AdventureWorks 2019.
Schema Examination (0:45 - 1:40) — Key columns across the Customer and SalesOrderHeader tables are reviewed for joins and aggregations.
Aggregating Metrics (1:40 - 2:30) — A query is built to group orders by customer ID, calculating order counts, AOV, and lifespan.
Applying CLV Formula (2:30 - 3:05) — Base metrics inside a CTE are multiplied together to calculate projected customer lifetime value.
Profitability Segmentation (3:05 - 3:45) — A CASE expression segments customers into non-profitable, profitable, and very profitable tiers.

SQL Cheat Sheet

DATEDIFF(day, MIN(OrderDate), GETDATE()) — Calculates elapsed days between first order and current date
```
DATEDIFF(day, MIN(OrderDate), GETDATE()) AS DaysSinceFirstPurchase
```
AOV * Frequency * (Days / 365.0) — Computes estimated customer lifetime value in currency units
```
(AVG(TotalDue) * COUNT(SalesOrderID) * (DaysSinceFirstPurchase / 365.0)) AS CLV
```

CASE WHEN CLV < 1000 THEN ... END — Assigns customer profitability tiers based on value thresholds

CASE WHEN CLV < 1000 THEN 'Non-Profitable' WHEN CLV <= 5000 THEN 'Profitable' ELSE 'Very Profitable' END

AVG(TotalDue) — Calculates Average Order Value per grouped entity
```
AVG(soh.TotalDue) AS AverageOrderValue
```
COUNT(SalesOrderID) — Counts total completed orders to establish purchase frequency
```
COUNT(soh.SalesOrderID) AS PurchaseFrequency
```

Comparison Table

Customer Segment	CLV Threshold	Business Characterization
Non-Profitable	< 1,000	Does not cover acquisition costs
Profitable	1,000 – 5,000	Moderate engagement exceeding acquisition costs
Very Profitable	> 5,000	High-frequency, high-spend core revenue drivers

Common Pitfalls

Mistake: Dividing lifespan days by integer 365 instead of a float. Avoid: Divide by 365.0 to prevent integer truncation in SQL Server.
Mistake: Calculating CLV directly in the aggregation query without a CTE. Avoid: Aggregate base metrics in a CTE before applying multi-variable formulas.
Mistake: Filtering on profile attributes like customer names instead of CustomerID. Avoid: Group and join exclusively on indexed unique keys like CustomerID.

FAQs

Why is lifespan calculated from the first purchase date instead of account creation date? Order history measures active purchasing behavior, so the first order date accurately reflects the transactional lifespan start.
Why are customers with CLV below 1,000 classified as non-profitable? Low lifetime spend often fails to cover the combined cost of customer acquisition (CAC) and account servicing.
What table provides the financial total for orders in AdventureWorks? The Sales.SalesOrderHeader table contains the TotalDue column, which stores the complete amount owed per order.

Common pitfalls

Mistake: Dividing lifespan days by integer 365 instead of a float. Avoid: Divide by 365.0 to prevent integer truncation in SQL Server.
Mistake: Calculating CLV directly in the aggregation query without a CTE. Avoid: Aggregate base metrics in a CTE before applying multi-variable formulas.
Mistake: Filtering on profile attributes like customer names instead of CustomerID. Avoid: Group and join exclusively on indexed unique keys like CustomerID.

Welcome to this data analytics with SQL Server series. In today's video, we will dive into an important in customer analytics, customer segmentation and customer lifetime value, CLV. These concepts are critical for business as they provide insight into which customers are driving the most revenue and help in uh plan marketing efforts for effectively. Uh we will be working with AdventureWorks 2019 database to calculate the customer life lifetime value based on key metrics such as average order value, AOV, uh purchase frequency, and customer lifespan. Finally, uh we will segment uh the customer into non-profitable, uh profitable, and very profitable groups as shown uh in the uh in final code. Uh customer segmentation allow business to target specific groups of uh customers uh with tailored marketing strategies uh and focus effort uh on retaining high-value customers, ensuring profitability uh in long term. Okay. Uh here we go, okay? And uh we work with uh to this tables in uh for this scenario. Uh first, Sales.Customer. Here. Uh this table contains information about customers and each customer is uniquely identify by a customer ID. Uh customer ID, it's essential for uh storing customer-related details, but uh in the context of this query, we are primarily focusing on the customer ID to join uh with the sale data. Uh customer ID, uh a unique identifier for each customer. There are might be other columns like a uh first name, last name, email, or a or phone, but uh these are not use this uh query for now since we are only concern uh with purchase behavior. And this table used to identify customer making purchases. Uh we pull out the customer ID from here uh and use uh it to link with the sales data in Sales.SalesOrderHeader table. Okay. And second table is uh Sales.SalesOrderHeader. This table contains information about sales uh orders. Uh each order is uh represented by a sales order ID and uh it's uh linked to the customer who place the order via customer ID. Uh key columns uh are uh SalesOrderID here. Uh uh a unique uh a unique identifier for each uh sale order and customer uh and CustomerID. Uh customer ID, the foreign key uh key linking each sale to customer in the Sales.Customer table. Uh and next is TotalDue. The total uh the total amount the customer owes uh for that particular order. This columns is crucial for calculating total sales and average order value, uh AOV, and OrderDate uh column, uh the date when the order was placed, uh which is important for uh calculating customer lifespan, uh the number of days since the customer's first purchase. And uh this table is where we extract uh data about uh customer transaction, uh the total uh sales, the sum of all uh orders placed by customer uh for uh use uh purchase frequency, the count of the how many orders each customer has placed, uh and uh used for uh uh average order value, AOV, the average amount spent for order by customer, and uh days since first purchase, the different between the first uh purchase date and the current date, which is used to calculate uh the customer's uh lifespan. Okay. Uh and now we back to code. Okay. And first step is uh preparing customer sales data. Uh I select uh the Sales.Customer table. Uh extracting the customer ID from the Sales.Customer table. Okay. Uh and uh I need join uh with uh Sales.SalesOrderHeader to get the sales details. Okay. Uh counting the total number of uh purchase uh each customer. Uh okay. And next next column now calculating the total sales value for each customer. And next column is uh uh the calculating uh the average amount uh spent per order by each customer, uh it stands for AOV. Okay. And finally calculating Uh sorry. uh the calculating number of days since the customer's uh customer's first purchase, uh means customer lifespan. And uh after uh joining the Sales.SalesOrderHeader to get the sales details, uh now grouping by customer ID to get the sales statistics for each customer. Okay. You can see the result set based on uh above code. And I put uh this uh query uh in a CTE. Okay. And now I start uh second uh step, calculating customer uh lifetime uh value, uh CLV. Uh first column selecting the customer ID for output. Next, uh purchase frequency, select the previously calculated purchase uh frequency from uh CTE, and uh selecting the total sales amount for each customer, uh selecting average order value for each customer, days since uh first uh purchase, calculating the uh customer life uh uh lifetime value, CLV, using the formula, and select the AOV uh based on that uh this formula. And now execute. Okay. You can see the result. Uh the formula use CLV: average order value multiply purchase frequency uh multiply uh life uh span uh in years. Lifespans is calculated uh as a days since first purchase / uh 365 uh to get years. And uh this formula estimate how much revenue the customer will uh generate during uh the uh their lifetime. Okay. And finally, uh third step. Okay. Uh third step is customer segmentation. Uh okay. And it's final result. Uh non-profitable uh customer: CLV smaller than 1,000. Uh here. Uh customer whose total uh contribution uh CLV to the business is less than 1,000. Uh this threshold indicates that uh this customer likely do not generate enough revenue to cover uh the cost of acquiring uh them or uh servicing them. Uh the value of 1,000 was chosen uh based on typical customer acquisition cost and low purchasing uh patterns. Uh and next uh is uh profitable. Uh this customers have a moderate level of engagement and spending, contributing between 1,000 and 5,000 in total sales over their lifetime. Uh the range of uh 1,000 to 1 uh 5,000, sorry, 1,000 to 5,000 represent a profitable segment where the customer uh customer's value exceeds uh their acquisition cost, but they are not yet uh considered to high value. And finally, the here, yeah, uh high-val- very uh profitable uh profitable customers uh smaller uh sorry, bigger than 5,000. These customers are the most valuable uh to the business having contributed over 5,000 in lifetime revenue. Uh a threshold of the 5,000 is typically chosen for high-value customers who regularly purchase, uh have high order values, and are critical to the long-term uh profitability of company. Uh okay, thank you for watching. Uh have a good day. Bye.

Frequently asked questions

Why is lifespan calculated from the first purchase date instead of account creation date?

Order history measures active purchasing behavior, so the first order date accurately reflects the transactional lifespan start.

Why are customers with CLV below 1,000 classified as non-profitable?

Low lifetime spend often fails to cover the combined cost of customer acquisition (CAC) and account servicing.

What table provides the financial total for orders in AdventureWorks?

The `Sales.SalesOrderHeader` table contains the `TotalDue` column, which stores the complete amount owed per order.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.