google-analytics - 大查询 : question on adding time as a dimension

Question

我正在尝试在 Big Query 中将“时间”或“日期”作为维度添加到我的 Google Analytics(分析)查询中...如何将每日或每月的“日期”添加到此查询中？我了解如何在 WHERE 语句中过滤日期，但我不明白如何在显示的结果中添加“日期”字段。见附上的代码。

错误输出的几个 _TableSuffix 选项

SELECT
  hits.page.pagePath, 
  COUNT(*) AS pageviews
FROM
  `xyz.33759344.ga_sessions_*` AS GA,
  UNNEST(GA.hits) AS hits
WHERE
  hits.type = 'PAGE'
  AND
_TABLE_SUFFIX BETWEEN '20190201' AND '20190331'
AND
hits.page.pagePath LIKE '%step/checkout'
GROUP BY
  hits.page.pagePath
ORDER BY
  pageviews DESC

我想在代码中添加每日和(作为单独的查询)每月计数。

Answer 1

要按日期显示计数，您可以简单地按列日期分组。请参阅下面使用公共(public) GA 样本数据的示例。
(另请查看 BigQuery Datetime Functions 了解如何从日期中提取月份)

#standardSQL
SELECT  hits.page.pagePath, 
  COUNT(*) AS pageviews, date
FROM
   `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS GA,
  UNNEST(GA.hits) AS hits
WHERE
  hits.type = 'PAGE'
  AND
_TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
AND
hits.page.pagePath LIKE '%/google+redesign/electronics'
GROUP BY
  hits.page.pagePath, date
ORDER BY
  pageviews DESC

结果如下所示: