Wire GA4 → BigQuery for unsampled, queryable event-level data. Covers the
one-time export setup, the events_YYYYMMDD table schema, partitioning + clustering,
and the SQL patterns for the reports the Data API can't do well (true cohort
retention, custom-event attribution, large date ranges). Trigger with
"GA4 BigQuery", "GA4 to BQ", "event-level GA4 data", "unsampled GA4",
"GA4 export setup", "GA4 SQL".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Wire GA4 → BigQuery for unsampled, queryable event-level data. Covers the
one-time export setup, the events_YYYYMMDD table schema, partitioning + clustering,
and the SQL patterns for the reports the Data API can't do well (true cohort
retention, custom-event attribution, large date ranges). Trigger with
"GA4 BigQuery", "GA4 to BQ", "event-level GA4 data", "unsampled GA4",
"GA4 export setup", "GA4 SQL".
The Data API is good but bounded — sampled past a threshold, capped at ~150 dimensions, no cohort joins. BigQuery export gives you the raw event stream as SQL-queryable tables, free at the standard GA4 tier (up to 1M events/day), with no sampling and full event payloads.
This skill: one-time setup, then the SQL recipes for what the Data API can't do well.
Data location: pick the BQ region for the export tables (US multi-region is fine for most cases; EU if you need data residency)
Export type:
Daily — single events_YYYYMMDD table per day, written ~24h after midnight. Fine for most reporting.
Streaming — events_intraday_YYYYMMDD table written ~near-real-time. Costs more, useful for hot ops dashboards.
Most setups: enable both. Streaming for "today", daily for everything else.
Include advertising identifiers — uncheck unless you specifically need device-graph data (most don't)
Save
The first daily table lands within 24h. The first streaming table is near-immediate. After that you have a new events_YYYYMMDD every day forever, no maintenance needed.
2. Verify the export is working
PROJECT=your-gcp-project
DATASET=analytics_123456789 # auto-named after the property ID
bq ls"$PROJECT:$DATASET" 2>&1 | head -10
# Expect: events_YYYYMMDD tables + events_intraday_YYYYMMDD if streaming enabled
If you see no dataset, the link is configured but the first export hasn't fired yet — wait 24h.
3. Authorize a service account for querying
The SA from ga4-auth-setup only has Data API access. For BQ queries, grant the same SA:
All event parameters; value is itself a union STRUCT (string_value, int_value, float_value, double_value)
user_pseudo_id
STRING
GA4's cookie-based user ID (anonymous unless user_id is set)
user_id
STRING
If you set user_id via gtag('set', {user_id: '...'})
user_properties
ARRAY<STRUCT<key, value>>
Same shape as event_params
device.*
STRUCT
category / os / browser / model
geo.*
STRUCT
country / region / city
traffic_source.*
STRUCT
source / medium / campaign of FIRST session (not current)
session_traffic_source_last_click.*
STRUCT
source / medium of CURRENT session — what you usually want
ga_session_id (param)
INT64
Pulled via (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id')
ga_session_number (param)
INT64
Same idiom — 1 = first session, 2 = second, etc.
The event_params and user_properties arrays are the gnarly bit. Pulling a parameter requires UNNEST + filter. The idiom:
-- Pull the page_location for every page_viewSELECT
TIMESTAMP_MICROS(event_timestamp) AS ts,
(SELECT value.string_value FROMUNNEST(event_params) WHERE key ='page_location') AS page,
user_pseudo_id
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN'20260513'AND'20260520'AND event_name ='page_view'
LIMIT 100;
_TABLE_SUFFIX BETWEEN '...' AND '...' is the canonical way to scan a date range across the wildcard table. Always set it — without a suffix filter, you query the entire history and pay for it.
Recipe 1 — True cohort retention
The thing the Data API can't do cleanly:
WITH first_seen AS (
SELECT
user_pseudo_id,
DATE(MIN(TIMESTAMP_MICROS(event_timestamp))) AS first_date
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN'20260401'AND'20260520'GROUPBY user_pseudo_id
),
activity AS (
SELECT
user_pseudo_id,
DATE(TIMESTAMP_MICROS(event_timestamp)) AS active_date
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN'20260401'AND'20260520'GROUPBY user_pseudo_id, active_date
)
SELECT
DATE_TRUNC(f.first_date, WEEK) AS cohort_week,
DATE_DIFF(a.active_date, f.first_date, WEEK) AS weeks_since,
COUNT(DISTINCT a.user_pseudo_id) AS active_users
FROM first_seen f
JOIN activity a USING (user_pseudo_id)
GROUPBY cohort_week, weeks_since
ORDERBY cohort_week, weeks_since;
Output: rows of (cohort_week, weeks_since, active_users). Pivot in your tool of choice for the classic triangle chart.
Recipe 2 — Sessions table (denormalized from events)
GA4's BQ export is event-rows, not session-rows. To reason about sessions, build the session table yourself:
WITH sessions AS (
SELECT
user_pseudo_id,
(SELECT value.int_value FROMUNNEST(event_params) WHERE key ='ga_session_id') AS session_id,
MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
COUNT(*) AS event_count,
COUNTIF(event_name ='page_view') AS pageviews,
ANY_VALUE(device.category) AS device,
ANY_VALUE(geo.country) AS country,
ANY_VALUE(session_traffic_source_last_click.manual_campaign.source) AS source,
ANY_VALUE(session_traffic_source_last_click.manual_campaign.medium) AS medium,
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN'20260513'AND'20260520'GROUPBY user_pseudo_id, session_id
HAVING session_id ISNOT NULL
)
SELECT*FROM sessions
ORDERBY session_start DESC
LIMIT 100;
You'd usually CREATE TABLE or CREATE MATERIALIZED VIEW over this — querying the events table directly every time is slow + expensive.
Recipe 3 — Top pages by source
SELECT
(SELECT value.string_value FROMUNNEST(event_params) WHERE key ='page_location') AS page,
session_traffic_source_last_click.manual_campaign.source AS source,
COUNT(*) AS pageviews,
COUNT(DISTINCT user_pseudo_id) AS users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN'20260513'AND'20260520'AND event_name ='page_view'GROUPBY page, source
HAVING pageviews >10-- filter the long tailORDERBY pageviews DESC
LIMIT 50;
Cost considerations
BigQuery costs $5/TB scanned (first 1 TB/month free). What that translates to in practice:
Scenario
~TB / query
Small site, 1k events/day, 30-day window
< 1 GB
Medium site, 100k events/day, 30-day window
~10 GB
Large site, 1M events/day, 30-day window
~100 GB
Same scenarios but querying the full 14-month history
12-15x the above
Stay under the free tier with normal usage. Cost-saving patterns:
Always set _TABLE_SUFFIX BETWEEN — don't scan all history if you only need 7 days
Materialize hot queries — CREATE TABLE / CREATE MATERIALIZED VIEW for sessions, daily aggregates, etc.
SELECT only the columns you need — BQ is columnar; selecting event_params array always reads the whole array even if you only want one parameter
Use --dry-run before any new query to see TB scanned: bq query --dry_run --use_legacy_sql=false "SELECT ..."
Streaming vs daily — which to query
Table prefix
When
events_YYYYMMDD
Stable historical data. Use for reports, analysis.
events_intraday_YYYYMMDD
"Today" data, near-realtime. Schema is identical, but rows may not be deduped yet.
events_* (wildcard)
When the date range crosses both. BQ will scan both transparently.
Today's data lives in events_intraday_TODAY; tomorrow it gets rolled into events_TODAY and the intraday table for today is dropped. So if you have a query that needs both stable history + today, use events_* and filter on _TABLE_SUFFIX.
Common gotchas
Issue
Why
Numbers don't match the GA4 UI exactly
UI uses different identity-stitching for cross-device users; raw events are pre-stitching. Off by a few % is expected.
event_params UNNEST returns NULL
The key doesn't exist on that event. Always wrap in (SELECT ... LIMIT 1) so missing-key events return NULL instead of erroring.
Query scans way more than expected
Missing _TABLE_SUFFIX filter, OR using events_* without a _TABLE_SUFFIX BETWEEN clause
events_intraday_* has 2x the rows you'd expect
Intraday tables aren't deduped; the same event may appear twice if it was buffered + retried. The daily rollup dedupes.
No user_id even though I set it on the front-end
user_id is the explicit identifier; user_pseudo_id is the auto-generated cookie ID. If user_id is consistently NULL, the gtag('set', {user_id: ...}) call is firing AFTER the event you're checking, or it's set on a property that the export doesn't pull.
Related skills
ga4-auth-setup — for the service account that queries BQ
ga4-data-api-query — when you DON'T need event-level granularity (Data API is faster + cheaper for aggregates)
ga4-common-reports — the Data API recipes that BQ supersedes
