GA4 in BigQuery: the queries that answer real questions

The GA4 interface answers the questions Google anticipated. BigQuery answers the rest, and the price of admission is understanding one unusual thing about the schema. Once that clicks, most of the analysis you actually want is forty lines of SQL.

This assumes the export is already switched on. If you turned it on recently and need the months before that, the history cannot be backfilled from GA4 — worth knowing before you plan around it.

The one thing about the schema

In BigQuery, one row is one event. Not a session, not a user. And the parameters that event carried do not live in columns — they live in event_params, an array of key/value structs:

event_date      = '20260929'
event_timestamp = 1759104000000000      -- microseconds, not milliseconds
event_name      = 'purchase'
user_pseudo_id  = '1234567.8901234'
event_params    = [
  { key: 'ga_session_id',  value: { int_value: 1759103000 } },
  { key: 'page_location',  value: { string_value: 'https://...' } },
  { key: 'session_engaged', value: { string_value: '1' } }
]

So you cannot write WHERE page_location = .... You reach into the array with a scalar subquery, and this pattern is roughly 80% of all GA4 SQL:

SELECT
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'ga_session_id')  AS session_id
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
  AND event_name = 'purchase'

Two details in there that matter more than they look:

events_* with _TABLE_SUFFIX is how you query date-sharded tables. The filter is not optional — leave it out and every query scans your entire history. That is the one way this gets expensive.

event_timestamp is in microseconds. Divide by 1,000,000 for seconds, or use TIMESTAMP_MICROS(). Treating it as milliseconds puts your data in 1970 and the mistake is not always obvious.

Sessions and users, counted properly

ga_session_id is not unique on its own — it is a timestamp-derived integer scoped to each user, so two users can share one. The session key is the pair:

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS date,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions,
  COUNTIF(event_name = 'purchase') AS purchases,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
GROUP BY date
ORDER BY date

This will not match the GA4 interface exactly, and that is expected rather than a bug. The interface models conversions it did not observe and reconciles identities; the export gives you what was collected. Differences under about 10% are normal. Above that, check your date range and your session key before you suspect the export.

A funnel that tells you where people leave

Funnel exploration in the interface is fine until you need a step defined by something it will not let you express. In SQL the step is just a COUNTIF:

WITH sessions AS (
  SELECT
    CONCAT(user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS session_key,
    COUNTIF(event_name = 'view_item')     > 0 AS saw_product,
    COUNTIF(event_name = 'add_to_cart')   > 0 AS added,
    COUNTIF(event_name = 'begin_checkout')> 0 AS checked_out,
    COUNTIF(event_name = 'purchase')      > 0 AS purchased
  FROM `my-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
  GROUP BY session_key
)
SELECT
  COUNTIF(saw_product) AS step_1_product,
  COUNTIF(added) AS step_2_cart,
  COUNTIF(checked_out) AS step_3_checkout,
  COUNTIF(purchased) AS step_4_purchase,
  ROUND(100 * COUNTIF(added)       / NULLIF(COUNTIF(saw_product), 0), 1) AS pct_product_to_cart,
  ROUND(100 * COUNTIF(checked_out) / NULLIF(COUNTIF(added), 0), 1)       AS pct_cart_to_checkout,
  ROUND(100 * COUNTIF(purchased)   / NULLIF(COUNTIF(checked_out), 0), 1) AS pct_checkout_to_purchase
FROM sessions

Swap any step for a condition of your own — a specific page, a parameter value, a device class — and the shape of the query does not change. That substitution is the whole reason to be in here.

First-touch vs last-touch, on your own data

This is the query that justifies the export. GA4’s reporting no longer lets you compare models — first-click, linear and time-decay were removed in 2023 — but the raw events carry no attribution at all, which means you can assign credit however you like:

WITH touches AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    COALESCE(collected_traffic_source.manual_source, 'direct') AS source,
    COALESCE(collected_traffic_source.manual_medium, 'none')   AS medium,
    event_name,
    ecommerce.purchase_revenue AS revenue
  FROM `my-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260701' AND '20260930'
),
converters AS (
  SELECT user_pseudo_id, MIN(event_timestamp) AS converted_at, SUM(revenue) AS revenue
  FROM touches
  WHERE event_name = 'purchase'
  GROUP BY user_pseudo_id
),
ranked AS (
  SELECT
    t.user_pseudo_id,
    c.revenue,
    CONCAT(t.source, ' / ', t.medium) AS channel,
    ROW_NUMBER() OVER (PARTITION BY t.user_pseudo_id ORDER BY t.event_timestamp ASC)  AS first_rank,
    ROW_NUMBER() OVER (PARTITION BY t.user_pseudo_id ORDER BY t.event_timestamp DESC) AS last_rank
  FROM touches t
  JOIN converters c USING (user_pseudo_id)
  WHERE t.event_timestamp <= c.converted_at
)
SELECT
  channel,
  ROUND(SUM(IF(first_rank = 1, revenue, 0)), 2) AS first_touch_revenue,
  ROUND(SUM(IF(last_rank  = 1, revenue, 0)), 2) AS last_touch_revenue
FROM ranked
GROUP BY channel
ORDER BY last_touch_revenue DESC

Run it and the two columns will disagree, sometimes severely. Branded search and direct inflate on the last-touch side; paid social and display inflate on the first-touch side. The size of that gap is the practical measure of how much your channel report depends on a bookkeeping choice.

One honest limit: this is keyed on user_pseudo_id, which is a cookie on one browser. Cross-device journeys appear as separate people, so the real path is longer than anything this query can see. If that matters for your business, the fix is user_id on logged-in traffic plus server-side tracking, not a cleverer window function.

Keeping the bill near zero

The first terabyte of queries each month is free and storage runs a couple of cents per gigabyte, so most properties pay nothing. Three habits keep it that way:

• Always filter _TABLE_SUFFIX. This is the big one. Without it you scan every day you have ever collected, every time.
• Never SELECT *. BigQuery bills by columns scanned. Naming five columns instead of selecting all of them is often a 10× difference on the same rows.
• Materialise what you reuse. If a transformation feeds a dashboard, write it to a table on a schedule instead of recomputing it on every refresh. This is also what keeps a dashboard your team actually opens from being slow enough that they stop.

Use the dry-run byte estimate in the console before running anything new. It is free and it tells you immediately whether you forgot the date filter.

Four traps worth knowing in advance

events_intraday_* is a different table. If the export includes streaming, today’s data sits in a separate intraday table with a slightly different shape. events_* quietly matches both, so a wildcard query can mix finished days with a partial one. Be explicit when the distinction matters.

user_pseudo_id is null when consent is denied. Depending on your consent setup, some events arrive without an identifier. They are real events that cannot be grouped into a user, and ignoring that silently understates your totals.

(not set) is a real value. It means the dimension was not collected for that event, not that nothing happened. Filter it deliberately rather than letting it ride along in a GROUP BY.

Ecommerce revenue lives in two places. ecommerce.purchase_revenue at the event level and items as a repeated record for line-level detail. Summing both without thinking double-counts.

The short version

One row is one event, parameters hide in event_params behind UNNEST, timestamps are microseconds, and _TABLE_SUFFIX is what stands between you and a surprising invoice. With those four facts you can count sessions correctly, build a funnel with any step definition you like, and compare attribution models on your own paths — which is the analysis the interface will not give you at any price.

If your reports and your revenue disagree and you want to know which one is lying, that is usually where I start.