A flat-schema BigQuery query for purchase revenue is 1 line of SQL. The same query on GA4’s nested export? 8–12 lines with UNNEST, COALESCE, and subqueries. That’s not a BigQuery problem—that’s a GA4 schema problem. Google designed the export for petabyte-scale storage efficiency across billions of events. Your WooCommerce store with 500 orders a month doesn’t need that architecture. Direct WordPress streaming to BigQuery creates flat tables where SELECT product_name, revenue FROM events just works.

Why GA4’s BigQuery Schema Is So Complicated#

GA4’s BigQuery export packs everything into nested RECORD types. The event_params field—where all the useful data lives—is a REPEATED RECORD containing key-value pairs (Google Analytics BigQuery Export Schema, 2025). That means every parameter you want to access requires UNNEST to unpack the array and COALESCE across four different value types: string_value, int_value, float_value, and double_value.

The GA4 BigQuery export contains 25+ nested fields including event_params, user_properties, items arrays, and privacy_info records (Google Analytics Help documentation, 2025). Want the page title from a page_view event? That’s buried inside event_params as a key-value pair. Want the transaction revenue from a purchase event? Same place—nested, repeated, requiring subqueries.

Here’s what a purchase revenue query looks like on GA4’s schema:

SELECT
  event_date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
  (SELECT COALESCE(value.double_value, value.float_value, CAST(value.int_value AS FLOAT64))
   FROM UNNEST(event_params) WHERE key = 'value') AS revenue,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'currency') AS currency
FROM `project.dataset.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
ORDER BY event_date

That’s 8 lines to answer “how much revenue did each purchase generate?” And it gets worse. GA4 custom parameters only appear in BigQuery event_params—requiring UNNEST even though they display as simple dimensions in the GA4 UI (Fresh Egg, 2024). Every custom dimension you set up? Another UNNEST subquery.

You may be interested in: GA4 BigQuery UNNEST Guide: Why Simple Queries Require Complex SQL

Google Built This Schema for Google, Not for You#

There’s a reason the schema looks like this, and it’s not incompetence. Google processes trillions of events across millions of properties. Nested RECORD types are storage-efficient at that scale—they avoid column bloat when most events don’t use most parameters.

OptimizeSmart notes that Google intentionally avoids pre-calculating metrics as top-level columns, promoting session-level aggregation over event-centric logic (OptimizeSmart, 2026). Translation: Google wants you to query data in a way that benefits their architecture, not yours.

For a platform handling petabytes, this makes engineering sense. For a WooCommerce store owner who just wants to know which products sold last Tuesday, it’s an unnecessary barrier. You’re paying the complexity tax for a schema designed to serve Google’s infrastructure needs—not your business questions. And the learning curve compounds. Every new query requires the same UNNEST pattern. Want to segment by traffic source? UNNEST. Filter by a custom event parameter? UNNEST. Join user properties with event data? Double UNNEST. Store owners who excited about BigQuery’s power often abandon it within weeks because every question requires the same tedious boilerplate.

What a Flat Schema Actually Looks Like#

Now compare. Here’s the same purchase revenue query on a flat table:

SELECT event_date, transaction_id, revenue, currency
FROM events
WHERE event_name = 'purchase' AND event_date BETWEEN '2026-01-01' AND '2026-01-31'

One query. No UNNEST. No COALESCE. No subqueries. A flat schema query for purchase revenue requires 1 line of SQL—the GA4 equivalent requires 8–12 lines with subqueries (Seresa technical comparison, 2025).

In a flat schema, every data point gets its own column. transaction_id, revenue, currency, product_name, page_title—they’re all top-level fields. You write SQL the way you’d expect SQL to work.

Here’s how the two approaches compare for common WooCommerce questions:

Business Question	GA4 Nested Schema	Flat Schema
Total revenue this month	UNNEST + COALESCE + subquery	SELECT SUM(revenue)
Top products by sales	UNNEST items array + UNNEST event_params	SELECT product_name, COUNT(*)
Traffic sources	UNNEST traffic_source fields	SELECT source, medium
Custom dimension analysis	UNNEST + filter by key name	SELECT custom_field

Every common query drops from multi-line UNNEST gymnastics to standard SQL that any marketer can read.

Tools That Flatten GA4 Exports vs Tools That Start Flat#

If you’re already stuck with GA4’s nested export, there are tools that can help. GA4 Flattener is an open-source Cloud Function that creates flattened views of your GA4 export tables. OWOX provides a similar service commercially. Both work—but they’re bandages on an architecture problem.

Flattening after the fact means you’re still running GA4’s export, paying for the nested storage, then paying again to process it into something usable. You’re adding a pipeline step to fix a schema you never chose.

You may be interested in: Fix GA4 BigQuery Data Mismatch: Why Numbers Differ

The alternative? Skip GA4’s schema entirely. Direct WordPress-to-BigQuery streaming sends events from your server straight to BigQuery using the Streaming Insert API. You define the schema. You choose the columns. The data arrives flat because you built it flat from the start.

Direct Streaming Creates the Schema You Actually Need#

When WordPress events stream directly to BigQuery, the schema matches your business—not Google’s infrastructure requirements. A WooCommerce purchase event arrives as a row with columns like event_name, transaction_id, revenue, currency, product_name, customer_email_hash, and source.

No nested records. No repeated fields. No UNNEST. Your custom dimensions? Top-level columns. Your WooCommerce product categories? Top-level columns. Everything accessible with the SQL you already know—or can learn in an afternoon.

The schema you choose determines whether BigQuery feels like a powerful business tool or an impenetrable data swamp.

Transmute Engine™ streams WooCommerce events to flat BigQuery tables by default—your schema, designed for your queries. As a first-party Node.js server running on your subdomain, it sends events directly via BigQuery’s Streaming Insert API, creating tables where standard SQL answers business questions without UNNEST or COALESCE.

Key Takeaways#

• GA4’s nested BigQuery schema requires UNNEST and COALESCE for every parameter extraction—turning 1-line queries into 8–12 line subquery chains.
• Google designed this schema for petabyte-scale storage efficiency, not for store owners asking simple business questions.
• Flat schemas put every data point in its own column—standard SQL works without special BigQuery functions.
• Flattening tools like GA4 Flattener add processing steps to fix a schema problem. Direct streaming avoids the problem entirely.
• Direct WordPress-to-BigQuery streaming lets you define your own schema—optimized for your queries, not Google’s infrastructure.

Ready for BigQuery tables you can actually query? See how Transmute Engine streams flat data from WordPress to BigQuery.