Google Analytics BigQuery Export Event Count Issue

Question

I'm trying to get a total events count for a particular event in BigQuery along with a custom dimension for versions of the site.

This query works perfect but does not include my custom dimension:

SELECT
hits.eventInfo.eventCategory AS eventCategory,
COUNT(*) AS total_events
FROM `ga_sessions_*`,
UNNEST(hits) AS hits
WHERE _TABLE_SUFFIX = '20200630'
AND totals.visits = 1
AND hits.type = 'EVENT' 
AND hits.eventInfo.eventCategory = 'Cart CTA' 
GROUP BY 
hits.eventInfo.eventCategory

But when I add the UNNEST for customDimensions, I get a total that is twice the correct total.

SELECT
hits.eventInfo.eventCategory AS eventCategory,
COUNT(*) AS total_events
FROM `ga_sessions_*`,
UNNEST(hits) AS hits,
UNNEST(hits.customDimensions) AS cd
WHERE _TABLE_SUFFIX = '20200630'
AND totals.visits = 1
AND hits.type = 'EVENT' 
AND hits.eventInfo.eventCategory = 'Cart CTA' 
GROUP BY 
hits.eventInfo.eventCategory

I think there is something wrong with customDimension unnest, but I don't know how to solve. I've tried using LEFT JOIN with the UNNEST but I get the same result.

Answer 1

I think there is something wrong with customDimension unnest

This is by design! When you do UNNEST for the row that has N records in that unnested column - you actually generate N rows in place of that one row. So, obviously COUNT(*) will be different ...

I don't know how to solve

... unless you filter by specific value of that unnested field