[GA4] Skema BigQuery Export

RECORD event_params

RECORD item_params

Kolom privacy_info

RECORD user_properties

RECORD user_ltv

RECORD item_params

Jika Anda menggunakan versi lama dari salah satu SDK tersebut dan berencana mengupgrade ke Android 17.2.5 (atau yang lebih baru) dan/atau iOS 16.20.0 (atau yang lebih baru), Anda harus mempertimbangkan hal berikut:

• Untuk menggunakan skema BigQuery Export saat ini, Anda harus mengupgrade SDK ke Android 17.2.5 (atau yang lebih baru) dan/atau iOS 16.20.0 (atau yang lebih baru). Jika menggunakan versi SDK yang lebih lama, data yang diekspor akan mencerminkan skema lama.
• Setelah mengupgrade dan seterusnya, Anda hanya dapat mengakses data item dalam array item berulang; data item tidak akan tersedia lagi dalam parameter peristiwa standar seperti sebelum versi SDK tersebut.
• Perubahan ini dibuat untuk mendukung analisis multiproduk.
• Akibatnya, Anda mungkin perlu menyesuaikan referensi ke data item/produk.

1. Login ke halaman Cloud Platform Console >: Manage resources.
2. Buka project yang datanya ingin Anda migrasikan, lalu klik Activate Google Cloud Shell di bagian atas halaman.
3. Saat shell terbuka, salin skrip di bawah ke file bernama migration_script.sql:
   1. Berikan perintah cat > migration_script.sql
   2. Salin dan tempel skrip di bawah ke dalam shell.
   3. Tekan Ctrl + D untuk menyimpan dan keluar.
   Skrip (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. Buka shell baru, dan salin skrip bash di bawah ke file bernama migration.sh:
   1. Berikan perintah cat > migration.sh
   2. Salin dan tempel skrip di bawah ke dalam shell.
   3. Tekan Ctrl + D untuk menyimpan dan keluar.
   Ubah skrip berikut untuk menyertakan ID properti Analytics Anda, project ID BigQuery, ID aplikasi Firebase, nama set data BigQuery, serta tanggal mulai dan akhir data yang Anda inginkan.
   Skrip (migration.sh):

   # ID Properti Analytics untuk Project. Temukan ini di Setelan Analytics di Firebase.
   PROPERTY_ID=ID Properti Analytics Anda
   
   # Project BigQuery Export.
   BQ_PROJECT_ID="ID Project BigQuery Anda" (mis., "firebase-public-project")
   
   # ID Aplikasi Firebase untuk aplikasi.
   FIREBASE_APP_ID="ID Aplikasi Firebase Anda" (mis., "1:300830567303:ios:09b1ab1d3ca29bda")
   
   # Set data sumber impor.
   BQ_DATASET="nama set data BigQuery yang ingin Anda impor" (mis., "com_firebase_demo_IOS")
   
   # Platform
   PLATFORM="platform aplikasi. ANDROID atau IOS"
   
   # Rentang tanggal yang diinginkan untuk menjalankan migrasi, [START_DATE,END_DATE] inklusif.
   START_DATE=20180324
   END_DATE=20180327
   
   # Jangan ubah skrip di bawah, kecuali jika Anda memahami apa yang Anda lakukan :)
   startdate=$(date -d"$START_DATE"  +%Y%m%d) || exit -1
   enddate=$(date -d"$END_DATE"  +%Y%m%d) || exit -1
   
   # Iterasikan ke seluruh tanggal.
   DATE="$startdate"
   while [ "$DATE" -le "$enddate" ]; do
   
           # Tabel BQ yang dibuat dari parameter di atas.
           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
   
   # AKHIR SKRIP
   
   

5. Buka shell baru, dan masukkan perintah bash migration.sh

user_dim

event_dim