[GA4] BigQuery Export schema

This article explains the format and schema of the Google Analytics 4 property data and the Google Analytics for Firebase data that is exported to BigQuery.

Datasets

For each Google Analytics 4 property and each Firebase project that is linked to BigQuery, a single dataset named "analytics_<property_id>" is added to your BigQuery project. Property ID refers to your Analytics Property ID, which you can find in the property settings for your Google Analytics 4 property, and in App Analytics Settings in Firebase. Each Google Analytics 4 property and each app for which BigQuery exporting is enabled will export its data to that single dataset.

Tables

Within each dataset, a table named events_YYYYMMDD is created each day if the Daily export option is enabled.

If the Streaming export option is enabled, a table named events_intraday_YYYYMMDD is created. This table is populated continuously as events are recorded throughout the day. This table is deleted at the end of each day once events_YYYYMMDD is complete.

Not all devices on which events are triggered send their data to Analytics on the same day the events are triggered. To account for this latency, Analytics will update the daily tables (events_YYYYMMDD) with events for those dates for up to three days after the dates of the events. Events will have the correct time stamp regardless of arriving late. Events that arrive after that three-day window are not recorded.

 

If you are using BigQuery sandbox, there is no intraday import of events, and additional limits apply.

Upgrade from the sandbox if you want intraday imports.

Columns

Each column in the events_YYYYMMDD table represents an event-specific parameter. Note that some parameters are nested within RECORDS, and some RECORDS such as items and event_params are repeatable. Table columns are described below.

event

The event fields contain information that uniquely identifies an event.
Field name Data type Description
event_date STRING The date when the event was logged (YYYYMMDD format in the registered timezone of your app).
event_timestamp INTEGER The time (in microseconds, UTC) when the event was logged on the client.
event_previous_timestamp INTEGER The time (in microseconds, UTC) when the event was previously logged on the client.
event_name STRING The name of the event.
event_value_in_usd FLOAT The currency-converted value (in USD) of the event's "value" parameter.
event_bundle_sequence_id INTEGER The sequential ID of the bundle in which these events were uploaded.
event_server_timestamp_offset INTEGER Timestamp offset between collection time and upload time in micros.

event_params RECORD

The event_params RECORD can store campaign-level and contextual event parameters as well as any user-defined event parameters. The event_params RECORD is repeated for each key that is associated with an event.

The set of parameters stored in the event_params RECORD is unique to each implementation. To see the complete list of event parameters for your implementation, query the event parameter list

Field name Data type Description
event_params.key STRING The name of the event parameter.
event_params.value RECORD A record containing the event parameter's value.
event_params.value.string_value STRING If the event parameter is represented by a string, such as a URL or campaign name, it is populated in this field.
event_params.value.int_value INTEGER If the event parameter is represented by an integer, it is populated in this field.
event_params.value.double_value FLOAT If the event parameter is represented by a double value, it is populated in this field.
event_params.value.float_value FLOAT If the event parameter is represented by a floating point value, it is populated in this field. This field is not currently in use.

item_params RECORD

The item_params RECORD can store item parameters as well as any user-defined item parameters. The set of parameters stored in the item_params RECORD is unique to each implementation.

Field name Data type Description
item_params.key STRING The name of the item parameter.
item_params.value RECORD A record containing the item parameter’s value.
item_params.value.string_value STRING If the item parameter is represented by a string, it is populated in this field.
item_params.value.int_value INTEGER If the item parameter is represented by an integer, it is populated in this field.
item_params.value.double_value FLOAT If the item parameter is represented by a double value, it is populated in this field.
item_params.value.float_value FLOAT If the item parameter is represented by a floating point value, it is populated in this field.

user

The user fields contain information that uniquely identifies the user associated with the event.
Field name Data type Description
is_active_user BOOLEAN

Whether the user was active (True) or inactive (False) at any point in the calendar day

Included in only the daily tables (events_YYYYMMDD).

user_id STRING The unique ID assigned to a user.
user_pseudo_id STRING The pseudonymous id (e.g., app instance ID) for the user.
user_first_touch_timestamp INTEGER The time (in microseconds) at which the user first opened the app or visited the site.

privacy_info fields

The privacy_info fields contain information based on the consent status of a user when consent mode is enabled.
Field name Data type Description
privacy_info.ads_storage STRING

