Calculating Customer Lifetime Value in BigQuery

Why LTV Matters for Advertising

Customer Lifetime Value (LTV) is the total revenue a customer generates over their relationship with your business. Knowing your LTV enables smart customer acquisition decisions: if your average LTV is £200, you can profitably spend up to £200 to acquire a customer (targeting a specific LTV:CAC ratio based on your margin).

BigQuery with GA4 purchase data enables LTV calculation at the cohort level — how much has each acquisition cohort generated over their lifetime on your platform?

Simple LTV: Total Revenue per User

SELECT
  user_pseudo_id,
  COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params)
    WHERE key = 'transaction_id')) as total_orders,
  SUM((SELECT value.double_value FROM UNNEST(event_params)
    WHERE key = 'value')) as lifetime_revenue,
  MIN(event_date) as first_purchase_date,
  MAX(event_date) as last_purchase_date
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX >= '20260101'
  AND event_name = 'purchase'
GROUP BY user_pseudo_id
ORDER BY lifetime_revenue DESC

Cohort LTV: Revenue by Acquisition Week

WITH first_purchase AS (
  SELECT
    user_pseudo_id,
    MIN(event_date) as acquisition_date
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX >= '20260101'
    AND event_name = 'purchase'
  GROUP BY user_pseudo_id
),
all_purchases AS (
  SELECT
    user_pseudo_id,
    event_date,
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value') as order_value
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX >= '20260101'
    AND event_name = 'purchase'
)
SELECT
  fp.acquisition_date,
  DATE_DIFF(ap.event_date, fp.acquisition_date, WEEK) as weeks_since_acquisition,
  COUNT(DISTINCT fp.user_pseudo_id) as cohort_size,
  SUM(ap.order_value) as total_revenue,
  ROUND(SUM(ap.order_value) / COUNT(DISTINCT fp.user_pseudo_id), 2) as ltv_per_user
FROM first_purchase fp
JOIN all_purchases ap ON fp.user_pseudo_id = ap.user_pseudo_id
GROUP BY fp.acquisition_date, weeks_since_acquisition
ORDER BY fp.acquisition_date, weeks_since_acquisition

Interpreting LTV Results

The cohort LTV query shows how much revenue each acquisition cohort has generated at each week after their first purchase. Reading week 0 gives you initial order value; week 12 shows 3-month LTV; week 52 gives 1-year LTV. Comparing cohort LTV curves reveals which acquisition periods or channels generate higher-value customers over time.

LTV for Advertising Decisions

Compare LTV by traffic source (if you can identify acquisition channel for each user) to find which channels bring the most valuable customers. A channel with higher CPA but 40% higher 6-month LTV may be more profitable than a cheap-CPA channel with low retention.

Summary

BigQuery LTV calculation: join first purchase date per user to all subsequent purchases, group by acquisition date and weeks since acquisition, and calculate revenue per cohort user. This reveals how LTV accumulates over time for each cohort. Use LTV data to set informed target CPAs in Google Ads — the right maximum CPA for a campaign is determined by the LTV of users that campaign acquires, not just the immediate order value.

See our BigQuery Setup service for LTV analysis development.

Need LTV analysis built in BigQuery? Contact Adslytics.