Why Calculate User Lifetime Value in BigQuery from GA4 Data?

User Lifetime Value (LTV) is the total revenue a customer generates across all their purchases over their entire relationship with your business. It is one of the most strategically important metrics in ecommerce—knowing your average LTV allows you to calculate how much you can afford to spend acquiring a new customer while remaining profitable, and segmenting LTV by acquisition channel reveals which channels bring your most valuable customers (not just your cheapest or most frequent ones).

GA4’s built-in LTV report exists but is limited: it uses a fixed 120-day window, cannot be broken down by custom dimensions, and is subject to GA4’s standard sampling and data retention limits. By calculating LTV directly from the GA4 BigQuery Export, you get unlimited lookback windows, full SQL flexibility, zero sampling, and the ability to segment by any dimension in your data—including custom ones that GA4 does not expose in its interface.

Prerequisites

You need GA4’s BigQuery Export enabled and at least 30–90 days of purchase event data in BigQuery for LTV calculations to be meaningful. Your GA4 implementation must reliably capture the purchase event with a value parameter representing order revenue and a user_id or consistent user_pseudo_id to link purchases to individual users. If you have a logged-in user system, setting GA4’s user_id to your internal customer ID produces the most accurate LTV calculations because it links behavior across devices.

Step 1: Basic User LTV Query — Total Revenue Per User

The simplest LTV query sums all purchase revenue per user across your entire data history:

SELECT
  user_pseudo_id,
  user_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(PARSE_DATE('%Y%m%d', event_date)) AS first_purchase_date,
  MAX(PARSE_DATE('%Y%m%d', event_date)) AS last_purchase_date,
  DATE_DIFF(MAX(PARSE_DATE('%Y%m%d', event_date)), MIN(PARSE_DATE('%Y%m%d', event_date)), DAY) AS customer_lifespan_days
FROM
  `your_project.ga4_export.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20260417'
  AND event_name = 'purchase'
GROUP BY
  user_pseudo_id, user_id
ORDER BY
  lifetime_revenue DESC;

This returns one row per user with their total orders, lifetime revenue, first and last purchase dates, and customer lifespan in days. Sort by lifetime_revenue DESC to immediately identify your highest-value customers. The top 20% of customers by LTV often account for 60–80% of total revenue—a Pareto distribution that has significant implications for how you allocate retention marketing spend.

Step 2: LTV by Acquisition Channel

The most strategically valuable LTV analysis segments customers by their first acquisition channel. This reveals which channels bring customers who spend more over time, not just which channels drive the most initial orders at the lowest cost:

WITH first_session AS (
  -- Get each user's first session source/medium
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS first_source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS first_medium
  FROM (
    SELECT
      user_pseudo_id,
      event_params,
      ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) AS rn
    FROM `your_project.ga4_export.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20260417'
      AND event_name = 'session_start'
  )
  WHERE rn = 1
),

user_revenue AS (
  SELECT
    user_pseudo_id,
    SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS lifetime_revenue,
    COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id')) AS orders
  FROM `your_project.ga4_export.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20260417'
    AND event_name = 'purchase'
  GROUP BY user_pseudo_id
)

SELECT
  fs.first_source,
  fs.first_medium,
  COUNT(DISTINCT ur.user_pseudo_id) AS customers,
  ROUND(AVG(ur.lifetime_revenue), 2) AS avg_ltv,
  ROUND(SUM(ur.lifetime_revenue), 2) AS total_revenue,
  ROUND(AVG(ur.orders), 2) AS avg_orders_per_customer
FROM
  first_session fs
JOIN
  user_revenue ur ON fs.user_pseudo_id = ur.user_pseudo_id
GROUP BY
  fs.first_source, fs.first_medium
HAVING
  COUNT(DISTINCT ur.user_pseudo_id) >= 10  -- Filter out statistically insignificant channels
ORDER BY
  avg_ltv DESC;

This query frequently reveals that organic search customers have 40–60% higher LTV than paid social customers—because users who found you by searching have higher purchase intent and become more loyal repeat buyers. Email-acquired customers often have the highest LTV of all, since users who subscribe tend to be highly engaged brand followers. These insights directly inform budget allocation: invest more in channels that generate high-LTV customers, not just high-volume first purchases.

Step 3: Cohort-Based LTV — Revenue by Acquisition Month

Cohort-based LTV shows how much revenue users acquired in a given month generate over time. This is essential for understanding your payback period—how many months before a customer’s cumulative revenue exceeds your acquisition cost:

WITH user_cohorts AS (
  SELECT
    user_pseudo_id,
    DATE_TRUNC(MIN(PARSE_DATE('%Y%m%d', event_date)), MONTH) AS cohort_month
  FROM `your_project.ga4_export.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20260417'
    AND event_name = 'session_start'
  GROUP BY user_pseudo_id
),
purchases AS (
  SELECT
    user_pseudo_id,
    PARSE_DATE('%Y%m%d', event_date) AS purchase_date,
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value') AS revenue
  FROM `your_project.ga4_export.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20260417'
    AND event_name = 'purchase'
)
SELECT
  uc.cohort_month,
  DATE_DIFF(p.purchase_date, uc.cohort_month, MONTH) AS months_since_acquisition,
  COUNT(DISTINCT p.user_pseudo_id) AS purchasing_users,
  ROUND(SUM(p.revenue), 2) AS cohort_revenue
FROM user_cohorts uc
JOIN purchases p ON uc.user_pseudo_id = p.user_pseudo_id
GROUP BY uc.cohort_month, months_since_acquisition
ORDER BY uc.cohort_month, months_since_acquisition;

Step 4: Visualize LTV in Looker Studio

Save your LTV queries as BigQuery views and connect them to Looker Studio. Build a channel LTV comparison bar chart with first_source / first_medium on the X axis and avg_ltv as the metric. Add a cohort LTV line chart showing cumulative revenue over the months since acquisition—each line represents a different cohort month, letting you see whether more recent cohorts are monetizing faster or slower than earlier ones. These charts make the channel LTV story immediately visible to stakeholders who cannot read SQL output.

Conclusion

BigQuery LTV queries from GA4 export data give you the unsampled, unlimited-lookback customer value analysis that no GA4 report can match. By segmenting LTV by acquisition channel and cohort month, you gain the strategic insight needed to allocate marketing budgets toward channels that build lasting customer relationships—not just channels that drive cheap initial acquisitions that never convert again. Schedule these queries to run weekly and connect the results to a Looker Studio dashboard for ongoing monitoring.