google-analytics - 需要BigQuery SQL查询以从Google Analytics(分析)数据中收集页面停留时间

Question

谁能帮助BIgQuery SQL查询从Google Analytics(分析)数据中提取特定页面的页面停留时间？

对于访问过特定页面的每个visitorId，我希望该页面在页面上停留的时间。这样一来，我可以计算页面上的中位数时间，而不是平均值。

我假设将需要visitorId，hits.hitsNumber和hits.time维度。同样，需要以某种方式从下一个匹配的hits.time中减去查看页面的匹配的hits.time。

任何帮助，不胜感激。

Answer 1

试试这个:

SELECT 
  fullVisitorId,
  hits.page.hostname,
  hits.page.pagePath,
  hits.hitNumber,
  hits.time,
  nextTime,
  (nextTime - hits.time) as timeOnPage
FROM(
  SELECT
    fullVisitorId, 
    hits.page.hostname,
    hits.page.pagePath,
    hits.hitNumber,
    hits.time,
    LEAD(hits.time, 1) OVER (PARTITION BY fullVisitorId, visitNumber ORDER BY hits.time ASC) as nextTime
  FROM [PROJECTID:DATASETID.ga_sessions_YYYYMMDD]
  WHERE hits.type = "PAGE"
)

该代码的关键是 LEAD()函数，该函数根据 PARTITION BY和 ORDER BY限定符从分区的下一行中获取指定的值。

希望对您有所帮助!