How the GA4 export is organised
Each GA4 property linked to BigQuery gets one dataset called analytics_ followed by the property ID. Inside it, Google creates these tables (names as in Google's documentation, October 2026):
- events_YYYYMMDD: one table per day from the daily export. This is the one to query.
- events_intraday_YYYYMMDD: the current day, filled continuously if streaming export is on. Google deletes it once the daily table for that day is complete.
- users_YYYYMMDD and pseudonymous_users_YYYYMMDD: the optional user-data export. One row per user ID or per pseudonymous ID whose data changed that day, with lifetime values, audiences and predictions.
The event tables are date-sharded rather than partitioned: one table per day. To query a date range you use the wildcard events_* and filter on _TABLE_SUFFIX. That filter is also what keeps costs down, because BigQuery only reads the tables that match.
One more thing to know before counting anything: Google keeps updating each daily table with events that arrive up to three days late, so the most recent days can still change.
The fields you will use most
Traffic source, collected traffic source and session last click
This is where most people get lost, because the export has three records with source and medium, and each answers a different question:
- traffic_source: the source, medium and campaign that first acquired the user. It does not change with later visits and is not filled in intraday tables.
- collected_traffic_source: what was collected with each event: UTM values (manual_source, manual_medium, manual_campaign_name and the rest), gclid, dclid and srsltid. It is raw data, before GA4 applies any attribution.
- session_traffic_source_last_click: the last-click source of the session, across Google Ads and manual campaigns. Its cross_channel_campaign sub-record has source, medium and campaign_name, and there are separate sub-records for Google Ads, Search Ads 360, Display & Video 360 and Campaign Manager 360.
For session reports, session_traffic_source_last_click.cross_channel_campaign is the closest thing to session source / medium in GA4. It is still last click. The attribution GA4 applies in its own reports, such as data-driven attribution for key events, is not in the export, so if you want another model you build it yourself: how attribution models differ.
If your export is older than this record, check that it is filled in your oldest tables before using it for long comparisons.
Five GA4 BigQuery SQL queries
These queries use BigQuery standard SQL. Replace my-project.analytics_123456789 with your project and dataset, and the dates with your own range. Each one reads only the columns it needs, which is what you pay for. Google's developer documentation has more examples on its basic and advanced queries pages.
1. Users, active users and sessions
SELECT
COUNT(DISTINCT user_pseudo_id) AS users,
COUNT(DISTINCT IF(is_active_user, user_pseudo_id, NULL)) AS active_users,
COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
Sessions are counted as unique pairs of user and ga_session_id over the whole range, not added up day by day: otherwise a session that crosses midnight counts twice.
2. Page views by page
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
COUNT(*) AS page_views,
COUNT(DISTINCT user_pseudo_id) AS users
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
AND event_name = 'page_view'
GROUP BY page
ORDER BY page_views DESC
LIMIT 50
page_location is the full URL, query string included, so the same page with different UTM tags appears as separate rows. Strip the query string if you need clean paths.
3. Sessions by source and medium
SELECT
session_traffic_source_last_click.cross_channel_campaign.source AS source,
session_traffic_source_last_click.cross_channel_campaign.medium AS medium,
COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
GROUP BY source, medium
ORDER BY sessions DESC
4. Key events and revenue by source and medium
SELECT
session_traffic_source_last_click.cross_channel_campaign.source AS source,
session_traffic_source_last_click.cross_channel_campaign.medium AS medium,
COUNTIF(event_name = 'generate_lead') AS leads,
COUNTIF(event_name = 'purchase') AS purchases,
SUM(IF(event_name = 'purchase', ecommerce.purchase_revenue, 0)) AS revenue
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
GROUP BY source, medium
ORDER BY leads DESC
There is no column that flags key events: list the event names you have marked as key events in GA4.
5. Key events by channel
WITH ev AS (
SELECT event_name,
LOWER(session_traffic_source_last_click.cross_channel_campaign.medium) AS medium
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
)
SELECT
CASE
WHEN medium IN ('cpc', 'ppc', 'paidsearch') THEN 'Paid search'
WHEN medium = 'organic' THEN 'Organic search'
WHEN medium = 'email' THEN 'Email'
WHEN medium = 'referral' THEN 'Referral'
WHEN medium IS NULL OR medium IN ('(none)', '(not set)') THEN 'Direct or unknown'
ELSE 'Other'
END AS channel,
COUNTIF(event_name = 'generate_lead') AS leads,
COUNTIF(event_name = 'purchase') AS purchases
FROM ev
GROUP BY channel
ORDER BY leads DESC
This is a simplified grouping. The export has no default channel group column, and GA4's own channel rules are longer (social sites, shopping, video and so on), so write down your version once and keep it in a shared view everyone uses.
Why BigQuery and GA4 numbers do not match
Before you build dashboards on raw events
Every query above repeats the same subquery to pull ga_session_id out of event_params. That is fine for analysis. A dashboard that runs those queries on raw events every time someone opens it reads the event tables again and again, and with on-demand pricing each read is billed.
The usual approach is a clean layer: a sessions table, a key events table and a channel mapping, rebuilt once a day, which Looker Studio dashboards or Power BI read instead of the raw export. It is cheaper, faster, and everybody works with the same definitions.
If the export is not running yet, or it has been paused, read how the daily export limit works and what you can still recover from the past.
GA4 BigQuery export schema FAQ
How do I get the session ID in the GA4 BigQuery export?
It is the ga_session_id event parameter, stored in event_params as an integer. Combine it with user_pseudo_id, or with user_id, to get a unique session.
Should I query events_ or events_intraday_ tables?
The events_YYYYMMDD tables. Google recommends them because they are the stable, complete data for each day. The intraday table is temporary and is deleted once the daily table is complete.
Why does BigQuery show more users than GA4?
Usually because of Google signals, consent mode modelling and the way GA4 estimates counts. Filtering on is_active_user gets you closer to Active users, but expect a small gap even then.
Where is the session source and medium?
In session_traffic_source_last_click.cross_channel_campaign, which holds the last-click source, medium and campaign of the session. traffic_source is the source that first acquired the user.
Is there a default channel group column?
No. The export has source, medium and campaign, and you write the grouping rules yourself in SQL.
Can I see conversions in BigQuery?
Yes, as events. There is no key event flag, so you filter by the event names you have marked as key events in GA4.
Want a clean GA4 layer in BigQuery?
Tell us what your team needs to measure. In a 30-minute call we will look at how your export is set up and tell you which tables and definitions would make it usable without writing SQL every day.
Book the 30-minute call