BigQuery执行GA页面停留时长统计SQL报Unrecognized name: clientId错误排查

报错核心原因 #

• BigQuery原生导出的GA会话表（ga_sessions_*）的表结构中不存在名为clientId的顶层字段。你认知中对应客户端标识的真实内置字段是fullVisitorId，GA导出表从设计之初就没有使用clientId作为字段名，直接在查询中引用该名称自然会触发“无法识别字段”的报错。
• 即便把字段名替换为正确的fullVisitorId，原有查询的内层子查询的SELECT输出列表中，完全没有包含任何访客/会话ID类的字段，外层查询根本无法从子查询结果中读取到相关数据，依然会触发同类报错。
• 原有查询的聚合逻辑和统计目标错配：你写的GROUP BY 1是按查询第一列（也就是你想引用的访客ID字段）分组，和“统计站点各页面整体停留时长”的目标不匹配——按单用户ID分组根本无法汇总出页面维度的全局统计值。

附带需要修正的逻辑偏差 #

原有停留时长的计算公式存在数值错误：查询直接对nextTime、last_interaction的原始时间戳求和，没有减去当前页面自身的加载时间戳hits_time，计算出的结果会是时间轴的累计值，远大于真实的页面停留时长。正确的停留时长计算逻辑为：

• 非退出页的停留时长 = 同会话下下一个页面hit的时间戳 - 当前页面hit的时间戳
• 退出页的停留时长 = 同会话内最后一次有效交互的时间戳 - 当前页面hit的时间戳

修正后可正常运行的查询 #

SELECT
  pagePath,
  SUM(
    CASE
      WHEN isExit IS NOT NULL THEN last_interaction - hits_time
      ELSE nextTime - hits_time
    END
  ) AS total_time_on_page_seconds
FROM (
  SELECT
    fullVisitorId,
    visitid,
    hits.page.pagePath,
    hits.isExit,
    hits.time/1000 AS hits_time,
    LEAD(hits.time/1000, 1) OVER (PARTITION BY fullVisitorId, visitid ORDER BY hits.time ASC) AS nextTime,
    MAX(IF(hits.isInteraction = TRUE, hits.time / 1000, 0)) OVER (PARTITION BY fullVisitorId, visitid) AS last_interaction
  FROM
    `merck-bigquery.1===.ga_sessions_20201231`,
    UNNEST(hits) AS hits
  WHERE
    hits.type = "PAGE"
    AND hits.page.hostname = 'www.msdmed.ru'
)
GROUP BY
  pagePath
ORDER BY
  pagePath ASC

关键修正点说明 #

• 移除了不存在的clientId字段引用，如果确实需要按单访客维度拆分停留时长，只需要把fullVisitorId加到外层SELECT列表和GROUP BY条件中即可
• 将内层查询原本仅在窗口函数分区中使用、但未输出的fullVisitorId、visitid字段加入返回列表，保证窗口逻辑正常运行的同时，外层查询可以正常读取关联字段
• 给原有时长计算逻辑补充了当前页面的时间戳差值，保证计算出的结果是真实的页面停留秒数
• 把原有的按列序号分组的逻辑，替换为明确按pagePath分组，匹配站点全局页面停留时长的统计需求

内容的提问来源于stack exchange，提问作者Olga K.