Queries for Google Analytics user-data export

  • BigQuery user-data export creates two daily tables: users_YYYYMMDD for user IDs and pseudonymous_users_YYYYMMDD for pseudonymous identifiers.

  • To query a specific date range, use the _TABLE_SUFFIX pseudo column in the WHERE clause along with a wildcard in the FROM clause.

  • You can retrieve user IDs and pseudonymous IDs of users who recently changed a specific user property by querying the relevant table and filtering by property name and timestamp.

  • A summary query can help understand why specific users were included or excluded from the user-data export based on various change types.

  • Remember to set your PROJECT_ID and PROPERTY_ID variables to utilize the example queries effectively.

The sample queries in this page apply to the BigQuery user-data export for Google Analytics. The BigQuery user-data export creates two tables for each day:

  1. A users_YYYYMMDD table, which contains a row for every user ID that changed.
  2. A pseudonymous_users_YYYYMMDD table, which contains a row for every pseudonymous identifier that changed..

Check out the BigQuery Export user-data schema for more details.

Query a specific date range

To query a specific date range from a BigQuery user-data export dataset, use the _TABLE_SUFFIX pseudo column in the WHERE clause of your query.

For example, the following query counts the number of unique users updated between August 1, 2023 and August 15, 2023 with a lifetime engagement of at least five minutes.

users

-- Example: Query a specific date range for users meeting a lifetime engagement criterion.
--
-- Counts unique users that are in the BigQuery user-data exports for a specific date range and have
-- a lifetime engagement of 5 minutes or more.

SELECT
  COUNT(DISTINCT user_id) AS user_count
FROM
  -- Uses a table suffix wildcard to define the set of daily tables to query.
  `PROJECT_ID.analytics_PROPERTY_ID.users_202308*`
WHERE
  -- Filters to users updated between August 1 and August 15.
  _TABLE_SUFFIX BETWEEN '01' AND '15'
  -- Filters by users who have a lifetime engagement of 5 minutes or more.
  AND user_ltv.engagement_time_millis >= 5 * 60 * 1000;

pseudonymous_users

-- Example: Query a specific date range for users meeting a lifetime engagement criterion.
--
-- Counts unique pseudonymous users that are in the BigQuery user-data exports for a specific date
-- range and have a lifetime engagement of 5 minutes or more.

SELECT
  COUNT(DISTINCT pseudo_user_id) AS pseudo_user_count
FROM
  -- Uses a table suffix wildcard to define the set of daily tables to query.
  `PROJECT_ID.analytics_PROPERTY_ID.pseudonymous_users_202308*`
WHERE
  -- Filters to users updated between August 1 and August 15.
  _TABLE_SUFFIX BETWEEN '01' AND '15'
  -- Filters by users who have a lifetime engagement of 5 minutes or more.
  AND user_ltv.engagement_time_millis >= 5 * 60 * 1000;

Each example limits the data to August 1, 2023 through August 15, 2023 by using two features:

  1. The wildcard 202308* in the FROM clause.
  2. A _TABLE_SUFFIX condition in the WHERE clause that filters tables based on the wildcard portion of the table name. For the wildcard of 202308*, the wildcard portion is the day of the month.

You can use a similar approach to query multiple months of data. For example, to query January through October of 2023, modify the query to have:

  1. The wildcard 2023*.
  2. A _TABLE_SUFFIX condition of _TABLE_SUFFIX BETWEEN '0101' AND '1031'.

You can also query multiple years of data. For example, to query October 2022 through February 2023, modify the query to have:

  1. The wildcard 202*.
  2. A _TABLE_SUFFIX condition of _TABLE_SUFFIX BETWEEN '21001' AND '30331'.

User IDs for recent user property changes

The following query shows how to retrieve the user_id and pseudo_user_id of all users who recently changed a specific user property.

users

-- Example: Get the list of user_ids with recent changes to a specific user property.
DECLARE
  UPDATE_LOWER_BOUND_MICROS INT64;

-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE
  REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';

-- Sets the variable for the earliest update time to include. This comes after setting
-- the REPORTING_TIMEZONE so this expression can use that variable.
SET UPDATE_LOWER_BOUND_MICROS = UNIX_MICROS(
    TIMESTAMP_SUB(
      TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, REPORTING_TIMEZONE),
      INTERVAL 14 DAY));

-- Selects users with changes to a specific user property since the lower bound.
SELECT
  users.user_id,
  FORMAT_TIMESTAMP('%F %T',
    TIMESTAMP_MICROS(
      MAX(properties.value.set_timestamp_micros)),
      REPORTING_TIMEZONE) AS max_set_timestamp
FROM
  -- Uses a table prefix to scan all data for 2023. Update the prefix as needed to query a different
  -- date range.
  `PROJECT_ID.analytics_PROPERTY_ID.users_2023*` AS users,
  users.user_properties properties
WHERE
  properties.value.user_property_name = 'job_function'
  AND properties.value.set_timestamp_micros >= UPDATE_LOWER_BOUND_MICROS
GROUP BY
  1;

pseudonymous_users

-- Example: Get the list of pseudo_user_ids with recent changes to a specific user property.
DECLARE
  UPDATE_LOWER_BOUND_MICROS INT64;

-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE
  REPORTING_TIMEZONE STRING DEFAULT 'America/Los_Angeles';

-- Sets the variable for the earliest update time to include. This comes after setting
-- the REPORTING_TIMEZONE so this expression can use that variable.
SET UPDATE_LOWER_BOUND_MICROS = UNIX_MICROS(
    TIMESTAMP_SUB(
      TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, REPORTING_TIMEZONE),
      INTERVAL 14 DAY));

-- Selects users with changes to a specific user property since the lower bound.
SELECT
  users.pseudo_user_id,
  FORMAT_TIMESTAMP('%F %T',
    TIMESTAMP_MICROS(
      MAX(properties.value.set_timestamp_micros)),
      REPORTING_TIMEZONE) AS