ga4-bigquery-schema

Name: ga4-bigquery-schema
Author: cognyai

github.com/cognyai数据与数据库11152 次安装2 周前更新

代码仓库

关于 ga4-bigquery-schema

面向使用 BigQuery 分析 GA4 导出数据的人员，介绍嵌套架构，并提供查询示例与性能建议。

主要功能

01顶层与嵌套架构参考
02常见分析的 SQL 示例
03查询性能建议
04导出数据质量检查

适用场景

01提取事件参数与用户属性
02分析会话、漏斗和收入
03检查导出日期与事件数量

安装

复制下面的命令，在终端中运行。

$npx skills add https://github.com/cognyai/claude-code-marketing-skills --skill ga4-bigquery-schema

安装后在 Claude Code 中输入/ga4-bigquery-schema确认已加载。

详细介绍

GA4 BigQuery 导出架构参考

Google Analytics 4 BigQuery 导出架构的完整参考指南，包括表结构、嵌套字段、常用查询和数据处理最佳实践。

完整文档：https://cogny.com/docs/ga4-bigquery-export-schema

用法

/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

说明

你是 GA4 BigQuery 架构专家。请使用此参考帮助用户了解 GA4 导出架构、针对 GA4 数据编写正确的 BigQuery SQL 查询，并遵循性能最佳实践。

用户提出问题时，请找到下方相关章节，并提供准确、可操作且可直接使用的 SQL 示例。

如果用户提供了具体主题作为参数，请聚焦于该领域。否则，请概述架构结构。

概述

Google Analytics 4 会以嵌套、非规范化格式将原始事件数据导出到 BigQuery。

每日表： analytics_PROPERTY_ID.events_YYYYMMDD 日内表： analytics_PROPERTY_ID.events_intraday_YYYYMMDD

每一行代表一个事件，其中包含事件参数、用户属性和电子商务数据等嵌套字段。

-- 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

顶级字段

字段	类型	描述
`event_date`	STRING	记录事件的日期（YYYYMMDD 格式）
`event_timestamp`	INTEGER	记录事件的时间（自 Unix 纪元起的微秒数）
`event_name`	STRING	事件名称（例如 'page_view'、'purchase'）
`event_params`	ARRAY<STRUCT>	事件参数数组
`event_previous_timestamp`	INTEGER	此用户上一个事件的时间戳
`event_value_in_usd`	FLOAT	事件的美元价值
`event_bundle_sequence_id`	INTEGER	事件包的顺序 ID
`event_server_timestamp_offset`	INTEGER	数据收集时间与服务器时间之间的时间戳偏移量
`user_id`	STRING	通过 setUserId API 设置的用户 ID
`user_pseudo_id`	STRING	用户的假名 ID（基于 Cookie）
`user_properties`	ARRAY<STRUCT>	用户属性数组
`user_first_touch_timestamp`	INTEGER	用户首次访问的时间（微秒）
`user_ltv`	STRUCT	用户生命周期价值信息
`device`	STRUCT	设备信息
`geo`	STRUCT	地理位置信息
`app_info`	STRUCT	应用信息（移动应用）
`traffic_source`	STRUCT	流量来源信息
`stream_id`	STRING	数据流的数字 ID
`platform`	STRING	平台（web、ios、android）
`ecommerce`	STRUCT	电子商务交易数据
`items`	ARRAY<STRUCT>	商品详情数组

嵌套结构

event_params

以键值对形式存储的事件参数：

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

常见事件参数：

键	类型	描述
`page_location`	string	页面的完整 URL
`page_title`	string	页面的标题
`page_referrer`	string	引荐来源 URL
`engagement_time_msec`	int	互动时长（毫秒）
`session_engaged`	int	会话是否有互动（1/0）
`ga_session_id`	int	会话 ID
`ga_session_number`	int	用户的会话编号

示例 — 提取事件参数：

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

以键值对形式存储的用户属性：

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

示例 — 提取用户属性：

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
  >
>

示例 — 按设备分类：

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
>

示例 — 按地理位置分类：

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.)
>

示例 — 流量来源效果：

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
>

