Analyzing Brand Occurrences and Event Counts in GA4 with BigQuery

[Emma]

I see what’s going on here. The subquery is causing an issue because it’s possible that unnesting the array ‘items’ might get more than one item and trying to transform these into a scalar (a single value) is causing the issue. You’re asking it to get an ‘item_brand’ but it gets multiple possible hits for it which confuses it.

By inserting LIMIT 1, you’re essentially telling your query to just pick one brand out of possibly multiple ones it finds, thereby avoiding the ‘more than one element’ problem.

So your edited query tells BigQuery to “get the event_date, event_name, and one (any) brand for each cart added event”. This should work for you, as it limits the item_brand output to only one per event. Hope this helps!

[Isaac]

Your query is running into an issue because of the subquery: (SELECT item_brand FROM unnest(items)) AS brand. This subquery is expected to return a single value, however, it appears that the unnest(items) operation is returning multiple values which cause it to throw an error. The unnest() function is used to flatten array data, and in the context of your query, it seems items might be yielding more than one item_brand per event.

You’ll have to tweak your query in a way that can handle multiple item_brand per single event recorded. This will depend on how you wanted to treat situations where multiple brands appear in ‘items’ of a single event. You could aggregate values (like collecting all brands into an array or string), or unnest and duplicate the parent row for each brand (using CROSS JOIN), or making some logic to choose a single brand from each event.

For instance, if you prefer to register each brand separately for the same event, you might try to use CROSS JOIN instead of the subquery. This could look something like the following:

`
SELECT
event_date,
event_name,
item.item_brand as brand
FROM bigquery-xxxxxx.analytics_xxxxxxxxx.events_2022-5*, UNNEST(items) as item
WHERE event_name = ‘add_to_cart’
`

This will create a new row for each brand in each add_to_cart event. As a consequence, you’ll have multiple entries for each event, one for each brand.