Group by month Google Analytics / BigQuery

Question

I am pretty new to BigQuery and have a question about grouping the Date using Google Analytics data (StandardSQL). The data is currently on daily level, how can I aggregate this to Year/Month level?

Desired outcome: Data on year/month level + selection of only the last 12 months.

#StandardSQL
SELECT
TIMESTAMP(PARSE_DATE('%Y%m%d',date)) as Date,
SUM(totals.visits) AS Visits,
totals.timeOnSite AS TimeOnSite,
totals.newVisits AS NewVisit
FROM
`XXXX.ga_sessions_20*`
WHERE
_TABLE_SUFFIX >= '180215'
GROUP by
Date,
TimeOnSite,
NewVisit

Thanks in advance!

Solution 1

You can use DATE_TRUNC function (https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#date_trunc) for that:

#StandardSQL
SELECT
DATE_TRUNC(PARSE_DATE('%Y%m%d',date), MONTH) as MonthStart,
SUM(totals.visits) AS Visits,
totals.timeOnSite AS TimeOnSite,
totals.newVisits AS NewVisit
FROM
`XXXX.ga_sessions_20*`
WHERE
_TABLE_SUFFIX >= '180215'
GROUP by
Date,
TimeOnSite,
NewVisit

Solution 2

As you limit the data selection to the previous year and if you have a field in your database that registers the date of the visit, you can get your aggregated results per month using this query:

#StandardSQL SELECT EXTRACT(MONTH FROM 'date_field_of_the_visit') AS Month, sum(totals.visits) AS Visits FROM 'XXXX.ga_sessions_20*' WHERE _TABLE_SUFFIX >= '170312' Group by Month