Whether ad targeting is enabled for a user.

Possible values: Yes, No, Unset

privacy_info.analytics_storage STRING

Whether Analytics storage is enabled for the user.

Possible values: Yes, No, Unset

privacy_info.uses_transient_token STRING

Whether a web user has denied Analytics storage and the developer has enabled measurement without cookies based on transient tokens in server data.

Possible values: Yes, No, Unset

user_properties RECORD

The user_properties RECORD contains any user properties that you have set. It is repeated for each key that is associated with a user.
Field name Data type Description
user_properties.key STRING The name of the user property.
user_properties.value RECORD A record for the user property value.
user_properties.value.string_value STRING The string value of the user property.
user_properties.value.int_value INTEGER The integer value of the user property.
user_properties.value.double_value FLOAT The double value of the user property.
user_properties.value.float_value FLOAT This field is currently unused.
user_properties.value.set_timestamp_micros INTEGER The time (in microseconds) at which the user property was last set.

user_ltv RECORD

The user_ltv RECORD contains Lifetime Value information about the user. This RECORD is not populated in intraday tables.
Field name Data type Description
user_ltv.revenue FLOAT The Lifetime Value (revenue) of the user. This field is not populated in intraday tables.
user_ltv.currency STRING The Lifetime Value (currency) of the user. This field is not populated in intraday tables.

device

The device RECORD contains information about the device from which the event originated.
Field name Data type Description
device.category STRING The device category (mobile, tablet, desktop).
device.mobile_brand_name STRING The device brand name.
device.mobile_model_name STRING The device model name.
device.mobile_marketing_name STRING The device marketing name.
device.mobile_os_hardware_model STRING The device model information retrieved directly from the operating system.
device.operating_system STRING The operating system of the device.
device.operating_system_version STRING The OS version.
device.vendor_id STRING IDFV (present only if IDFA is not collected).
device.advertising_id STRING Advertising ID/IDFA.
device.language STRING The OS language.
device.time_zone_offset_seconds INTEGER The offset from GMT in seconds.
device.is_limited_ad_tracking BOOLEAN

The device's Limit Ad Tracking setting.

On iOS14+, returns false if the IDFA is non-zero.

device.web_info.browser STRING The browser in which the user viewed content.
device.web_info.browser_version STRING The version of the browser in which the user viewed content.
device.web_info.hostname STRING The hostname associated with the logged event.

geo

The geo RECORD contains information about the geographic location where the event was initiated.
Field name Data type Description
geo.continent STRING The continent from which events were reported, based on IP address.
geo.sub_continent STRING The subcontinent from which events were reported, based on IP address.
geo.country STRING The country from which events were reported, based on IP address.
geo.region STRING The region from which events were reported, based on IP address.
geo.metro STRING The metro from which events were reported, based on IP address.
geo.city STRING The city from which events were reported, based on IP address.

app_info

The app_info RECORD contains information about the app in which the event was initiated.
Field name Data type Description
app_info.id STRING The package name or bundle ID of the app.
app_info.firebase_app_id STRING The Firebase App ID associated with the app
app_info.install_source STRING The store that installed the app.
app_info.version STRING The app's versionName (Android) or short bundle version.

collected_traffic_source

The collected_traffic_source RECORD contains the traffic source data that was collected with the event.

Field name Data type Description
manual_campaign_id STRING The manual campaign id (utm_id) that was collected with the event.
manual_campaign_name STRING The manual campaign name (utm_campaign) that was collected with the event.
manual_source STRING The manual campaign source (utm_source) that was collected with the event. Also includes parsed parameters from referral params, not just UTM values. 
manual_medium STRING The manual campaign medium (utm_medium) that was collected with the event. Also includes parsed parameters from referral params, not just UTM values.
manual_term STRING The manual campaign keyword/term (utm_term) that was collected with the event.
manual_content STRING The additional manual campaign metadata (utm_content) that was collected with the event.
gclid STRING The Google click identifier that was collected with the event.
dclid STRING The Google Marketing Platform (GMP) identifier that was collected with the event.
srsltid  STRING The Google Merchant Center identifier that was collected with the event.

traffic_source

The traffic_source RECORD contains information about the traffic source that first acquired the user. This record is not populated in intraday tables.

