Analytics -> BigQuery -> Tableau | Date Returning Null

Question

This feels like more of a Tableau issue than BigQuery or Analytics but figured I'd put it to you all.

I've created some views in BigQuery from Google Analytics for use as a live connection in Tableau but I can't for the life of me get the date field to format as a date. Even if I cast it as a date in BQ it's interpreted in Tableau as a string and if I try and change that it returns all null values.

The default export in BQ is 'yyyymmdd' I can cast this as a date in the format 'yyyy-mm-dd' but still no luck.

I've tried a number of different methods including running the custom SQL query from Tableau which someone in their forums recommended but nothing has worked.

Thanks for any help you can offer.

Cheers,

Example:

#legacySQL
SELECT
  CAST(date AS date) AS date,
  hits.hour AS hour,
  hits.eventInfo.eventAction eventAction,
  COUNT(1) eventHits
FROM (TABLE_DATE_RANGE([big-query-project-34643.162968675.ga_sessions_],
      DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
      DATE_ADD(CURRENT_TIMESTAMP(), -1, 'HOUR'))),
  (TABLE_DATE_RANGE([big-query-project-34643.162968675.ga_realtime_sessions_],
      DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),
      CURRENT_TIMESTAMP()))
WHERE
  hits.eventInfo.eventCategory LIKE 'account - manage autoship'
  AND hits.type = 'EVENT'
GROUP BY
  date,
  hour,
  eventAction,
ORDER BY
  date,
  hour

I ran into this in the Tableau forums and got really excited but wasn't able to change my result. community.tableau.com/thread/219000 — Geoff

Geoff Geoff · Accepted Answer · 2018-09-21T13:04:22

Ended up figuring this out through brute force and thought I'd post the answer. Thanks to both of you for leaving comments. Turns out I needed to transform the date through the concat function then cast as date. ended up with this:

CAST(CONCAT(SUBSTR(date,1,4),'-',SUBSTR(date,5,2),'-',SUBSTR(date,7,2)) AS DATE) AS Date,

Analytics -> BigQuery -> Tableau | Date Returning Null

1 Answers