如何在BigQuery中同时保留着陆页维度与购物行为漏斗统计？

你遇到的问题本质是会话级维度提取与hits层行为统计的冲突——展开hits后直接GROUP BY会因为同一个会话有多条hit记录导致维度重复，而窗口函数不能直接用于GROUP BY子句。下面是两种可行的解决思路：

思路1：CTE预计算会话级数据 #

先通过CTE（公共表表达式）在会话维度上完成所有必要的维度提取和行为标记，再进行最终聚合：

WITH session_metrics AS (
  SELECT
    fullVisitorId,
    visitId,
    -- 取会话的第一个hit的hostname和着陆页（UA中着陆页就是第一个页面hit）
    FIRST_VALUE(h.page.hostname) OVER (PARTITION BY fullVisitorId, visitId ORDER BY h.hitNumber) AS hostname,
    FIRST_VALUE(h.page.pagePath) OVER (PARTITION BY fullVisitorId, visitId ORDER BY h.hitNumber) AS landing_page,
    -- 标记该会话是否有商品浏览（UA中action_type=2对应商品详情页浏览）
    MAX(IF(h.eCommerceAction.action_type = '2', 1, 0)) OVER (PARTITION BY fullVisitorId, visitId) AS has_product_view
  FROM
    `your-project.your-dataset.ga_sessions_*`,
    UNNEST(hits) AS h
  WHERE
    _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)
-- 去重后按维度聚合统计
SELECT
  hostname,
  landing_page,
  COUNT(DISTINCT CONCAT(fullVisitorId, '-', visitId)) AS total_sessions,
  SUM(has_product_view) AS sessions_with_product_views
FROM session_metrics
GROUP BY hostname, landing_page

思路2：双层聚合绕过冲突 #

先按会话维度聚合，用ANY_VALUE()保留会话级的hostname和着陆页（每个会话的这两个值是唯一的，ANY_VALUE会正确返回），同时统计行为标记，再按维度二次聚合：

SELECT
  hostname,
  landing_page,
  COUNT(*) AS total_sessions,
  SUM(has_product_view) AS sessions_with_product_views
FROM (
  SELECT
    fullVisitorId,
    visitId,
    ANY_VALUE(h.page.hostname) AS hostname,
    ANY_VALUE(h.page.pagePath) AS landing_page,
    -- 会话内只要有一个商品浏览hit，就标记为1
    MAX(IF(h.eCommerceAction.action_type = '2', 1, 0)) AS has_product_view
  FROM
    `your-project.your-dataset.ga_sessions_*`,
    UNNEST(hits) AS h
  WHERE
    _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY fullVisitorId, visitId
)
GROUP BY hostname, landing_page

关键细节 #

• 之前FIRST_VALUE无法使用，是因为你试图直接在GROUP BY子句中调用窗口函数——窗口函数的计算逻辑是基于分组后的窗口，不能直接作为GROUP BY的字段，必须先在子查询/CTE中计算出结果再聚合。
• ANY_VALUE()是BigQuery中针对会话级唯一维度的高效用法，它会忽略重复值，直接返回该会话对应的hostname和着陆页，无需额外排序。
• UA的购物行为action_type对应关系：2=商品详情页浏览，3=添加购物车，6=发起结账，7=完成购买，你可以根据需要替换成其他漏斗阶段的统计。

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