BQBrains | Querying the GA4 BigQuery Export: Sessions, Funnels, and Attribution That Match the UI

The Google Analytics 4 export is, for many organisations, the single largest and most-queried dataset in BigQuery — and the one analysts get wrong most often. The schema is event-level, deeply nested, and deliberately unopinionated: there is no sessions column, no bounce_rate, and no attribution model. Everything the GA4 interface shows you has to be rebuilt in SQL. This tutorial shows how to do that correctly, and how to do it without scanning a year of events every morning.

We use the public sample, so you can run every query as written:

bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

1. The schema in ninety seconds

One row per event. The columns that matter most:

Column	What it holds
`event_date`, `event_timestamp`	Date string (`YYYYMMDD`) and microsecond timestamp
`event_name`	`page_view`, `session_start`, `purchase`, and your custom events
`event_params`	`ARRAY<STRUCT<key STRING, value STRUCT<string_value, int_value, float_value, double_value>>>`
`user_pseudo_id`	Device/cookie identifier — the join key for sessions
`user_id`	Your own logged-in identifier, when you set it
`traffic_source`	First-touch source for the user, not the session
`collected_traffic_source`	Per-event source/medium/campaign — the one you usually want
`items`	Repeated ecommerce items
`ecommerce`	Transaction-level revenue and IDs

Two traps are worth flagging before any SQL. First, traffic_source is user-scoped and first-touch; using it for campaign reporting silently attributes everything to how the user was originally acquired. Second, the tables are sharded by day (events_20260114), not partitioned, so a naive SELECT * FROM events_* scans the entire history.

2. Unnesting event_params without a self-join per parameter

The classic beginner pattern is one (SELECT value.int_value FROM UNNEST(event_params) ...) subquery per field. It works, but it is verbose. A small UDF keeps things readable:

CREATE OR REPLACE FUNCTION `analytics.ga4_param_string`(
  params ARRAY<STRUCT<key STRING,
    value STRUCT<string_value STRING, int_value INT64,
                 float_value FLOAT64, double_value FLOAT64>>>,
  name STRING
) AS (
  (SELECT value.string_value FROM UNNEST(params) WHERE key = name LIMIT 1)
);

CREATE OR REPLACE FUNCTION `analytics.ga4_param_int`(
  params ARRAY<STRUCT<key STRING,
    value STRUCT<string_value STRING, int_value INT64,
                 float_value FLOAT64, double_value FLOAT64>>>,
  name STRING
) AS (
  (SELECT COALESCE(value.int_value, CAST(value.double_value AS INT64))
   FROM UNNEST(params) WHERE key = name LIMIT 1)
);

Now a flattened event view reads like a normal table:

SELECT
  PARSE_DATE('%Y%m%d', event_date)                         AS event_day,
  TIMESTAMP_MICROS(event_timestamp)                        AS event_ts,
  event_name,
  user_pseudo_id,
  `analytics.ga4_param_int`(event_params, 'ga_session_id')    AS ga_session_id,
  `analytics.ga4_param_int`(event_params, 'engagement_time_msec') AS engagement_msec,
  `analytics.ga4_param_string`(event_params, 'page_location')  AS page_location,
  `analytics.ga4_param_string`(event_params, 'session_engaged') AS session_engaged
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131';

The _TABLE_SUFFIX filter is not optional. It is the only thing standing between you and a full-history scan.

3. Rebuilding sessions

A GA4 session key is user_pseudo_id || ga_session_id. Engaged sessions are those with session_engaged = '1' or more than 10 seconds of engagement time. Bounce rate is simply the inverse of engagement rate.

WITH ev AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_day,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='session_engaged') AS engaged,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec') AS eng_msec,
    event_name, event_timestamp
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
),
sessions AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING)) AS session_key,
    user_pseudo_id,
    MIN(event_day)                                          AS session_day,
    MAX(IF(engaged = '1', 1, 0))                            AS is_engaged,
    SUM(IFNULL(eng_msec, 0)) / 1000                         AS engagement_seconds,
    COUNTIF(event_name = 'page_view')                       AS pageviews,
    COUNTIF(event_name = 'purchase')                        AS purchases,
    TIMESTAMP_DIFF(TIMESTAMP_MICROS(MAX(event_timestamp)),
                   TIMESTAMP_MICROS(MIN(event_timestamp)), SECOND) AS duration_s
  FROM ev
  WHERE session_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  session_day,
  COUNT(*)                                        AS sessions,
  COUNT(DISTINCT user_pseudo_id)                  AS users,
  SAFE_DIVIDE(COUNTIF(is_engaged = 1), COUNT(*))  AS engagement_rate,
  1 - SAFE_DIVIDE(COUNTIF(is_engaged = 1), COUNT(*)) AS bounce_rate,
  SAFE_DIVIDE(SUM(pageviews), COUNT(*))           AS pages_per_session
FROM sessions
GROUP BY session_day
ORDER BY session_day;

Expect your numbers to land close to, but not exactly on, the GA4 interface. The interface applies data thresholding, consent modelling, and (on unsampled reports) its own late-hit handling; the export does not. Differences of a percent or two are normal. Differences of thirty percent mean you used traffic_source, forgot session_id IS NOT NULL, or mixed time zones — the export stamps event_date in the property's reporting time zone but event_timestamp in UTC.

