Accessing Google Analytics Custom Dimensions with BigQuery

user_8400165 0
Viewed 3357

i want to get the following schema out of my GA BigQuery data: Hostname; customDimension2; customDimensions3; PageViews; ScreenViews; TotalEvents; Sessions

At first i just want to get the Hostname and cd2 my query look like the following:

SELECT hits.page.hostname, hits.customDimensions.value  
FROM `dataset`, UNNEST(hits) as hits  
WHERE hits.customDimensions.index = 2 
LIMIT 1000

I get the following Error:
Cannot access field index on a value with type ARRAY<STRUCT<index INT64, value STRING>> at [1:162]

So how can i handle two different BigQuery Arrays?

custom-dimensions google-bigquery google-analytics sql
2 Answers

Since you can have up to 200 fields in that array and you usually only want one of them it is better to not cross join with it but write a little subquery.

SELECT 
  page.hostname, 
  (SELECT value FROM UNNEST(h.customDimensions) WHERE index=2) AS cd2 
FROM `dataset`, 
  UNNEST(hits) as h  
LIMIT 1000

The more data you have the faster this query will perform in comparison to the cross join version. Subquery is always faster than cross join.

user_65328220

Below is for BigQuery Standard SQL

#standardSQL
SELECT hit.page.hostname, customDimension.value  
FROM `dataset`, UNNEST(hits) AS hit, UNNEST(hit.customDimensions) AS customDimension 
WHERE customDimension.index = 2 
LIMIT 100
user_52219440
Related