如何在BigQuery中编写带条件的STRING_AGG函数处理用户旅程数据？

我明白你的问题出在哪了——你写的CASE语句只处理了hit.contentGroup.contentGroup1 = '(not set)'的情况，当contentGroup是正常值（比如产品页、咨询页这些）时，整个CASE表达式返回的是NULL，而STRING_AGG函数会自动忽略NULL值，所以最终结果里只留下了匹配%join/complete%的条目，把用户旅程的其他环节都弄丢了。

修正后的查询 #

你需要调整CASE的逻辑，确保所有符合条件的hit都能返回一个非NULL的值给STRING_AGG，这样正常的内容组会被保留，特定的(not set)会被替换成你想要的名称：

SELECT 
  date, 
  fullVisitorId, 
  visitId, 
  STRING_AGG(
    CASE 
      -- 先处理需要替换的(not set)情况
      WHEN hit.contentGroup.contentGroup1 = '(not set)' AND hit.page.pagePath LIKE '%join/complete%' THEN 'join_complete'
      -- 其他情况（包括普通的(not set)和正常的contentGroup）直接返回原内容
      ELSE hit.contentGroup.contentGroup1 
    END, 
    '#' 
    ORDER BY hit.hitNumber
  ) AS user_journey
FROM `xxxxx-ga-datasets.xxxx.ga_sessions_*` AS t, 
UNNEST(hits) AS hit 
WHERE 
  _TABLE_SUFFIX BETWEEN '20190101' AND '20190101' 
  AND hit.type='PAGE' 
GROUP BY 1, 2, 3 
ORDER BY 1, 2

关键调整说明 #

1. 完整覆盖所有场景：新的CASE语句不再只处理(not set)的情况，而是通过ELSE返回所有其他场景的原始值，确保每个hit都能贡献一个值到聚合结果里。
2. 精准匹配替换规则：把(not set)和pagePath的条件合并到同一个WHEN子句里，逻辑更清晰，只替换符合路径规则的那部分(not set)。

扩展建议 #

如果之后你需要处理其他(not set)的场景（比如其他注册环节的页面），只需要继续添加WHEN条件即可，比如：

CASE 
  WHEN hit.contentGroup.contentGroup1 = '(not set)' AND hit.page.pagePath LIKE '%join/complete%' THEN 'join_complete'
  WHEN hit.contentGroup.contentGroup1 = '(not set)' AND hit.page.pagePath LIKE '%join/signup%' THEN 'join_signup'
  ELSE hit.contentGroup.contentGroup1 
END

这样就能完整保留用户的整个旅程路径，同时修正特定页面的分组缺失问题。

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