Note: The traffic_source values do not change if the user interacts with subsequent campaigns after installation.

Field name Data type Description
traffic_source.name STRING Name of the marketing campaign that first acquired the user. This field is not populated in intraday tables.
traffic_source.medium STRING Name of the medium (paid search, organic search, email, etc.) that first acquired the user. This field is not populated in intraday tables.
traffic_source.source STRING Name of the network that first acquired the user. This field is not populated in intraday tables.

stream and platform

The stream and platform fields contain information about the stream and the app platform.
Field name Data type Description
stream_id STRING The numeric ID of the data stream from which the event originated.
platform STRING The data stream platform (Web, IOS or Android) from which the event originated.

ecommerce

This ecommerce RECORD contains information about any ecommerce events that have been setup on a website or app.
Field name Data type Description
ecommerce.total_item_quantity INTEGER Total number of items in this event, which is the sum of items.quantity.
ecommerce.purchase_revenue_in_usd FLOAT Purchase revenue of this event, represented in USD with standard unit. Populated for purchase event only.
ecommerce.purchase_revenue FLOAT Purchase revenue of this event, represented in local currency with standard unit. Populated for purchase event only.
ecommerce.refund_value_in_usd FLOAT The amount of refund in this event, represented in USD with standard unit. Populated for refund event only.
ecommerce.refund_value FLOAT The amount of refund in this event, represented in local currency with standard unit. Populated for refund event only.
ecommerce.shipping_value_in_usd FLOAT The shipping cost in this event, represented in USD with standard unit.
ecommerce.shipping_value FLOAT The shipping cost in this event, represented in local currency.
ecommerce.tax_value_in_usd FLOAT The tax value in this event, represented in USD with standard unit.
ecommerce.tax_value FLOAT The tax value in this event, represented in local currency with standard unit.
ecommerce.transaction_id STRING The transaction ID of the ecommerce transaction.
ecommerce.unique_items INTEGER The number of unique items in this event, based on item_id, item_name, and item_brand.

items

The items RECORD contains information about items included in an event. It is repeated for each item.
Field name Data type Description
items.item_id STRING The ID of the item.
items.item_name STRING The name of the item.
items.item_brand STRING The brand of the item.
items.item_variant STRING The variant of the item.
items.item_category STRING The category of the item.
items.item_category2 STRING The sub category of the item.
items.item_category3 STRING The sub category of the item.
items.item_category4 STRING The sub category of the item.
items.item_category5 STRING The sub category of the item.
items.price_in_usd FLOAT The price of the item, in USD with standard unit.
items.price FLOAT The price of the item in local currency.
items.quantity INTEGER The quantity of the item. Quantity set to 1 if not specified.
items.item_revenue_in_usd FLOAT The revenue of this item, calculated as price_in_usd * quantity. It is populated for purchase events only, in USD with standard unit.
items.item_revenue FLOAT The revenue of this item, calculated as price * quantity. It is populated for purchase events only, in local currency with standard unit.
items.item_refund_in_usd FLOAT The refund value of this item, calculated as price_in_usd * quantity. It is populated for refund events only, in USD with standard unit.
items.item_refund FLOAT The refund value of this item, calculated as price * quantity. It is populated for refund events only, in local currency with standard unit.
items.coupon STRING Coupon code applied to this item.
items.affiliation STRING A product affiliation to designate a supplying company or brick and mortar store location.
items.location_id STRING The location associated with the item.
items.item_list_id STRING The ID of the list in which the item was presented to the user.
items.item_list_name STRING The name of the list in which the item was presented to the user.
Items.item_list_index STRING The position of the item in a list.
items.promotion_id STRING The ID of a product promotion.
items.promotion_name STRING The name of a product promotion.
items.creative_name STRING The name of a creative used in a promotional spot.
items.creative_slot STRING The name of a creative slot.

item_params RECORD

The item_params RECORD stores the custom item parameters that you defined. Note that predefined item parameters like item_id, item_name etc, are not included here, instead they are exported as explicit fields.

The set of parameters stored in the item_params RECORD is unique to each implementation. To learn more about ecommerce implementations and the Google Analytics 4 items array, see Measure ecommerce.

