One purchase, six rows: CROSS JOIN UNNEST(event_params) before counting in the GA4 export

In the GA4 export every event is one row, and its parameters aren’t columns: they’re an array called event_params, with one record per parameter (key plus a value with string_value, int_value and double_value). To use a parameter you have to open that array with UNNEST. How you open it decides whether your counts are right.

What a multiplied count looks like

A query to count purchases and add up revenue for a day, written with CROSS JOIN UNNEST(event_params) to get at value:

• COUNT(*) gives several times the number of purchases in the GA4 report: in the example, six times.
• Revenue added up from another column of the event (or from a parameter read in a second join) is multiplied by the same factor.
• The factor isn’t fixed: it depends on how many parameters each event carries, so it changes from one event to the next and from one day to the next.
• Events with an empty event_params disappear from the result.

Why UNNEST multiplies rows

It’s standard SQL behaviour, not a quirk of GA4. CROSS JOIN UNNEST(array) returns one row for each element of the array, repeating the rest of the event’s columns on each one. A purchase with six parameters (page_location, ga_session_id, value, currency, transaction_id, engagement_time_msec) becomes six rows.

From there, everything you calculate on the joined result counts parameter rows, not events:

• COUNT(*) after the join counts parameters.
• SUM of event columns (for example ecommerce.purchase_revenue) adds the same value once per parameter.
• A second UNNEST to read another parameter multiplies again: rows × rows.
• A WHERE key = 'value' after the join brings it back to one row per event, but only for that key; as soon as you need two parameters, the problem comes back.

And as it is a CROSS JOIN, an event whose array is empty has no row to join with and is lost.

How to find a multiplied count

1. Search your queries and views for CROSS JOIN UNNEST(event_params) or the comma form, FROM events_*, UNNEST(event_params).
2. Compare COUNT(*) with the number of distinct events in the same query. If they differ, rows are being multiplied.
3. Compare the number of purchase events and the revenue with the GA4 report for the same closed day. A gap of several times over, rather than a few percent, points to this.

How to read parameters without duplicating events

The pattern in Google’s GA4 query cookbook is a scalar subquery per parameter: it reads one value from the array and leaves one row per event.

SELECT
  COUNT(*) AS purchases,
  SUM((SELECT COALESCE(value.double_value, value.int_value)
       FROM UNNEST(event_params) WHERE key = 'value')) AS revenue,
  (SELECT value.string_value
   FROM UNNEST(event_params) WHERE key = 'currency') AS currency
FROM `analytics_123.events_20261028`
WHERE event_name = 'purchase'
GROUP BY currency

• One subquery per parameter you need: there’s nothing to multiply because the event row is never repeated.
• Read the right field for the type: a number may arrive in int_value or double_value depending on how it was sent, so COALESCE both if you’re not sure.
• If you really need one row per parameter (to list which ones an event carries, for example), use LEFT JOIN UNNEST(event_params) so as not to lose events, and don’t count events on that result.

Documentation: BigQuery Export schema and GA4 BigQuery basic queries.

How to check each event counts once

• COUNT(*) equals the number of distinct events in the query.
• The number of purchases and the revenue for a closed day are close to the GA4 report (some gap is normal; the next posts explain why).
• Adding another parameter to the query doesn’t change the count.

The parameters, the IDs and the figures in the example are illustrative.