BigQueryスキーマが微妙に異なる(GA4とApp+Web)

アナリティクス

前回、Googleアナリティクス4からBigQueryへExportする手順を紹介しました。

Googleアナリティクスの旧App+WebでもBigQueryへExportできていましたが、今回の手順でExportする場合とで少々違う点がありますので紹介します。

GA4からExportする場合のスキーマ

GA4から直接Exportする場合のBigQueryのスキーマは以下になります。(2020年10月21日時点)

Field nameData typeDescription
event_dateSTRINGNULLABLE
event_timestampINTEGERNULLABLE
event_nameSTRINGNULLABLE
event_paramsRECORDREPEATED
event_params.keySTRINGNULLABLE
event_params.valueRECORDNULLABLE
event_params.value.string_valueSTRINGNULLABLE
event_params.value.int_valueINTEGERNULLABLE
event_params.value.float_valueFLOATNULLABLE
event_params.value.double_valueFLOATNULLABLE
event_previous_timestampINTEGERNULLABLE
event_value_in_usdFLOATNULLABLE
event_bundle_sequence_idINTEGERNULLABLE
event_server_timestamp_offsetINTEGERNULLABLE
user_idSTRINGNULLABLE
user_pseudo_idSTRINGNULLABLE
user_propertiesRECORDREPEATED
user_properties.keySTRINGNULLABLE
user_properties.valueRECORDNULLABLE
user_properties.value.string_valueSTRINGNULLABLE
user_properties.value.int_valueINTEGERNULLABLE
user_properties.value.float_valueFLOATNULLABLE
user_properties.value.double_valueFLOATNULLABLE
user_properties.value.set_timestamp_microsINTEGERNULLABLE
user_first_touch_timestampINTEGERNULLABLE
user_ltvRECORDNULLABLE
user_ltv.revenueFLOATNULLABLE
user_ltv.currencySTRINGNULLABLE
deviceRECORDNULLABLE
device.categorySTRINGNULLABLE
device.mobile_brand_nameSTRINGNULLABLE
device.mobile_model_nameSTRINGNULLABLE
device.mobile_marketing_nameSTRINGNULLABLE
device.mobile_os_hardware_modelSTRINGNULLABLE
device.operating_systemSTRINGNULLABLE
device.operating_system_versionSTRINGNULLABLE
device.vendor_idSTRINGNULLABLE
device.advertising_idSTRINGNULLABLE
device.languageSTRINGNULLABLE
device.is_limited_ad_trackingSTRINGNULLABLE
device.time_zone_offset_secondsINTEGERNULLABLE
device.browserSTRINGNULLABLE
device.browser_versionSTRINGNULLABLE
device.web_infoRECORDNULLABLE
device.web_info.browserSTRINGNULLABLE
device.web_info.browser_versionSTRINGNULLABLE
device.web_info.hostnameSTRINGNULLABLE
geoRECORDNULLABLE
geo.continentSTRINGNULLABLE
geo.countrySTRINGNULLABLE
geo.regionSTRINGNULLABLE
geo.citySTRINGNULLABLE
geo.sub_continentSTRINGNULLABLE
geo.metroSTRINGNULLABLE
app_infoRECORDNULLABLE
app_info.idSTRINGNULLABLE
app_info.versionSTRINGNULLABLE
app_info.install_storeSTRINGNULLABLE
app_info.firebase_app_idSTRINGNULLABLE
app_info.install_sourceSTRINGNULLABLE
traffic_sourceRECORDNULLABLE
traffic_source.nameSTRINGNULLABLE
traffic_source.mediumSTRINGNULLABLE
traffic_source.sourceSTRINGNULLABLE
stream_idSTRINGNULLABLE
platformSTRINGNULLABLE
event_dimensionsRECORDNULLABLE
event_dimensions.hostnameSTRINGNULLABLE
ecommerceRECORDNULLABLE
ecommerce.total_item_quantityINTEGERNULLABLE
ecommerce.purchase_revenue_in_usdFLOATNULLABLE
ecommerce.purchase_revenueFLOATNULLABLE
ecommerce.refund_value_in_usdFLOATNULLABLE
ecommerce.refund_valueFLOATNULLABLE
ecommerce.shipping_value_in_usdFLOATNULLABLE
ecommerce.shipping_valueFLOATNULLABLE
ecommerce.tax_value_in_usdFLOATNULLABLE
ecommerce.tax_valueFLOATNULLABLE
ecommerce.unique_itemsINTEGERNULLABLE
ecommerce.transaction_idSTRINGNULLABLE
itemsRECORDREPEATED
items.item_idSTRINGNULLABLE
items.item_nameSTRINGNULLABLE
items.item_brandSTRINGNULLABLE
items.item_variantSTRINGNULLABLE
items.item_categorySTRINGNULLABLE
items.item_category2STRINGNULLABLE
items.item_category3STRINGNULLABLE
items.item_category4STRINGNULLABLE
items.item_category5STRINGNULLABLE
items.price_in_usdFLOATNULLABLE
items.priceFLOATNULLABLE
items.quantityINTEGERNULLABLE
items.item_revenue_in_usdFLOATNULLABLE
items.item_revenueFLOATNULLABLE
items.item_refund_in_usdFLOATNULLABLE
items.item_refundFLOATNULLABLE
items.couponSTRINGNULLABLE
items.affiliationSTRINGNULLABLE
items.location_idSTRINGNULLABLE
items.item_list_idSTRINGNULLABLE
items.item_list_nameSTRINGNULLABLE
items.item_list_indexSTRINGNULLABLE
items.promotion_idSTRINGNULLABLE
items.promotion_nameSTRINGNULLABLE
items.creative_nameSTRINGNULLABLE
items.creative_slotSTRINGNULLABLE