Field name Data type Description
items.item_params.key STRING The name of the item parameter.
items.item_params.value RECORD A record containing the item parameter’s value.
items.item_params.value.string_value STRING If the item parameter is represented by a string, it is populated in this field.
items.item_params.value.int_value INTEGER If the item parameter is represented by an integer, it is populated in this field.
items.item_params.value.double_value FLOAT If the item parameter is represented by a double value, it is populated in this field.
items.item_params.value.float_value FLOAT If the item parameter is represented by a floating point value, it is populated in this field.

Rows

Data for a single event may be represented in one or multiple rows, depending on whether it contains repeated RECORDS. A page_view event with multiple event_params, for example, would look similar to the following table. The initial row contains the event name, date, timestamp and other non-repeated data items. The event_params RECORD is repeated for each parameter associated with the event. These repeated RECORDS are populated in subsequent rows directly under the initial event row.

event_date event_timestamp event_name event_params.key event_params_value.string_value
20220222 1643673600483790 page_view page_location https://example.com
      page_title Home
      medium referral
      source google
      page_referrer https://www.google.com
      <parameters...> <values...>

 

This event data is displayed as follows in the GA4 user interface.

Considerations for updating SDKs to Android 17.2.5 (or later) and/or iOS: 16.20.0 (or later)

If you used prior versions of either SDK and are planning to upgrade to Android 17.2.5 (or later) and/or iOS 16.20.0 (or later), you should consider the following:

  • To use the current BigQuery Export schema, you must upgrade your SDK to Android 17.2.5 (or later) and/or iOS 16.20.0 (or later).  When using earlier SDK versions, exported data will reflect the old schema
  • Upon upgrade and from that point forward, you will only be able to access item data within the repeated items array; item data will no longer be available in standard event parameters as it has been prior to these SDK versions.
  • This change was made to support multiple-product analysis.
  • You may need to adjust your references to item/product data as a result.
