How to Track Customer Lifetime Value

The technical requirements to track customer lifetime value properly

In our experience building data foundations for scaling startups, the ability to track customer lifetime value remains the most significant gap in revenue operations. Customer Lifetime Value, or LTV, is the total monetary value a customer brings to a business over the entire duration of their relationship. While the concept is simple, the implementation is often technically complex because it requires joining disparate datasets across billing systems, CRMs, and marketing platforms.

To track customer lifetime value effectively, your data team must move beyond simple averages and move toward granular, event-based modeling. Most teams struggle because their data is siloed in a tool like Stripe or HubSpot, making it difficult to account for refunds, varying gross margins, or acquisition costs. A robust LTV calculation requires a centralized data warehouse like BigQuery or Snowflake where these sources can be unified.

When we build these systems for clients, we focus on creating a single source of truth for "Customer Value" that persists even if a customer changes their subscription tier or email address. This requires a strong identity resolution layer in your data warehouse. Without this foundation, your LTV numbers will always be skewed by duplicate records and missing historical context.

Measure customer lifetime value using different methodologies

There is no single way to calculate LTV that works for every business model. Depending on your data maturity and business type, you will likely use one of three main approaches: historical LTV, predictive LTV, or cohort-based LTV.

Historical LTV is the simplest to measure customer lifetime value because it relies on actual money that has already changed hands. You sum the total revenue from a customer and multiply it by your gross margin. This is highly accurate for looking backward, but it offers little utility for predicting future revenue or justifying aggressive marketing spend today.

Predictive LTV uses machine learning or statistical modeling to estimate how much a customer will spend in the future based on their early behavior. This is common in high-growth SaaS and e-commerce companies that need to know their ROI before a customer has even been on the platform for six months. In our AI Stack Audit, we often find that teams try to jump into predictive modeling before they have a stable historical data foundation, leading to "garbage in, garbage out" scenarios.

Cohort-based LTV is the middle ground. It groups customers by their signup month and tracks how the average value of that group evolves over time. This helps you identify if the quality of customers you are acquiring is improving or declining. If your January cohort has a six-month LTV of $500 but your June cohort has a six-month LTV of $350, you have a signal that your recent marketing efforts may be attracting lower-quality leads.

Track CLV for SaaS companies with recurring revenue

Subscription-based businesses have a specific set of challenges when calculating value. To track CLV for SaaS, you must account for expansion revenue, contractions, and the probability of churn. The standard formula for SaaS LTV is:

(ARPU x Gross Margin) / Churn Rate

ARPU stands for Average Revenue Per User. While this formula works for a quick back-of-the-envelope calculation, it is dangerous to use for operational decisions. If your churn rate is extremely low, this formula can produce an "infinite" LTV, which is not grounded in reality. Instead, we recommend capping the lifespan at 36 or 60 months to keep the projections conservative.

In a modern data stack, we use dbt to create a "subscription items" table that captures every change in a customer's plan. This allows us to track the incremental value added by upsells. If a customer starts at $50 per month and upgrades to $150 per month after three months, your LTV model must reflect that step-change in value rather than just averaging the total. This is a core component of our Learn AI Data Engineering track, where we teach practitioners how to build these specific revenue models.

How to build an LTV calculation for B2B organizations

Calculating value in a B2B context is different because of long sales cycles and high-touch account management. For B2B, the focus shifts from individual users to accounts. An LTV calculation for B2B must account for the fact that a single "customer" might represent hundreds of users across multiple departments.

To track this accurately, you need to join your CRM data (like HubSpot or Salesforce) with your financial records. This allows you to see not just what the contract says, but what was actually paid. Many B2B companies ignore the "Cost to Serve" when calculating LTV. If a high-value customer requires ten hours of support every week, their actual lifetime value is significantly lower than a "quiet" customer with a smaller contract.

We recommend building a SQL model that calculates "Net LTV" by subtracting support tickets and implementation costs from the total contract value. This provides the finance team with a true ROI on different customer segments.

-- Example dbt model for B2B Net LTV
WITH customer_revenue AS (
    SELECT 
        customer_id,
        SUM(amount_usd) AS total_revenue
    FROM {{ ref('stg_stripe_invoices') }}
    WHERE status = 'paid'
    GROUP BY 1
),
customer_costs AS (
    SELECT 
        customer_id,
        (COUNT(ticket_id) * 50) AS estimated_support_cost -- $50 per ticket
    FROM {{ ref('stg_zendesk_tickets') }}
    GROUP BY 1
)
SELECT 
    r.customer_id,
    r.total_revenue,
    COALESCE(c.estimated_support_cost, 0) AS support_cost,
    (r.total_revenue - COALESCE(c.estimated_support_cost, 0)) AS net_ltv
