如何将BigQuery中的扁平表转换为GA4嵌套表结构

直接修改你的查询，将event action和event label封装为键值对数组，匹配GA4原始会话表的结构：

with test as (
  select 123 cid, "sports" ec, "load" ea, "module" el union all
  select 123,"marquee","load","home" union all
  select 125,"bet_add","sports","football" union all
  select 126, "bet_place","games","tennis" 
)
select 
  cid,
  array_agg(
    struct(
      ec as event_category,
      [
        struct("event_action" as name, ea as value),
        struct("event_label" as name, el as value)
      ] as event_params
    )
  ) as events
from test 
group by 1

说明 #

• 每个事件对象包含event_category字段，对应原表的ec值
• event_params数组中，通过struct分别定义event_action和event_label的键值对，完全对齐GA4的参数结构
• 保持array_agg按cid聚合的逻辑，最终输出每个用户ID对应的事件数组

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