• Google Analytics 4

  Analyzing Brand Occurrences and Event Counts in GA4 with BigQuery

  Posted by Noah on 22 April 2023 at 12:34 pm

  Hey there,

  So let me make sure I got this right. You’re checking the number of times different brands got added to the cart in GA4, correct? And you’re using BigQuery to get this information for each day.

  You want your result to look something like this right?

  – 2022-05-01, add_to_cart, Nike, 42
  – 2022-05-01, add_to_cart, Adidas, 32
  – 2022-05-02, add_to_cart, Nike, 47
  – 2022-05-03, add_to_cart, Nike, 23

  You’re experimenting with a query, which seems like it hits a snag, and gives you an error – ‘Scalar subquery produced more than one element’.

  Your query is:

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

  Can anyone explain to me what I am doing wrong?

  Isaac replied 1 year, 3 months ago 3 Members · 2 Replies
• 2 Replies

• 

  Emma

  Member

  23 April 2023 at 10:53 pm

  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

  Member

  8 May 2023 at 2:49 pm

  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.

Log In to Reply