Customer Lifetime Value: How to Calculate and Track LTV From Your Database

Customer lifetime value is one of the most consequential metrics in any subscription or repeat-purchase business. It tells you how much revenue a typical customer generates over their entire relationship with your product and therefore how much you can afford to spend acquiring new ones.

The concept is simple. Calculating it accurately, from your actual database, is less so. This guide walks through the SQL behind common LTV calculations, how to track it over time, how to segment it by meaningful dimensions, and how to set up monitoring so you catch problems before they compound.

What LTV Actually Measures (and Where Most Calculations Go Wrong)

LTV is not the same as average order value, and it's not just monthly revenue times average subscription length. A real LTV calculation needs to account for churn customers who leave before the theoretical average lifetime.

The most commonly used formula in SaaS is:

LTV = Average Revenue Per User (ARPU) ÷ Churn Rate

If your average customer pays $50/month and 3% of customers cancel each month, LTV = $50 ÷ 0.03 = $1,667.

But this formula assumes a steady-state business with constant churn. Real businesses have cohorts that behave differently. A customer who signed up during a promotional period has different retention characteristics than someone who found you through a review site. The aggregate formula hides that signal.

The more reliable approach is to calculate LTV from actual payment history, cohort by cohort.

The SQL Behind LTV Calculations

Assuming a payments table and a users table:

-- Total revenue per customer
SELECT
  user_id,
  MIN(paid_at) AS first_payment,
  MAX(paid_at) AS last_payment,
  SUM(amount) / 100.0 AS total_revenue_usd,
  COUNT(*) AS payment_count,
  EXTRACT(EPOCH FROM (MAX(paid_at) - MIN(paid_at))) / 86400.0 AS active_days
FROM payments
WHERE status = 'succeeded'
GROUP BY user_id;

From there, calculating average LTV is straightforward:

WITH customer_revenue AS (
  SELECT
    p.user_id,
    SUM(p.amount) / 100.0 AS total_revenue_usd,
    DATE_TRUNC('month', MIN(p.paid_at)) AS cohort_month
  FROM payments p
  WHERE p.status = 'succeeded'
  GROUP BY p.user_id
)
SELECT
  cohort_month,
  COUNT(*) AS customers,
  ROUND(AVG(total_revenue_usd), 2) AS avg_ltv,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue_usd), 2) AS median_ltv,
  ROUND(MAX(total_revenue_usd), 2) AS max_ltv
FROM customer_revenue
GROUP BY cohort_month
ORDER BY cohort_month DESC;

The median here is important. In most subscription businesses, LTV is right-skewed a small number of high-value customers pull the average up significantly. The median gives a more honest picture of what a typical customer is worth.

Tracking LTV Over Time: Cumulative Revenue Curves

One of the most useful LTV visualizations is the cumulative revenue curve how much has a cohort of customers paid, at each month after their signup?

WITH customer_cohorts AS (
  SELECT
    u.id AS user_id,
    DATE_TRUNC('month', u.created_at) AS signup_cohort
  FROM users u
  WHERE u.created_at >= '2024-01-01'
),
payment_months AS (
  SELECT
    cc.user_id,
    cc.signup_cohort,
    DATE_PART('month', AGE(DATE_TRUNC('month', p.paid_at), cc.signup_cohort)) AS months_since_signup,
    SUM(p.amount) / 100.0 AS revenue
  FROM customer_cohorts cc
  JOIN payments p ON p.user_id = cc.user_id
    AND p.status = 'succeeded'
  GROUP BY cc.user_id, cc.signup_cohort,
           DATE_PART('month', AGE(DATE_TRUNC('month', p.paid_at), cc.signup_cohort))
)
SELECT
  signup_cohort,
  months_since_signup,
  COUNT(DISTINCT user_id) AS paying_customers,
  ROUND(SUM(revenue), 2) AS cohort_revenue,
  ROUND(AVG(SUM(revenue)) OVER (
    PARTITION BY signup_cohort
    ORDER BY months_since_signup
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ), 2) AS cumulative_avg_ltv
FROM payment_months
GROUP BY signup_cohort, months_since_signup
ORDER BY signup_cohort, months_since_signup;

This query shows, for each signup cohort, how much the average customer in that cohort has paid by each month of their lifecycle. When you plot this, you can see whether newer cohorts are paying more or less than older ones at the same point in their lifecycle which tells you whether product changes, pricing changes, or customer quality shifts are affecting long-term value.

Segmenting LTV by Acquisition Channel, Plan, and Geography

