GA4 + BigQuery: Setup Guide and 10 Essential Queries for 2025

GA4's native UI covers the first 80% of common analytics questions, and then hits a wall: cardinality limits, 90-day exploration windows, sampling on large datasets, and no way to join with your CRM or ad platform data. BigQuery Export fixes all of that, and Google made it free for every GA4 account since 2022, ending the Universal Analytics 360 era where hit-level data cost 150,000 EUR per year.

This guide covers the full setup in 20 minutes, the GA4 schema you need to understand before writing SQL, and 10 specific queries I use on every client engagement. Based on GA4 + BigQuery deployments I've done for European e-commerce, SaaS, and publishers since the export became free in 2022.

If you're still deciding whether BigQuery makes sense for your stack, start with the BigQuery basics guide first. If you already run BigQuery and want to keep the bill in check, the cost optimization guide pairs with this one.

Key Takeaways

GA4 BigQuery Export is free. Storage (0.02 USD/GB/month) and query processing (5 USD/TB) apply, but the export itself has no cost.

Setup is 5 clicks in GA4 Admin + 5 clicks in Google Cloud Console. Data starts flowing the next day (daily batch) or within an hour (streaming, paid).

Schema is nested: event_params, user_properties, and items are arrays of structs. UNNEST is the main SQL pattern you'll use.

Every query needs _TABLE_SUFFIX filtering. Without it, wildcards scan all historical days and costs explode.

The 10 queries below cover top pages, funnels, channel attribution, cohorts, e-commerce details, and consent-split traffic. Start with these, adapt to your KPIs.

What You Actually Get From GA4 BigQuery Export

The native GA4 UI shows aggregated data. BigQuery Export gives you hit-level events, every page view, click, purchase, custom event, captured in its raw form with all parameters intact.

Concretely:

• Every event as a row in daily tables (events_YYYYMMDD).
• Every parameter you set in GTM or the gtag config (page_title, value, currency, custom stuff).
• Every user property (plan_tier, customer_type, anything you push via gtag('set', 'user_properties', ...)).
• Full e-commerce details in the items array (each product in each purchase, with price, quantity, brand, category).
• Session and user identifiers (user_pseudo_id, ga_session_id) for stitching sessions and joining with your own data.
• Device, geography, traffic source fields unaggregated.
• Historical retention beyond GA4 UI's 14-month max.

What you don't get:

• Demographics and interests (those are modeled in GA4 and not exported).
• Audiences (those are computed in GA4 based on exported data, not exported themselves).
• Real-time (daily batch export arrives the next day; streaming is separate and paid).

Setup Step by Step (20 Minutes)

Step 1: Create or Pick a Google Cloud Project

Go to console.cloud.google.com. Click the project picker at the top, New Project. Name it something like my-company-ga4-export. Takes 30 seconds.

If you already use Google Cloud for something else, you can reuse an existing project, but for clean billing isolation, a dedicated project per GA4 property is cleaner.

Step 2: Enable BigQuery API

In Google Cloud Console: APIs & Services > Library > search "BigQuery API" > Enable. Already enabled on many projects. Takes 10 seconds.

Step 3: Grant GA4's Service Account Access

GA4 uses an internal service account to push data to your BigQuery. In BigQuery, the linking process handles this automatically, you don't need to do anything manual here. Just note that GA4 will add a service account (firebase-measurement@system.gserviceaccount.com) to your project's IAM with BigQuery Data Editor and Job User roles.

Step 4: Link BigQuery in GA4 Admin

In GA4: Admin (gear icon, bottom left) > Property column > BigQuery Links > Link.

Pick:

• Cloud project: the one you created in Step 1
• Data location: EU (multi-region) for European data (GDPR-friendly) or a specific region like europe-west1
• Data streams: select the web stream(s) you want to export
• Frequency: Daily (free) and/or Streaming (paid, 0.05 USD per GB inserted)

Click Next, Submit. Done.

Step 5: Wait for the First Export (24 hours)

Daily batch export runs after midnight in your property's timezone. If you set this up on Tuesday, expect events_YYYYMMDD for Tuesday's data to show up Wednesday morning.

Step 6: Verify and Run Your First Query

In BigQuery Studio, navigate to your project > your dataset (named analytics_XXXXXXXXX where X is your GA4 property ID) > events_YYYYMMDD. Click Preview to see sample rows, or run:

SELECT COUNT(*) AS events, COUNT(DISTINCT user_pseudo_id) AS users
FROM my-project.analytics_123456789.events_*
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