FROM customer_revenue r
LEFT JOIN customer_costs c ON r.customer_id = c.customer_id

Integrating LTV into your marketing attribution

Once you have a reliable way to track customer lifetime value, the next step is feeding that data back into your marketing platforms. This is often called "Conversion Value" or "Profit-Driven Marketing." Instead of telling Google Ads that every lead is worth $10, you can tell it exactly how much that specific lead's cohort is worth over time.

This creates a competitive advantage. If your competitors are bidding based on a $50 CPA (Cost Per Acquisition) but your data shows that customers from a specific keyword have an LTV of $2,000, you can afford to bid much higher and still maintain a healthy LTV to CAC ratio.

We often see companies fail here because their data pipelines are too slow. If it takes three weeks to calculate LTV for a new cohort, the marketing team cannot adjust their bids in real-time. This is why we advocate for automated ELT (Extract, Load, Transform) pipelines that refresh these metrics daily. By automating the flow from Stripe to BigQuery to your BI tool, you remove the human bottleneck from the decision-making process.

Common pitfalls in customer value tracking

The most frequent mistake we see is ignoring the "Gross Margin" component. If you track revenue instead of profit, you are essentially lying to yourself about your unit economics. For a software company, gross margin might be 80% or 90%, but for an e-commerce or services company, it could be as low as 20%. Calculating LTV based on top-line revenue will lead to overspending on marketing and eventually running out of cash.

Another pitfall is failing to account for "Negative Churn." In many SaaS businesses, existing customers grow their spend over time through seat expansions or usage-based billing. If your LTV model assumes a static contract value, you are underestimating the worth of your best customers.

Finally, avoid the "Average Trap." A single "whale" customer can skew your average LTV and make your business look healthier than it is. We always recommend looking at Median LTV alongside the Mean to get a clearer picture of what a typical customer is worth.

How to structure your dbt models for LTV

A production-grade LTV system should be broken down into layers within your data warehouse. We generally use the following structure in dbt:

1. Staging Layer: Clean up the raw data from your CRM and billing systems. Rename columns, cast data types, and handle null values.
2. Intermediate Layer: Perform the joins between customers and invoices. Calculate the duration of each customer's lifecycle (days since first purchase).
3. Mart Layer: The final table that stakeholders see. This should have one row per customer with columns for total_lifetime_revenue, active_months, acquisition_source, and predicted_ltv_next_12_months.

By modularizing the logic this way, you make the system easier to audit. If the finance team questions a specific customer's value, you can trace the calculation back through the intermediate models to the raw invoice data.

Frequently Asked Questions About Customer Lifetime Value

How do I calculate LTV if I don't have historical data?

If you are a new business without years of historical data, you should use a "Proxy LTV" based on industry benchmarks or early retention signals. For example, if you know that 80% of your customers renew for a second month, you can use that retention rate to project a theoretical lifespan. However, you must update these assumptions with real data as soon as it becomes available.

Should I include acquisition costs in my LTV calculation?

LTV and CAC (Customer Acquisition Cost) should be kept as separate metrics, but they are used together to calculate the LTV:CAC ratio. LTV measures the value a customer brings in, while CAC measures what it cost to get them. Combining them into a single "Net LTV" including acquisition cost is less common because it makes it harder to see the efficiency of your marketing spend vs. the efficiency of your product's monetization.

What is a good LTV to CAC ratio for a startup?

For a venture-backed SaaS company, a 3:1 LTV to CAC ratio is generally considered the gold standard. This means that for every $1 you spend on marketing, you get $3 in lifetime value. If your ratio is 1:1, you are barely breaking even on your marketing spend. If it is 5:1 or higher, you are likely under-spending and could afford to grow much faster.

How often should LTV metrics be updated?

For most companies, updating LTV metrics once a day is sufficient. This allows marketing teams to see the impact of their campaigns on a near-real-time basis without the technical overhead of a streaming data pipeline. If you are a high-volume B2C company, you might consider more frequent updates, but the business value of sub-daily LTV updates is usually marginal.

Which tools are best for tracking customer lifetime value?

While many CRMs claim to track LTV, a custom build using a data warehouse (BigQuery or Snowflake), an ingestion tool (Fivetran or Airbyte), and a transformation tool (dbt) is the most reliable approach. This "Modern Data Stack" allows you to join data from multiple sources that a single CRM cannot handle. We teach this exact stack in our Learn AI Bootcamp for teams looking to bring these capabilities in-house.

Ready to build your revenue data foundation?

If you want to stop guessing your unit economics and start making decisions based on accurate data, we can help. Our AI Stack Audit provides a comprehensive review of your current data architecture and a roadmap for implementing a reliable LTV tracking system. We help you move from messy spreadsheets to a production-grade data foundation that scales with your business. Book a free consultation with our team to discuss your specific tracking needs.