GA4 BigQuery Export and Analysis

GA4 BigQuery Export and Analysis

Complete guide to GA4 BigQuery export including setup, schema, SQL query patterns, and data analysis.

Overview

GA4 BigQuery export provides raw, event-level data access for advanced analysis, custom reporting, machine learning, and long-term data warehousing. Unlike GA4 reports, BigQuery data is unsampled and allows SQL-based analysis.

Why Use BigQuery

BigQuery Export Setup

Prerequisites

• GA4 property (standard or 360)
• Google Cloud project
• BigQuery API enabled
• Editor permissions on GA4 property

Setup Steps

Step 1: Create/Select Google Cloud Project

1. Go to console.cloud.google.com
2. Create new project or select existing
3. Enable BigQuery API

Step 2: Link GA4 to BigQuery

1. GA4 Admin -> Product Links -> BigQuery Links
2. Click "Link"
3. Choose Google Cloud project
4. Select dataset location (US, EU, etc.)
5. Configure export:
   • Daily: Complete export once per day
   • Streaming: Real-time (360 only)
6. Include advertising IDs (optional)
7. Confirm setup

Export Options

Data Availability

• Daily tables: ~24 hours after day ends
• Intraday tables: ~3 updates per day
• Streaming: Minutes after collection (360)

Table Structure

Table Naming

project.dataset.events_YYYYMMDD     # Daily export
project.dataset.events_intraday_YYYYMMDD  # Intraday
project.dataset.events_*            # Wildcard all dates

Key Schema Fields

Event Fields

User Fields

Device Fields

Geo Fields

Traffic Source Fields

E-commerce Fields

SQL Query Patterns

Query 1: Event Count by Name

SELECT
  event_name,
  COUNT(*) as event_count
FROM
  `project.dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
GROUP BY
  event_name
ORDER BY
  event_count DESC

Query 2: Extract Event Parameters

SELECT
  event_date,
  event_name,
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') as page_title
FROM
  `project.dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
  AND event_name = 'page_view'
LIMIT 1000

Query 3: Purchase Analysis

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) as purchasers,
  COUNT(DISTINCT ecommerce.transaction_id) as transactions,
  SUM(ecommerce.purchase_revenue_in_usd) as total_revenue,
  AVG(ecommerce.purchase_revenue_in_usd) as avg_order_value
FROM
  `project.dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
  AND event_name = 'purchase'
  AND ecommerce.transaction_id IS NOT NULL
GROUP BY
  event_date
ORDER BY
  event_date

Query 4: UNNEST Items Array

SELECT
  event_date,
  item.item_name,
  item.item_category,
  SUM(item.quantity) as total_quantity,
  SUM(item.item_revenue_in_usd) as total_revenue
FROM
  `project.dataset.events_*`,
  UNNEST(items) as item
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
  AND event_name = 'purchase'
GROUP BY
  event_date,
  item.item_name,
  item.item_category
ORDER BY
  total_revenue DESC

Query 5: User Journey Analysis

WITH user_events AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') as page_location
  FROM
    `project.dataset.events_*`
  WHERE
    _TABLE_SUFFIX = '20250115'
)
SELECT
  user_pseudo_id,
  ARRAY_AGG(
    STRUCT(event_name, page_location, event_timestamp)
    ORDER BY event_timestamp
  ) as event_sequence
FROM
  user_events
GROUP BY
  user_pseudo_id
LIMIT 100

Query 6: Session Attribution

SELECT
  event_date,
  traffic_source.source,
  traffic_source.medium,
  traffic_source.name as campaign,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CONCAT(user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id'))) as sessions
FROM
  `project.dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
GROUP BY
  event_date,
  traffic_source.source,
  traffic_source.medium,
  traffic_source.name
ORDER BY
  sessions DESC

Helper Functions

-- Reusable functions for parameter extraction
CREATE TEMP FUNCTION GetParamString(params ANY TYPE, target_key STRING)
RETURNS STRING
AS (
  (SELECT value.string_value FROM UNNEST(params) WHERE key = target_key)
);

CREATE TEMP FUNCTION GetParamInt(params ANY TYPE, target_key STRING)
RETURNS INT64
AS (
  (SELECT value.int_value FROM UNNEST(params) WHERE key = target_key)
);

-- Usage
SELECT
  event_date,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamInt(event_params, 'engagement_time_msec') as engagement_time
FROM
  `project.dataset.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'

Query Optimisation

Best Practices

1. Use _TABLE_SUFFIX Filtering:

-- Good
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'

-- Bad (scans all partitions)
WHERE event_date BETWEEN '20250101' AND '20250131'

2. Filter on Clustered Columns:

-- Tables clustered by event_name and event_timestamp
WHERE event_name IN ('page_view', 'purchase')

3. Select Specific Columns:

-- Good
SELECT event_name, user_pseudo_id, event_timestamp

-- Bad (high cost)
SELECT *

4. Limit UNNEST Operations:

-- Good: Inline UNNEST
(SELECT value.string_value FROM UNNEST(event_params)
 WHERE key = 'page_location')

-- Avoid: Full UNNEST in FROM
FROM table, UNNEST(event_params) as param
WHERE param.key = 'page_location'

5. Use LIMIT During Development:

LIMIT 1000  -- Test query first

Cost Management

BigQuery Pricing

Free Tier

• 10 GB storage free/month
• 1 TB queries free/month

Reducing Costs

1. Partition by date using _TABLE_SUFFIX
2. Select only needed columns
3. Use LIMIT for testing
4. Create materialised views for frequent queries
5. Set up cost alerts in Google Cloud

Data Retention

GA4 vs BigQuery

Setting Table Expiration

ALTER TABLE `project.dataset.events_20250101`
SET OPTIONS (
  expiration_timestamp=TIMESTAMP "2026-01-01 00:00:00 UTC"
)

Common Use Cases

1. Unsampled Reporting

GA4 UI may sample large datasets. BigQuery provides complete data.

2. Custom Attribution

• Access full user journey
• Build custom attribution models
• Credit touchpoints as needed

3. Data Integration

• Join GA4 with CRM data
• Combine with product catalogue
• Enrich with external sources

4. Machine Learning

• Export to ML tools
• Predict churn, LTV, conversions
• Train custom models

5. Long-term Analysis

• Historical analysis beyond GA4 limits
• Year-over-year comparisons
• Trend analysis

Troubleshooting

No Data in Tables

Causes:

• Link just created (wait 24 hours)
• Export paused
• Wrong project/dataset

Solutions:

1. Wait for first export
2. Check BigQuery Links status
3. Verify project configuration

Missing Events

Causes:

• Events not firing
• Consent mode blocking
• Filter applied

Solutions:

1. Verify in DebugView first
2. Check consent configuration
3. Review data filters

High Query Costs

Causes:

• SELECT * usage
• Missing date filters
• Large date ranges

Solutions:

1. Select specific columns
2. Always use _TABLE_SUFFIX
3. Narrow date ranges

Quick Reference

Table Names

• Daily: events_YYYYMMDD
• Intraday: events_intraday_YYYYMMDD
• Wildcard: events_*

Date Filter

WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'

Extract Parameter

(SELECT value.string_value FROM UNNEST(event_params)
 WHERE key = 'param_name')

UNNEST Items

FROM table, UNNEST(items) as item

Costs

• Storage: $0.02/GB/month
• Queries: $5/TB scanned
• Free: 10 GB storage, 1 TB queries/month