CODEWITHSUNDEEP
google-bigquery
Ichta
Ichta

Reputation: 308

Selecting the value of a hit level custom metric from google analytics export in big query

We have created a hit level custom metric in google analytics that I want to retrieve in BigQuery. When running the following query: 

#StandardSQL
SELECT h.page.pagePath, SUM(h.customMetrics.value)
FROM `141884522.ga_sessions_20181024`, UNNEST(hits) as h
GROUP BY h.page.pagePath

I get this error:

Error: Cannot access field value on a value with type ARRAY<STRUCT<index 
INT64, value INT64>> at [2:45]

I can select just h.customMetrics (without grouping) which returns h.customMetrics.value and h.customMetrics.index but I cannot select the value or index specifically.

Anyone knows how to do that?

Upvotes: 0

Views: 578

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

#standardSQL
SELECT h.page.pagePath, SUM(metric.value)
FROM `141884522.ga_sessions_20181024`, UNNEST(hits) AS h, UNNEST(h.customMetrics) metric
GROUP BY h.page.pagePath

Btw, if you want to see all pagePath's even those with missing metrics (in cse if it is a case with your data) - i would recommend replacing CROSS JOIN with LEFT JOIN as in below example 

#standardSQL
SELECT h.page.pagePath, SUM(metric.value)
FROM `141884522.ga_sessions_20181024`, UNNEST(hits) AS h  
LEFT JOIN UNNEST(h.customMetrics) metric
GROUP BY h.page.pagePath

Upvotes: 2

Related Questions