BigQuery + GA4: 5 Practical Use Cases for Marketers

Beyond the Connection: Actually Using BigQuery

Connecting GA4 to BigQuery is a first step. But without concrete use cases, the export remains an untapped database costing you storage fees. Here are five analyses that the GA4 interface simply cannot provide — and that alone justify the investment in a BigQuery + automation infrastructure.

1. Funnel Analysis Without Sampling

GA4’s funnel exploration is subject to sampling beyond 10 million events. In BigQuery, you query 100% of the data.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END) AS step_1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_pseudo_id END) AS step_2_cart,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS step_3_purchase
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260201' AND '20260228'

You get exact conversion rates, segmentable at will by source, device, or campaign.

2. User Journey Analysis

GA4 limits path explorations to a few levels of depth. BigQuery lets you reconstruct each user’s complete journey.

SELECT
  user_pseudo_id,
  STRING_AGG(event_name, ' > ' ORDER BY event_timestamp) AS journey
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX = '20260301'
GROUP BY user_pseudo_id
HAVING COUNT(*) BETWEEN 3 AND 20
LIMIT 1000

By aggregating journeys, you identify the sequences that most often lead to conversion and those that precede abandonment.

3. Custom Attribution Model

GA4 attribution models are limited to last click (cross-channel) and data-driven. With BigQuery, you build the model that fits your business.

WITH touchpoints AS (
  SELECT
    user_pseudo_id,
    traffic_source.source,
    traffic_source.medium,
    event_timestamp,
    COUNTIF(event_name = 'purchase') OVER (PARTITION BY user_pseudo_id) AS has_purchase
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260201' AND '20260228'
    AND event_name IN ('session_start', 'purchase')
)
SELECT source, medium, COUNT(DISTINCT user_pseudo_id) AS assisted_conversions
FROM touchpoints
WHERE has_purchase > 0
GROUP BY source, medium
ORDER BY assisted_conversions DESC

This type of query reveals the true value of assist channels that last-click attribution systematically ignores.

4. Cohort Retention Analysis

GA4 offers a basic retention report. BigQuery enables week-by-week granularity with free segmentation.

WITH first_visit AS (
  SELECT user_pseudo_id, MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS acquisition_date
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260228'
  GROUP BY user_pseudo_id
)
SELECT
  fv.acquisition_date,
  DATE_DIFF(DATE(TIMESTAMP_MICROS(e.event_timestamp)), fv.acquisition_date, WEEK) AS week,
  COUNT(DISTINCT e.user_pseudo_id) AS active_users
FROM `project.analytics_XXXXXX.events_*` e
JOIN first_visit fv ON e.user_pseudo_id = fv.user_pseudo_id
GROUP BY acquisition_date, week
ORDER BY acquisition_date, week

5. Anomaly Detection via SQL

Rather than manually checking your dashboards every morning, automate the detection of significant deviations.

SELECT
  event_date,
  COUNT(*) AS daily_events,
  AVG(COUNT(*)) OVER (ORDER BY event_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_7d,
  SAFE_DIVIDE(COUNT(*) - AVG(COUNT(*)) OVER (ORDER BY event_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING),
    AVG(COUNT(*)) OVER (ORDER BY event_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)) AS pct_deviation
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260220' AND '20260301'
GROUP BY event_date
HAVING ABS(pct_deviation) > 0.3

A deviation greater than 30% from the 7-day rolling average signals a tracking issue, a campaign spike, or a technical anomaly worth investigating. Coupled with an automated alert, this type of query effectively replaces manual monitoring.