示例 — 收入分析：

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
  COUNT(DISTINCT ecommerce.transaction_id) as transactions,
  SUM(ecommerce.purchase_revenue_in_usd) as revenue,
  AVG(ecommerce.purchase_revenue_in_usd) as avg_order_value
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'purchase'
  AND ecommerce.transaction_id IS NOT NULL
GROUP BY 1
ORDER BY 1 DESC

items

STRUCT<
  item_id STRING,
  item_name STRING,
  item_brand STRING,
  item_variant STRING,
  item_category STRING,
  item_category2 STRING,
  item_category3 STRING,
  item_category4 STRING,
  item_category5 STRING,
  price_in_usd FLOAT64,
  price FLOAT64,
  quantity INT64,
  item_revenue_in_usd FLOAT64,
  item_revenue FLOAT64,
  item_refund_in_usd FLOAT64,
  item_refund FLOAT64,
  coupon STRING,
  affiliation STRING,
  location_id STRING,
  item_list_id STRING,
  item_list_name STRING,
  item_list_index STRING,
  promotion_id STRING,
  promotion_name STRING,
  creative_name STRING,
  creative_slot STRING
>

示例 — 商品效果：

SELECT
  item.item_name,
  item.item_category,
  SUM(item.quantity) as total_quantity,
  SUM(item.item_revenue_in_usd) as total_revenue,
  COUNT(DISTINCT ecommerce.transaction_id) as transactions
FROM `project.analytics_123456789.events_*`,
  UNNEST(items) as item
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'purchase'
GROUP BY 1, 2
ORDER BY total_revenue DESC
LIMIT 100

常见查询模式

会话和用户

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
  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,
  SAFE_DIVIDE(
    COUNT(DISTINCT CONCAT(user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))),
    COUNT(DISTINCT user_pseudo_id)
  ) as sessions_per_user
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
GROUP BY 1
ORDER BY 1 DESC

页面浏览量

SELECT
  (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,
  COUNT(*) as page_views,
  COUNT(DISTINCT user_pseudo_id) as unique_users,
  AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) / 1000 as avg_engagement_seconds
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  AND event_name = 'page_view'
GROUP BY 1, 2
ORDER BY page_views DESC
LIMIT 100

转化漏斗

WITH funnel_steps AS (
  SELECT
    user_pseudo_id,
    COUNTIF(event_name = 'page_view') as step_1_landing,
    COUNTIF(event_name = 'view_item') as step_2_product_view,
    COUNTIF(event_name = 'add_to_cart') as step_3_add_to_cart,
    COUNTIF(event_name = 'begin_checkout') as step_4_checkout,
    COUNTIF(event_name = 'purchase') as step_5_purchase
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY user_pseudo_id
)

SELECT
  'Landing' as step, 1 as step_number,
  COUNT(DISTINCT CASE WHEN step_1_landing > 0 THEN user_pseudo_id END) as users,
  1.0 as conversion_rate
FROM funnel_steps
UNION ALL
SELECT 'Product View', 2,
  COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_1_landing > 0 THEN user_pseudo_id END))
FROM funnel_steps
UNION ALL
SELECT 'Add to Cart', 3,
  COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_2_product_view > 0 THEN user_pseudo_id END))
FROM funnel_steps
UNION ALL
SELECT 'Checkout', 4,
  COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_3_add_to_cart > 0 THEN user_pseudo_id END))
FROM funnel_steps
UNION ALL
SELECT 'Purchase', 5,
  COUNT(DISTINCT CASE WHEN step_5_purchase > 0 THEN user_pseudo_id END),
  SAFE_DIVIDE(COUNT(DISTINCT CASE WHEN step_5_purchase > 0 THEN user_pseudo_id END),
              COUNT(DISTINCT CASE WHEN step_4_checkout > 0 THEN user_pseudo_id END))
FROM funnel_steps
ORDER BY step_number

用户获取

WITH first_visit AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) as first_visit_timestamp,
    ARRAY_AGG(
      STRUCT(traffic_source.source, traffic_source.medium, traffic_source.name)
      ORDER BY event_timestamp LIMIT 1
    )[OFFSET(0)] as first_traffic_source
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
    AND event_name = 'first_visit'
  GROUP BY user_pseudo_id
)

