Why your GA4 export to BigQuery needs a data model, and how to build it

What the GA4 BigQuery Export contains

The export writes one table per day, named events_YYYYMMDD, plus an events_intraday_ table for the current day if streaming is on. Each row is one event.

Three things about the shape matter for modeling:

• Event parameters are nested. event_params is an array of key and value records. The page URL, the session id and the engagement time are all inside it, so reading one of them needs UNNEST.
• There is no session row. A session exists only as a ga_session_id parameter repeated on each of its events.
• Users are pseudonymous ids. user_pseudo_id identifies a browser or device. user_id is filled only if the site sends it.

Google documents the full schema in the GA4 BigQuery Export schema reference.

The user-data export is a separate option. Google’s user-data queries page (checked October 2026) says it “creates two tables for each day”: users_YYYYMMDD, with a row for every user ID that changed, and pseudonymous_users_YYYYMMDD, with a row for every pseudonymous identifier that changed. They carry lifetime totals, audiences and predictions per user. They do not carry sessions, and a day’s table lists only the users whose data changed that day, not every user.

Why numbers from the raw export disagree

Because the export has no sessions table, every analyst who needs sessions builds one inside their own query. The definitions differ in small ways, and the totals differ with them.

The commonest example is the session key. ga_session_id is the timestamp of the session start, in seconds. Two users who start a session in the same second get the same value. It identifies a session only together with user_pseudo_id.

On Google’s public GA4 sample dataset, for January 2021:

The first query is not a syntax error and its answer looks reasonable. It is 3,498 sessions short, about 3%. A conversion rate built on it is wrong by the same margin, and nothing flags it.

The same kind of choice appears for every derived number: which events count as a conversion, whether a session that crosses midnight is one session or two, which source a session is credited to. A data model is where each choice is made once.

The GA4 data model: three tables

A workable model has three layers. Each is a query on the one below.

Add dimension tables as needed: a channel mapping from source and medium, a page table, a calendar. With those, the model is a star schema with sessions or events as the fact table. See star schema for the pattern.

Flattened events

The first table pulls the parameters you use out of the array and gives them names. It keeps event grain.

SELECT
  user_pseudo_id,
  (
    SELECT value.int_value
    FROM UNNEST(event_params)
    WHERE key = 'ga_session_id'
  ) AS ga_session_id,
  event_name,
  event_timestamp,
  ecommerce.purchase_revenue
    AS revenue
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX
  BETWEEN '20210101' AND '20210131'

Sessions

The sessions table groups events by the combined key. Assuming the query above is saved as events_flat:

SELECT
  CONCAT(
    user_pseudo_id, '-',
    CAST(ga_session_id AS STRING)
  ) AS session_id,
  user_pseudo_id,
  MIN(TIMESTAMP_MICROS(
    event_timestamp))
    AS session_start,
  COUNTIF(event_name = 'page_view')
    AS page_views,
  COUNTIF(event_name = 'purchase')
    AS purchases,
  SUM(revenue) AS revenue
FROM events_flat
WHERE ga_session_id IS NOT NULL
GROUP BY 1, 2

Run on 7 October 2026 with the first query inlined as a WITH clause, this produced 118,380 sessions from 94,790 users, with 1,204 purchases and revenue of 57,350, out of 1,210,147 event rows.

Those four numbers are now fixed by the table’s definition. Anyone who reads the sessions table gets them, whatever tool they use.

Users

The users table groups sessions by user_pseudo_id: first session date, number of sessions, total revenue. If the site sends user_id, this is also where the two ids are reconciled, and that rule deserves a written sentence of its own. If the user-data export is on, join pseudonymous_users_ on pseudo_user_id for audiences and predictions, taking the most recent row for each id, since each daily table holds only the users that changed.

Decisions to write down

A GA4 model is mostly a list of decisions. These five change the numbers most, so state each one in the table’s description.

1. The session key. User and session id together, as above.
2. What a conversion is. Name the events. “Purchase” and “generate_lead” are different businesses.
3. Which source a session gets. The export has traffic_source for the user’s first visit and source parameters on events. Say which one feeds the channel column.
4. The date of a session. The start date, in which time zone.
5. Which days are final. Daily tables can be updated after they first appear, so say how many recent days a scheduled rebuild covers.

For the general versions of these mistakes, see mistakes in data modeling.

Joining GA4 to ad spend and CRM data

Sessions on their own answer traffic questions. The business questions need other sources: what the sessions cost and what they led to.

Two joins cover most of it:

• Ad spend, by date, source, medium and campaign. This works only if campaign names in the ad platforms match the UTM values on the site, so the channel mapping table matters more than it looks.
• CRM or order data, by a user id or a transaction id that the site sends to GA4 and the backend also stores.

Both are joins between tables of different grain. Aggregate each side to the shared grain first, for example date and campaign, and then join. Joining session rows to daily spend rows directly repeats the spend once per session and inflates cost.

Where the definitions should live

Once the three queries exist, the remaining risk is that people do not use them. If the raw events_* tables are what a colleague or an AI assistant can see, they will count sessions from them, and the 3% gap comes back.

The fix is to publish the modeled tables as the thing people read. In OWOX Data Marts each of the three queries becomes a Data Mart: the SQL, a description that carries the decisions above, an owner and described output fields. Relationships between Data Marts are declared on keys such as user_pseudo_id. Reports in Google Sheets or Data Studio read the Sessions Data Mart, not the export.

Two limits to be clear about. GA4 is not a source OWOX Data Marts connects to directly. The data arrives through Google’s own BigQuery export, and the Data Marts are defined on top of it. And the product does not write the sessionization or the conversion logic. Those are the analyst’s SQL, published and governed as written.

Related: Joinable Data Marts for declaring relationships, and the E-commerce data model, which includes sessions and traffic sources.

Google BigQueryGA4 User Properties in BigQuery: user_id, user_pseudo_id, First Touch and LTVOctober 16, 2026→Data ModelingWhat is Data Modeling? Process, Types and TechniquesOctober 26, 2026→Google BigQueryHow to Unnest GA4 event_params in BigQuery: page_location, ga_session_id and ItemsOctober 16, 2026→

See all articles →