In BigQuery, using GA4, how would I write a query to get all the item events and…
- User
In BigQuery, using GA4, how would I write a query to get all the item events and parameters
- Assistant
Here’s a “recipe” you can drop into BigQuery to flatten out all GA4 item‐related events plus their parameters. It:
- Reads from your
events_YYYYMMDDtables - UNNESTs both
event_paramsandevent_items - Coalesces string/int/float into a single
param_valueoritem_value - (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 useCOALESCE(...)+CAST(...)so we can hoist strings, ints, floats into one column.
• Tweak theWHERE 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 aGROUP BYif you prefer one row per event. - Reads from your
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.