You exported GA4 data to BigQuery expecting to run simple queries. Instead, you got UNNEST, COALESCE, and nested RECORD types. The GA4 BigQuery schema stores event parameters as arrays with four separate value columns for different data types—and extracting a single parameter like page_location requires a subquery that most store owners never learned to write.

This isn’t a learning curve. It’s an architectural barrier that pushes WordPress and WooCommerce businesses away from their own data.

Why GA4 BigQuery Data Is Structured This Way#

Google designed the GA4 BigQuery export for storage efficiency, not query simplicity. Instead of creating a flat table with one column per event parameter, GA4 stores all parameters in a single nested field called event_params.

The event_params field uses BigQuery’s RECORD data type—essentially a table within a table. Each row in your events table contains multiple rows of parameter data, with each parameter stored as a key-value pair.

Here’s what makes it complex: the value isn’t stored in one column but four—string_value, int_value, float_value, and double_value. Only one contains data for any given parameter, but you need to know which one to query.

What the Schema Actually Looks Like

The GA4 event_params field structure looks like this:

ARRAY with key (STRING) and value STRUCT containing string_value (STRING), int_value (INT64), float_value (FLOAT64), and double_value (FLOAT64).

Three array data type fields exist in the GA4 export: event_params, user_properties, and items. All three require UNNEST operations to access their contents.

When you view your GA4 table in BigQuery, event parameters appear as multiple rows per event. You might see session_engaged, engagement_time_msec, page_title, and page_referrer all nested within a single event row. Before you can analyze this data, you need to flatten it.

You may be interested in: Looker Studio vs Metabase: Which Free BI Tool for WooCommerce BigQuery Data

The UNNEST Syntax Explained#

UNNEST is a BigQuery function that converts an array into a set of rows. This process is called “flattening.” For GA4 data, you use UNNEST inside a subquery to extract specific parameter values.

Here’s a basic query to get page_location:

SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location FROM your_table WHERE event_name = 'page_view'

The subquery UNNESTs the event_params array, filters for the page_location key, and returns just the string_value column because page_location is a string.

If you query the wrong value column, you get NULL. Transaction values might be stored as int_value while page_location uses string_value. Getting this wrong means your query returns empty results with no error message.

When Data Types Get Complicated

Some parameters can contain different data types across events. Google’s official documentation shows queries using COALESCE to handle this:

SELECT COALESCE(value.int_value, value.float_value, value.double_value) FROM UNNEST(event_params) WHERE key = 'value'

COALESCE returns the first non-NULL value from the list. For numeric parameters that might be stored as integer, float, or double, you need this fallback logic in every query.

This is where many people give up. Writing a single query to answer “what were my top pages last month” requires understanding nested data types, subqueries, UNNEST syntax, and COALESCE patterns.

Why WordPress Store Owners Struggle#

The GA4 BigQuery schema assumes SQL expertise that most marketers and store owners don’t have. Tutorials exist—OWOX published a 7-part series just to explain UNNEST operations—but the learning curve is steep for people who just want answers about their business.

The questions are simple. The SQL isn’t.

Want to know which products were added to cart most often? You need to UNNEST both event_params AND the items array. Want to combine that with user_properties? That’s another nested RECORD to flatten.

Adswerve built an open-source tool called GA4 Flattener specifically because this complexity was blocking adoption. The tool transforms nested GA4 data into flat tables that work with standard SQL queries.

You may be interested in: WooCommerce Server-Side Tracking Setup: 15 Minutes vs 15 Hours

Alternatives to the UNNEST Nightmare#

You have three options for working with BigQuery data without becoming an UNNEST expert.

Option 1: Use Flattener Tools

Tools like GA4 Flattener create flat views of your nested GA4 data. Instead of querying event_params directly, you query a transformed table where each parameter is a simple column. The tradeoff: you’re adding complexity to your data pipeline and maintaining another tool.

Option 2: Use Looker Studio Templates

Pre-built Looker Studio dashboards connect to GA4 BigQuery exports and handle the UNNEST logic in their data sources. You get visualizations without writing SQL. The tradeoff: you’re limited to what the template creator built, and custom questions still require custom queries.

Option 3: Send Data Directly to BigQuery

Server-side tracking can send events directly to BigQuery using flat schemas. Instead of accepting GA4’s nested export format, you control the table structure from the start.

Transmute Engine™ routes events from WordPress to BigQuery using a flat schema where page_location, transaction_id, and every other parameter are simple columns. Query your data with straightforward SELECT statements:

SELECT page_location, COUNT(*) as pageviews FROM your_table GROUP BY page_location

No UNNEST. No subqueries. No data type guessing.

When Nested Schemas Make Sense#

To be fair, nested data structures aren’t inherently bad. They’re storage-efficient—Google can store your entire event parameter set in a single field rather than creating hundreds of columns. For enterprises with data engineers on staff, UNNEST is just another SQL pattern to learn.

But for WordPress store owners running WooCommerce, the tradeoff doesn’t work. You don’t need storage efficiency. You need answers. And the GA4 schema puts a technical barrier between you and insights about your own business.

Key Takeaways#

• GA4 BigQuery exports use nested RECORD types where event_params is an array with four separate value columns
• UNNEST functions are required to extract any individual parameter value—there’s no simple SELECT alternative with native GA4 exports
• Wrong data type selection returns NULL without error messages, making debugging queries difficult
• OWOX and Adswerve built dedicated tools specifically because the native complexity blocks adoption
• Flat schema alternatives exist through flattener tools or direct BigQuery integrations that avoid nested structures entirely
• Server-side tracking solutions can send events to BigQuery using simple column-based tables

Ready to query your WooCommerce data without SQL gymnastics? Explore flat-schema BigQuery integration at Seresa.