google-analytics - Big Query 着陆页数据与 Google Analytics(分析)界面不一致

Question

我正在使用 BigQuery 来报告 Google Analytics(分析)数据。我正在尝试使用 BigQuery 重新创建着陆页数据。

以下查询报告的 session 数比 Google Analytics(分析)界面中的 session 数少 18%:

SELECT DISTINCT
  fullVisitorId,
  visitID,
  h.page.pagePath AS LandingPage
FROM
  `project-name.dataset.ga_sessions_*`, UNNEST(hits) AS h
WHERE 
  hitNumber = 1
AND h.type = 'PAGE'
AND _TABLE_SUFFIX BETWEEN '20170331' AND '20170331'
ORDER BY fullVisitorId DESC

我的方法哪里出了问题？为什么我无法获得与 GA 接口(interface)报告的数字相差很小的范围？

Answer 1

多种原因:

1.等效着陆页的大查询:

SELECT
  LandingPage,
  COUNT(sessionId) AS Sessions,
  100 * SUM(totals.bounces)/COUNT(sessionId) AS BounceRate,
  AVG(totals.pageviews) AS AvgPageviews,
  SUM(totals.timeOnSite)/COUNT(sessionId) AS AvgTimeOnSite,
from(
  SELECT
    CONCAT(fullVisitorId,STRING(visitId)) AS sessionID,
    totals.bounces,
    totals.pageviews,
    totals.timeOnSite,
    hits.page.pagePath AS landingPage
  FROM (
    SELECT
      fullVisitorId,
      visitId,
      hits.page.pagePath,
      totals.bounces,
      totals.pageviews,
      totals.timeOnSite,
      MIN(hits.hitNumber) WITHIN RECORD AS firstHit,
      hits.hitNumber AS hitNumber
    FROM (TABLE_DATE_RANGE ([XXXYYYZZZ.ga_sessions_],TIMESTAMP('2016-08-01'), TIMESTAMP ('2016-08-31')))
    WHERE
      hits.type = 'PAGE'
      AND hits.page.pagePath'')
  WHERE
    hitNumber = firstHit)
GROUP BY
  LandingPage
ORDER BY
  Sessions DESC,
  LandingPage

---

下一个:

预先计算的数据——预先聚合的表格

这些是 Google 用于加速用户界面的预先计算的数据。谷歌没有具体说明何时完成，但可以在任何时间完成。这些被称为预聚合表

---

因此，如果您将 GA UI 中的数字与 Big Query 输出进行比较，您总会发现差异。请继续依赖您的大查询数据。