如何在BigQuery中关联GA4同ga_session_id的purchase与自定义事件参数？

直接用WITH子句+JOIN就能把两个事件的参数合并到同一行，不用再导出后手动关联。核心是利用两个事件共有的ga_session_id作为关联键，同时注意修正原查询里的拼写错误（比如campaing_name应该是campaign_name，variantion_name应该是variation_name）。

完整SQL代码 #

WITH purchase_data AS (
  SELECT
    event_date,
    traffic_source.medium,
    traffic_source.source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'form_id') AS form_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS session_id
  FROM `table1`
  WHERE event_name = 'purchase'
),
experiment_data AS (
  SELECT
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'campaign_id') AS experiment_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'campaign_name') AS experiment_name, -- 修正拼写错误
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'variation_name') AS variation_name -- 修正拼写错误
  FROM `table1`
  WHERE event_name = 'kameleoon_experiment'
)

-- 关联两个数据集，保留所有purchase事件，匹配对应的实验数据
SELECT
  p.event_date,
  p.medium,
  p.source,
  p.form_id,
  p.session_id,
  e.experiment_id,
  e.experiment_name,
  e.variation_name
FROM purchase_data p
LEFT JOIN experiment_data e
  ON p.session_id = e.session_id
-- 如果只保留同时有purchase和experiment的会话，把LEFT JOIN改成INNER JOIN

补充说明 #

• 如果同一个session_id对应多个kameleoon_experiment事件，可能会出现重复行。这种情况可以在experiment_data里用DISTINCT或者聚合函数去重，比如：

experiment_data AS (
  SELECT DISTINCT
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'campaign_id') AS experiment_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'campaign_name') AS experiment_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'variation_name') AS variation_name
  FROM `table1`
  WHERE event_name = 'kameleoon_experiment'
)

• 用LEFT JOIN可以保留所有产生了purchase的会话，即使该会话没有触发kameleoon_experiment事件；如果只需要保留同时触发两个事件的会话，换成INNER JOIN即可。

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