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.