If you get a row count, the pipe is working.

Want me to set up GA4 BigQuery Export and the first dashboards for you? Get in touch for a scoped data engineering project, typical setup + 5 starter queries + Looker Studio dashboard takes me 2-3 days.

Understanding the GA4 Schema (Before You Write SQL)

The schema is the hardest part for teams new to GA4 Export. Five concepts to internalize:

1. One Table Per Day

Daily exports create events_YYYYMMDD (finalized) and optionally events_intraday_YYYYMMDD (streaming, current day). Always filter with _TABLE_SUFFIX on the wildcard events_* to scan only the days you need.

2. event_params Is a Nested Array

Each event row has an event_params column that's actually an array of (key, value) pairs. To filter or select a specific parameter:

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX = '20251030'

The value struct has four fields: string_value, int_value, float_value, double_value. Use whichever matches the parameter type.

3. user_properties Works the Same Way

User-scoped custom dimensions live in user_properties, same nested structure. Access with UNNEST(user_properties).

4. items Array for E-commerce

Each purchase or view_item event has an items array. Each item is a struct with item_id, item_name, price, quantity, item_brand, item_category, etc. To explode a purchase into one row per item:

SELECT
event_timestamp,
item.item_id,
item.item_name,
item.price,
item.quantity
FROM project.analytics_123.events_*, UNNEST(items) AS item
WHERE _TABLE_SUFFIX = '20251030'
AND event_name = 'purchase'

5. Traffic Source Fields Are Flat

Unlike event_params, traffic_source is a struct with three flat fields: source, medium, name (campaign). These capture the first traffic source for a user. For session-scoped source/medium, extract from event_params keys source, medium, campaign.

10 Essential Queries for Real Analytics

These are the queries I end up writing on every client project. Adapt column lists and filter dates to your KPIs.

Query 1: Top Pages by Unique Users (Last 7 Days)

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
COUNT(DISTINCT user_pseudo_id) AS users,
COUNT(*) AS pageviews
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'page_view'
GROUP BY page
ORDER BY users DESC
LIMIT 100

Query 2: Simple Funnel (Visit → Product View → Add to Cart → Purchase)

WITH funnel AS (
SELECT
user_pseudo_id,
MAX(IF(event_name = 'session_start', 1, 0)) AS visited,
MAX(IF(event_name = 'view_item', 1, 0)) AS viewed,
MAX(IF(event_name = 'add_to_cart', 1, 0)) AS added,
MAX(IF(event_name = 'purchase', 1, 0)) AS purchased
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY user_pseudo_id
)
SELECT
SUM(visited) AS step_1_visits,
SUM(viewed) AS step_2_viewed,
SUM(added) AS step_3_cart,
SUM(purchased) AS step_4_purchase,
SAFE_DIVIDE(SUM(purchased), SUM(visited)) * 100 AS overall_conversion_pct
FROM funnel

Query 3: Sessions per Channel Grouping

SELECT
COALESCE(traffic_source.medium, '(none)') AS medium,
COALESCE(traffic_source.source, '(direct)') AS source,
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 project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'session_start'
GROUP BY medium, source
ORDER BY sessions DESC

Query 4: Revenue by Source/Medium

SELECT
COALESCE(traffic_source.source, '(direct)') AS source,
COALESCE(traffic_source.medium, '(none)') AS medium,
COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params) WHERE key='transaction_id')) AS orders,
SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key='value')) AS revenue
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'purchase'
GROUP BY source, medium
ORDER BY revenue DESC

Query 5: Monthly Retention Cohort

WITH user_cohorts AS (
SELECT
user_pseudo_id,
FORMAT_DATE('%Y-%m', MIN(PARSE_DATE('%Y%m%d', event_date))) AS cohort_month
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY user_pseudo_id
),
user_activity AS (
SELECT DISTINCT
user_pseudo_id,
FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', event_date)) AS activity_month
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
)
SELECT
c.cohort_month,
a.activity_month,
COUNT(DISTINCT c.user_pseudo_id) AS retained_users
FROM user_cohorts c
JOIN user_activity a USING (user_pseudo_id)
GROUP BY cohort_month, activity_month
ORDER BY cohort_month, activity_month

Query 6: Custom Event Breakdown by Parameter

Replace my_custom_event with your actual event name and my_param with the parameter:

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'my_param') AS param_value,
COUNT(*) AS event_count,
COUNT(DISTINCT user_pseudo_id) AS unique_users
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'my_custom_event'
GROUP BY param_value
ORDER BY event_count DESC

