如何用BigQuery生成含完整URL的GA4 banner_click着陆页报告？

核心需求拆解 #

你需要基于GA4的BigQuery事件数据，生成带完整URL的着陆页报告，同时统计每个着陆页对应的banner_click事件数量。原查询已经实现了获取完整着陆页URL、统计会话数和用户数，但缺少关联banner_click的逻辑。

修改后的查询语句 #

WITH landing_pages AS (
  -- 提取每个会话的着陆页（完整URL）
  SELECT
    CONCAT(user_pseudo_id, '.', (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 = 'page_location') AS landing_page
  FROM
    `big-query-323234.analytics_343345454.events_20230306`
  WHERE
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') = 1
),
banner_clicks AS (
  -- 统计每个会话内的banner_click事件数量
  SELECT
    CONCAT(user_pseudo_id, '.', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
    COUNT(*) AS banner_click_count
  FROM
    `big-query-323234.analytics_343345454.events_20230306`
  WHERE
    event_name = 'banner_click'
  GROUP BY
    session_id
)
-- 关联着陆页与banner点击数据，汇总最终指标
SELECT
  lp.landing_page,
  COUNT(DISTINCT lp.session_id) AS sessions,
  COUNT(DISTINCT SPLIT(lp.session_id, '.')[OFFSET(0)]) AS users,
  COALESCE(SUM(bc.banner_click_count), 0) AS banner_click_total
FROM
  landing_pages lp
LEFT JOIN
  banner_clicks bc ON lp.session_id = bc.session_id
GROUP BY
  lp.landing_page
ORDER BY
  sessions DESC;

关键逻辑说明 #

1. 会话ID的构建：用user_pseudo_id和ga_session_id拼接成唯一会话ID，确保能准确关联同一会话内的着陆页和banner_click事件。
2. 着陆页提取：保留原查询中page_location的逻辑，直接获取完整网站URL，替代GA4默认的路径格式。
3. banner点击统计：单独筛选event_name = 'banner_click'的事件，按会话分组统计点击次数，再通过左连接关联到着陆页数据，确保没有点击的着陆页也会显示（用COALESCE将NULL转为0）。
4. 指标补充：除了原有的会话数、用户数，新增banner_click_total字段统计每个着陆页对应的总banner点击量。

可选扩展 #

如果需要统计有banner点击行为的用户数，可以在SELECT语句中添加：

COUNT(DISTINCT CASE WHEN bc.banner_click_count > 0 THEN SPLIT(lp.session_id, '.')[OFFSET(0)] END) AS users_with_banner_click

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