The GA4 export is powerful and awkward in equal measure: every useful value is buried in an array you have to unnest again and again. Flattening it once into a wide table turns that repeated pain into a clean base everyone can query. Here is how to build one.

Why flatten at all

Left raw, every query against the export repeats the same UNNEST gymnastics to pull page, source, or session ID, which is slow to write, easy to get wrong, and expensive when many people do it. A flattened table promotes the parameters you use most into real columns, so downstream queries read like normal SQL. It is the single highest-leverage cleanup you can do on the export.

Decide the grain first

Before writing anything, choose what one row represents. The two common choices are:

  • One row per event, which keeps full detail and is the usual base layer.
  • One row per session, which is smaller and better for channel and conversion reporting.

Start with one row per event, since you can always aggregate up to sessions later, but not the other way around.

Promote the parameters you use

Flattening is mostly a list of scalar subqueries, each pulling one parameter into a named column:

CREATE OR REPLACE TABLE `project.staging.events_flat`
PARTITION BY event_day AS
SELECT
  PARSE_DATE('%Y%m%d', event_date)          AS event_day,
  event_timestamp,
  event_name,
  user_pseudo_id,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key='ga_session_id')     AS session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_location')     AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_title')        AS page_title,
  collected_traffic_source.manual_source     AS source,
  collected_traffic_source.manual_medium     AS medium,
  device.category                            AS device_category,
  geo.country                                AS country,
  ecommerce.purchase_revenue                 AS purchase_revenue
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'

Notice nested RECORDs like device.category and geo.country need only dot notation, while repeated event_params need the subquery. The result is a table where every column is directly usable.

Partition and schedule it

A flattened table earns its keep only if it stays cheap and current, which means two things. Partition it by the event day so queries prune to the dates they need, and rebuild it on a schedule so it keeps up with the daily export. A scheduled query or a dbt or Dataform model that appends yesterday's data incrementally is the maintainable pattern, rather than a full rebuild every run.

Keep raw access for the long tail

A wide table is a convenience layer, not a replacement. Promote the twenty or so parameters people actually use, and leave the rest in the raw export for the occasional deep question. Trying to flatten every possible parameter produces an unwieldy table that is expensive to build and still misses something, so draw the line at what earns its column.

Not everything flattens to a column

Some fields are one-to-many and resist a single row, chiefly the items array on ecommerce events, where one purchase holds many products. Forcing those into columns loses data. The clean pattern is a second flat table at item grain, one row per product per order, built by UNNESTing items, while the event table stays one row per event. Match the grain to the question rather than cramming everything into one shape.

Keep names and types stable

A flat table becomes a shared contract the moment others query it, so treat its column names and types as an interface. Rename thoughtfully, cast values to consistent types, and document what each promoted parameter means. A column silently changing type or meaning between rebuilds breaks every downstream query that trusted it, so version the build logic in git alongside your other models, where a change to what the table contains is reviewable rather than a surprise.

The export buries every value in an array, and flattening is how you stop paying that unnest tax on every query. Choose the event grain, promote the parameters you actually use into real columns, partition and schedule the build, and keep the raw export for the long tail. Do that and the awkward, powerful export becomes an ordinary, fast table the whole team can query without flinching.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.