GA4 → BigQuery: the data pipeline, step by step

GA4 in the UI is fine for exploring. But the moment you want to cross sources, keep history beyond the retention limits, or answer specific business questions, you need the raw data. That’s where the BigQuery export comes in — free, native, and powerful.

When do you actually need it?

Three typical triggers:

• You’re hitting GA4’s retention limits (2 or 14 months) and want to keep your history.
• You need to cross GA4 with other sources (CRM, media cost, margin, stock).
• The GA4 UI can’t answer your question (sampling, complex custom dimensions, bespoke funnels).

If none of that applies, the UI is enough. Otherwise, the BigQuery pipeline quickly becomes essential.

1. Enable the export

In GA4 → Admin → BigQuery Links. Pick a GCP project, the frequency (daily, plus streaming if needed), and the events to export. From the next day, your data lands in an analytics_XXXXXX dataset.

2. Understand the event schema

Each row = one event, with a nested structure:

• event_name, event_timestamp
• event_params (key/value array)
• user_pseudo_id, user_id
• device, geo, traffic_source…

The main hurdle is unnesting event_params. Once that becomes a reflex, everything opens up.

3. The UNNEST reflex

To pull a parameter, you unfold the event_params array and read the right key. Example: count page_view events per day with their page_location.

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS day,
  (SELECT value.string_value
     FROM UNNEST(event_params)
    WHERE key = 'page_location') AS page,
  COUNT(*) AS views
FROM `project.analytics_XXXXXX.events_*`
WHERE event_name = 'page_view'
  AND _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY day, page
ORDER BY views DESC;

The _TABLE_SUFFIX filters the daily tables (events_YYYYMMDD) before reading them: that’s what keeps costs under control. Once you’ve internalised (SELECT … FROM UNNEST(event_params) WHERE key = …), you can extract any parameter.

4. A few useful queries

• Sessions and users per day
• Conversion funnel event by event
• First/last-touch attribution from traffic_source
• Revenue by channel, crossed with e-commerce data

5. Modelling

Rather than rewriting the same UNNEST everywhere, build clean intermediate tables (sessions, users, orders) — ideally versioned and documented. That’s what turns a raw dataset into a reliable, reusable analytical foundation. At this point a tool like dbt quickly pays off to orchestrate and test those models.

6. Connect Looker Studio

Looker Studio connects straight to BigQuery. Best practice: point Looker at your modelled tables (not the raw export), for fast dashboards and controlled costs.

7. Cost & best practices

• Partition by date and cluster large tables.
• Avoid SELECT * on the raw export.
• Always filter on _TABLE_SUFFIX (or the partition column) to scan only the period you need.
• Materialise heavy aggregates instead of recomputing them on every dashboard open.
• Watch the volume scanned (BigQuery bills on bytes read, not on the number of queries).

At the end of the pipeline: raw events turned into decisions — with full history, the cross-source views the GA4 UI can’t give you, and costs that stay negligible for most sites.