/ga4-bigquery-schema

name: ga4-bigquery-schema
description: GA4 BigQuery Export Schema Reference — complete field reference, nested structures, query patterns, and performance tips
version: "1.1.1"
author: Cogny AI
platforms: []
user-invocable: true
argument-hint: "<query or topic>"
allowed-tools:
  - WebSearch
  - Read
  - Write
  - Bash
  # Enhanced when connected via Cogny MCP (read-only)
  - mcp__cogny__list_integrations
  - mcp__cogny__refresh_mcp_discovery
  - mcp__cogny__list_datasets
  - mcp__cogny__list_tables
  - mcp__cogny__inspect_schema
  - mcp__cogny__execute_bigquery_sql
  - mcp__cogny__mcp__Google_Analytics_4__tool_list_account_summaries
  - mcp__cogny__mcp__Google_Analytics_4__tool_run_report

GA4 BigQuery Export Schema Reference

Complete reference guide to the Google Analytics 4 BigQuery export schema, including table structure, nested fields, common queries, and data processing best practices.

Full docs: https://cogny.com/docs/ga4-bigquery-export-schema

Usage

/ga4-bigquery-schema                      # Show full schema overview
/ga4-bigquery-schema event_params         # Explain event_params structure
/ga4-bigquery-schema conversion funnel    # Show conversion funnel query pattern
/ga4-bigquery-schema ecommerce            # Explain ecommerce fields

Instructions

You are a GA4 BigQuery schema expert. Use this reference to help users understand the GA4 export schema, write correct BigQuery SQL queries against GA4 data, and follow performance best practices.

When the user asks a question, find the relevant section below and provide precise, actionable answers with ready-to-use SQL examples.

If the user provides a specific topic as an argument, focus on that area. Otherwise, provide an overview of the schema structure.

Works with

Works with zero accounts: every query below is copy-paste SQL. When connected:

| Job | Solo / no account | Cogny Cloud | Plus GA4 connected | |-----|-------------------|-------------|--------------------| | Find the export dataset | user supplies `project.analytics_ID` | `list_datasets` → `list_tables` | — | | Check the real schema | the reference below | `inspect_schema` | — | | Run a query | user runs it | `execute_bigquery_sql` (read-only SELECTs) | — | | Reconcile export vs UI | — | `execute_bigquery_sql` | `tool_run_report` (same dates) |

BigQuery tools are Cloud-only (section 2 of `../marketing-engineer/references/mcp-routing.md`). On Solo, give the SQL and stop.

Run it on the user's data (Cloud)

If Cogny isn't connected, skip this and note what connecting adds.

1. `list_datasets`, pick the one named `analytics_<property id>`; `list_tables` to confirm daily `events_*` tables exist (and `events_intraday_*` if streaming is on). 2. `inspect_schema` on one daily table before writing SQL; custom `event_params` keys and `user_properties` vary per property. 3. Swap `project.analytics_123456789` in the chosen query for the real path, keep the `_TABLE_SUFFIX` filter, then `execute_bigquery_sql`. 4. **Reconcile** (GA4 connected): run `tool_run_report` (`property_id`, same `date_ranges`, `metrics: [eventCount, totalUsers]`, `dimensions: [eventName]`) next to the export count. A gap under ~2% is normal (late hits, thresholding); a larger one, or users far apart, usually means consent mode modelling in the UI or a broken export day (see Data Quality Checks). Name the source on every number.

---

Overview

Google Analytics 4 exports raw event data to BigQuery in a nested, denormalized format.

**Daily Tables:** `analytics_PROPERTY_ID.events_YYYYMMDD` **Intraday Tables:** `analytics_PROPERTY_ID.events_intraday_YYYYMMDD`

Each row represents a single event with nested fields for event parameters, user properties, and e-commerce data.

-- View table schema
SELECT
  column_name,
  data_type,
  description
FROM `project.analytics_123456789.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name LIKE 'events_%'
ORDER BY ordinal_position

Top-Level Fields

| Field | Type | Description | |-------|------|-------------| | `event_date` | STRING | Date when the event was logged (YYYYMMDD format) | | `event_timestamp` | INTEGER | Time when the event was logged (microseconds since Unix epoch) | | `event_name` | STRING | Name of the event (e.g., 'page_view', 'purchase') | | `event_params` | ARRAY\<STRUCT\> | Array of event parameters | | `event_previous_timestamp` | INTEGER | Timestamp of previous event by this user | | `event_value_in_usd` | FLOAT | Value of the event in USD | | `event_bundle_sequence_id` | INTEGER | Sequential ID of the event bundle | | `event_server_timestamp_offset` | INTEGER | Timestamp offset between collection and server | | `user_id` | STRING | User ID set via setUserId API | | `user_pseudo_id` | STRING | Pseudonymous ID for the user (cookie-based) | | `user_properties` | ARRAY\<STRUCT\> | Array of user properties | | `user_first_touch_timestamp` | INTEGER | First time user visited (microseconds) | | `user_ltv` | STRUCT | User lifetime value information | | `device` | STRUCT | Device information | | `geo` | STRUCT | Geographic information | | `app_info` | STRUCT | App information (mobile apps) | | `traffic_source` | STRUCT | First-touch source that acquired the **user** (never changes) | | `collected_traffic_source` | STRUCT | UTMs / click IDs collected on **this event** | | `session_traffic_source_last_click` | STRUCT | Last-click attributed **session** source (manual, Google Ads, CM360, DV360, SA360) | | `stream_id` | STRING | Numeric ID of the data stream | | `platform` | STRING | Platform (web, ios, android) | | `ecommerce` | STRUCT | E-commerce transaction data | | `items` | ARRAY\<STRUCT\> | Array of item (product) details |

Nested Structures

event_params

Event parameters stored as key-value pairs:

STRUCT<
  key STRING,
  value STRUCT<
    string_value STRING,
    int_value INT64,
    float_value FLOAT64,
    double_value FLOAT64
  >
>

**Common event parameters:**