Setup
When you set up BigQuery Export, you have the option to include a daily export of user data.
Data tables
When you export user data, Analytics 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.
- Data for unconsented users is not exported to this table.
- User IDs are not exported to this table.
- Last active timestamp is exported to 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.
- Data for unconsented users can be exported to this table if it includes a user ID.
- Pseudo IDs are not exported to this table
- Last active timestamp is exported to this table.
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.
Schema
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).
Audit
| Field name | Data type | Description |
|---|---|---|
| occurrence_date | STRING | Date when the record change was triggered |
| last_updated_date | STRING | Date when the record was updated in the table |
User
| Field name | Data type | Description |
|---|---|---|
| user_id | STRING | ID for the User-ID namespace in reporting identity (User ID table only) |
| pseudo_user_id | STRING | ID for the Pseudonymous namespace (Pseudo ID table only) |
| stream_id | INTEGER | Data-stream ID (Pseudo ID table only) |
User info
| Field name | Data type | Description |
|---|---|---|
| user_info.last_active_timestamp_micros | INTEGER | Date of the user's last activity (timestamp in microseconds) |
| user_info.user_first_touch_timestamp_micros | INTEGER | Date of the user's first_open or first_visit event, whichever is earlier (timestamp in microseconds) |
| user_info.first_purchase_date | STRING | Date of the user's first purchase (YYYYMMDD) |
Privacy info
| Field name | Data type | Description |
|---|---|---|
| privacy_info | RECORD | Privacy information |
| privacy_info.is_ads_personalization_allowed | STRING |
If a user is eligible for ads personalization, isAdsPersonalizationAllowed returns 'true'. If a user is not eligible for ads personalization, isAdsPersonalizationAllowed returns 'false'. isAdsPersonalizationAllowed returns '(not set)' if Google Analytics is not currently able to return whether this user is eligible for ads personalization; users where isAdsPersonalizationAllowed returns '(not set)' may or may not be eligible for personalized ads. For personalized ads, you should treat users where isAdsPersonalizationAllowed = '(not set)' as isAdsPersonalizationAllowed = 'false' because, in the most general case, some of the '(not set)' rows will include users that are not eligible for ads personalization. Users where isAdsPersonalizationAllowed = 'false' can still be used for non-advertising use cases like A/B testing & data explorations. |
| privacy_info.is_limited_ad_tracking | STRING | The device's Limit Ad Tracking setting. Possible values include: 'true', 'false', and '(not set)'. isLimitedAdTracking returns '(not set)' if Google Analytics is not currently able to return this device's Limit Ad Tracking setting. |
Audiences
| Field name | Data type | Description |
|---|---|---|
| audiences | RECORD | Audience information |
| audiences.id | INTEGER | ID of the audience |
| audiences.name | STRING | Name of the audience |
| audiences.membership_start_timestamp_micros | INTEGER | When the user was first included in the audience (timestamp in microseconds) |
| audiences.membership_expiry_timestamp_micros | INTEGER |
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 | BOOLEAN | true or false based on your NPA settings for events and user-scoped custom dimensions included in your audience definition |
Properties
| Field name | Data type | Description |
|---|---|---|
| user_properties | RECORD | User-property information |
| user_properties.key | STRING | User-property dimension name |
| user_properties.value.string_value | STRING | User-property dimension value |
| user_properties.value.set_timestamp_micros | INTEGER | When the dimension value was last set (timestamp in microseconds) |
Device
| Field name | Data type | Description |
|---|---|---|
| device | RECORD | Device information |
| device.operating_system | STRING | Device operating system |
| device.category | STRING | Category of the device (mobile, tablet, desktop) |
| device.mobile_brand_name | STRING | Device brand name |
| device.mobile_model_name | STRING | Device model name |
| device.unified_screen_name | STRING | Screen name |
Geo
| Field name | Data type | Description |
|---|---|---|
| geo | RECORD | Geographic information |
| geo.city | STRING | City from which events were reported |
| geo.country | STRING | Country from which events were reported |
| geo.continent | STRING | Continent from which events were reported |
| geo.region | STRING | Region from which events were reported |
Lifetime
| Field name | Data type | Description |
|---|---|---|
| user_ltv | RECORD | Lifetime information |
| user_ltv.revenue_in_usd | DOUBLE | Lifetime total revenue (in USD) |
| user_ltv.sessions | INTEGER | Lifetime total number of sessions |
| user_ltv.engagement_time_millis | INTEGER | Lifetime total engagement time (in milliseconds) |
| user_ltv.purchases | INTEGER | Lifetime total number of purchases |
| user_ltv.engaged_sessions | INTEGER | Lifetime total number of engaged sessions |
| user_ltv.session_duration_micros | INTEGER | Lifetime total session duration (in milliseconds) |
Predictions
| Field name | Data type | Description |
|---|---|---|
| predictions | RECORD | Prediction information |
| predictions.in_app_purchase_score_7d | DOUBLE | Probability that a user who was active in the last 28 days will log an in_app_purchase event within the next 7 days |
| predictions.purchase_score_7d | DOUBLE | Probability that a user who was active in the last 28 days will log a purchase event within the next 7 days |
| predictions.churn_score_7d | DOUBLE | Probability that a user who was active on your app or site within the last 7 days will not be active within the next 7 days |
| predictions.revenue_28d_in_usd | FLOAT | Revenue expected (in USD) from all purchase events within the next 28 days from a user who was active in the last 28 days |