BigQuery查询Firebase事件时缺失firebase_screen_name数据排查

你遇到的BigQuery查询不到屏幕名称问题由两个核心错误导致：

• 查询逻辑错误：你使用event_params[OFFSET(0)]固定读取数组第一个元素的写法完全不可靠，BigQuery中event_params数组的键值对顺序没有固定规则，不会永远将屏幕类参数放在第一位，这种写法会漏掉绝大多数符合条件的事件，你现在能查到部分结果只是小概率匹配上了顺序刚好符合的样本。
• 参数名匹配错误：你要找的firebase_screen_name、firebase_screen字段本身就不存在于你手动上报的事件中：
  • 你通过Analytics.logEvent手动上报screen_view事件时传入的AnalyticsParameterScreenName参数，在BigQuery中对应的键名是screen_name，传入的AnalyticsParameterScreenClass对应的键名是screen_class，没有firebase_前缀。
  • 你在返回结果中看到的firebase_screen_class、firebase_screen_id等带firebase_前缀的参数，是SDK自动采集屏幕事件时生成的旧版字段，不会存储你手动传入的自定义屏幕名称。
  • Google Analytics控制台会自动合并自动采集、手动上报的多来源屏幕字段做归一化展示，因此你可以在控制台看到屏幕名，不代表BigQuery中存在你预期的两个带前缀的字段。

不要用固定偏移量读取event_params数组，使用UNNEST语法拆分数组后按键名匹配参数，筛选指定屏幕类的查询语句如下：

SELECT *
FROM `project.analytics_000000000.events_20220608`
WHERE 
  event_name = 'screen_view' 
  AND platform = 'ANDROID'  
  AND EXISTS(
    SELECT 1 FROM UNNEST(event_params) ep
    WHERE ep.key = 'screen_class' AND ep.value.string_value = 'XActivity'
  )

如果需要同时提取屏幕名称（兼容自动采集、手动上报两种场景），可以使用如下写法：

SELECT 
  event_timestamp,
  user_pseudo_id,
  COALESCE(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'screen_name'),
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen')
  ) AS screen_name,
  COALESCE(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'screen_class'),
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen_class')
  ) AS screen_class
FROM `project.analytics_000000000.events_20220608`
WHERE event_name = 'screen_view' AND platform = 'ANDROID'

如果你查询的是2022年的历史数据，需要确认当时使用的Firebase SDK版本：

• Android SDK版本低于20.0.0、iOS SDK版本低于8.0.0时，手动上报screen_view事件的screen_name参数会被写入firebase_screen键下，升级后的新版本SDK才会将手动上报的参数写入不带前缀的screen_name键，因此使用上面的COALESCE写法兼容两种版本上报的数据，可以避免漏数。

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