Identity Stitching in GA4 BigQuery: Building a Master User ID

Many GA4 dashboards and customer lifetime value (LTV) models treat user_id and user_pseudo_id in the BigQuery export as if they described complete customer journeys. They do not. Both identifiers record what a device or a login revealed, not who the customer is.

user_id is populated only when the site sets it, usually after login, and its consistency depends on the implementation. user_pseudo_id is tied to a browser cookie or app instance. Browsing on a phone and converting on a laptop after logging in, clearing cookies or switching browsers are routine behaviour, not edge cases, and each one creates a new user_pseudo_id for the same person. Measured on user_pseudo_id alone, a customer who clears cookies between a first and second purchase becomes two one-time buyers, which biases any repeat-purchase or retention analysis.

The export does not resolve this. It carries the raw user_id and user_pseudo_id on each event row; the blended reporting identity used in the GA4 interface, including Google signals and modelling, is not applied. User counts in BigQuery therefore differ from the interface, and any cross-device view has to be built in the warehouse.

Identity stitching builds a master_user_id (or true_customer_id) that unifies interactions across devices, sessions and data sources. Start deterministically: give user_id priority, then map every user_pseudo_id that has appeared alongside a given user_id to that person. Extend the graph with consented first-party identifiers such as CRM IDs, transaction-system IDs or hashed email addresses. Probabilistic matching can close remaining gaps, but only where the legal basis and privacy rules permit it.

BigQuery and dbt carry the workload. The pipeline joins the GA4 export to internal systems, applies the matching rules and maintains the identity resolution tables incrementally. dbt manages the SQL transformations, tests data quality and records lineage. Without this layer, unique users are over- or undercounted and journey paths stay broken.

Consent requirements and browser restrictions make client-side identifiers less durable. A consent-driven, first-party identifier captured from the first interaction, such as the one Robin Signals provides, gives the identity graph a more stable anchor where standard GA4 identifiers fall short.