在BigQuery中如何通过CASE WHEN同时为两个变量赋值（GA4场景）

当然支持。你可以在SELECT语句中，针对utm_medium和utm_source分别编写CASE WHEN逻辑，针对无UTM标记的渠道，结合GA4会话的原生流量源字段就能实现需求。

以下是适配你场景的SQL示例：

WITH session_channel_data AS (
  SELECT
    user_pseudo_id,
    session_id,
    -- 提取UTM参数（你已实现的部分）
    REGEXP_EXTRACT(page_location, r'utm_medium=([^&]+)') AS extracted_utm_medium,
    REGEXP_EXTRACT(page_location, r'utm_source=([^&]+)') AS extracted_utm_source,
    -- 原生流量源字段
    traffic_source.medium AS native_medium,
    traffic_source.source AS native_source
  FROM
    `your-project-id.your-dataset-id.ga4_sessions_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)
SELECT
  user_pseudo_id,
  session_id,
  -- 优先用提取的UTM，无则根据原生渠道赋值
  CASE
    WHEN extracted_utm_medium IS NOT NULL THEN extracted_utm_medium
    WHEN native_medium = 'organic' THEN 'organic'
    WHEN native_medium = 'referral' THEN 'referral'
    WHEN native_medium = '(none)' THEN 'direct'
    ELSE native_medium
  END AS utm_medium,
  CASE
    WHEN extracted_utm_source IS NOT NULL THEN extracted_utm_source
    WHEN native_medium = 'organic' THEN native_source -- 自然搜索源（如google、bing）
    WHEN native_medium = 'referral' THEN native_source -- 引荐站点域名
    WHEN native_medium = '(none)' THEN '(direct)'
    ELSE native_source
  END AS utm_source
FROM
  session_channel_data

关键逻辑说明： #

• 优先使用从page_location提取的UTM参数，保证带标记的渠道优先生效
• 无UTM时，根据GA4原生traffic_source字段判断赋值：
  • 自然搜索：utm_medium设为organic，utm_source沿用原生搜索源（如google）
  • 引荐渠道：utm_medium设为referral，utm_source设为引荐站点域名
  • 直接访问：utm_medium设为direct，utm_source设为(direct)
• 若有其他特殊渠道类型，可直接在CASE分支中补充判断逻辑

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