I got a question about extracting custom dimension in the google big query. This question is already asked by some people already, however, the solution is not working..
The question is, when I tried to extract the information for the custom dimension like this
SELECT
fullvisitorId,
visitid,
hit.hitnumber,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 1) as productCategory,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 2) as loyaltyClass,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 3) as existingCustomer
FROM [<id>.ga_sessions_20180805],UNNEST(hits) as hit
LIMIT 100
Then I got an error "Table name "hits" cannot be resolved: dataset name is missing."
I tried to use the solutions from others like this
SELECT
fullvisitorId,
visitid,
hit.hitnumber,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 1) as productCategory,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 2) as loyaltyClass,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 3) as existingCustomer
FROM `<id>.ga_sessions_*`, UNNEST(hits) AS h
WHERE _TABLE_SUFFIX = '20180805'
Then I got another error Invalid table name: <id>.ga_sessions_*
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)].
Update: I even tried the most basic query
SELECT
*
FROM [<id>.ga_sessions_20180805]
LEFT JOIN UNNEST(hits) as hits
LIMIT 10
Still returns the same error....
What is the error that I made for both script? And how can I get the custom dimension value?
Many thanks!
you can use case when which is supported by all
SELECT
fullvisitorId,
visitid,
h.hitnumber,
case when x.index = 1 then x.value end as productCategory,
case when x.index = 2 then x.value end as loyaltyClass,
case when x.index = 3 then x.value end as existingCustomer
FROM [<id>.ga_sessions_20180805]
LEFT JOIN UNNEST( hits ) as h
WHERE _TABLE_SUFFIX = '20180805'
Note: enable standard SQL for the query, or use the new BigQuery UI