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

Solution 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, 
  (SELECTvalueFROMUNNEST(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.

Solution 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