关于Google Analytics会话级字段在BigQuery中多值问题的问询

我之前处理GA数据的时候也碰到过这个坑，太懂这种分组后还是解决不了的头疼感了！你观察到的现象其实是GA会话跟踪机制里一个容易被忽略的细节导致的——用户在同一个会话（即同一fullVisitorId+visitId）期间，浏览器的user-agent字符串发生了变更。

为什么会出现这种情况？ #

• GA的会话级字段理论上应该是整个会话保持一致的，但如果用户在会话过程中触发了user-agent的变化（比如切换浏览器的桌面/移动视图、更新浏览器版本、安装了修改user-agent的插件，甚至是用同一账号在不同设备上延续了同一个会话），GA的后端会在会话记录里保留这些不同的设备相关信息，最终导出到BigQuery时，就会出现同一会话对应多个字段值的情况。
• 这也解释了总用户数和按设备拆分用户数的差异：同一个用户的同一会话被拆分到了多个设备类别下，导致按设备统计时用户被重复计算，总数自然就和总用户数对不上了。

怎么解决这个问题？ #

针对这种情况，你可以通过BigQuery的窗口函数来统一会话级的设备信息，这里给你两个常用的处理思路：

1. 取会话首次/最后一次记录的设备信息
   比如取会话中最后一次访问的设备类型作为整个会话的统一设备值，对应的SQL示例如下：

   WITH session_device_data AS (
     SELECT
       fullVisitorId,
       visitId,
       device.category AS device_type,
       -- 按点击次数倒序，取最后一次的设备信息
       ROW_NUMBER() OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.hitNumber DESC) AS row_rank
     FROM
       `你的项目ID.你的数据集ID.ga_sessions_*`
     CROSS JOIN UNNEST(hits) AS hits
   )
   SELECT
     fullVisitorId,
     visitId,
     device_type AS unified_session_device
   FROM session_device_data
   WHERE row_rank = 1
   

2. 取会话中出现频次最高的设备信息
   如果会话中设备类型有多次切换，你可以统计每个设备类型在会话中的出现次数，取占比最高的那个：

   WITH device_count AS (
     SELECT
       fullVisitorId,
       visitId,
       device.category AS device_type,
       COUNT(*) AS device_count,
       ROW_NUMBER() OVER (PARTITION BY fullVisitorId, visitId ORDER BY COUNT(*) DESC) AS row_rank
     FROM
       `你的项目ID.你的数据集ID.ga_sessions_*`
     CROSS JOIN UNNEST(hits) AS hits
     GROUP BY fullVisitorId, visitId, device.category
   )
   SELECT
     fullVisitorId,
     visitId,
     device_type AS unified_session_device
   FROM device_count
   WHERE row_rank = 1
   

另外，你也可以进一步排查user-agent变化的具体场景，比如过滤掉那些异常的user-agent变更记录，或者结合用户的设备历史数据来做更精准的归类。

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