GA4 BigQuery Export: Unsampled Data, Custom Attribution, and SQL Queries That Work
GA4's free BigQuery export is the escape hatch from GA4's sampling limits and 90-day reporting windows. This guide covers setup, cost control, the event_params UNNEST pattern that trips everyone up, and four analysis use cases you can't do in the GA4 UI.
AttriModel Team
AttriModel Team
Why Export to BigQuery?
GA4's built-in reports are limited — you can't do arbitrary SQL queries, historical data has sampling issues for large properties, and some cross-session analysis is impossible in the UI. BigQuery export solves all of this.
Free for all GA4 properties, the BigQuery export gives you raw, unsampled event-level data that you can query with SQL, connect to Looker Studio, or feed into custom ML models.
What You Get
Each row in BigQuery represents one event. You get:
- event_name, event_timestamp
- User identifiers (user_pseudo_id, user_id if set)
- All event parameters
- All user properties
- Device, geo, traffic source dimensions
Setup: Step by Step
- Create a BigQuery project in Google Cloud Console (free tier is sufficient for most)
- In GA4 Admin → BigQuery Linking → Link
- Select your GCP project and choose daily export (free) or streaming (costs money)
- Choose which events to export (all events recommended)
Allow 24 hours for the first export to appear.
Understanding the Data Structure
`sql
-- Basic query: page views in the last 7 days
SELECT
event_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_url,
COUNT(*) as page_views
FROM your-project.analytics_XXXXXXXXX.events_*
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
AND event_name = 'page_view'
GROUP BY 1, 2
ORDER BY 3 DESC
`
The unnested event_params structure is the trickiest part — each parameter is stored as a key-value pair that you unnest with UNNEST().
Cost Management
BigQuery charges per query (terabytes scanned) and per storage. For most marketing use cases:
- Storage: usually under $5/month
- Queries: use partition filters (_TABLE_SUFFIX) and column selection to minimize costs
Always use date filters in WHERE clause to limit partition scanning.
Practical Use Cases
Custom attribution: Join GA4 event data with your CRM to build first-touch, last-touch, or custom attribution models in SQL.
Cohort analysis: Track user behavior over time beyond GA4's 90-day limit.
Funnel with time gaps: GA4 funnels require events in a single session. BigQuery lets you build funnels across multiple sessions.
Anomaly detection: Query daily metrics and compare against trailing averages to flag traffic spikes or drops automatically.