tomusher
commented
8 months ago For those looking to resolve this with a schema change, this is the new schema, which can be applied with the BigQuery CLI:
bq update [project]:[dataset].base_ga4__events [schema_file][
{
"name": "event_date_dt",
"type": "DATE"
},
{
"name": "event_timestamp",
"type": "INTEGER"
},
{
"name": "event_name",
"type": "STRING"
},
{
"fields": [
{
"name": "key",
"type": "STRING"
},
{
"fields": [
{
"name": "string_value",
"type": "STRING"
},
{
"name": "int_value",
"type": "INTEGER"
},
{
"name": "float_value",
"type": "FLOAT"
},
{
"name": "double_value",
"type": "FLOAT"
}
],
"name": "value",
"type": "RECORD"
}
],
"mode": "REPEATED",
"name": "event_params",
"type": "RECORD"
},
{
"name": "event_previous_timestamp",
"type": "INTEGER"
},
{
"name": "event_value_in_usd",
"type": "FLOAT"
},
{
"name": "event_bundle_sequence_id",
"type": "INTEGER"
},
{
"name": "event_server_timestamp_offset",
"type": "INTEGER"
},
{
"name": "user_id",
"type": "STRING"
},
{
"name": "user_pseudo_id",
"type": "STRING"
},
{
"name": "privacy_info_analytics_storage",
"type": "STRING"
},
{
"name": "privacy_info_ads_storage",
"type": "STRING"
},
{
"name": "privacy_info_uses_transient_token",
"type": "STRING"
},
{
"fields": [
{
"name": "key",
"type": "STRING"
},
{
"fields": [
{
"name": "string_value",
"type": "STRING"
},
{
"name": "int_value",
"type": "INTEGER"
},
{
"name": "float_value",
"type": "FLOAT"
},
{
"name": "double_value",
"type": "FLOAT"
},
{
"name": "set_timestamp_micros",
"type": "INTEGER"
}
],
"name": "value",
"type": "RECORD"
}
],
"mode": "REPEATED",
"name": "user_properties",
"type": "RECORD"
},
{
"name": "user_first_touch_timestamp",
"type": "INTEGER"
},
{
"name": "user_ltv_revenue",
"type": "FLOAT"
},
{
"name": "user_ltv_currency",
"type": "STRING"
},
{
"name": "device_category",
"type": "STRING"
},
{
"name": "device_mobile_brand_name",
"type": "STRING"
},
{
"name": "device_mobile_model_name",
"type": "STRING"
},
{
"name": "device_mobile_marketing_name",
"type": "STRING"
},
{
"name": "device_mobile_os_hardware_model",
"type": "STRING"
},
{
"name": "device_operating_system",
"type": "STRING"
},
{
"name": "device_operating_system_version",
"type": "STRING"
},
{
"name": "device_vendor_id",
"type": "STRING"
},
{
"name": "device_advertising_id",
"type": "STRING"
},
{
"name": "device_language",
"type": "STRING"
},
{
"name": "device_is_limited_ad_tracking",
"type": "STRING"
},
{
"name": "device_time_zone_offset_seconds",
"type": "INTEGER"
},
{
"name": "device_browser",
"type": "STRING"
},
{
"name": "device_browser_version",
"type": "STRING"
},
{
"name": "device_web_info_browser",
"type": "STRING"
},
{
"name": "device_web_info_browser_version",
"type": "STRING"
},
{
"name": "device_web_info_hostname",
"type": "STRING"
},
{
"name": "geo_continent",
"type": "STRING"
},
{
"name": "geo_country",
"type": "STRING"
},
{
"name": "geo_region",
"type": "STRING"
},
{
"name": "geo_city",
"type": "STRING"
},
{
"name": "geo_sub_continent",
"type": "STRING"
},
{
"name": "geo_metro",
"type": "STRING"
},
{
"name": "app_info_id",
"type": "STRING"
},
{
"name": "app_info_version",
"type": "STRING"
},
{
"name": "app_info_install_store",
"type": "STRING"
},
{
"name": "app_info_firebase_app_id",
"type": "STRING"
},
{
"name": "app_info_install_source",
"type": "STRING"
},
{
"name": "user_campaign",
"type": "STRING"
},
{
"name": "user_medium",
"type": "STRING"
},
{
"name": "user_source",
"type": "STRING"
},
{
"name": "stream_id",
"type": "STRING"
},
{
"name": "platform",
"type": "STRING"
},
{
"fields": [
{
"name": "total_item_quantity",
"type": "INTEGER"
},
{
"name": "purchase_revenue_in_usd",
"type": "FLOAT"
},
{
"name": "purchase_revenue",
"type": "FLOAT"
},
{
"name": "refund_value_in_usd",
"type": "FLOAT"
},
{
"name": "refund_value",
"type": "FLOAT"
},
{
"name": "shipping_value_in_usd",
"type": "FLOAT"
},
{
"name": "shipping_value",
"type": "FLOAT"
},
{
"name": "tax_value_in_usd",
"type": "FLOAT"
},
{
"name": "tax_value",
"type": "FLOAT"
},
{
"name": "unique_items",
"type": "INTEGER"
},
{
"name": "transaction_id",
"type": "STRING"
}
],
"name": "ecommerce",
"type": "RECORD"
},
{
"fields": [
{
"name": "item_id",
"type": "STRING"
},
{
"name": "item_name",
"type": "STRING"
},
{
"name": "item_brand",
"type": "STRING"
},
{
"name": "item_variant",
"type": "STRING"
},
{
"name": "item_category",
"type": "STRING"
},
{
"name": "item_category2",
"type": "STRING"
},
{
"name": "item_category3",
"type": "STRING"
},
{
"name": "item_category4",
"type": "STRING"
},
{
"name": "item_category5",
"type": "STRING"
},
{
"name": "price_in_usd",
"type": "FLOAT"
},
{
"name": "price",
"type": "FLOAT"
},
{
"name": "quantity",
"type": "INTEGER"
},
{
"name": "item_revenue_in_usd",
"type": "FLOAT"
},
{
"name": "item_revenue",
"type": "FLOAT"
},
{
"name": "item_refund_in_usd",
"type": "FLOAT"
},
{
"name": "item_refund",
"type": "FLOAT"
},
{
"name": "coupon",
"type": "STRING"
},
{
"name": "affiliation",
"type": "STRING"
},
{
"name": "location_id",
"type": "STRING"
},
{
"name": "item_list_id",
"type": "STRING"
},
{
"name": "item_list_name",
"type": "STRING"
},
{
"name": "item_list_index",
"type": "STRING"
},
{
"name": "promotion_id",
"type": "STRING"
},
{
"name": "promotion_name",
"type": "STRING"
},
{
"name": "creative_name",
"type": "STRING"
},
{
"name": "creative_slot",
"type": "STRING"
},
{
"fields": [
{
"name": "key",
"type": "STRING"
},
{
"fields": [
{
"name": "string_value",
"type": "STRING"
},
{
"name": "int_value",
"type": "INTEGER"
},
{
"name": "float_value",
"type": "FLOAT"
},
{
"name": "double_value",
"type": "FLOAT"
}
],
"name": "value",
"type": "RECORD"
}
],
"mode": "REPEATED",
"name": "item_params",
"type": "RECORD"
}
],
"mode": "REPEATED",
"name": "items",
"type": "RECORD"
},
{
"name": "session_id",
"type": "INTEGER"
},
{
"name": "page_location",
"type": "STRING"
},
{
"name": "session_number",
"type": "INTEGER"
},
{
"name": "session_engaged",
"type": "INTEGER"
},
{
"name": "engagement_time_msec",
"type": "INTEGER"
},
{
"name": "page_title",
"type": "STRING"
},
{
"name": "page_referrer",
"type": "STRING"
},
{
"name": "event_source",
"type": "STRING"
},
{
"name": "event_medium",
"type": "STRING"
},
{
"name": "event_campaign",
"type": "STRING"
},
{
"name": "event_content",
"type": "STRING"
},
{
"name": "event_term",
"type": "STRING"
},
{
"name": "is_page_view",
"type": "INTEGER"
},
{
"name": "is_purchase",
"type": "INTEGER"
}
]
marcusinthesky
The GA4 BigQuery Export now has an
item_paramsREPEATED RECORD, as an attribute of theitemsREPEATED RECORD, which will not merge with existing data. The immediate requirement may be a fill refresh, which may be expensive and risky for users managing large properties, or a change to thebase_selectmacro, detailed the attributes present initems. This may require updating the query, or issueing a schema changes.