Reply To: Analyzing Brand Occurrences and Event Counts in GA4 with BigQuery

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