How to query GA4 event data in BigQuery: sessions, sources and revenue
The GA4 export stores one row per event, so sessions, session sources, transactions and funnels all have to be rebuilt in SQL. This guide runs each of those queries on Google's public GA4 sample and shows the real result and the bytes read, with the trap in each: ga_session_id is not unique on its own, session_engaged comes in two types, filtering on event_date reads every day, and purchase events are not the same as transactions.
The GA4 export to BigQuery has no sessions table, no source report and no orders table. It has one row per event. Every number you know from GA4 reports has to be rebuilt from those rows, and each rebuild has one place where a plausible query returns a wrong number without an error.
| What you want | Build it from | Trap |
|---|---|---|
| Only the days you need | _TABLE_SUFFIX |
filtering on event_date reads every day |
| Session ID | ga_session_id in event_params, read as int_value |
not unique without user_pseudo_id |
| Event time | event_timestamp |
microseconds, UTC |
| Engaged sessions | session_engaged parameter |
stored as text in some rows, as a number in others |
| Session source / medium | source and medium parameters, first one in the session |
traffic_source is the user’s first source, not the session’s |
| Transactions and revenue | ecommerce.transaction_id |
purchase events are not transactions |
| Funnel | step timestamps per session | an open count is not a sequence |
| Today’s data | events_intraday_* |
best effort, deleted when the daily table is complete |
Note: Originally published in April 2024. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026, on Google’s public GA4 sample for 1 to 7 January 2021. Bytes are as BigQuery reported them for each query.
All queries use bigquery-public-data.ga4_obfuscated_sample_ecommerce, obfuscated event data from the Google Merchandise Store. To run one on your own export, change the table to your-project.analytics_PROPERTYID.events_*. For first steps on the same sample (event counts, top pages, best-selling items, an open funnel), see BigQuery query examples; the columns themselves are described in the GA4 events table schema.
Reading to get this done?
Choose the days with _TABLE_SUFFIX
Choose the days with _TABLE_SUFFIX
The daily export writes one table per day, events_YYYYMMDD. A query on events_* reads all of them unless the WHERE clause limits _TABLE_SUFFIX, the part of the table name after the asterisk. Three dry runs of the same event count on the sample, which holds 92 days:
| Filter | Bytes processed |
|---|---|
| none | 56.0 MB |
_TABLE_SUFFIX BETWEEN '20210101' AND '20210107' |
3.2 MB |
event_date BETWEEN '20210101' AND '20210107' |
98.9 MB |
The event_date filter returns the same rows as the suffix filter and costs more than no filter at all: BigQuery still opens every daily table and now also reads the event_date column in each. Only _TABLE_SUFFIX decides which tables are read. More in wildcard tables and BigQuery pricing.
For a rolling window, compute the suffix: _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) covers the last seven complete days.
Read ga_session_id and other parameters
Read ga_session_id and other parameters
Session ID, session number, page title and every custom parameter sit in event_params, an array of key and value pairs. The value is a struct with one field per type, and a parameter fills only one of them. ga_session_id is a number, so it is read from int_value:
SELECT
FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp)) AS event_time_utc,
event_name,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS ga_session_id,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_number') AS ga_session_number,
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'page_title') AS page_title
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210101`
ORDER BY event_timestamp, event_name
LIMIT 4;| event_time_utc | event_name | ga_session_id | ga_session_number | page_title |
|---|---|---|---|---|
| 2021-01-01 00:00:08 | first_visit | 8529617896 | 1 | Home |
| 2021-01-01 00:00:08 | page_view | 8529617896 | 1 | Home |
| 2021-01-01 00:00:08 | session_start | 8529617896 | 1 | Home |
| 2021-01-01 00:00:12 | page_view | 8529617896 | 1 | Home |
7.3 MB. Read it from string_value and you get NULL on every row, with no error. The subquery pattern is explained step by step in how to unnest GA4 event parameters.
Trap: ga_session_id alone is not a session. Two users can carry the same ID:
SELECT
COUNT(DISTINCT session_id) AS distinct_ga_session_ids,
COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS sessions
FROM (
SELECT
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS session_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
);| distinct_ga_session_ids | sessions |
|---|---|
| 25,581 | 26,331 |
50.7 MB. Counting the ID alone loses 750 sessions in one week. Always pair it with user_pseudo_id.
Trap: one parameter, two types. The same key does not always land in the same field:
SELECT
p.key,
COUNTIF(p.value.string_value IS NOT NULL) AS as_string,
COUNTIF(p.value.int_value IS NOT NULL) AS as_int,
COUNTIF(p.value.double_value IS NOT NULL) AS as_double
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS p
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
AND p.key IN ('ga_session_id', 'session_engaged', 'value', 'source')
GROUP BY p.key
ORDER BY p.key;| key | as_string | as_int | as_double |
|---|---|---|---|
| ga_session_id | 0 | 245,209 | 0 |
| session_engaged | 198,352 | 24,555 | 0 |
| source | 67,364 | 0 | 0 |
| value | 0 | 49 | 89 |
74.3 MB. session_engaged arrives as the text '1' on most events and as the number 1 on others. A query that checks only string_value = '1' undercounts engaged sessions:
SELECT
COUNT(DISTINCT IF(p.value.string_value = '1',
CONCAT(user_pseudo_id, CAST(s.value.int_value AS STRING)), NULL))
AS engaged_string_only,
COUNT(DISTINCT IF(COALESCE(p.value.string_value,
CAST(p.value.int_value AS STRING)) = '1',
CONCAT(user_pseudo_id, CAST(s.value.int_value AS STRING)), NULL))
AS engaged_any_type
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS p,
UNNEST(event_params) AS s
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
AND p.key = 'session_engaged'
AND s.key = 'ga_session_id';| engaged_string_only | engaged_any_type |
|---|---|
| 23,966 | 24,899 |
79.3 MB. The string-only version misses 933 engaged sessions. Run the type check above on your own export for every parameter you report on, and read numeric values with COALESCE(value.int_value, value.double_value).
The GA4 export has one row per event, and your session and revenue queries return numbers that don't match GA4.
Paste this into your AI assistant. It writes the session, source and transaction queries against your own GA4 export in BigQuery and checks each total against your GA4 reports.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/query-ga4-event-data-in-bigquery.md. The task: write me the BigQuery SQL that rebuilds sessions, session source and medium, and transactions from my GA4 event export, and show where each total differs from my GA4 reports. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
The flat events table used below
The flat events table used below
The rest of the queries start from the same CTE. It reads each parameter once, with the types handled, so the queries after it stay short. Paste it before any query below and that query runs as is:
WITH ev AS (
SELECT
event_date,
event_timestamp,
event_name,
user_pseudo_id,
user_id,
traffic_source.source AS first_user_source,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS session_id,
(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING))
FROM UNNEST(event_params)
WHERE key = 'session_engaged') AS session_engaged,
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'medium') AS medium,
ecommerce.transaction_id,
ecommerce.purchase_revenue_in_usd AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
)Convert event_timestamp
Google’s export schema describes event_timestamp as the time “in microseconds, UTC” when the event was received, and event_date as the date in the property’s registered time zone (read in October 2026). TIMESTAMP_MICROS turns the number into a timestamp, and DATE(..., 'time zone') gives the calendar day somewhere else:
SELECT
event_date,
COUNT(*) AS events,
FORMAT_TIMESTAMP('%F %T', MIN(TIMESTAMP_MICROS(event_timestamp))) AS first_utc,
FORMAT_TIMESTAMP('%F %T', MAX(TIMESTAMP_MICROS(event_timestamp))) AS last_utc,
COUNTIF(FORMAT_DATE('%Y%m%d',
DATE(TIMESTAMP_MICROS(event_timestamp), 'America/New_York')) != event_date)
AS other_day_in_new_york
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210103'
GROUP BY event_date
ORDER BY event_date;| event_date | events | first_utc | last_utc | other_day_in_new_york |
|---|---|---|---|---|
| 20210101 | 22,096 | 2021-01-01 00:00:08 | 2021-01-01 23:59:55 | 4,151 |
| 20210102 | 23,599 | 2021-01-02 00:00:00 | 2021-01-02 23:59:59 | 4,310 |
| 20210103 | 24,743 | 2021-01-03 00:00:55 | 2021-01-03 23:59:58 | 4,747 |
1.3 MB read, 10 MB billed, the on-demand minimum. In this sample the days run midnight to midnight UTC, so the property’s time zone is UTC. Between 18% and 20% of each day’s events (18.8%, 18.3% and 19.2%) fall on a different calendar day in New York. If your reports are in local time, group by the converted date, and widen the _TABLE_SUFFIX range by a day on each side so no events are cut off.
Users, sessions and engaged sessions per day
Users, sessions and engaged sessions per day
A session is user_pseudo_id plus session_id. Building a sessions CTE first, one row per session, makes every session metric a simple count:
-- paste the ev CTE first, then a comma
sessions AS (
SELECT
user_pseudo_id,
session_id,
MIN(event_date) AS day,
LOGICAL_OR(session_engaged = '1') AS engaged
FROM ev
GROUP BY user_pseudo_id, session_id
)
SELECT
day,
COUNT(DISTINCT user_pseudo_id) AS users,
COUNT(*) AS sessions,
COUNTIF(engaged) AS engaged_sessions,
ROUND(COUNTIF(engaged) / COUNT(*), 3) AS engagement_rate
FROM sessions
GROUP BY day
ORDER BY day;| day | users | sessions | engaged_sessions | engagement_rate |
|---|---|---|---|---|
| 20210101 | 2,160 | 2,311 | 2,224 | 0.962 |
| 20210102 | 2,577 | 2,728 | 2,614 | 0.958 |
| 20210103 | 2,574 | 2,785 | 2,654 | 0.953 |
| 20210104 | 3,076 | 3,367 | 3,195 | 0.949 |
| 20210105 | 4,094 | 4,485 | 4,230 | 0.943 |
| 20210106 | 5,479 | 5,995 | 5,655 | 0.943 |
| 20210107 | 4,167 | 4,660 | 4,327 | 0.929 |
81.8 MB. The week has 26,331 sessions and 24,899 engaged ones, the same totals as the type check above. GA4 counts a session as engaged when it lasts longer than 10 seconds, has a key event, or has 2 or more page or screen views, and bounce rate is the share of sessions that were not engaged (GA4 help, read in October 2026). So bounce rate here is 1 - engagement_rate. The rates in this obfuscated sample are high; read yours, not these.
Each session is counted on the day it started. A query that counts distinct sessions per event_date counts a session that runs past midnight on both days: on 2 January that gives 2,734 sessions in the query examples, 6 more than here.
Sessions by source and medium
The traffic_source column looks like the answer and is not. Google’s schema says it holds “the traffic source that first acquired the user” (read in October 2026). Every session of a returning user carries the source of their first visit.
The session’s own source is in the source and medium event parameters. They appear only on some events, so take the first one in each session:
-- paste the ev CTE first, then a comma
sessions AS (
SELECT
user_pseudo_id,
session_id,
ARRAY_AGG(source IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[SAFE_OFFSET(0)] AS source,
ARRAY_AGG(medium IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[SAFE_OFFSET(0)] AS medium,
ANY_VALUE(first_user_source) AS first_user_source,
COUNTIF(event_name = 'purchase') AS purchases
FROM ev
GROUP BY user_pseudo_id, session_id
)
SELECT
IFNULL(source, '(none)') AS source,
IFNULL(medium, '(none)') AS medium,
COUNT(*) AS sessions,
SUM(purchases) AS purchases,
COUNTIF(source != first_user_source) AS differs_from_first_user_source
FROM sessions
GROUP BY source, medium
ORDER BY sessions DESC
LIMIT 6;| source | medium | sessions | purchases | differs_from_first_user_source |
|---|---|---|---|---|
| (none) | (none) | 9,667 | 0 | 0 |
| organic | 6,045 | 48 | 1,359 | |
| shop.googlemerchandisestore.com | referral | 2,908 | 27 | 1,968 |
| (direct) | (none) | 2,759 | 12 | 0 |
<Other> |
<Other> |
1,897 | 2 | 0 |
<Other> |
referral | 1,258 | 36 | 130 |
87.1 MB. Two findings. 1,359 of the google / organic sessions belong to users whose first source was something else, so a report on traffic_source would move them out of organic. And 9,667 sessions carry no source parameter at all. A hand-written report usually labels them direct. The GA4 interface does not: for session-scoped dimensions it uses the “paid and organic channels last click” model (GA4 help, read in October 2026), which credits a direct return visit to the user’s earlier campaign. That is the usual reason a source report built from raw events shows far more direct traffic than GA4.
Newer exports carry two more records that close the gap. collected_traffic_source holds the campaign values present on the events (manual_source, manual_medium, gclid and more). session_traffic_source_last_click holds “the last-click attributed session traffic source data”, for example session_traffic_source_last_click.cross_channel_campaign.source and .medium (Google’s schema, read in October 2026). The 2021 sample predates both, so they cannot be run here; if your export has them, group sessions by the second one to match the GA4 interface. The GA4 export mistakes guide covers more of these mismatches.
Purchases, transactions and revenue
Purchases, transactions and revenue
-- paste the ev CTE first, then a comma
orders AS (
SELECT
transaction_id,
COUNT(*) AS purchase_events,
SUM(revenue) AS revenue_all_events,
MAX(revenue) AS revenue
FROM ev
WHERE event_name = 'purchase'
GROUP BY transaction_id
)
SELECT
SUM(purchase_events) AS purchase_events,
ROUND(SUM(revenue_all_events), 2) AS naive_revenue,
COUNTIF(transaction_id != '(not set)') AS transactions,
COUNTIF(transaction_id != '(not set)' AND purchase_events > 1) AS sent_twice,
SUM(IF(transaction_id = '(not set)', purchase_events, 0)) AS without_id,
ROUND(SUM(IF(transaction_id != '(not set)', revenue, 0)), 2) AS revenue
FROM orders;| purchase_events | naive_revenue | transactions | sent_twice | without_id | revenue |
|---|---|---|---|---|---|
| 144 | 7,982 | 136 | 2 | 6 | 7,944 |
77.8 MB. 144 purchase events are 136 transactions: two orders were sent twice, and six purchase events have the transaction ID (not set). Counting events instead of IDs inflates orders by 6% and revenue by $38. Grouping by transaction_id first and taking one revenue value per order is the fix. If your ad platform and your export disagree on orders, check this before anything else. Item-level revenue needs UNNEST(items), shown in the query examples.
A closed funnel: view_item, add_to_cart, purchase
A closed funnel: view_item, add_to_cart, purchase
An open funnel counts users who did each step at any time. A closed funnel requires the steps in order, in the same session. Take each step’s first timestamp per session and compare them:
-- paste the ev CTE first, then a comma
steps AS (
SELECT
user_pseudo_id,
session_id,
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 = 'purchase', event_timestamp, NULL)) AS t_buy
FROM ev
GROUP BY user_pseudo_id, session_id
)
SELECT
COUNTIF(t_view IS NOT NULL) AS viewed_item,
COUNTIF(t_cart > t_view) AS then_added_to_cart,
COUNTIF(t_buy > t_cart AND t_cart > t_view) AS then_purchased,
COUNTIF(t_buy IS NOT NULL) AS any_purchase_session
FROM steps;| viewed_item | then_added_to_cart | then_purchased | any_purchase_session |
|---|---|---|---|
| 3,944 | 667 | 80 | 136 |
84.5 MB. Of the sessions that viewed an item, 16.9% then added to cart and 2.0% then bought. 136 sessions had a purchase, but only 80 went through all three steps in that order in that session. The other 56 skipped a step there or did them in another order, for example buying from a cart filled in an earlier session. A closed session funnel understates conversion for stores with long consideration; report both and say which is which. For step-by-step timing between events, see window functions.
Events per user and user_id
user_pseudo_id is the browser or app instance. user_id is filled only if your site sends its own ID, usually after login. Count per user with the login ID where there is one:
-- paste the ev CTE first, then a comma
per_user AS (
SELECT
COALESCE(user_id, user_pseudo_id) AS user_key,
LOGICAL_OR(user_id IS NOT NULL) AS signed_in,
COUNT(*) AS events,
COUNTIF(event_name = 'add_to_cart') AS add_to_cart,
COUNT(DISTINCT session_id) AS sessions
FROM ev
GROUP BY user_key
)
SELECT
COUNT(*) AS users,
COUNTIF(signed_in) AS users_with_user_id,
ROUND(AVG(events), 1) AS avg_events,
APPROX_QUANTILES(events, 2)[OFFSET(1)] AS median_events,
MAX(events) AS max_events,
COUNTIF(add_to_cart >= 3) AS users_3plus_add_to_cart,
COUNTIF(sessions > 1) AS users_2plus_sessions
FROM per_user;| users | users_with_user_id | avg_events | median_events | max_events | users_3plus_add_to_cart | users_2plus_sessions |
|---|---|---|---|---|---|---|
| 22,847 | 0 | 10.7 | 6 | 1,011 | 311 | 2,507 |
82.5 MB. The sample has no user_id at all, so every count falls back to the browser ID. The average of 10.7 events against a median of about 6 (APPROX_QUANTILES is approximate) shows a long tail; one ID logged 1,011 events in a week, worth checking as a bot or an internal tester before it goes into any average. To count logins per user, swap add_to_cart for your login event name. User properties and lifetime metrics are covered in extracting GA4 user properties.
Today’s data: events_intraday
Daily tables arrive once a day, and Google says the timing is not guaranteed. With Streaming export on, the current day goes to events_intraday_YYYYMMDD, which Google describes as making data “available within a few minutes”, as “a best-effort service” with no completeness guarantee, and deletes that table once events_YYYYMMDD is complete (GA4 help, read in October 2026).
events_* matches the intraday tables too, with suffixes like intraday_20261016. A BETWEEN on dates never matches them, because the letter i sorts after every digit. To include today, ask for the intraday suffix explicitly:
SELECT
_TABLE_SUFFIX AS table_suffix,
COUNT(*) AS events
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210130' AND '20210131'
OR _TABLE_SUFFIX LIKE 'intraday_%'
GROUP BY table_suffix
ORDER BY table_suffix;| table_suffix | events |
|---|---|
| 20210130 | 30,395 |
| 20210131 | 26,489 |
0 bytes: a plain row count is answered from table metadata. The sample has no intraday tables, so only the two daily ones return. On your export, a row for intraday_ plus today’s date appears while today is in progress. Use intraday for monitoring, and the daily tables for anything you report as final. Setting up the export itself is in the GA4 to BigQuery guide.
From a working query to a number everyone uses
From a working query to a number everyone uses
These queries are where a report starts. The trouble begins with the second copy: one person counts sessions with ga_session_id alone, another reads session_engaged as text only, a third groups by traffic_source, and the meeting turns into which number is right.
In OWOX Data Marts the analyst publishes each of these queries once as a Data Mart, with a description of every column, and delivers it on a schedule to Google Sheets, Data Studio, Excel, email or Slack. GA4 data still reaches BigQuery through Google’s own export; OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data. Marketers then read one sessions number, one source report and one transactions count, in the tools they already use for and .
The GA4 export has one row per event, and your session and revenue queries return numbers that don't match GA4.
Paste this into your AI assistant. It writes the session, source and transaction queries against your own GA4 export in BigQuery and checks each total against your GA4 reports.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/query-ga4-event-data-in-bigquery.md. The task: write me the BigQuery SQL that rebuilds sessions, session source and medium, and transactions from my GA4 event export, and show where each total differs from my GA4 reports. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
How do I link my GA4 property to BigQuery?
In GA4, open Admin, then Product links, then BigQuery links, and click Link. Choose the Google Cloud project and data location, pick the data streams and any events to exclude, then choose Daily export, Streaming export or both and submit. You need the Editor role on the GA4 property and Owner access to the Cloud project, and data usually starts arriving within 24 hours.
How do I count sessions from GA4 event data in BigQuery?
Read `ga_session_id` from `event_params` as `int_value` and combine it with `user_pseudo_id`, because `ga_session_id` alone is not unique across users. Group events by that pair to get one row per session, then count rows. In one week of Google's public GA4 sample, counting `ga_session_id` alone gave 25,581 sessions instead of 26,331.
Can I analyze real-time GA4 data in BigQuery?
Yes, if Streaming export is on: the current day is written to `events_intraday_YYYYMMDD` within a few minutes. Google describes streaming as best effort with no completeness guarantee, and deletes the intraday table once the daily `events_YYYYMMDD` table is complete, so use intraday for monitoring and the daily tables for final numbers. A date range filter on `_TABLE_SUFFIX` does not match intraday tables, so add `_TABLE_SUFFIX` LIKE '`intraday_`%' to include today.
What are GA4 event parameters in the BigQuery export?
They are the key and value pairs stored in the `event_params` array of each event row, such as `ga_session_id`, `page_title`, source or a custom parameter. Each value sits in one of `string_value`, `int_value`, `float_value` or `double_value`, and the same key can arrive in different fields, so check the types before you filter. You read one with a subquery such as SELECT `value.int_value` FROM UNNEST(`event_params`) WHERE key = '`ga_session_id`'.

