Extract month from GA date in BigQuery

Question

I am trying to translate a Legacy SQL query into Standard SQL. My issue is replacing MONTH() with a suitable function in Standard. I read the docs and EXTRACT() seems to be the solution, but I think the date field in BigQuery being a string is stopping this from working correctly.

Here is my Legacy code:

SELECT month(date) AS Mon, hits.eventInfo.eventAction, ROUND(AVG(hits.product.productPrice/1000000),2) AS avg_price
FROM (TABLE_DATE_RANGE([xxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
WHERE hits.eventInfo.eventCategory = "Ecommerce"
AND hits.eventInfo.eventAction = "Add to Basket PDP"
OR hits.eventInfo.eventAction = "Add to Basket PLP"
GROUP BY Mon_of_year, hits.eventInfo.eventAction

Here is my Standard code where I have tried to resolve this:

SELECT extract(YEAR from PARSE_DATE('%y%m%d',date)) AS Mon, hits.eventInfo.eventAction, ROUND(AVG(hits_product.productPrice/1000000),2) AS avg_price
FROM 
`xxx.ga_sessions_20*` AS t
CROSS JOIN UNNEST(hits) AS hits
CROSS JOIN UNNEST(hits.product) AS hits_product
WHERE
  parse_DATE('%y%m%d',
    _table_suffix) BETWEEN DATE_sub(CURRENT_DATE(),
    interval 7 day)
  AND DATE_sub(CURRENT_DATE(),
    interval 1 day)
AND hits.eventInfo.eventCategory = "Ecommerce"
AND hits.eventInfo.eventAction = "Add to Basket PDP"
OR hits.eventInfo.eventAction = "Add to Basket PLP"
GROUP BY mon, hits.eventInfo.eventAction

But this is giving me the error: Error: Failed to parse input string "20170304"

Any suggestions on how to get this working?

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-01-04T13:54:27

It looks like you were close, but you need to use a capital Y instead of lowercase y for the year part, i.e.:

PARSE_DATE('%Y%m%d', date)

If you want to get the month number, you can use EXTRACT(MONTH FROM ...) or if you want simply to truncate the date to a month boundary, you can use DATE_TRUNC(..., MONTH).

Extract month from GA date in BigQuery

1 Answers