Schema Theory

Description

Customer Lifetime Value (LTV) estimates the total revenue a business expects from a customer over the entire duration of their relationship. It helps businesses understand long-term profitability per customer and make informed marketing, sales, and product investment decisions.

Visual Example

DAX Formula

LTV = 
CALCULATE(
    DIVIDE(
        SUMX(
            VALUES(Customer[CustomerID]),
            CALCULATE(SUM(Sales[Revenue]))
        ),
        DISTINCTCOUNT(Customer[CustomerID])
    ),
    REMOVEFILTERS(Date)
)       

SQL Statement

SELECT
  SUM(s.Revenue) / NULLIF(COUNT(DISTINCT c.CustomerID), 0) AS LTV
FROM Sales s
JOIN Customer c ON s.CustomerID = c.CustomerID;

Usage Notes

• Use REMOVEFILTERS(Date) to include all customer history in the calculation, regardless of date filters on visuals.
• Segment by acquisition cohort to compare LTV trends over time or by channel.
• For fixed periods like 12-month or 3-year LTV, apply a DATESINPERIOD filter.
• Pair LTV with CAC to measure customer profitability and marketing effectiveness.

Downloads