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

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

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

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

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

Field name	Data type	Description
event_date	STRING	NULLABLE
event_timestamp	INTEGER	NULLABLE
event_name	STRING	NULLABLE
event_params	RECORD	REPEATED
event_params.key	STRING	NULLABLE
event_params.value	RECORD	NULLABLE
event_params.value.string_value	STRING	NULLABLE
event_params.value.int_value	INTEGER	NULLABLE
event_params.value.float_value	FLOAT	NULLABLE
event_params.value.double_value	FLOAT	NULLABLE
event_previous_timestamp	INTEGER	NULLABLE
event_value_in_usd	FLOAT	NULLABLE
event_bundle_sequence_id	INTEGER	NULLABLE
event_server_timestamp_offset	INTEGER	NULLABLE
user_id	STRING	NULLABLE
user_pseudo_id	STRING	NULLABLE
user_properties	RECORD	REPEATED
user_properties.key	STRING	NULLABLE
user_properties.value	RECORD	NULLABLE
user_properties.value.string_value	STRING	NULLABLE
user_properties.value.int_value	INTEGER	NULLABLE
user_properties.value.float_value	FLOAT	NULLABLE
user_properties.value.double_value	FLOAT	NULLABLE
user_properties.value.set_timestamp_micros	INTEGER	NULLABLE
user_first_touch_timestamp	INTEGER	NULLABLE
user_ltv	RECORD	NULLABLE
user_ltv.revenue	FLOAT	NULLABLE
user_ltv.currency	STRING	NULLABLE
device	RECORD	NULLABLE
device.category	STRING	NULLABLE
device.mobile_brand_name	STRING	NULLABLE
device.mobile_model_name	STRING	NULLABLE
device.mobile_marketing_name	STRING	NULLABLE
device.mobile_os_hardware_model	STRING	NULLABLE
device.operating_system	STRING	NULLABLE
device.operating_system_version	STRING	NULLABLE
device.vendor_id	STRING	NULLABLE
device.advertising_id	STRING	NULLABLE
device.language	STRING	NULLABLE
device.is_limited_ad_tracking	STRING	NULLABLE
device.time_zone_offset_seconds	INTEGER	NULLABLE
device.browser	STRING	NULLABLE
device.browser_version	STRING	NULLABLE
device.web_info	RECORD	NULLABLE
device.web_info.browser	STRING	NULLABLE
device.web_info.browser_version	STRING	NULLABLE
device.web_info.hostname	STRING	NULLABLE
geo	RECORD	NULLABLE
geo.continent	STRING	NULLABLE
geo.country	STRING	NULLABLE
geo.region	STRING	NULLABLE
geo.city	STRING	NULLABLE
geo.sub_continent	STRING	NULLABLE
geo.metro	STRING	NULLABLE
app_info	RECORD	NULLABLE
app_info.id	STRING	NULLABLE
app_info.version	STRING	NULLABLE
app_info.install_store	STRING	NULLABLE
app_info.firebase_app_id	STRING	NULLABLE
app_info.install_source	STRING	NULLABLE
traffic_source	RECORD	NULLABLE
traffic_source.name	STRING	NULLABLE
traffic_source.medium	STRING	NULLABLE
traffic_source.source	STRING	NULLABLE
stream_id	STRING	NULLABLE
platform	STRING	NULLABLE
event_dimensions	RECORD	NULLABLE
event_dimensions.hostname	STRING	NULLABLE
ecommerce	RECORD	NULLABLE
ecommerce.total_item_quantity	INTEGER	NULLABLE
ecommerce.purchase_revenue_in_usd	FLOAT	NULLABLE
ecommerce.purchase_revenue	FLOAT	NULLABLE
ecommerce.refund_value_in_usd	FLOAT	NULLABLE
ecommerce.refund_value	FLOAT	NULLABLE
ecommerce.shipping_value_in_usd	FLOAT	NULLABLE
ecommerce.shipping_value	FLOAT	NULLABLE
ecommerce.tax_value_in_usd	FLOAT	NULLABLE
ecommerce.tax_value	FLOAT	NULLABLE
ecommerce.unique_items	INTEGER	NULLABLE
ecommerce.transaction_id	STRING	NULLABLE
items	RECORD	REPEATED
items.item_id	STRING	NULLABLE
items.item_name	STRING	NULLABLE
items.item_brand	STRING	NULLABLE
items.item_variant	STRING	NULLABLE
items.item_category	STRING	NULLABLE
items.item_category2	STRING	NULLABLE
items.item_category3	STRING	NULLABLE
items.item_category4	STRING	NULLABLE
items.item_category5	STRING	NULLABLE
items.price_in_usd	FLOAT	NULLABLE
items.price	FLOAT	NULLABLE
items.quantity	INTEGER	NULLABLE
items.item_revenue_in_usd	FLOAT	NULLABLE
items.item_revenue	FLOAT	NULLABLE
items.item_refund_in_usd	FLOAT	NULLABLE
items.item_refund	FLOAT	NULLABLE
items.coupon	STRING	NULLABLE
items.affiliation	STRING	NULLABLE
items.location_id	STRING	NULLABLE
items.item_list_id	STRING	NULLABLE
items.item_list_name	STRING	NULLABLE
items.item_list_index	STRING	NULLABLE
items.promotion_id	STRING	NULLABLE
items.promotion_name	STRING	NULLABLE
items.creative_name	STRING	NULLABLE
items.creative_slot	STRING	NULLABLE

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

