GA4’s built-in Lifetime Value report is limited to 12 weeks of data and uses predicted LTV rather than actual purchase history. For accurate LTV calculations based on real transaction data across longer time horizons, BigQuery is the right tool. A well-built LTV model in BigQuery lets you segment users by acquisition cohort, calculate actual revenue per user over any time window, and identify which acquisition channels produce the highest long-term value customers — enabling smarter budget allocation than any last-click attribution model can provide.

Defining LTV for Your Business

Before writing any SQL, define what LTV means for your specific business. For eCommerce, LTV is typically total revenue from purchase events attributed to a user across their lifetime or a defined window (90-day LTV, 1-year LTV). For subscription businesses, LTV should include subscription revenue from your billing system, not just GA4 events, since subscription payments often happen server-side without browser events. For lead generation, LTV might be the average deal value for users who converted from a given acquisition source. The right LTV definition depends on your revenue model, and the best LTV models combine GA4 event data with your CRM or billing data in BigQuery.

Cohort-Based LTV Query

-- LTV by acquisition cohort and channel
WITH first_visit AS (
  SELECT
    user_pseudo_id,
    MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_visit_date,
    MIN_BY(traffic_source.medium, event_timestamp) AS acquisition_medium,
    MIN_BY(traffic_source.source, event_timestamp) AS acquisition_source
  FROM `your_project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20240131'
  GROUP BY user_pseudo_id
),
purchases AS (
  SELECT
    user_pseudo_id,
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS purchase_date,
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value') AS revenue
  FROM `your_project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20240131'
    AND event_name = 'purchase'
)
SELECT
  FORMAT_DATE('%Y-%m', fv.first_visit_date) AS acquisition_cohort,
  fv.acquisition_medium,
  COUNT(DISTINCT fv.user_pseudo_id) AS cohort_users,
  COUNT(DISTINCT IF(p.purchase_date IS NOT NULL, fv.user_pseudo_id, NULL)) AS buyers,
  ROUND(SUM(p.revenue), 2) AS total_revenue,
  ROUND(SUM(p.revenue) / COUNT(DISTINCT fv.user_pseudo_id), 2) AS ltv_per_user,
  ROUND(COUNT(DISTINCT IF(p.purchase_date IS NOT NULL, fv.user_pseudo_id, NULL)) / COUNT(DISTINCT fv.user_pseudo_id) * 100, 1) AS conversion_rate_pct
FROM first_visit fv
LEFT JOIN purchases p
  ON fv.user_pseudo_id = p.user_pseudo_id
  AND DATE_DIFF(p.purchase_date, fv.first_visit_date, DAY) BETWEEN 0 AND 365
GROUP BY acquisition_cohort, acquisition_medium
HAVING cohort_users > 50
ORDER BY acquisition_cohort DESC, ltv_per_user DESC

Windowed LTV Comparison

Comparing LTV at different time windows (30-day, 90-day, 365-day) reveals how quickly different acquisition channels monetize. Paid Search users might show high 30-day LTV because they convert quickly, but lower 365-day LTV because they are deal-seekers who do not return. Organic Search users might show lower 30-day LTV but much higher 365-day LTV because they are more brand-loyal. This distinction changes your optimal bidding strategy: if Organic SEO drives high long-term LTV, investing in content marketing has a higher actual return than last-click conversion reports suggest.

-- Compare LTV at 30, 90, 365 day windows
SELECT
  fv.acquisition_medium,
  ROUND(SUM(IF(DATE_DIFF(p.purchase_date, fv.first_visit_date, DAY) <= 30, p.revenue, 0)) / COUNT(DISTINCT fv.user_pseudo_id), 2) AS ltv_30d,
  ROUND(SUM(IF(DATE_DIFF(p.purchase_date, fv.first_visit_date, DAY) <= 90, p.revenue, 0)) / COUNT(DISTINCT fv.user_pseudo_id), 2) AS ltv_90d,
  ROUND(SUM(IF(DATE_DIFF(p.purchase_date, fv.first_visit_date, DAY) <= 365, p.revenue, 0)) / COUNT(DISTINCT fv.user_pseudo_id), 2) AS ltv_365d
FROM first_visit fv
LEFT JOIN purchases p ON fv.user_pseudo_id = p.user_pseudo_id
GROUP BY acquisition_medium
HAVING COUNT(DISTINCT fv.user_pseudo_id) > 200
ORDER BY ltv_365d DESC

Feeding LTV Back into Google Ads

The most actionable output of a BigQuery LTV model is feeding average LTV values back into Google Ads as target ROAS targets or as value rules. If your BigQuery analysis shows that users acquired via Brand keywords have a 365-day LTV of $450 while users acquired via competitor keywords have a 365-day LTV of $180, you should set different target ROAS values for brand vs. competitor campaigns rather than using a uniform ROAS target across all campaigns. Export the LTV ratios from BigQuery, calculate the appropriate ROAS multipliers, and update your campaign bidding accordingly. This creates a compounding advantage where your bidding is calibrated to long-term value rather than immediate transaction revenue.