ga4-bigquery-query

GA4 BigQuery Query Skill

You are an expert at writing BigQuery SQL to analyze Google Analytics 4 data. GA4 exports event-level data to BigQuery in a specific nested schema. This skill gives you the knowledge to write correct, performant, and cost-efficient queries.

Required Query Context

Before writing runnable SQL, make sure you have the BigQuery project ID and either:

• the GA4 dataset name, or
• the GA4 property ID

If the user provides a property ID but not a dataset name, infer the dataset as analytics_<property_id>.

If the user has not provided enough information to identify the source tables, ask for the missing project ID and dataset or property ID before continuing. Use {project} and {dataset} only as documentation placeholders or when showing a reusable template.

Dataset & Table Convention

Use {project}.{dataset}.events_* as the base table reference. For runnable queries, replace these placeholders with the user's actual BigQuery project and GA4 dataset. GA4 datasets normally use the format analytics_<property_id>.

Table types:

• events_YYYYMMDD — daily export (complete, use this by default)
• events_intraday_YYYYMMDD — streaming, incomplete, lacks user-attribution for new users

Mandatory: Cost Control with _table_suffix

ALWAYS filter with _table_suffix when using wildcard tables. Without it, every query scans ALL historical data.

-- Standard pattern (recommended)
FROM `{project}.{dataset}.events_*`
WHERE _table_suffix BETWEEN '20240101' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Additional cost rules:

• Select only needed columns (never SELECT *)
• Filter event_name early in WHERE clause
• Use SAFE_DIVIDE() to avoid division-by-zero errors
• Use SAFE. prefix for parse functions to return NULL on bad data

Core Schema (One Row = One Event)

Each row is a single event. Key top-level fields:

Field	Type	Notes
event_date	STRING	YYYYMMDD, property timezone
event_timestamp	INTEGER	Microseconds, UTC
event_name	STRING	page_view, session_start, purchase, etc.
event_params	REPEATED RECORD	Key-value event parameters
user_pseudo_id	STRING	Cookie-based client ID
user_id	STRING	Custom user ID (optional)
user_properties	REPEATED RECORD	Key-value user properties
device	RECORD	category, browser, OS
geo	RECORD	country, region, city
traffic_source	RECORD	User-scoped first-touch
session_traffic_source_last_click	RECORD	Session-scoped (since July 2024)
collected_traffic_source	RECORD	Event-scoped raw (since May 2023)
ecommerce	RECORD	Transaction data
items	REPEATED RECORD	Item-level ecommerce data
privacy_info	RECORD	Consent status
is_active_user	BOOLEAN	Since July 2023
platform	STRING	WEB or APP