This Week's BigQuery Insight: UNNEST Creates More Rows

Have you ever wondered why some BigQuery queries against GA4 data are unexpectedly slow?

The answer is often a single word: UNNEST.

GA4 stores parameters in a nested structure. Every time you use UNNEST(event_params), BigQuery creates additional rows behind the scenes.

Imagine a single event has these parameters:

• page_location

• page_title

• page_referrer

• session_id

• source

That one event is now treated as five rows after UNNEST.

If your table contains 10 million events with an average of 15 parameters each, you've just turned 10 million rows into 150 million rows.

That's why query performance can suddenly degrade.

Instead of unnesting everything:

SELECT *
FROM `project.analytics_123456.events_*`,
UNNEST(event_params)

Ask yourself: "Do I really need every parameter, or just one?"

If you only need page_location, consider extracting just that value:

SELECT
  (
    SELECT value.string_value
    FROM UNNEST(event_params)
    WHERE key = 'page_location'
  ) AS page_location
FROM `project.analytics_123456.events_*`

The lesson this week: Every UNNEST has a cost.

Understanding how nested data expands in BigQuery will make you a faster, more efficient analyst, especially when working with GA4 exports.

Remember: The biggest BigQuery optimizations often come from processing fewer rows, not writing more complex SQL.

Thank you,

Anil Batra, Optizent Academy

P.S. Want to become more confident with BigQuery? Enroll in my BigQuery for Marketers and Marketing Analysts course.

P.P.S. Using GA4? Make sure you're analyzing good data. Run a quick audit with GA Auditor to identify common implementation and tracking issues.