Field name	Data type	Description
event_date	STRING	NULLABLE
event_timestamp	INTEGER	NULLABLE
event_name	STRING	NULLABLE
event_params	RECORD	REPEATED
event_params.key	STRING	NULLABLE
event_params.value	RECORD	NULLABLE
event_params.value.string_value	STRING	NULLABLE
event_params.value.int_value	INTEGER	NULLABLE
event_params.value.float_value	FLOAT	NULLABLE
event_params.value.double_value	FLOAT	NULLABLE
event_previous_timestamp	INTEGER	NULLABLE
event_value_in_usd	FLOAT	NULLABLE
event_bundle_sequence_id	INTEGER	NULLABLE
event_server_timestamp_offset	INTEGER	NULLABLE
user_id	STRING	NULLABLE
user_pseudo_id	STRING	NULLABLE
user_properties	RECORD	REPEATED
user_properties.key	STRING	NULLABLE
user_properties.value	RECORD	NULLABLE
user_properties.value.string_value	STRING	NULLABLE
user_properties.value.int_value	INTEGER	NULLABLE
user_properties.value.float_value	FLOAT	NULLABLE
user_properties.value.double_value	FLOAT	NULLABLE
user_properties.value.set_timestamp_micros	INTEGER	NULLABLE
user_first_touch_timestamp	INTEGER	NULLABLE
user_ltv	RECORD	NULLABLE
user_ltv.revenue	FLOAT	NULLABLE
user_ltv.currency	STRING	NULLABLE
device	RECORD	NULLABLE
device.category	STRING	NULLABLE
device.mobile_brand_name	STRING	NULLABLE
device.mobile_model_name	STRING	NULLABLE
device.mobile_marketing_name	STRING	NULLABLE
device.mobile_os_hardware_model	STRING	NULLABLE
device.operating_system	STRING	NULLABLE
device.operating_system_version	STRING	NULLABLE
device.vendor_id	STRING	NULLABLE
device.advertising_id	STRING	NULLABLE
device.language	STRING	NULLABLE
device.is_limited_ad_tracking	STRING	NULLABLE
device.time_zone_offset_seconds	INTEGER	NULLABLE
device.browser	STRING	NULLABLE
device.browser_version	STRING	NULLABLE
device.web_info	RECORD	NULLABLE
device.web_info.browser	STRING	NULLABLE
device.web_info.browser_version	STRING	NULLABLE
device.web_info.hostname	STRING	NULLABLE
geo	RECORD	NULLABLE
geo.continent	STRING	NULLABLE
geo.country	STRING	NULLABLE
geo.region	STRING	NULLABLE
geo.city	STRING	NULLABLE
geo.sub_continent	STRING	NULLABLE
geo.metro	STRING	NULLABLE
app_info	RECORD	NULLABLE
app_info.id	STRING	NULLABLE
app_info.version	STRING	NULLABLE
app_info.install_store	STRING	NULLABLE
app_info.firebase_app_id	STRING	NULLABLE
app_info.install_source	STRING	NULLABLE
traffic_source	RECORD	NULLABLE
traffic_source.name	STRING	NULLABLE
traffic_source.medium	STRING	NULLABLE
traffic_source.source	STRING	NULLABLE
stream_id	STRING	NULLABLE
platform	STRING	NULLABLE
event_dimensions	RECORD	NULLABLE
event_dimensions.hostname	STRING	NULLABLE
ecommerce	RECORD	NULLABLE
ecommerce.total_item_quantity	INTEGER	NULLABLE
ecommerce.purchase_revenue_in_usd	FLOAT	NULLABLE
ecommerce.purchase_revenue	FLOAT	NULLABLE
ecommerce.refund_value_in_usd	FLOAT	NULLABLE
ecommerce.refund_value	FLOAT	NULLABLE
ecommerce.shipping_value_in_usd	FLOAT	NULLABLE
ecommerce.shipping_value	FLOAT	NULLABLE
ecommerce.tax_value_in_usd	FLOAT	NULLABLE
ecommerce.tax_value	FLOAT	NULLABLE
ecommerce.unique_items	INTEGER	NULLABLE

items	RECORD	REPEATED
items.item_id	STRING	NULLABLE
items.item_name	STRING	NULLABLE
items.item_brand	STRING	NULLABLE
items.item_variant	STRING	NULLABLE
items.item_category	STRING	NULLABLE
items.item_category2	STRING	NULLABLE
items.item_category3	STRING	NULLABLE
items.item_category4	STRING	NULLABLE
items.item_category5	STRING	NULLABLE
items.price_in_usd	FLOAT	NULLABLE
items.price	FLOAT	NULLABLE
items.quantity	INTEGER	NULLABLE
items.item_revenue_in_usd	FLOAT	NULLABLE
items.item_revenue	FLOAT	NULLABLE
items.item_refund_in_usd	FLOAT	NULLABLE
items.item_refund	FLOAT	NULLABLE
items.coupon	STRING	NULLABLE
items.affiliation	STRING	NULLABLE
items.location_id	STRING	NULLABLE
items.item_list_id	STRING	NULLABLE
items.item_list_name	STRING	NULLABLE
items.item_list_index	STRING	NULLABLE
items.promotion_id	STRING	NULLABLE
items.promotion_name	STRING	NULLABLE
items.creative_name	STRING	NULLABLE
items.creative_slot	STRING	NULLABLE

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

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

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

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