GA4 user properties in BigQuery: user_id, user_pseudo_id, first touch and LTV

How to set a user property in GA4

Google gives three routes (read in October 2026):

• Google tag (gtag.js). The set command applies user properties to every event on the page:

gtag('set', 'user_properties', {
  plan: 'pro',
  customer_type: 'returning'
});

• Google Tag Manager. In the Google Analytics 4 Event tag, add one row per property under User Properties.
• Measurement Protocol (server side). Send a user_properties object; Google notes a request can carry user properties without any events.

Register the property so reports can use it; Google says to create the user-scoped custom dimension before you start setting the property: Admin → Data display → Custom definitions → Custom dimensions → Create custom dimensions, set the scope to User and pick the user property. You need the Editor role on the property, and Google says reporting on a new custom dimension starts after 24 to 48 hours.

The limits and rules that break setups most often (Google’s collection limits and user-property pages, read in October 2026):

• 25 user properties per standard property. Names up to 24 characters, values up to 36 characters.
• Reserved names: first_open_time, first_visit_time, last_deep_link_referrer, user_id and first_open_after_install. Names cannot start with google_, ga_ or firebase_.
• No backfill. Once set, a property applies to all future events for that user, including later sessions, but not to events that happened before it was set.

user_id is not a user property

user_id is on the reserved list, so a GTM row named user_id is the wrong tool. The User-ID has its own setting: gtag('config', 'TAG_ID', {'user_id': 'USER_ID'}) on every page for signed-in users, and user_id set to null when they sign out (Google warns against sending an empty string or the text “null”). Google states the user_id field is available in the exported data; it is limited to 256 characters and is exported whatever the consent status.

Where user properties land in BigQuery

The GA4 to BigQuery link lives in Admin → Product links → BigQuery links. It needs the Editor role on the GA4 property and Owner access to the BigQuery project. Standard (free) GA4 properties can export, with a limit of 1 million events per day for the daily export; the streaming export has no event limit. Google says data starts arriving within 24 hours, and its setup page describes no backfill of the days before the link existed (all read in October 2026). The full walkthrough is in our GA4 to BigQuery setup guide.

Once linked, user properties appear in two places:

1. events_YYYYMMDD: on every event, as the user_properties record. This is the history: what the user’s properties were when each event happened. Field-by-field notes on the rest of this table are in the events table schema guide.
2. users_YYYYMMDD and pseudonymous_users_YYYYMMDD: only if you tick the daily user data export when setting up the link. One row per user whose data changed that day (more on these below).

The sample used in the property examples

Google’s public GA4 sample is no help for user properties (see the trap below), so the first three queries run on a small copy of the export’s structure. Paste this before any of them:

WITH props AS (
  SELECT 'A' AS user_pseudo_id,
         TIMESTAMP '2026-10-01 09:00:00' AS event_time,
         'plan' AS key, 'free' AS string_value,
         CAST(NULL AS INT64) AS int_value,
         CAST(NULL AS FLOAT64) AS double_value,
         TIMESTAMP '2026-10-01 09:00:00' AS set_at
  UNION ALL SELECT 'A', '2026-10-03 10:00:00', 'plan', 'pro', NULL, NULL, '2026-10-03 10:00:00'
  UNION ALL SELECT 'A', '2026-10-03 10:00:00', 'orders', NULL, 2, NULL, '2026-10-03 10:00:00'
  UNION ALL SELECT 'A', '2026-10-05 11:00:00', 'plan', 'pro', NULL, NULL, '2026-10-03 10:00:00'
  UNION ALL SELECT 'A', '2026-10-05 11:00:00', 'orders', NULL, 2, NULL, '2026-10-03 10:00:00'
  UNION ALL SELECT 'B', '2026-10-02 12:00:00', 'plan', 'free', NULL, NULL, '2026-10-02 12:00:00'
  UNION ALL SELECT 'B', '2026-10-02 12:00:00', 'orders', '1', NULL, NULL, '2026-10-02 12:00:00'
  UNION ALL SELECT 'C', '2026-10-04 15:00:00', 'score', NULL, NULL, 7.5, '2026-10-04 15:00:00'
),
events AS (
  -- one row per event, user properties nested as in the GA4 export
  SELECT
    user_pseudo_id,
    UNIX_MICROS(event_time) AS event_timestamp,
    ARRAY_AGG(STRUCT(key, STRUCT(string_value, int_value, double_value,
              UNIX_MICROS(set_at) AS set_timestamp_micros) AS value))
      AS user_properties
  FROM props
  GROUP BY user_pseudo_id, event_time
)

Three users, five events. User A starts on free, upgrades to pro on 3 October, and the pro value is carried onto the 5 October event with its original set time. User B’s orders was sent as text, user A’s as a number. User C has a score and no plan.

Flatten user_properties

SELECT
  user_pseudo_id,
  TIMESTAMP_MICROS(event_timestamp) AS event_time,
  up.key,
  up.value.string_value,
  up.value.int_value,
  up.value.double_value,
  TIMESTAMP_MICROS(up.value.set_timestamp_micros) AS set_at
