BigQuery export: work with raw GA4 event data

The export is event-level, not a copy of reports

The GA4 BigQuery export provides raw, structured event records. Each row represents an event and includes fields such as event_name, event_timestamp, user_pseudo_id, session-related fields, device, geography, ecommerce items, and nested event parameters. It does not simply reproduce every standard GA4 report table.

Daily export tables are commonly named events_YYYYMMDD; streaming export can also provide intraday tables for fresher data. Export begins after linking, so plan ahead if you will need raw historical analysis. Check your project’s location, permissions, billing, retention, and privacy controls before enabling a production workflow.

• One row per event.
• event_params contains repeated key/value records.
• items contains repeated ecommerce item records.
• event_timestamp is stored in microseconds.
• user_pseudo_id is a pseudonymous identifier, not a person’s name or email.

UNNEST turns nested fields into usable rows

BigQuery stores event parameters as repeated records. UNNEST lets you read each parameter key and its typed value fields. A parameter’s value may appear in string_value, int_value, float_value, or double_value depending on what was sent. Choose the field that matches your implementation and keep types consistent.

Use a correlated subquery when you want one parameter value without multiplying event rows. A cross join with UNNEST can be useful when you intentionally want one row per parameter or item, but it changes the grain of the result. Always state whether your output rows represent events, parameters, items, users, or sessions.

SELECT
  event_date,
  event_name,
  (
    SELECT value.string_value
    FROM UNNEST(event_params)
    WHERE key = 'form_name'
  ) AS form_name
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'generate_lead';

Reconcile before you automate

Raw queries and GA4 interface reports may differ because of processing, identity, attribution, thresholds, filters, time zones, and metric definitions. Start with simple event counts, document your filters, and reconcile against a known period. Do not assume the interface is wrong when your first SQL count differs.

Control query cost by selecting only needed columns, filtering _TABLE_SUFFIX, and avoiding unbounded SELECT * scans on large tables. Build reusable, reviewed queries or table functions for common definitions. Raw access increases flexibility, but it also makes you responsible for every metric rule.

Read the export schema as nested event records

Start by inspecting one small day and a handful of fields. Top-level columns describe the event and its context, while repeated records hold parameters and items. Some fields are records with nested subfields, so a flat spreadsheet mental model will not be enough.

event_name tells you the action; event_timestamp gives event time in microseconds; event_date provides a date partition useful for controlled queries. user_pseudo_id supports pseudonymous event-level analysis, while ga_session_id inside event_params is commonly used for session grouping when present. Validate your own implementation rather than assuming every field is always populated.

Decide the grain of every query result before writing SELECT. One row per event, one row per item, one row per session, and one row per user require different expansions and aggregations. Many inflated metrics come from unnesting items and then counting events without deduplication.

Write session and user aggregations without changing the grain accidentally

To count sessions, extract the session identifier and combine it with the appropriate pseudonymous user key where your design requires it. Counting only a session number across users can merge unrelated sessions. To count users, count distinct user_pseudo_id while acknowledging its identity limitations.

Build incrementally: first filter events, then create a base table with one row per event and needed fields, then aggregate. Common table expressions make the grain of each stage visible. Review intermediate counts before adding another join or UNNEST.

When joining conversion events back to sessions, avoid duplicating session metrics. Aggregate events to one row per session first, then join to a session-level table. This pattern keeps session counts and session acquisition values stable.

WITH events_base AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params)
      WHERE key = 'ga_session_id') AS session_id,
    event_name,
    event_timestamp
  FROM `project.analytics_123456789.events_20260131`
), sessions AS (
  SELECT user_pseudo_id, session_id
  FROM events_base
  WHERE session_id IS NOT NULL
  GROUP BY user_pseudo_id, session_id
)
SELECT COUNT(*) AS observed_sessions FROM sessions;

Analyze ecommerce items with explicit deduplication

Unnesting items creates one row per item within each purchase event. This is ideal for product revenue and quantity analysis, but counting purchase events after expansion requires a distinct transaction identifier. Keep transaction_id in the item-level base so order metrics can be recovered correctly.

Check whether your business metric uses item revenue, purchase event value, quantity, tax, shipping, or refunds. These are not interchangeable. Define revenue in the query comments and reconcile a sample of known orders with the commerce system.

For product performance, decide whether the denominator is item views, add-to-cart events, purchasing users, or orders containing the item. “Product conversion rate” is ambiguous until that denominator is written down.

SELECT
  item.item_id,
  item.item_name,
  SUM(item.quantity) AS units,
  COUNT(DISTINCT transaction_id) AS orders_containing_item
FROM `project.analytics_123456789.events_20260131`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
  AND transaction_id IS NOT NULL
GROUP BY item.item_id, item.item_name
ORDER BY units DESC;

Create a reconciliation and cost-control checklist

Compare a small set of definitions against the interface: event count by day, sessions using your documented logic, purchasers, and purchase count by transaction ID. Investigate processing delays, filters, identity, attribution, time zones, and thresholding before assuming one source is defective.

Control cost by selecting named columns, restricting table suffixes, avoiding repeated scans of large raw tables, and materializing reviewed intermediate tables when justified. Test complex queries on small periods first. Query cost and freshness are architectural decisions, not afterthoughts.

Version-control production SQL with metric definitions and owners. A query that produces the right number once is not yet a governed data product. Add tests for null identifiers, duplicate transactions, date boundaries, and unexpected row growth.

Worked example: calculate daily orders and item units together

A retailer needs one table per day containing unique orders and units sold. Because items are nested, unnesting items is necessary for units but can duplicate order counts. The safe approach is to preserve transaction_id at item level, count distinct transactions, and sum item quantity with the approved refund and cancellation exclusions.

The query should also define the business date. If stakeholders operate in the property’s local time zone, relying only on export event_date may create boundary differences. Build and validate a timestamp conversion rather than silently mixing UTC and local-day logic.

Reconcile a small set of order IDs with the commerce system before automating the query. Check multi-item orders, quantity greater than one, partial refunds, duplicate delivery, and missing transaction IDs.

WITH item_rows AS (
  SELECT
    event_date,
    transaction_id,
    item.item_id,
    item.quantity
  FROM `project.analytics_123456789.events_20260131`,
    UNNEST(items) AS item
  WHERE event_name = 'purchase'
    AND transaction_id IS NOT NULL
)
SELECT
  event_date,
  COUNT(DISTINCT transaction_id) AS orders,
  SUM(quantity) AS units
FROM item_rows
GROUP BY event_date
ORDER BY event_date;

SQL review checklist before a query becomes a metric

A reviewed query is a metric definition in code. It should state the source tables, date logic, filters, row grain at each stage, parameter types, deduplication keys, and expected aggregation. Comments should explain business choices rather than merely repeating SQL keywords.

Run the query on a small known period and compare intermediate row counts. Look specifically for the moment a join or UNNEST changes the grain. Production scheduling also needs an owner, cost expectation, freshness rule, and failure alert.

• Source table suffix and business time zone are explicit.
• Every CTE has a stated row grain.
• UNNEST is used only when repeated-record expansion is intended.
• Orders and users use documented distinct identifiers.
• Typed parameter fields match the implementation.
• Revenue, refunds, and cancellations have explicit rules.
• Small-period reconciliation and query-cost checks passed.