Query 7: First Touch → Purchase (Attribution)

WITH first_touch AS (
SELECT
user_pseudo_id,
ARRAY_AGG(STRUCT(traffic_source.source, traffic_source.medium) ORDER BY event_timestamp ASC LIMIT 1)[OFFSET(0)] AS first
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY user_pseudo_id
),
purchases AS (
SELECT
user_pseudo_id,
SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key='value')) AS revenue
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'purchase'
GROUP BY user_pseudo_id
)
SELECT
ft.first.source AS first_touch_source,
ft.first.medium AS first_touch_medium,
COUNT(DISTINCT p.user_pseudo_id) AS buyers,
SUM(p.revenue) AS revenue
FROM first_touch ft
JOIN purchases p USING (user_pseudo_id)
GROUP BY first_touch_source, first_touch_medium
ORDER BY revenue DESC

Query 8: Engagement Time by Page

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 AS avg_engagement_sec,
COUNT(*) AS events
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'user_engagement'
GROUP BY page
HAVING events > 100
ORDER BY avg_engagement_sec DESC

Query 9: E-commerce Top Products with Revenue

SELECT
item.item_id,
item.item_name,
item.item_brand,
item.item_category,
SUM(item.quantity) AS units_sold,
SUM(item.quantity * item.price) AS revenue
FROM project.analytics_123.events_*, UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'purchase'
GROUP BY item.item_id, item.item_name, item.item_brand, item.item_category
ORDER BY revenue DESC
LIMIT 50

Query 10: Traffic With vs Without Consent (Consent Mode V2)

If you've implemented Consent Mode V2, GA4 logs consent state on each event. You can split traffic by consented vs non-consented:

SELECT
CASE
WHEN privacy_info.analytics_storage = 'Yes' THEN 'granted'
ELSE 'denied'
END AS consent_state,
COUNT(*) AS events,
COUNT(DISTINCT user_pseudo_id) AS users
FROM project.analytics_123.events_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY consent_state

This reveals your actual consent rate by numbers, directly from your tracking pipeline. For European ecommerce, typical rates are 60-80% granted when banner is implemented correctly.

Mini-Story: The Question That Flipped a Quarter

When Marcin, head of growth at a Polish B2B SaaS, started using BigQuery in May 2025, his team's first real question was "what's the conversion rate from first visit to first paid trial, broken down by first-touch channel, over 18 months?" This question was physically impossible in GA4's UI (14-month retention max, no channel × conversion cohort view).

With 90 days of historical BigQuery Export already accumulated plus loaded 6 months of backfill data, we wrote Query 7 (first touch → purchase) adapted for paid trial events. Answer came back in 8 seconds: organic search drove 2.3× more paid trials per first touch than paid ads, despite his budget being 70/30 paid/organic.

Next quarter's allocation flipped: 50/50. Quarter after: 30 paid/70 organic plus SEO investment. Paid trials up 42% year-over-year, paid acquisition cost down 60%.

The query took 10 minutes to write. The strategic shift it enabled was worth months.

Common Mistakes (and How to Fix Them)

Mistake 1: Forgetting _TABLE_SUFFIX filter. Query scans all historical events tables. Bill explodes. Every query must filter the wildcard.

**Mistake 2: Using events_intraday_* for historical reports.** Intraday tables are current day only. Historical queries should use events_* (which excludes intraday).

Mistake 3: UNNEST(event_params) without SELECT filter. Pulling event_params pulls the whole array for every row. Use (SELECT value.xxx FROM UNNEST(event_params) WHERE key='your_key') instead of UNNEST in FROM clause when you only need one parameter.

Mistake 4: Treating traffic_source as session-scoped. It's user-first-touch scoped. For session source/medium, extract from event_params keys source and medium.

Mistake 5: Using event_date as a date filter without _TABLE_SUFFIX. WHERE event_date = '20251030' still scans all partitions because the filter is on a column, not a partition. Use _TABLE_SUFFIX = '20251030' instead.

Connecting to Looker Studio / Power BI

BigQuery Export tables can feed directly into any BI tool.

Looker Studio (free, Google-native): Add data > BigQuery > pick your project > pick analytics_XXX > use custom query or table. For dashboards refreshed hourly with 10 widgets, typical cost is under 5 USD/month thanks to query cache.

