BigQuery: question on adding time as a dimension

Question

I am trying to add 'time' or 'date' as a dimension to my Google Analytics query in Big Query...how would I add either daily or monthly 'date' to this query? I understand how to filter on dates in the WHERE statement, but I don't understand how I can add a 'DATE' field to the results displayed. see code attached.

several _TableSuffix options that error out

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

I want to add daily and (as a seperate query) monthly counts to the code.

Answer 1

For showing tally by date, you can simply GROUP BY column date. See below example using public GA sample data. (Also check BigQuery Datetime Functions for how to extract month from date)

#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

Results look like: