GA4 BigQuery Cost Optimisation: 9 SQL Patterns (2026)

Why are GA4 BigQuery queries expensive?

GA4 exports raw event data as a nested, repeated schema. The event_params column is an ARRAY of STRUCTs — every row contains an array of key-value pairs for all event parameters. Querying `event_params` with `UNNEST` processes the entire column, including parameters you don't need. For a property exporting 10M events/day with 25 parameters per event across 365 days, a single SELECT * query processes ~91 billion cells.

At BigQuery's on-demand pricing (~$5/TB), naively written queries can cost $50–$500 per run. The 9 patterns below reduce costs by targeting only the data and columns you actually need.

Pattern 1 — Always use partition pruning with `_TABLE_SUFFIX`

GA4 BigQuery exports to date-partitioned tables: events_YYYYMMDD. Without a partition filter, BigQuery scans all tables.

Cost reduction: 90%+ for queries that don't need full history.

Always include _TABLE_SUFFIX as the first filter in every GA4 BigQuery query.

Pattern 2 — Use column projection (never SELECT *)

GA4's schema has ~60 columns. SELECT * reads all of them. Most queries need 5–10.

Cost reduction: 30–60% by not reading device, geo, app_info, and other unused structs.

Pattern 3 — Filter event_name before UNNESTing event_params

UNNEST expands the entire event_params array for every row. Filter to only the events you need before UNNEST to minimise the rows expanded.

Cost reduction: Proportional to the fraction of events that match your event_name filter. For `purchase` events (1–3% of all events), this reduces UNNEST work by ~97%.

Pattern 4 — Use subquery extraction instead of multiple UNNESTs

Each UNNEST(event_params) in a query reads the entire array. If you need 5 parameters, 5 separate UNNESTs reads the array 5 times.

This pattern (correlated subqueries) is actually BigQuery's recommended approach for GA4 because it's cleaner than cross-joining, and modern BigQuery's query optimiser handles it efficiently. The cost is comparable to a single UNNEST for most queries. The real win here is readability, not cost — but it avoids the cartesian product risk of a JOIN + UNNEST pattern.

Pattern 5 — Use the intraday tables carefully

GA4 exports two table types: events_YYYYMMDD (processed, finalized) and events_intraday_YYYYMMDD (today's unprocessed data). Querying intraday tables is fine for same-day dashboards, but avoid UNIONing them with historical tables unless the date range genuinely requires it.

Pattern 6 — Pre-aggregate with scheduled queries

For repeated analyses (weekly channel report, daily conversion summary), run a scheduled query that pre-aggregates the data into a summary table. Dashboards then query the summary table (kilobytes) instead of the raw events table (gigabytes).

Cost reduction: Dashboard queries go from $0.50–$5 per run to $0.00001 per run.

Pattern 7 — Use INFORMATION_SCHEMA to preview scan size

Before running an expensive query, preview how much data it will scan:

Pattern 8 — Filter items array by event before expanding

For item-level queries, filter to purchase events before expanding the items array:

Pattern 9 — Use materialised views for repeated patterns

For very frequently run queries (e.g., the hourly active users query powering a live operations dashboard), BigQuery materialised views automatically maintain pre-computed results and update incrementally:

Queries against the materialised view are nearly free since the data is pre-computed.

Cost monitoring setup

Prevent surprise BigQuery bills:

1. BigQuery → Administration → Capacity management → set a project-level spending limit
2. Google Cloud → Billing → Budgets & alerts → set alert at $50, $100, $200 to catch runaway queries early
3. BigQuery → Job history → sort by "Bytes processed" descending → identify your most expensive queries weekly