Power BI: Use the Google BigQuery connector (DirectQuery or Import mode). Import mode is cheaper if your dashboard refreshes a few times a day; DirectQuery is needed for real-time.

Tableau: Built-in BigQuery connector. Extract mode recommended to avoid per-query costs.

Custom web apps: Use BigQuery REST API or client libraries (Python google-cloud-bigquery, Node.js @google-cloud/bigquery). Cache aggressively on your side to avoid hitting BigQuery on every request.

For Looker Studio specifically, see my Looker Studio guide which covers the BigQuery connector setup and common dashboard patterns.

Mini-Story: The Expensive Lesson

Not every GA4 BigQuery story ends in a win. Magda, analytics manager at a Polish media company, set up GA4 Export in January 2025 and handed the project to a junior analyst without SQL training. Six weeks later, her Google Cloud bill was 1,240 EUR.

Root cause: the junior had built a Looker Studio dashboard with 14 widgets, each running SELECT FROM events_ with no _TABLE_SUFFIX. Dashboard refreshed every 15 minutes. Each refresh scanned 900 GB of data. Math: 14 × 900 GB × 96 refreshes/day × 30 days = daily bills stacking up fast.

Fix: 2 hours of query rewrites (adding _TABLE_SUFFIX, specific column lists), dashboard refresh interval bumped to hourly, per-query bytes-billed limit set to 10 GB. Next month's bill: 38 EUR.

Same dashboard, same data, 97% cost reduction. The lesson: BigQuery is cheap only if someone who knows SQL sets up the queries. If you're handing the project to analysts without SQL background, either train them first or build the queries yourself and lock them into a managed dashboard.

Want me to set up GA4 BigQuery Export with cost-safe dashboards from day one? See my services, I deploy the export, write 5-10 starter queries, configure budget alerts, and hand over a Looker Studio dashboard that won't surprise you on billing day.

Frequently Asked Questions

Is GA4 BigQuery Export really free?

Yes. Since 2022, every GA4 property has free BigQuery Export. In Universal Analytics, this required paid GA 360 (150,000+ EUR/year). What you pay for is BigQuery storage (0.02 USD/GB/month) and query processing (5 USD/TB scanned). For typical mid-market usage, that's 10-50 USD/month total.

How long does it take for data to appear in BigQuery?

Daily batch export: the next day after midnight in your property's timezone. Streaming export: within an hour of the event, for extra cost (0.05 USD per GB inserted). Historical backfill from before you enabled export isn't available, you only get data from the link date forward.

Can I export historical data from before the BigQuery link?

No. GA4 only starts exporting from the day you link the project. If you need historical data, you'd need to have enabled Universal Analytics BigQuery Export previously (paid GA 360) or pull data via GA4 API into BigQuery manually.

What's the difference between daily and streaming export?

Daily batch is free, runs once after midnight, arrives next day. Streaming is paid (0.05 USD per GB streamed), arrives within minutes, shows up in events_intraday_YYYYMMDD tables. Most teams don't need streaming, daily is enough for dashboards updated hourly.

How do I handle user_id (logged-in users) in GA4 BigQuery?

If you set user_id via the GA4 config (gtag('config', 'G-XXX', {user_id: '123'})), it appears in the user_id column (flat, not nested). Use it to join GA4 sessions to your CRM. Without user_id, you only have user_pseudo_id (Google's cookie-based device identifier).

Why is my BigQuery bill higher than expected?

Almost always one of five issues: scheduled queries rebuilding full aggregates, queries without _TABLE_SUFFIX, SELECT * on wide schemas, streaming inserts when batch would suffice, or dashboards with too-frequent refresh busting cache. See the BigQuery cost optimization guide for specific fixes.

Conclusion

GA4 BigQuery Export is the single biggest upgrade a serious analytics team can make, and it's free. Setup takes 20 minutes, and the 10 queries above cover 80% of what the native UI can't answer: historical cohorts, channel × outcome attribution, e-commerce item-level analysis, consent-split traffic.

Start simple: enable export, wait a day, run Query 1 (top pages) to verify, then adapt Queries 2-10 to your KPIs. Within a month you'll have answered 3-5 business questions your GA4 UI physically couldn't. Cost: under 20 USD/month for typical mid-market traffic if you follow the partition filtering patterns.

Want me to deploy GA4 BigQuery Export and build your first analytics dashboard? Get in touch for a scoped data engineering project. Setup + 10 starter queries + Looker Studio dashboard + budget alerts typically takes 2-3 days. Transparent pricing, no long engagements.