sqlgoogle-analyticsgoogle-bigquerycustom-dimensions

Accessing Google Analytics Custom Dimensions with BigQuery



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?


Solution

  • 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.