FROM events, UNNEST(user_properties) AS up
ORDER BY user_pseudo_id, event_time, up.key;

Eight rows from five events: UNNEST returns one row per property per event. Counting users or events over this result counts them more than once. How UNNEST works in general is in the UNNEST guide for GA4 event parameters; user_properties behaves the same way as event_params.

The value at the time of each event

To keep one row per event, read the property with a subquery instead of a join:

SELECT
  user_pseudo_id,
  TIMESTAMP_MICROS(event_timestamp) AS event_time,
  (SELECT value.string_value FROM UNNEST(user_properties)
   WHERE key = 'plan') AS plan
FROM events
ORDER BY user_pseudo_id, event_time;

This is the right read when the question is “what were people on when they did X”: user A’s 1 October event belongs to free, even though A is pro today. User C gets NULL, not an error: the property was never set.

The latest value per user

For a user list (one row per person, current values), take the value with the largest set_timestamp_micros:

SELECT
  user_pseudo_id,
  MAX_BY(up.value.string_value, up.value.set_timestamp_micros) AS plan,
  TIMESTAMP_MICROS(MAX(up.value.set_timestamp_micros)) AS plan_set_at
FROM events, UNNEST(user_properties) AS up
WHERE up.key = 'plan'
GROUP BY user_pseudo_id
ORDER BY user_pseudo_id;

User C is missing: the WHERE up.key = 'plan' filter drops users who never had the property. When you need every user, and several properties as columns, move the key test into MAX_BY and keep users with LEFT JOIN:

SELECT
  user_pseudo_id,
  MAX_BY(up.value.string_value,
         IF(up.key = 'plan', up.value.set_timestamp_micros, NULL))
    AS plan,
  MAX_BY(up.value.int_value,
         IF(up.key = 'orders', up.value.set_timestamp_micros, NULL))
    AS orders_int_only,
  MAX_BY(COALESCE(up.value.string_value,
                  CAST(up.value.int_value AS STRING)),
         IF(up.key = 'orders', up.value.set_timestamp_micros, NULL))
    AS orders,
  MAX_BY(up.value.double_value,
         IF(up.key = 'score', up.value.set_timestamp_micros, NULL))
    AS score
FROM events
LEFT JOIN UNNEST(user_properties) AS up
GROUP BY user_pseudo_id
ORDER BY user_pseudo_id;

MAX_BY ignores rows where the second argument is NULL, so each column only looks at its own key. The orders_int_only column is the trap: user B’s orders were sent as text, land in string_value, and a query that reads only int_value reports nothing for B. Coalesce the value columns, then cast once you know the type.

The trap in Google’s public GA4 sample

The ga4_obfuscated_sample_ecommerce dataset is where most tutorials run their queries, and its user properties cannot be used:

SELECT data_type
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'events_20210131'
  AND column_name = 'user_properties';

Every field is INT64, the key included, so WHERE key = 'plan' fails with “No matching signature for operator = for argument types: INT64, STRING”. Between November 2020 and January 2021 only 134 of 4,295,584 events carry a user_properties entry at all, and every key and value in them is NULL. A query that “works” on your own export will not run here, and the reverse.

The user-level fields outside that record are usable, so the rest of this page runs on the sample for January 2021. Always filter _TABLE_SUFFIX: these queries scanned 34 to 268 MB, while the same columns across all three months of the dataset come to 1.68 GB (dry run). More on that in wildcard tables.

user_id vs user_pseudo_id

SELECT
  COUNT(*) AS events,
  COUNT(DISTINCT user_pseudo_id) AS pseudo_ids,
  COUNT(user_id) AS events_with_user_id,
  COUNTIF(ARRAY_LENGTH(user_properties) > 0) AS events_with_user_properties,
  COUNTIF(user_first_touch_timestamp IS NULL) AS no_first_touch
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131';

• user_pseudo_id is set on every event. Google’s schema calls it “The pseudonymous id (e.g., app instance ID) for the user”, assigned when the user first opens the app or visits the site. On the web it follows the browser, so one person on a phone and a laptop is two pseudo IDs.
• user_id is “The unique ID assigned to a user”: the ID you send for signed-in users. The sample never sends one, hence 0. In your own export it is NULL for every event before sign-in and after sign-out.
• Which one to count. Google’s own sample queries say to count distinct user_id for users, and to fall back to user_pseudo_id if your site does not send a user_id with each hit or you are unsure. Most sites are in the second case.

user_first_touch_timestamp

It is “The time (in microseconds) at which the user first opened the app or visited the site.” Convert it with TIMESTAMP_MICROS:

WITH users AS (
  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(MIN(user_first_touch_timestamp)) AS first_touch
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
  GROUP BY user_pseudo_id
)
SELECT
  CASE
    WHEN first_touch IS NULL THEN 'no first touch'
    WHEN first_touch >= TIMESTAMP '2021-01-01' THEN 'new in January'
    ELSE 'first seen before January'
  END AS user_type,
  COUNT(*) AS users,
  DATE(MIN(first_touch)) AS earliest_first_touch
