如何在BigQuery中基于多事件参数过滤Google Analytics 4事件

要同时筛选page_location和medium两个参数都符合条件的GA4事件，不能直接在UNNEST后的行上同时过滤（因为UNNEST会把每个参数拆成单独行，同一行不可能同时对应两个不同参数），可以用以下两种方法实现：

方法一：使用EXISTS子查询（推荐） #

通过两次UNNEST分别检查事件是否包含符合条件的参数，只有两个条件都满足的事件才会被选中：

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_date_time,
  event_name,
  event_params,
  platform,
  user_pseudo_id,
  user_id
FROM
  `blueray-cargo.analytics_296834888.events_*`
WHERE
  event_name = "first_visit"
  -- 检查是否存在符合条件的page_location参数
  AND EXISTS (
    SELECT 1
    FROM UNNEST(event_params) AS param
    WHERE param.key = "page_location" AND param.value.string_value = "https://myWeb.id//"
  )
  -- 检查是否存在符合条件的medium参数
  AND EXISTS (
    SELECT 1
    FROM UNNEST(event_params) AS param
    WHERE param.key = "medium" AND param.value.string_value = "organic"
  )
ORDER BY event_timestamp DESC

方法二：使用ARRAY_CONTAINS数组函数 #

直接检查event_params数组是否包含指定的参数结构：

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_date_time,
  event_name,
  event_params,
  platform,
  user_pseudo_id,
  user_id
FROM
  `blueray-cargo.analytics_296834888.events_*`
WHERE
  event_name = "first_visit"
  AND ARRAY_CONTAINS(
    event_params, 
    STRUCT(
      "page_location" AS key, 
      STRUCT(NULL AS int_value, "https://myWeb.id//" AS string_value, NULL AS float_value, NULL AS double_value) AS value
    )
  )
  AND ARRAY_CONTAINS(
    event_params, 
    STRUCT(
      "medium" AS key, 
      STRUCT(NULL AS int_value, "organic" AS string_value, NULL AS float_value, NULL AS double_value) AS value
    )
  )
ORDER BY event_timestamp DESC

说明 #

原查询的问题在于UNNEST(event_params)后，每个参数对应单独一行数据，无法在同一行同时匹配page_location和medium两个不同的param.key。上述两种方法都是从事件级别而非参数行级别进行条件判断，确保两个参数条件同时满足。

内容的提问来源于stack exchange，提问作者Agung