4. A funnel that survives re-runs

The readable way to build a funnel is one conditional aggregate per step, ordered by first occurrence within the session:

WITH steps AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params)
      WHERE key='ga_session_id') AS STRING)) AS session_key,
    MIN(IF(event_name = 'view_item',     event_timestamp, NULL)) AS t_view,
    MIN(IF(event_name = 'add_to_cart',   event_timestamp, NULL)) AS t_cart,
    MIN(IF(event_name = 'begin_checkout',event_timestamp, NULL)) AS t_checkout,
    MIN(IF(event_name = 'purchase',      event_timestamp, NULL)) AS t_purchase
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
  GROUP BY session_key
)
SELECT
  COUNTIF(t_view IS NOT NULL)                                AS viewed_item,
  COUNTIF(t_cart > t_view)                                   AS added_to_cart,
  COUNTIF(t_checkout > t_cart)                               AS began_checkout,
  COUNTIF(t_purchase > t_checkout)                           AS purchased,
  SAFE_DIVIDE(COUNTIF(t_purchase > t_checkout), COUNTIF(t_view IS NOT NULL)) AS view_to_purchase
FROM steps;

Comparing timestamps rather than just presence enforces step order, which is what the GA4 funnel exploration does in "ordered" mode. Swap > for IS NOT NULL to get the open-funnel version.

5. Attribution: last non-direct click

GA4's default reporting model is data-driven, which you cannot reproduce exactly from the export. Last non-direct click, however, is both reproducible and a perfectly defensible internal standard.

WITH touches AS (
  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS ts,
    collected_traffic_source.source   AS source,
    collected_traffic_source.medium   AS medium,
    collected_traffic_source.campaign AS campaign,
    event_name,
    ecommerce.purchase_revenue        AS revenue
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20210131'
),
attributed AS (
  SELECT
    * EXCEPT(source, medium, campaign),
    LAST_VALUE(IF(medium IN ('(none)', 'direct', ''), NULL, source) IGNORE NULLS)
      OVER (PARTITION BY user_pseudo_id ORDER BY ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attr_source,
    LAST_VALUE(IF(medium IN ('(none)', 'direct', ''), NULL, campaign) IGNORE NULLS)
      OVER (PARTITION BY user_pseudo_id ORDER BY ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attr_campaign
  FROM touches
)
SELECT
  IFNULL(attr_source, '(direct)')   AS source,
  IFNULL(attr_campaign, '(none)')   AS campaign,
  COUNTIF(event_name = 'purchase')  AS conversions,
  ROUND(SUM(IF(event_name = 'purchase', revenue, 0)), 2) AS revenue
FROM attributed
GROUP BY 1, 2
HAVING conversions > 0
ORDER BY revenue DESC
LIMIT 25;

Two caveats worth writing into the model's documentation: the lookback window is whatever date range you scan, so pull at least 30–90 days of history before the conversion window; and user_pseudo_id resets when cookies clear, so cross-device journeys need user_id stitching instead.

6. Intraday tables and late hits

The daily export lands once per day, typically within hours of midnight in the property's time zone; if streaming export is enabled you also get events_intraday_YYYYMMDD, updated continuously. The intraday table for a day is deleted once the daily table for that day is written, and the two can disagree — intraday rows lack some enrichment and may double-count.

The pattern that works in production:

CREATE OR REPLACE VIEW `analytics.ga4_events_all` AS
SELECT *, FALSE AS is_intraday
FROM `my-project.analytics_123456789.events_*`
UNION ALL
SELECT *, TRUE AS is_intraday
FROM `my-project.analytics_123456789.events_intraday_*`
WHERE PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) NOT IN (
  SELECT DISTINCT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)
  FROM `my-project.analytics_123456789.events_*`
);

And reprocess with a lag: when you materialise daily aggregates, always rebuild the last three days rather than only yesterday. GA4 backfills late-arriving hits, and a pipeline that only ever writes CURRENT_DATE() - 1 will permanently under-report mobile traffic.

7. Keeping the bill down

The GA4 export is where on-demand costs quietly run away, because every dashboard refresh re-scans raw events. Four habits fix most of it:

Always bound _TABLE_SUFFIX with literals or FORMAT_DATE('%Y%m%d', ...) on a constant, never on a value BigQuery cannot resolve at planning time.
Materialise a sessions table, partitioned by session_day and clustered by user_pseudo_id or channel, and point BI tools at that — not at events_*. Dashboards then scan megabytes instead of terabytes.
Build it incrementally with MERGE on a three-day window, so each run touches three partitions.
Set maximum_bytes_billed at the project or query level, so an exploratory SELECT * fails fast rather than costing a week's budget.

A typical mid-sized property goes from tens of terabytes scanned per month to a few hundred gigabytes on this pattern, with no loss of fidelity: the raw events remain there for the questions the aggregates cannot answer.

Where to go next

The session and attribution logic above belongs in version control, not in an analyst's query history. Wrapping it in Dataform or dbt models, with assertions on session counts and revenue totals, turns "the GA4 numbers" into a definition the whole company shares.

If you want a marketing analytics layer designed and built properly — sessions, channels, attribution, and cost controls — our BigQuery data engineering and cost optimization teams do this regularly. Get in touch with the shape of your GA4 estate and we will tell you what it should look like.