Use this script to migrate existing BigQuery datasets from the old export schema to the new one
  1. Log in to Cloud Platform Console >: Manager resources page.
  2. Open the project whose data you want to migrate, and click Activate Google Cloud Shell at the top of the page.
  3. When the shell opens, copy the script below to a file named migration_script.sql:
    1. Issue the command cat > migration_script.sql
    2. Copy and paste the script below into the shell.
    3. Press Ctrl+D to save and exit.
    Script (migration_script.sql):
      SELECT
      @date AS event_date,
      event.timestamp_micros AS event_timestamp,
      event.previous_timestamp_micros AS event_previous_timestamp,
      event.name AS event_name,
      event.value_in_usd  AS event_value_in_usd,
       user_dim.bundle_info.bundle_sequence_id AS event_bundle_sequence_id,
      user_dim.bundle_info.server_timestamp_offset_micros as event_server_timestamp_offset,
      (
      SELECT
        ARRAY_AGG(STRUCT(event_param.key AS key,
            STRUCT(event_param.value.string_value AS string_value,
              event_param.value.int_value AS int_value,
              event_param.value.double_value AS double_value, 
              event_param.value.float_value AS float_value) AS value))
      FROM
        UNNEST(event.params) AS event_param) AS event_params,
      user_dim.first_open_timestamp_micros AS user_first_touch_timestamp,
      user_dim.user_id AS user_id,
      user_dim.app_info.app_instance_id AS user_pseudo_id,
      "" AS stream_id,
      user_dim.app_info.app_platform AS platform,
      STRUCT( user_dim.ltv_info.revenue AS revenue,
        user_dim.ltv_info.currency AS currency ) AS user_ltv,
      STRUCT( user_dim.traffic_source.user_acquired_campaign AS name,
          user_dim.traffic_source.user_acquired_medium AS medium,
          user_dim.traffic_source.user_acquired_source AS source ) AS traffic_source,
      STRUCT( user_dim.geo_info.continent AS continent,
        user_dim.geo_info.country AS country,
        user_dim.geo_info.region AS region,
        user_dim.geo_info.city AS city ) AS geo,
      STRUCT( user_dim.device_info.device_category AS category,
        user_dim.device_info.mobile_brand_name,
        user_dim.device_info.mobile_model_name,
        user_dim.device_info.mobile_marketing_name,
        user_dim.device_info.device_model AS mobile_os_hardware_model,
        @platform AS operating_system,
        user_dim.device_info.platform_version AS operating_system_version,
        user_dim.device_info.device_id AS vendor_id,
        user_dim.device_info.resettable_device_id AS advertising_id,
        user_dim.device_info.user_default_language AS language,
        user_dim.device_info.device_time_zone_offset_seconds AS time_zone_offset_seconds,
        IF(user_dim.device_info.limited_ad_tracking, "Yes", "No") AS is_limited_ad_tracking ) AS device,
      STRUCT( user_dim.app_info.app_id AS id,
        @firebase_app_id  AS firebase_app_id,
        user_dim.app_info.app_version AS version,
        user_dim.app_info.app_store AS install_source ) AS app_info,
      (
      SELECT
        ARRAY_AGG(STRUCT(user_property.key AS key,
            STRUCT(user_property.value.value.string_value AS string_value,
              user_property.value.value.int_value AS int_value,
              user_property.value.value.double_value AS double_value,
              user_property.value.value.float_value AS float_value,
              user_property.value.set_timestamp_usec AS set_timestamp_micros ) AS value))
      FROM
        UNNEST(user_dim.user_properties) AS user_property) AS user_properties
    FROM
      `SCRIPT_GENERATED_TABLE_NAME`,
      UNNEST(event_dim) AS event
      
  4. Open a new shell, and copy the bash script below to a file named migration.sh:
    1. Issue the command cat > migration.sh
    2. Copy and paste the script below into the shell.
    3. Press Ctrl+D to save and exit.
    Modify the following script to include your Analytics property ID, BigQuery project ID, Firebase app ID, BigQuery dataset name, and the start and end dates of the data you want.
    Script (migration.sh):
    # Analytics Property ID for the Project. Find this in Analytics Settings in Firebase.
    PROPERTY_ID=your Analytics property ID
    
    # Bigquery Export Project.
    BQ_PROJECT_ID="your BigQuery Project ID" (e.g., "firebase-public-project")
    
    # Firebase App ID for the app.
    FIREBASE_APP_ID="your Firebase App ID" (e.g., "1:300830567303:ios:09b1ab1d3ca29bda")
    
    # Dataset to import from.
    BQ_DATASET="name of BigQuery dataset you want to import from" (e.g., "com_firebase_demo_IOS")
    
    # Platform
    PLATFORM="platform of the app. ANDROID or IOS"
    
    # Date range for which you want to run migration, [START_DATE,END_DATE] inclusive.
    START_DATE=20180324
    END_DATE=20180327
    
    # Do not modify the script below, unless you know what you are doing :)
    startdate=$(date -d"$START_DATE"  +%Y%m%d) || exit -1
    enddate=$(date -d"$END_DATE"  +%Y%m%d) || exit -1
    
    # Iterate through the dates.
    DATE="$startdate"
    while [ "$DATE" -le "$enddate" ]; do
    
            # BQ table constructed from above params.
            BQ_TABLE="$BQ_PROJECT_ID.$BQ_DATASET.app_events_$DATE"
    
            echo "Migrating $BQ_TABLE"
    
            cat migration_script.sql | sed -e "s/SCRIPT_GENERATED_TABLE_NAME/$BQ_TABLE/g" | bq query \
            --debug_mode \
            --allow_large_results \
            --noflatten_results \
            --use_legacy_sql=False \
            --destination_table analytics_$PROPERTY_ID.events_$DATE \
            --batch \
            --append_table \
            --parameter=firebase_app_id::$FIREBASE_APP_ID \
            --parameter=date::$DATE \
            --parameter=platform::$PLATFORM \
            --project_id=$BQ_PROJECT_ID
    
    
            temp=$(date -I -d "$DATE + 1 day")
            DATE=$(date -d "$temp" +%Y%m%d)
    
    done
    exit
    
    # END OF SCRIPT
    
    
  5. Open a new shell, and issue the command bash migration.sh

 

Old export schema

user_dim

