Skip to content
TheChatArchiveThe Chat Archive
o4-mini-2025-04-16· OpenAIHugging Face LMArena Human Preference 140K

In BigQuery, using GA4, how would I write a query to get all the item events and…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen3-235b-a22b-no-thinking’s answer.
  1. User

    In BigQuery, using GA4, how would I write a query to get all the item events and parameters

  2. Assistant

    Here’s a “recipe” you can drop into BigQuery to flatten out all GA4 item‐related events plus their parameters. It:

    1. Reads from your events_YYYYMMDD tables
    2. UNNESTs both event_params and event_items
    3. Coalesces string/int/float into a single param_value or item_value
    4. (Optionally) filters to only e-com events like view_item, add_to_cart, …
    -- ───────────────────────────────────────────────────
    -- 1) Replace project.dataset with your own
    -- 2) Adjust event_filter if you only want certain item events
    -- ───────────────────────────────────────────────────
    WITH
      raw AS (
        SELECT
          event_date,
          event_timestamp,
          user_pseudo_id,
          event_name,
          -- Flatten every event_param
          ep.key   AS param_key,
          -- Coalesce all possible types into ONE column
          COALESCE(
            ep.value.string_value,
            CAST(ep.value.int_value   AS STRING),
            CAST(ep.value.float_value AS STRING)
          )        AS param_value,
          -- Flatten every item record, if any
          itm.key  AS item_key,
          COALESCE(
            itm.value.string_value,
            CAST(itm.value.int_value   AS STRING),
            CAST(itm.value.float_value AS STRING)
          )        AS item_value
        FROM
          `your-project.your_dataset.events_*`
        CROSS JOIN
          UNNEST(event_params) AS ep
        LEFT JOIN
          UNNEST(event_items)  AS itm
        WHERE
          _TABLE_SUFFIX BETWEEN '20230101' AND '20231231'   -- restrict date range
          -- optional: only pick ecommerce/item events
          AND event_name IN (
            'view_item', 'view_item_list', 'select_item',
            'add_to_cart', 'remove_from_cart',
            'begin_checkout', 'purchase'
          )
      )
    
    SELECT
      event_date,
      event_timestamp,
      user_pseudo_id,
      event_name,
      param_key,
      param_value,
      item_key,
      item_value
    FROM
      raw
    ORDER BY
      event_timestamp
    LIMIT 1000;
    

    Explanation:

    • UNNEST(event_params) gives you every key/value pair attached to the event.
    • LEFT JOIN UNNEST(event_items) does the same for the e-commerce “item” array.
    • We use COALESCE(...) + CAST(...) so we can hoist strings, ints, floats into one column.
    • Tweak the WHERE event_name IN (…) clause to match all item-y events you care about (or drop it if you want literally every event).

    — You can also pivot these rows back into columns by using MAX(IF(param_key='foo', param_value,null)) over a GROUP BY if you prefer one row per event.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.