Aggregate LTV is a starting point. The actionable insights come from segmentation.

By pricing plan:

SELECT
  u.plan_tier,
  COUNT(DISTINCT p.user_id) AS customers,
  ROUND(AVG(customer_total.total_revenue), 2) AS avg_ltv,
  ROUND(SUM(customer_total.total_revenue) / COUNT(DISTINCT p.user_id), 2) AS check
FROM users u
JOIN (
  SELECT user_id, SUM(amount) / 100.0 AS total_revenue
  FROM payments
  WHERE status = 'succeeded'
  GROUP BY user_id
) customer_total ON customer_total.user_id = u.id
JOIN payments p ON p.user_id = u.id
GROUP BY u.plan_tier
ORDER BY avg_ltv DESC;

By acquisition source (assuming you track UTM data or a signup_source column):

SELECT
  u.signup_source,
  COUNT(DISTINCT u.id) AS customers,
  ROUND(AVG(customer_ltv.total_revenue), 2) AS avg_ltv,
  ROUND(SUM(customer_ltv.total_revenue), 2) AS total_revenue_from_channel
FROM users u
LEFT JOIN (
  SELECT user_id, SUM(amount) / 100.0 AS total_revenue
  FROM payments
  WHERE status = 'succeeded'
  GROUP BY user_id
) customer_ltv ON customer_ltv.user_id = u.id
GROUP BY u.signup_source
HAVING COUNT(DISTINCT u.id) >= 20  -- filter out low-volume sources
ORDER BY avg_ltv DESC;

This tells you which acquisition channels are bringing in customers who stay and pay not just customers who sign up. A channel with 500 signups and $200 LTV is worse than a channel with 50 signups and $1,200 LTV.

LTV:CAC Ratio Putting LTV in Context

LTV on its own doesn't tell you whether your business is healthy. The ratio that matters is LTV to Customer Acquisition Cost (CAC). The general benchmark is that LTV should be at least 3x CAC; below 1x means you're destroying value with each new customer.

If you track marketing spend and customer acquisition data in your database:

WITH monthly_acquisition AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS new_customers,
    signup_source
  FROM users
  GROUP BY 1, 3
),
monthly_spend AS (
  SELECT
    DATE_TRUNC('month', spend_date) AS month,
    channel,
    SUM(amount) AS total_spend
  FROM marketing_spend
  GROUP BY 1, 2
),
channel_ltv AS (
  SELECT
    u.signup_source AS channel,
    AVG(customer_rev.total_revenue) AS avg_ltv
  FROM users u
  LEFT JOIN (
    SELECT user_id, SUM(amount) / 100.0 AS total_revenue
    FROM payments WHERE status = 'succeeded' GROUP BY user_id
  ) customer_rev ON customer_rev.user_id = u.id
  GROUP BY u.signup_source
)
SELECT
  ma.month,
  ma.signup_source AS channel,
  ma.new_customers,
  ms.total_spend,
  ROUND(ms.total_spend::numeric / NULLIF(ma.new_customers, 0), 2) AS cac,
  ROUND(cl.avg_ltv, 2) AS ltv,
  ROUND(cl.avg_ltv / NULLIF(ms.total_spend::numeric / ma.new_customers, 0), 2) AS ltv_cac_ratio
FROM monthly_acquisition ma
LEFT JOIN monthly_spend ms ON ms.month = ma.month AND ms.channel = ma.signup_source
LEFT JOIN channel_ltv cl ON cl.channel = ma.signup_source
ORDER BY ma.month DESC, ltv_cac_ratio DESC;

Building an LTV Dashboard Without Writing SQL Every Time

Running these queries manually is useful for building understanding. It's not useful for monitoring you can't run a SQL script every morning before you've had coffee.

This is where automated dashboards earn their place. In AI for Database, you'd connect your database and ask questions like:

The system translates these to SQL, runs them against your live database, and displays the results as a table or chart. You can pin any of these as a dashboard tile that refreshes on a schedule daily, hourly, or weekly so the numbers are always current without anyone having to run a query.

The value of this for a growth team: instead of waiting for a data analyst to write and run a custom query, any team member can ask a follow-up question directly.

Setting Up LTV Alerts

Dashboards show you trends. Alerts tell you when something needs immediate attention.

Useful LTV-related alerts to set up:

In AI for Database, these become action workflows: define a SQL condition, set a threshold, and configure the action (Slack message, email, webhook to your CRM). The system checks the database on your schedule and fires when the condition is met no stored procedures, no DBA involvement.