Field Name Data Type Description
user_dim RECORD A record of user dimensions.
user_dim.user_id STRING The user ID set via the setUserId API.
user_dim.first_open_timestamp_micros INTEGER The time (in microseconds) at which the user first opened the app.
user_dim.user_properties RECORD A repeated record of user properties set with the setUserProperty API.
user_dim.user_properties.key STRING The name of the user property
user_dim.user_properties.value RECORD A record for information about the user property.
user_dim.user_properties.value.value RECORD A record for the user property value.
user_dim.user_properties.value.value.string_value STRING The string value of the user property.
user_dim.user_properties.value.value.int_value INTEGER The integer value of the user property.
user_dim.user_properties.value.value.double_value FLOAT The double value of the user property.
user_dim.user_properties.value.set_timestamp_usec INTEGER The time (in microseconds) at which the user property was last set.
user_dim.user_properties.value.index INTEGER The index (0-24) of the user property.
user_dim.device_info RECORD A record of device information.
user_dim.device_info.device_category STRING The device category (mobile, tablet, desktop).
user_dim.device_info.mobile_brand_name STRING The device brand name.
user_dim.device_info.mobile_model_name STRING The device model name.
user_dim.device_info.mobile_marketing_name STRING The device marketing name.
user_dim.device_info.device_model STRING The device model.
user_dim.device_info.platform_version STRING The OS version.
user_dim.device_info.device_id STRING IDFV (present only if IDFA is not available).
user_dim.device_info.resettable_device_id STRING Advertising ID/IDFA.
user_dim.device_info.user_default_language STRING The OS language.
user_dim.device_info.device_time_zone_offset_seconds INTEGER The offset from GMT in seconds.
user_dim.device_info.limited_ad_tracking BOOLEAN The device's Limit Ad Tracking setting.
user_dim.geo_info RECORD A record of the user's geographic information.
user_dim.geo_info.continent STRING The continent from which events were reported, based on IP address.
user_dim.geo_info.country STRING The country from which events were reported, based on IP address.
user_dim.geo_info.region STRING The region from which events were reported, based on IP address.
user_dim.geo_info.city STRING The city from which events were reported, based on IP address.
user_dim.app_info RECORD A record of information on the app.
user_dim.app_info.app_version STRING The app's versionName (Android) or short bundle version.
user_dim.app_info.app_instance_id STRING The unique id for this instance of the app.
user_dim.app_info.app_store STRING The store which installed this app.
user_dim.app_info.app_platform STRING The platform on which this app is running.
user_dim.traffic_source RECORD Name of the traffic source used to acquired the user. This field is not populated in intraday tables.
user_dim.traffic_source.user_acquired_campaign STRING The name of the marketing campaign which acquired the user. This field is not populated in intraday tables.
user_dim.traffic_source.user_acquired_medium STRING The name of the medium (paid search, organic search, email, etc.) which acquired the user. This field is not populated in intraday tables.
user_dim.traffic_source.user_acquired_source STRING The name of the network which acquired the user. This field is not populated in intraday tables.
user_dim.bundle_info RECORD A record of information regarding the bundle in which these events were uploaded.
user_dim.bundle_info.bundle_sequence_id INTEGER The sequential id of the bundle in which these events were uploaded.
user_dim.ltv_info RECORD A record of Lifetime Value information about this user. This field is not populated in intraday tables.
user_dim.ltv_info.revenue FLOAT The Lifetime Value (revenue) of this user. This field is not populated in intraday tables.
user_dim.ltv_info.currency STRING The Lifetime Value (currency) of this user. This field is not populated in intraday tables.
 

event_dim

Field Name Data Type Description
event_dim RECORD A repeated record of information pertaining to events in this bundle.
event_dim.date STRING The date on which this event was logged (YYYYMMDD format in the registered timezone of your app.)
event_dim.name STRING The name of this event.
event_dim.params RECORD A repeated record of the parameters associated with this event.
event_dim.params.key STRING The event parameter's key.
event_dim.params.value RECORD A record of the event parameter's value.
event_dim.params.value.string_value STRING The string value of the event parameter.
event_dim.params.value.int_value INTEGER The integer value of the event parameter.
event_dim.params.value.double_value FLOAT The double value of the event parameter.
event_dim.timestamp_micros INTEGER The time (in microseconds, UTC) at which this event was logged on the client.
event_dim.previous_timestamp_micros INTEGER The time (in microseconds, UTC) at which this event was previously logged on the client.

Was this helpful?

How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
1981468241214683264
true
Search Help Center
true
true
true
true
true
69256
false
false