Google Analytics（分析）4 事件数据导出基本查询

本页面中的查询示例适用于 Google Analytics（分析）4 的 BigQuery 事件导出数据。如果您在寻找适用于 Universal Analytics 的同类资源，不妨参阅适用于 Universal Analytics 的 BigQuery 实战宝典。

此处列出的所有查询都使用了示例数据集，并且应该能生成有效结果。如需使用您自己的 Google Analytics（分析）媒体资源的 BigQuery 事件导出数据，请在每个查询中查找注释 -- Replace table 并替换示例数据集 ID。如需数据集 ID，可以前往您在 BigQuery 界面中的 BigQuery 导出项目进行查找。例如，如果您的 BigQuery 导出数据集 ID 为 my- first-gcp-project:analytics_28239234，则将：

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

替换为：

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

查询特定日期范围内的数据

如需在 BigQuery 事件导出数据集内查询特定日期范围内的数据，请在查询的 WHERE 子句中使用 _TABLE_SUFFIX 伪列。如需了解详情，请参阅使用 _TABLE_SUFFIX 过滤选定表。

例如，以下查询会按日期和事件名称统计特定时间段内所选事件的唯一身份事件数：

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

用户数和新用户数

  • 要了解总用户数，请统计不同 user_id 的数量。但是，如果您的 Google Analytics（分析）客户端未随每次命中发回 user_id，或者您对此不确定，可以统计不同 user_pseudo_id 的数量。
  • 如需了解新用户数，您可以采用上述统计方法，但针对以下 event_name 值进行统计： 
-- Example: Get 'Total User' count and 'New User' count.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

每位购买者的平均交易次数

以下查询会显示每位购买者的平均交易次数。

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

特定事件名称的值

以下查询会显示所有 purchase 事件的 event_timestamp 及其关联的事件参数值：

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

可将上述查询修改为显示事件参数值的总数（而非列表）：

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

添加到购物车的十大热门商品

以下查询会显示被为数最多的用户添加到购物车的十大热门商品。

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_web_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

按购买者类型（属于购买者还是非购买者）显示的平均网页浏览量

以下查询会按用户所属的购买者类型（购买者还是非购买者）显示平均网页浏览量：

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

网页浏览顺序

以下查询会显示用户在唯一会话期间执行的网页浏览的顺序：

-- Example: Sequence of pageviews.

SELECT
  user_pseudo_id,
  event_timestamp,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_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
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
ORDER BY
  user_pseudo_id,
  ga_session_id,
  event_timestamp ASC;

事件参数列表

以下查询会列出您的数据集内显示的所有事件参数：

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

与 Google Ads 相联接

若要检索 GA4 事件的其他 Google Ads 数据，请设置适用于 Google Ads 的 BigQuery Data Transfer Service，然后将 GA4 事件数据中的 collected_traffic_source.gclid 联接到 Google Ads 转移作业中 ads_ClickStats_customer_id 的 gclid 字段。

请注意，GA4 事件数据导出功能每天会创建一个表，而 Google Ads 转移作业则会为每个客户填充一个 ads_ClickStats_customer_id 表。