Accessing Google Analytics Custom Dimensions with BigQuery

Question

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?

Answer 1

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.

Answer 2

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