Google Analytics 4 (GA4) has two main components, events and users. In the past, I showed you how you can send event data from GA4 to BigQuery using a built-in connector.
Now GA4 also allows you to send the user data through the existing BigQuery connector.
This connection enables a new set of tables, where each row represents a unique user. The table contains useful user-scoped fields for modeling and analysis, including a user's:
- Current audiences
- Lifetime value (LTV)
- Predictive scores for purchase and churn probabilities
- User properties
- Device and geographic details
To get started, enable user data export from the BigQuery Links page in the GA4 Admin.
Click on "BigQuery Link" under the "Product Links" section in the admin panel.
Scroll down on the page and check the box under "User data" to enable the daily export of the user properties.
The User Data export feature is a valuable tool for businesses that need to export user data from GA4. The feature allows you to export a subset of your user data, which can be helpful for compliance purposes or for data analysis.
Data tables created for User Properties
When you enable the user data export, it creates two new tables in your BigQuery project:
- Pseudo ID
- Contains a row for every pseudonymous identifier. Data for a user is updated when there is a change to one of the fields.
- The last active timestamp is exported to this table.
- Note: The user IDs associated with the Pseudo IDs are not exported to this table.
- Any users who do not consent to cookies are not available in this table.
- User ID
- Contains a row for every user ID. Data for a user is updated when there is a change to one of the fields.
- The last active timestamp is exported to this table.
- Note: The Pseudo IDs associated with the UserIDs are not exported in this table.
- The data for users who do not consent to cookies can be exported to this table if it includes a user ID.
Active users vs. all users in user-data export
This export includes any user whose data has changed that day. For example, if a user initiates a session and thus increments the lifetime value of user_ltv.sessions, then that user is included in the export. If a user is dropped from an audience because on this day they no longer match the include condition for the audience (e.g., they haven't made a purchase for the last 7 days), then that user's data has changed and they are included in the export.
Because users are included based on changes in data and not only on activity, the number of users in the export may exceed the value of the Active users metric for a given day or date range. (The Active users metric appears as Users in Reports.)
If you want to query your exported data to get just the number of active users, you can use some of the example queries outlined in our developer documentation.
User Data Schema in BigQuery
The following sections describe the user data that Analytics exports to the Pseudo ID and User ID tables (subject to the differences enumerated in the section above).
- occurrence_date - Date when the record change was triggered
- last_updated_date - Date when the record was updated in the table
- user_id - ID for the User-ID namespace in reporting identity (User ID table only)
- pseudo_user_id - ID for the Pseudonymous namespace (Pseudo ID table only)
- stream_id - Data-stream ID (Pseudo ID table only)
- user_info.last_active_timestamp_micros - Date of the user's last activity (timestamp in microseconds)
- user_info.last_active_timestamp_micros - Date of the user's first_open or first_visit event, whichever is earlier (timestamp in microseconds)
- user_info.user_first_touch_timestamp_micros
- user_info.first_purchase_date
- audiences (record)
- audiences.id
- audiences.name
- audiences.membership_start_timestamp_micros - When the user was first included in the audience (timestamp in microseconds)
- audiences.membership_expiry_timestamp_micros - When the user's audience membership will expire (timestamp in microseconds). Membership duration is reset when new activity requalifies the user for the audience
- audience.npa - true or false based on your NPA settings for events and user-scoped custom dimensions included in your audience definition
- user_properties - record that key and string values
- device record that has operating system, category, brand_name, model_name and unified_scree_name
- Gero record that has city, country, continent and region
- lifetime value record
- predictions record
Need help with GA4?
Contact us at support@optizent.com or fill out the form at the Contact Us page
Learn BigQuery with GA4 data
We have a complete BigQuery with GA4 data course. Check it on Optizent Academy at BigQuery for Marketers and Marketing Analysts with GA4
If you would like to have you entire team trained on GA4 and BigQuery then reach out to us at support@optizent.com or fill out the contact Optizent form.