Working with the GA4 BigQuery Export: A Practical Guide

GA4’s BigQuery export is one of the most powerful features of the platform. But the raw data is complex: nested tables, structs, arrays. This guide shows you how to turn it into something you can actually work with.

Enable the BigQuery export

In GA4, go to Admin → BigQuery Links and enable the export. Choose between:

• Daily: one export per day, free, enough for most cases
• Streaming: real time, costs more, useful for alerting

The export creates tables named events_YYYYMMDD in your BigQuery dataset.

Understand the data structure

Each row in the table is an event. Parameters are stored in RECORD (nested) columns:

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  -- Extract an event parameter
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  -- Extract the traffic source
  traffic_source.source,
  traffic_source.medium
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'

UNNEST is the key to reaching nested parameters. It’s the most common pattern you’ll use.

Build a query-ready sessions view

The raw data sits at the event level. To analyze at the session level, you need to aggregate:

CREATE OR REPLACE VIEW `project.dataset.sessions` AS
SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(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
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'
GROUP BY 1, 2

This view can then feed a Looker Studio dashboard directly.

Keep costs under control

BigQuery bills on the amount of data scanned. A few best practices:

• Always filter with _TABLE_SUFFIX to limit the tables scanned
• Materialize tables for recurring queries
• Use partitioning and clustering on your derived tables
• Monitor spend with GCP quotas and alerts

To understand where this data comes from and how to improve its quality, see the guide on setting up GA4. The same approach lets you track AI traffic (ChatGPT, Gemini, Claude) in GA4 and segment conversions by AI source.