Fix GA4 User-ID Cross-Device Tracking Discrepancies in BigQuery

September 20, 2026

Fixing GA4 User-ID discrepancies in BigQuery requires querying the events export schema with explicit handling for the user_id field alongside the client_id, while accounting for Google's internal reporting identity stitching logic. Standard GA4 user reports often misalign with custom SQL queries because the user interface applies asynchronous thresholding, blended device graphs, and modeled data that raw event logs exclude.

Key takeaways

• GA4 user reporting interface applies blended cross-device modeling, while BigQuery returns strictly observed data tied to explicit identifiers.
• Querying raw events demands grouping by both user_pseudo_id and user_id to properly handle unauthenticated session stitching.
• Server-side tagging setups often introduce race conditions where the User-ID arrives late in the data layer, requiring payload mutation checks similar to how teams fix GTM data layer undefined errors in GA4.
• Discrepancies often mirror attribution splits seen when trying to sync GA4 BigQuery data to Salesforce for attribution without consistent key normalization.

Why GA4 UI and BigQuery User Counts Diverge

You export your GA4 data to BigQuery. You write a straightforward count distinct query on user_id. The number is wildly lower than what the GA4 Exploration report shows. Panic sets in. Is the pipeline broken? Did Google drop rows? Usually, neither is true. The delta stems from fundamental differences between how the GA4 user interface processes data and how BigQuery stores raw event streams.

Google Analytics 4 uses a Reporting Identity setting with three modes: Blended, Observed, and Device-ID. If your property is set to Blended, Google applies machine learning to model user behavior across devices and unobserved sessions. BigQuery, however, contains only raw, collected events. It does not run Google's proprietary cross-device graph over your tables unless you explicitly write SQL to stitch those identities together.

Furthermore, thresholding applies in the GA4 UI to protect user privacy when user counts are low. BigQuery bypasses thresholding entirely. If your property hits thresholding limits, the UI will artificially inflate or alter user metrics, guaranteeing a mismatch with your SQL outputs.

The Mechanics of User-ID Stitching in Raw SQL

In the browser or app, a user starts an anonymous session. They have a user_pseudo_id (the equivalent of the old GA4 client ID). Halfway through the session, they log into your platform. Your front-end instrumentation calls gtag('set', { 'user_id': 'USER_12345' }). From that moment forward, events carry both the user_pseudo_id and the user_id.

However, events fired *before* the login event in that same session only carry the user_pseudo_id. If you write a naive BigQuery query that groups solely by user_id, you miss all pre-authentication activity. Conversely, if you group by user_pseudo_id, you treat the pre-login and post-login visitor as two distinct human beings.

To accurately reconcile cross-device tracking, your SQL must map anonymous pseudo IDs to authenticated user IDs retroactively within a given time window. Here is a pattern to build a mapping table before aggregating metrics:

WITH user_mapping AS (
  SELECT
    user_pseudo_id,
    user_id,
    ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) as rn
  FROM `your-project.analytics_123456789.events_*`
  WHERE user_id IS NOT NULL
    AND _TABLE_SUFFIX BETWEEN '20231001' AND '20231031'
)
SELECT
  COALESCE(m.user_id, e.user_pseudo_id) as unified_id,
  COUNT(DISTINCT e.event_date) as active_days
FROM `your-project.analytics_123456789.events_*` e
LEFT JOIN user_mapping m ON e.user_pseudo_id = m.user_pseudo_id AND m.rn = 1
WHERE e._TABLE_SUFFIX BETWEEN '20231001' AND '20231031'
GROUP BY 1

This query establishes a deterministic link. It assigns the first observed user_id associated with a user_pseudo_id across the entire dataset. Without this step, your cross-device user counts will plummet because GA4's UI does this stitching automatically behind the scenes.

Common Implementation Pitfalls in Tag Management

Discrepancies often trace back to flawed instrumentation rather than query logic. If your User-ID is passed inconsistently across subdomains or if client-side race conditions cause the User-ID to fire on the second page view instead of the first, your tracking breaks down. This mirrors the diagnostic work required when you need to fix utm_source=undefined in GA4 and Server-Side GTM due to asynchronous initialization.

Another frequent culprit is session boundary handling. When a user logs in, does your implementation trigger a new session? If it does not, GA4 stitches the User-ID to the existing session. If your custom event parameters overwrite user properties incorrectly, BigQuery will reflect fragmented identity states. Always inspect the is_active_user parameter and event parameters array carefully in your SQL exploration phase.

Validating Data Quality Against CRM and Server Logs

Do not trust GA4 user counts blindly. Cross-reference your BigQuery extractions against backend database logs or customer data platforms like Segment or RudderStack. Pull a sample of 50 known user IDs from your production database who made purchases across multiple devices last week. Check if your BigQuery SQL links their mobile app session to their desktop browser session.

If the connection fails, verify that your client-side implementation preserves the User-ID across domain hops and that your consent management platform (CMP) isn't clearing storage cookies prematurely. If consent signals are dropping out, ensure you review how to fix consent mode v2 signal missing in Google Ads & GA4 to prevent identity loss at the ingestion layer.

Frequently Asked Questions

Why does the GA4 UI show more users than my BigQuery SQL query?

The GA4 user interface often uses blended reporting identity models that incorporate machine learning and modeled data for unobserved users. BigQuery contains strictly observed, raw event data without Google's modeled user extensions or UI-level thresholding.

How do I handle users who browse anonymously before logging in?

You must construct a mapping table in BigQuery that associates user_pseudo_id with user_id based on the first occurrence of the login event. Join this mapping back to your main event stream to retroactively stitch pre-authentication behavior to the authenticated user profile.

Does BigQuery include deleted or hashed User-IDs?

BigQuery captures whatever string value you pass into the user_id field via your tracking implementation. If you pass hashed emails or internal database keys, BigQuery stores those exact strings. Ensure your values comply with Google's Personally Identifiable Information (PII) policies.

Why do cross-device user counts fluctuate when I change date ranges?

Because cross-device stitching often relies on rolling lookback windows or first-seen mapping tables scoped to specific partitions, querying dynamic date ranges can alter how anonymous sessions bind to historical user IDs. Always use static partition filters in your SQL to maintain consistent user counts over time.