一方、旧App+WebからFirebaseを使用してExportする場合のスキーマは以下になります。

Field nameData typeDescription
event_dateSTRINGNULLABLE
event_timestampINTEGERNULLABLE
event_nameSTRINGNULLABLE
event_paramsRECORDREPEATED
event_params.keySTRINGNULLABLE
event_params.valueRECORDNULLABLE
event_params.value.string_valueSTRINGNULLABLE
event_params.value.int_valueINTEGERNULLABLE
event_params.value.float_valueFLOATNULLABLE
event_params.value.double_valueFLOATNULLABLE
event_previous_timestampINTEGERNULLABLE
event_value_in_usdFLOATNULLABLE
event_bundle_sequence_idINTEGERNULLABLE
event_server_timestamp_offsetINTEGERNULLABLE
user_idSTRINGNULLABLE
user_pseudo_idSTRINGNULLABLE
user_propertiesRECORDREPEATED
user_properties.keySTRINGNULLABLE
user_properties.valueRECORDNULLABLE
user_properties.value.string_valueSTRINGNULLABLE
user_properties.value.int_valueINTEGERNULLABLE
user_properties.value.float_valueFLOATNULLABLE
user_properties.value.double_valueFLOATNULLABLE
user_properties.value.set_timestamp_microsINTEGERNULLABLE
user_first_touch_timestampINTEGERNULLABLE
user_ltvRECORDNULLABLE
user_ltv.revenueFLOATNULLABLE
user_ltv.currencySTRINGNULLABLE
deviceRECORDNULLABLE
device.categorySTRINGNULLABLE
device.mobile_brand_nameSTRINGNULLABLE
device.mobile_model_nameSTRINGNULLABLE
device.mobile_marketing_nameSTRINGNULLABLE
device.mobile_os_hardware_modelSTRINGNULLABLE
device.operating_systemSTRINGNULLABLE
device.operating_system_versionSTRINGNULLABLE
device.vendor_idSTRINGNULLABLE
device.advertising_idSTRINGNULLABLE
device.languageSTRINGNULLABLE
device.is_limited_ad_trackingSTRINGNULLABLE
device.time_zone_offset_secondsINTEGERNULLABLE
device.browserSTRINGNULLABLE
device.browser_versionSTRINGNULLABLE
device.web_infoRECORDNULLABLE
device.web_info.browserSTRINGNULLABLE
device.web_info.browser_versionSTRINGNULLABLE
device.web_info.hostnameSTRINGNULLABLE
geoRECORDNULLABLE
geo.continentSTRINGNULLABLE
geo.countrySTRINGNULLABLE
geo.regionSTRINGNULLABLE
geo.citySTRINGNULLABLE
geo.sub_continentSTRINGNULLABLE
geo.metroSTRINGNULLABLE
app_infoRECORDNULLABLE
app_info.idSTRINGNULLABLE
app_info.versionSTRINGNULLABLE
app_info.install_storeSTRINGNULLABLE
app_info.firebase_app_idSTRINGNULLABLE
app_info.install_sourceSTRINGNULLABLE
traffic_sourceRECORDNULLABLE
traffic_source.nameSTRINGNULLABLE
traffic_source.mediumSTRINGNULLABLE
traffic_source.sourceSTRINGNULLABLE
stream_idSTRINGNULLABLE
platformSTRINGNULLABLE
event_dimensionsRECORDNULLABLE
event_dimensions.hostnameSTRINGNULLABLE
ecommerceRECORDNULLABLE
ecommerce.total_item_quantityINTEGERNULLABLE
ecommerce.purchase_revenue_in_usdFLOATNULLABLE
ecommerce.purchase_revenueFLOATNULLABLE
ecommerce.refund_value_in_usdFLOATNULLABLE
ecommerce.refund_valueFLOATNULLABLE
ecommerce.shipping_value_in_usdFLOATNULLABLE
ecommerce.shipping_valueFLOATNULLABLE
ecommerce.tax_value_in_usdFLOATNULLABLE
ecommerce.tax_valueFLOATNULLABLE
ecommerce.unique_itemsINTEGERNULLABLE
itemsRECORDREPEATED
items.item_idSTRINGNULLABLE
items.item_nameSTRINGNULLABLE
items.item_brandSTRINGNULLABLE
items.item_variantSTRINGNULLABLE
items.item_categorySTRINGNULLABLE
items.item_category2STRINGNULLABLE
items.item_category3STRINGNULLABLE
items.item_category4STRINGNULLABLE
items.item_category5STRINGNULLABLE
items.price_in_usdFLOATNULLABLE
items.priceFLOATNULLABLE
items.quantityINTEGERNULLABLE
items.item_revenue_in_usdFLOATNULLABLE
items.item_revenueFLOATNULLABLE
items.item_refund_in_usdFLOATNULLABLE
items.item_refundFLOATNULLABLE
items.couponSTRINGNULLABLE
items.affiliationSTRINGNULLABLE
items.location_idSTRINGNULLABLE
items.item_list_idSTRINGNULLABLE
items.item_list_nameSTRINGNULLABLE
items.item_list_indexSTRINGNULLABLE
items.promotion_idSTRINGNULLABLE
items.promotion_nameSTRINGNULLABLE
items.creative_nameSTRINGNULLABLE
items.creative_slotSTRINGNULLABLE

両方とも100個以上のカラムがあるので分かりづらいですが、GA4からExportする場合は、ecommerce.transaction_id が新たに追加されていることがわかります。

これはeコマース測定の計測で必須なカラムになるため、とてもありがたいです。

ただ、gtagで送る場合はクーポン情報があるのですが、BigQueryのスキーマにはeコマースカラムにはクーポンがありません。(商品にはあるのですが。。。)

そのため、今後スキーマが変更になることが予想されますので、BigQuery内でこのテーブルを使うとき(特にスケジュールされたクエリなどで定期的に実行する場合)は、注意しましょう。

コメント

タイトルとURLをコピーしました