[GA4] BigQuery Export schema

event_params RECORD

item_params RECORD

privacy_info fields

user_properties RECORD

user_ltv RECORD

item_params RECORD

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.

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

user_dim

event_dim