Unnesting the GA4 items Array Without Overcounting Revenue

At first sight the items array in the GA4 BigQuery export looks like a simple container for product data. In practice it is a frequent cause of overcounted revenue and misattributed product performance, often in models built with good intentions by experienced analysts.

The issue lies in how BigQuery expands nested data. Unnesting items produces one row per item, which is intended. The trouble starts when the same query also unnests event_params, or another array, in the same FROM clause: the two unnests form a cross join. A purchase event with three items and two event parameters yields six rows. Each item appears twice, so summed item_revenue doubles, and any event-level metric such as ecommerce.purchase_revenue is counted six times.

Reviews of dbt projects regularly find a direct UNNEST of items followed by a SUM of item_revenue, with no filter on event_name and other arrays joined alongside. Without the filter, items attached to view_item or add_to_cart events are counted together with purchased items, inflating product-level counts. The result is inflated product revenue reports, skewed attribution models and poor marketing decisions, and the errors are slow to trace.

The fix starts with defining the grain. For product-level analysis that grain is one row per item per event, identified by user_pseudo_id, event_timestamp, event_name and the item's position in the array from UNNEST(items) WITH OFFSET. Filter to the relevant event types before unnesting, and extract any event parameters with scalar subqueries rather than a second UNNEST in the FROM clause. ROW_NUMBER() can then remove any remaining duplicates, and joins and aggregations should be designed against that grain. dbt makes this modular: an intermediate model at item grain becomes the single input for all product metrics.

Data quality also starts at the source. Careful BigQuery transformations remain necessary, but control over what is sent helps too. A server-side collection layer such as Robin Signals gives more control over the data and its structure, reducing the noise introduced by client-side collection and the remediation needed later.