Two GA4 User Tables in BigQuery Demystified

When you export GA4 data to BigQuery, you will see one or two user tables, depending on your GA4 implementation. These tables are

1. Pseudonymous Users table - This table contains information about anonymous users. All users on your site or app are anonymous by default. The naming format of this table is pseudonymous_users_YYYYMMDD. Each day of data creates a new table. These tables are listed as pseudonymous_users_(##) and just like other tables the name of this table also contains the date.

   Key things about this table:

   • It contains a row for every pseudonymous identifier.

   • This table is updated when there is a change to data in any one of the fields.

   • Users who have not given consent, are not exported to this table.

   • Known User IDs are not exported to this table (see the User ID table below for this information)

   • The last active timestamp is exported to this table.

2. Users - This table contains information about known users. This table will show up if you enable User ID integration with GA4. The naming format of this table is users_YYYYMMDD and is listed under users_(##).

   Key things about this table:

   • It contains a row for every user ID. D

   • This table is updated when there is a change to data in any one of the fields.

   • Users who have not given consent can be exported to this table if they include a user ID.

   • Pseudouser IDs are not exported to this table, they are contained in the Pseudonymous Users table

   • The last active timestamp is exported to this table.

Data Exported in Pseudonymous Users and User Tables

The daily export in the above tables includes any new users and all the users whose data has changed that day. If you need some examples to see which users will be included in the daily export then check the “Advanced Notes” section below.

Tables Schema

The schema for both tables is very similar, with just a minor difference.

The data in these tables can be grouped into a few major buckets, as listed below:

1. User ID

2. User Info

3. Privacy Info

4. User Properties

5. Device

6. Geo

7. Audiences

8. User Lifetime

9. Predictions

10. Audit

Let’s take a look at what type of data is contained in the major buckets.

User ID

User ID information is stored in three different columns.

• user_id, STRING (date type)

  ID for the User-ID namespace in reporting identity (only available in the User table)

• pseudo_user_id, STRING

  ID for the Pseudonymous namespace (only available in the Pseudonymous Users table only)

• stream_id, INTEGER

  Data-stream ID (only available in the Pseudonymous Users table)

User info

This data contains some important timelines of the user and is stored in the “user_info” column as a record data type. This type of column is called a record type column (or record data type) since it contains a record of information instead of just a single value. I will cover dealing with “records” in future posts. This column contains a record with the following information:

• user_info.last_active_timestamp_micros, INTEGER

  Contains the date of the user's last activity, stored as timestamp in microseconds.

• user_info.user_first_touch_timestamp_micros, INTEGER

  Contains the date of the user's first_open or first_visit event, whichever is earlier (timestamp in microseconds)

• user_info.first_purchase_date, STRING

  Contains the date of the user's first purchase stored in YYYYMMDD format.

 Privacy info