BigQuery Cost Optimisation for Analytics Teams

How BigQuery Costs Are Generated

BigQuery charges $5 per TB of data processed by queries (with 1TB/month free). The key: BigQuery charges for data scanned, not for rows returned. A query that scans 500GB to return 100 rows costs the same as one returning 1 million rows. Cost control means minimising data scanned.

Optimisation 1: Always Filter by Date Shard

The most important cost control in GA4 BigQuery queries. Using events_* (wildcard) without a _TABLE_SUFFIX filter scans ALL daily tables — months or years of data. Always include:

WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'

This limits the query to scan only the specified tables. A query on 31 days costs roughly 31x less than the same query on a full year.

Optimisation 2: SELECT Specific Columns, Not SELECT *

BigQuery is a columnar database — SELECT * reads every column. The GA4 event table has 30+ columns, many large (event_params is a REPEATED RECORD). Selecting only needed columns dramatically reduces data scanned:

-- EXPENSIVE (scans entire table width):
SELECT * FROM `project.analytics_PROPERTY.events_20260115`

-- CHEAP (scans only 4 columns):
SELECT event_name, user_pseudo_id, event_date, event_timestamp
FROM `project.analytics_PROPERTY.events_20260115`

Optimisation 3: Use Partitioned Tables and Scheduled Aggregation

Instead of running expensive raw-event queries for every report, create aggregated summary tables using scheduled queries. Run the expensive raw query once per day (when new data lands) and write results to an aggregated table. Dashboards then query the cheap summary table, not the raw events.

-- Schedule this to run daily, writing to a summary table:
INSERT INTO `project.analytics.daily_summary`
SELECT
  event_date,
  device.category,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(*) as events
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY event_date, device.category

Optimisation 4: Preview Estimated Costs Before Running

BigQuery shows the estimated bytes processed before running a query (bottom right of the query editor, before you click Run). Check this before running any ad-hoc query — if it says 50GB and you expected 500MB, there is likely a missing date filter.

Optimisation 5: BigQuery Reservations for Heavy Users

For teams running many large queries monthly, BigQuery Reservations (committed compute slots) can be more cost-effective than on-demand pricing. At high query volumes, reserved slots provide predictable costs regardless of data scanned.

Summary

BigQuery cost optimisation: always use _TABLE_SUFFIX date filters, SELECT only needed columns (never SELECT *), create aggregated summary tables via scheduled queries for dashboards, check estimated cost before running ad-hoc queries. Most analytics teams can run comprehensive GA4 analysis for $0-$20/month with these practices. Reserved slots are worth considering only at high query volumes (100+ TB/month).

See our BigQuery Setup service for cost-efficient analytics infrastructure.

Need help optimising BigQuery costs? Contact Adslytics.