GA4 → BigQuery: the data pipeline, step by step

Draft — reviewed and completed by Yoann before publishing.

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.

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. 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

4. 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.

5. 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.

6. Cost & best practices

• Partition by date and cluster large tables.
• Avoid SELECT * on the raw export.
• Materialise heavy aggregates instead of recomputing them on every dashboard open.
• Watch the volume scanned (BigQuery bills on bytes read).

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.