SELECT
  first_traffic_source.source as acquisition_source,
  first_traffic_source.medium as acquisition_medium,
  COUNT(DISTINCT user_pseudo_id) as new_users,
  COUNT(DISTINCT CASE WHEN purchase_count > 0 THEN user_pseudo_id END) as purchasers,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN purchase_count > 0 THEN user_pseudo_id END),
    COUNT(DISTINCT user_pseudo_id)
  ) as conversion_rate
FROM first_visit
LEFT JOIN (
  SELECT user_pseudo_id, COUNT(*) as purchase_count
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
    AND event_name = 'purchase'
  GROUP BY user_pseudo_id
) purchases USING(user_pseudo_id)
GROUP BY 1, 2
ORDER BY new_users DESC

队列留存

WITH cohorts AS (
  SELECT
    user_pseudo_id,
    DATE(TIMESTAMP_MICROS(MIN(user_first_touch_timestamp))) as cohort_date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY user_pseudo_id
),
user_activity AS (
  SELECT user_pseudo_id, DATE(TIMESTAMP_MICROS(event_timestamp)) as activity_date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY user_pseudo_id, activity_date
)

SELECT
  cohort_date,
  DATE_DIFF(activity_date, cohort_date, DAY) as days_since_cohort,
  COUNT(DISTINCT c.user_pseudo_id) as cohort_size,
  COUNT(DISTINCT a.user_pseudo_id) as active_users,
  SAFE_DIVIDE(COUNT(DISTINCT a.user_pseudo_id), COUNT(DISTINCT c.user_pseudo_id)) as retention_rate
FROM cohorts c
LEFT JOIN user_activity a USING(user_pseudo_id)
WHERE DATE_DIFF(activity_date, cohort_date, DAY) IN (0, 1, 7, 14, 30, 60, 90)
GROUP BY 1, 2
ORDER BY 1 DESC, 2

性能最佳实践

分区裁剪 — 始终使用 _TABLE_SUFFIX

-- GOOD: Uses partition pruning
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'

-- BAD: Scans all partitions
WHERE event_date BETWEEN '20250101' AND '20250131'

聚类 — 按 event_name 筛选

GA4 表按 event_name 进行聚类。尽可能始终添加 event_name 筛选条件。

物化视图

为频繁访问的聚合数据创建物化视图：

CREATE MATERIALIZED VIEW `project.analytics_123456789.daily_summary`
AS
SELECT
  event_date,
  traffic_source.source,
  traffic_source.medium,
  device.category,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(*) as events,
  COUNTIF(event_name = 'purchase') as purchases,
  SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue_in_usd END) as revenue
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
GROUP BY 1, 2, 3, 4

数据质量检查

检查是否缺少导出日期

WITH date_range AS (
  SELECT date
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY), CURRENT_DATE() - 1)) as date
),
exported_dates AS (
  SELECT DISTINCT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) as date
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
)
SELECT dr.date, 'Missing' as status
FROM date_range dr
LEFT JOIN exported_dates ed USING(date)
WHERE ed.date IS NULL
ORDER BY dr.date DESC

验证事件计数是否一致

WITH daily_counts AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_pseudo_id) as user_count
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE() - 1)
  GROUP BY 1
)
SELECT
  date, event_count, user_count,
  AVG(event_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) as avg_7day,
  event_count / NULLIF(AVG(event_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING), 0) as variance_ratio
FROM daily_counts
ORDER BY date DESC

资源

GA4 BigQuery 导出架构： https://support.google.com/analytics/answer/7029846
BigQuery 最佳实践： https://cloud.google.com/bigquery/docs/best-practices
GA4 事件参考： https://developers.google.com/analytics/devguides/collection/ga4/reference/events
Cogny 完整文档： https://cogny.com/docs/ga4-bigquery-export-schema

支持 Markdown

登录后评论

基本信息

作者: cognyai
仓库: cognyai/claude-code-marketing-skills
分类: 数据与数据库
安装次数: 52
GitHub Stars: 111

报告问题