GA4 + BigQuery: Customer-Intelligence Pipeline Patterns

The Google Analytics 4 to BigQuery export is the single most useful — and most underused — asset in a modern analytics stack. The reporting interface inside GA4 is built for aggregate dashboards: sampled, cardinality-limited, and locked to a fixed set of dimensions. The BigQuery export is the opposite: raw, unsampled, event-level data you own outright and can join to anything. That difference is exactly what customer intelligence needs.

This is a practitioner guide to the patterns I use when building customer-intelligence pipelines on GA4 + BigQuery for enterprise clients. It covers the export schema essentials that trip people up, the staging models that turn raw events into something queryable, and the analytical marts — sessionization, identity stitching, RFM, lifetime value, and cohorts — that the whole pipeline exists to produce. No client data appears here; the patterns are general and the SQL is illustrative.

Why GA4 + BigQuery for Customer Intelligence

GA4 was rebuilt around an event model — every interaction is an event with parameters, rather than the session-and-hit model of Universal Analytics. That event model is awkward in the reporting UI but ideal in a warehouse. Three things make the BigQuery export the right foundation:

• Raw, unsampled rows. The export delivers every event with no sampling and no cardinality collapse. Per-user analysis — the basis of customer intelligence — is impossible on sampled aggregates.
• Joinable to everything. Once events live in BigQuery you can join them to CRM records, order tables, product catalogs, and support tickets. Behavioral data plus transactional data is where real customer intelligence starts.
• You own the modeling. Sessions, identity, segments, and metrics are defined in SQL you control and can audit, not in a vendor's opaque reporting logic. When a number is questioned, you can trace it to the row.

The export itself is free for standard properties, including both the daily batch table and the streaming intraday table. You pay only normal BigQuery storage and query costs once the data lands — and partition pruning keeps those costs low if you model carefully.

The Pipeline in Three Layers

The architecture that holds up over time is a layered one. Each layer has a single job and feeds the next. Resist the urge to write one giant query against the raw export — it becomes unmaintainable the first time the schema surprises you.

GA4 Export Schema: The Parts That Matter

The export lands in a dataset named analytics_<property_id>, with one partitioned table per day (events_YYYYMMDD) and a streaming table (events_intraday_YYYYMMDD) when streaming export is enabled. Each row is a single event. The fields that drive every downstream model:

The two facts that cause the most confusion: timestamps are in microseconds, and there is no session table. GA4 does not store sessions as rows — you reconstruct them from events. Both are handled in the staging layer below.

Staging: Flatten Events and Rebuild Sessions

Because event_params is a repeated record, you pull a single parameter by unnesting it and selecting the typed value. This subquery pattern is the workhorse of the entire pipeline:

SELECT
  event_date,
  TIMESTAMP_MICROS(event_timestamp)            AS event_ts,
  event_name,
  user_pseudo_id,
  user_id,
  -- pull the session id out of the nested params
  (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')              AS ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location')              AS page_location,
  -- a globally-unique session key: device + session number
  CONCAT(
    user_pseudo_id, '.',
    CAST((SELECT value.int_value FROM UNNEST(event_params)
            WHERE key = 'ga_session_id') AS STRING)
  )                                            AS session_key
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'

Two disciplines make this layer durable:

The session key — user_pseudo_id concatenated with ga_session_id — is what GA4 itself uses internally to count sessions, and it is globally unique. Group by it to rebuild sessions: first and last event timestamp give session start/end and duration, the count of page_view events gives depth, and the presence of a purchase event flags a converting session. That single sessions model feeds funnels, engagement metrics, and attribution downstream.

Identity Resolution: Devices to People

This is the layer that separates real customer intelligence from page-view reporting. user_pseudo_id answers "which browser?"; customer intelligence needs "which person?" The mapping is rarely one-to-one — a single customer shows up as many pseudo-ids across devices and cookie resets, and a shared device can carry several.

The stitching pattern: for every user_pseudo_id that ever co-occurs with a known user_id (because the user logged in during at least one event), propagate that user_id to all of that device's events. Build a crosswalk table:

-- one row per device, resolved to a stable customer key where known
SELECT
  user_pseudo_id,
  -- the most recent non-null user_id seen on this device
  ARRAY_AGG(user_id IGNORE NULLS ORDER BY event_timestamp DESC LIMIT 1)[SAFE_OFFSET(0)]
    AS resolved_user_id
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY user_pseudo_id

Downstream, the customer key is COALESCE(resolved_user_id, user_pseudo_id) — use the real person when known, fall back to the device when not. Every per-customer metric keys on that. The honest caveat worth stating in any deliverable: stitching is a heuristic. Anonymous-before-login behavior only attaches to a person retroactively, and cross-device coverage is only as good as your login rate. Name those limits rather than implying the resolution is perfect.

The Marts: What Customer Intelligence Actually Produces

With clean sessions and resolved customers, the analytical marts are straightforward to express. These are the four that earn their keep on almost every engagement.

RFM is the highest-leverage starting point because it is cheap to compute and immediately actionable. From the resolved purchase events:

WITH purchases AS (
  SELECT
    COALESCE(resolved_user_id, user_pseudo_id)   AS customer_key,
    TIMESTAMP_MICROS(event_timestamp)            AS purchase_ts,
    (SELECT value.double_value FROM UNNEST(event_params)
       WHERE key = 'value')                      AS purchase_value
  FROM staged_events
  WHERE event_name = 'purchase'
)
SELECT
  customer_key,
  DATE_DIFF(CURRENT_DATE(), DATE(MAX(purchase_ts)), DAY)  AS recency_days,
  COUNT(*)                                                AS frequency,
  SUM(purchase_value)                                     AS monetary
FROM purchases
GROUP BY customer_key

Wrap each of recency, frequency, and monetary in NTILE(5) OVER (...) to score quintiles, then map the score triples to segment names. That single table drives lifecycle email, paid-audience exclusions, and win-back campaigns — the activation side of customer intelligence.

Scheduling and Orchestration

The export refreshes daily (intraday if streaming is on). The pipeline should refresh on the same cadence. Two common approaches:

For anything beyond a couple of models, dbt with incremental materializations is the pattern that scales: each run processes only the newest daily partition, models are tested, and the lineage from raw export to RFM mart is documented and reviewable. That auditability matters more than it looks — when a stakeholder questions a number, "here is the model and the test that guards it" is a far better answer than "the dashboard says so."

Common Dead Ends to Avoid

• Querying events_* without a date filter. The wildcard scans every day you have ever exported. Always constrain _TABLE_SUFFIX; it is the difference between cents and hundreds of dollars per query.
• Treating user_pseudo_id as a person. It is a device. Customer counts built on it overstate uniques and understate loyalty. Do the identity layer before any per-customer metric.
• Reading the wrong value field. Each event_params entry stores its value in exactly one typed field. Pull int_value for a session id and you will get nulls for a string param. Match the field to the parameter.
• Reconciling to the GA4 UI to the decimal. The export and the interface use different processing (late hits, identity, modeling). They will be close, not identical. Chasing an exact match wastes days; document the expected variance instead.
• Skipping the privacy boundary. Raw event data is sensitive. Decide up front what is retained, what is hashed, and who can query it — especially in regulated industries. Bake it into the staging layer, not as an afterthought.

Frequently Asked Questions

Related reading: AI in Enterprise Data Work — What Actually Ships — the patterns that deliver value versus the ones that stall. AI as an Operations Layer — scheduled analysis and DataOps opportunity discovery. LLMs Explained — why private, first-party data is where enterprise AI gets interesting. For the hardware side of running AI locally, see Blendlogic Tech.