如何在BigQuery中按不同event_params.key查询Firebase Analytics事件数据

你之前的写法是全局UNNEST事件参数后过滤，会将单条事件行拆分为多条参数行，只能匹配单个参数的筛选条件，无法同时提取多类不同key的参数值。你可以用标量子查询直接提取指定key的参数值，不需要提前打散事件行，写法如下：

核心语法说明 #

提取指定key的参数值时，用对应类型的value字段即可：

• 字符串类型参数：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = '你的参数名')
• 整数类型参数：(SELECT value.int_value FROM UNNEST(event_params) WHERE key = '你的参数名')
• 浮点数类型参数：(SELECT value.float_value FROM UNNEST(event_params) WHERE key = '你的参数名')

场景示例代码 #

1. 计算Read_Free_Article事件的平均停留时长 #

假设停留时长timeSpend是整数类型参数：

SELECT
  AVG(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'timeSpend')
  ) AS avg_time_spend
FROM
  `myProject.analytics_number.events_2021*`
WHERE
  event_name = "Read_Free_Article"
  -- 若需要保留之前筛选参数值为X的条件，增加以下行即可，将`匹配X的参数key`替换为实际参数名
  -- AND EXISTS(SELECT 1 FROM UNNEST(event_params) WHERE key = '匹配X的参数key' AND value.string_value = "X")

2. 按文章标题分组统计事件数量 #

假设标题title是字符串类型参数：

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'title') AS article_title,
  COUNT(*) AS event_count
FROM
  `myProject.analytics_number.events_2021*`
WHERE
  event_name = "Read_Free_Article"
GROUP BY
  article_title
ORDER BY
  event_count DESC

3. 多参数联合查询示例 #

同时提取标题、作者维度，统计对应阅读量和平均停留时长：

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'title') AS article_title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'author') AS article_author,
  AVG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'timeSpend')) AS avg_read_time,
  COUNT(*) AS read_count
FROM
  `myProject.analytics_number.events_2021*`
WHERE
  event_name = "Read_Free_Article"
GROUP BY
  article_title, article_author

注意事项 #

• 取值时需要和埋点时上报的参数类型对应，否则会返回空值
• 多参数筛选时用EXISTS子查询比全局UNNEST效率更高，也不会出现事件行重复导致统计数据不准的问题

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