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.

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 | Traffic source information | | 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:

| Key | Type | Description | |-----|------|-------------| | page_location | string | Full URL of the page | | page_title | string | Title of the page | | page_referrer | string | Referrer URL | | engagement_time_msec | int | Engagement time in milliseconds | | session_engaged | int | Whether session was engaged (1/0) | | ga_session_id | int | Session ID | | ga_session_number | int | Session number for user |

Example — extract event params:

SELECT
  event_name,
  (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,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') as engagement_time
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'page_view'
LIMIT 100

user_properties

User properties stored as key-value pairs:

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

Example — extract user properties:

SELECT
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_type') as user_type,
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'plan_level') as plan_level
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY user_pseudo_id, user_type, plan_level
LIMIT 100

device

STRUCT<
  category STRING,              -- desktop, mobile, tablet
  mobile_brand_name STRING,     -- Apple, Samsung, etc.
  mobile_model_name STRING,     -- iPhone 12, Galaxy S21, etc.
  mobile_marketing_name STRING,
  mobile_os_hardware_model STRING,
  operating_system STRING,      -- iOS, Android, Windows, macOS
  operating_system_version STRING,
  vendor_id STRING,
  advertising_id STRING,
  language STRING,              -- en-us, fr-fr, etc.
  is_limited_ad_tracking STRING,
  time_zone_offset_seconds INT64,
  browser STRING,               -- Chrome, Safari, Firefox
  browser_version STRING,
  web_info STRUCT<
    browser STRING,
    browser_version STRING,
    hostname STRING
  >
>

Example — device breakdown:

SELECT
  device.category as device_category,
  device.operating_system,
  device.browser,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_pseudo_id) as users
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1, 2, 3
ORDER BY event_count DESC

geo

STRUCT<
  continent STRING,     -- Americas, Europe, Asia, etc.
  sub_continent STRING, -- Northern Europe, Western Asia, etc.
  country STRING,       -- United States, United Kingdom, etc.
  region STRING,        -- California, England, etc.
  metro STRING,         -- Metro area
  city STRING
>

Example — geographic breakdown:

SELECT
  geo.country,
  geo.city,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(*) as events
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1, 2
ORDER BY users DESC
LIMIT 100

traffic_source

STRUCT<
  name STRING,   -- Traffic source name (google, facebook, etc.)
  medium STRING, -- Traffic medium (organic, cpc, referral, etc.)
  source STRING  -- Traffic source (google, facebook.com, etc.)
>

Example — traffic source performance:

SELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) as purchasers,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END),
    COUNT(DISTINCT user_pseudo_id)
  ) as conversion_rate
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1, 2
ORDER BY users DESC

ecommerce

STRUCT<
  total_item_quantity INT64,
  purchase_revenue_in_usd FLOAT64,
  purchase_revenue FLOAT64,
  refund_value_in_usd FLOAT64,
  refund_value FLOAT64,
  shipping_value_in_usd FLOAT64,
  shipping_value FLOAT64,
  tax_value_in_usd FLOAT64,
  tax_value FLOAT64,
  unique_items INT64,
  transaction_id STRING
>

Example — revenue analysis:

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
  COUNT(DISTINCT ecommerce.transaction_id) as transactions,
  SUM(ecommerce.pu

ga4-bigquery-schema

Como adicionar

Cole no README do seu repo

Skills relacionadas

learn-codebase

remove-deadcode

growth-engine

social-post-writer-seo

Receba novas skills de Marketing toda segunda