FROM users
GROUP BY user_type
ORDER BY users DESC;

Two things to read from this:

• First touch is not the first day in your export. The sample starts in November 2020, yet some users’ first touch is 14 October 2019. GA4 knew them before the export did. Use this field, not MIN(event_date), to tell new from returning users.
• It can be NULL. 1,638 users had no first-touch value on any of their January events, and 23,945 events overall had none. Decide where those users go before a report does it for you.

user_ltv: lifetime value on every event

user_ltv.revenue and user_ltv.currency are the user’s lifetime revenue so far, repeated on each event row (and not populated in the intraday tables). Take the latest value per user, never the sum:

WITH users AS (
  SELECT
    user_pseudo_id,
    MAX_BY(user_ltv.revenue, event_timestamp) AS ltv_revenue,
    MAX_BY(user_ltv.currency, event_timestamp) AS ltv_currency,
    SUM(user_ltv.revenue) AS wrong_sum_over_events
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
  GROUP BY user_pseudo_id
)
SELECT
  ltv_currency,
  COUNT(*) AS users,
  COUNTIF(ltv_revenue > 0) AS users_with_revenue,
  ROUND(SUM(ltv_revenue), 2) AS total_ltv,
  ROUND(AVG(IF(ltv_revenue > 0, ltv_revenue, NULL)), 2) AS avg_ltv_payers,
  ROUND(SUM(wrong_sum_over_events), 2) AS wrong_total
FROM users
GROUP BY ltv_currency;

Summing user_ltv.revenue across events gives 3,394,252 instead of 102,934: about 33 times too much, because each user’s running total is counted once per event. And 1,192 users have lifetime revenue while only 1,069 bought in January (next query): LTV includes purchases made before the date range you filtered on. More on reading LTV in customer lifetime value.

User counts the way Google’s sample queries define them

Google’s basic-queries page defines new users as users with a first_visit or first_open event in the period, and returning users as the rest:

WITH users AS (
  SELECT
    user_pseudo_id,
    LOGICAL_OR(event_name IN ('first_visit', 'first_open')) AS is_new,
    COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params)
                    WHERE key = 'ga_session_id')) AS sessions,
    LOGICAL_OR(event_name = 'purchase') AS purchased
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
  GROUP BY user_pseudo_id
)
SELECT
  COUNT(*) AS total_users,
  COUNTIF(is_new) AS new_users,
  COUNT(*) - COUNTIF(is_new) AS returning_users,
  ROUND(SUM(sessions) / COUNT(*), 2) AS sessions_per_user,
  COUNTIF(purchased) AS purchasers
FROM users;

The first_visit count (88,830) and the first-touch count above (88,806) disagree by 24 users for the same month. Neither is wrong; they are two definitions. Pick one, write it down, and use it everywhere, or two reports will show two “new users” numbers. This query reads event_params, which is why it scanned 268 MB. Session logic in more depth is in querying GA4 event data.

The users_ and pseudonymous_users_ tables

If you enable the user data export, GA4 writes two more daily tables (Google’s user-data schema and query pages, read in October 2026):

Both carry the same blocks: occurrence_date and last_updated_date; user_info (last_active_timestamp_micros, user_first_touch_timestamp_micros, first_purchase_date); audiences with membership start and expiry; user_properties with only key, value.string_value and value.set_timestamp_micros; device; geo; a user_ltv record with revenue_in_usd, sessions, engagement_time_millis, purchases, engaged_sessions and session_duration_micros; and predictions (7-day purchase and churn probabilities, 28-day expected revenue).

Two differences from the events table matter for queries:

• A row means “changed”, not “active”. A user lands in the day’s table when anything about them changed, including dropping out of an audience. Google warns the count can exceed Active users for the same day.
• Values are already per user. The latest property values and lifetime totals are there without MAX_BY, which makes these tables the simpler source for a user list. The user_ltv record here has more fields than the two in events_*.

From query to a report people trust

The decisions on this page (event-time or latest value, user_id or user_pseudo_id, first touch or first_visit, latest LTV rather than a sum) are each one line of SQL, and each one changes the number. When every report writes its own version, the numbers drift. The other common slips are collected in GA4 BigQuery Export mistakes.

GA4 data reaches BigQuery through Google’s own export; it is not an OWOX source. With OWOX Data Marts an analyst publishes the user-level query once as a Data Mart, with each of those choices written into the column descriptions, and that one data model feeds Google Sheets, Data Studio dashboards, Excel, email or Slack. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.

Google BigQueryGA4 BigQuery Export schema: the tables, the fields and how dates workOctober 16, 2026→Google BigQueryHow to query GA4 event data in BigQuery: sessions, sources and revenueOctober 16, 2026→Google BigQueryHow to unnest GA4 event_params in BigQuery: page_location, ga_session_id and itemsOctober 16, 2026→

See all articles →