# Google Analytics 4 BigQuery
# Introduction
In response to many challenges related to the analysis of Google Analytics 4 data in Google BigQuery, a module called "Google Analytics 4 BigQuery" was created in our platform. This module solves most problems with data processing, e.g. attribution of traffic sources to sessions, attribution of traffic sources to conversion.
The module provides session and attribution tables in models i.e. Google Analytics 4, Universal Analytics Cross-Device and Universal Analytics Device.

# Example of operation
- We configure the module in the WitCloud platform by granting access to Google Analytics 4 API and the table of exported data in BigQuery
- Based on the set schedule, the module will copy your historical data and process it into event, session and attribution tables in various models. In addition, it will create a schedule for processing your data once a day in the future.
- After processing the table, you can read the data in Google BigQuery, use it for your own analyzes or run the "All In One" report prepared by us in Looker Studio
# Data schema
Event table - the module provides a copied event table with additional fields regarding sessions, traffic sources and attribution
Expand to see the data schema
Field name | Type | Mode |
---|---|---|
event_date | STRING | NULLABLE |
user_pseudo_id | STRING | NULLABLE |
user_id | STRING | NULLABLE |
group_id | STRING | NULLABLE |
event_name | STRING | NULLABLE |
event_timestamp | INTEGER | NULLABLE |
event_bundle_sequence_id | INTEGER | NULLABLE |
event_previous_timestamp | INTEGER | NULLABLE |
event_server_timestamp_offset | INTEGER | NULLABLE |
event_value_in_usd | FLOAT | NULLABLE |
platform | STRING | NULLABLE |
stream_id | STRING | NULLABLE |
user_first_touch_timestamp | INTEGER | NULLABLE |
attribution_timespan | STRING | NULLABLE |
event_params.key | STRING | 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 |
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 |
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.browser | STRING | NULLABLE |
device.web_info.browser_version | STRING | NULLABLE |
device.web_info.hostname | STRING | 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.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 |
event_dimensions.hostname | STRING | 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 |
privacy_info.analytics_storage | STRING | NULLABLE |
privacy_info.ads_storage | STRING | NULLABLE |
privacy_info.uses_transient_token | STRING | NULLABLE |
traffic_source.name | STRING | NULLABLE |
traffic_source.medium | STRING | NULLABLE |
traffic_source.source | STRING | NULLABLE |
user_ltv.revenue | FLOAT | NULLABLE |
user_ltv.currency | STRING | NULLABLE |
user_properties.key | STRING | 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 |
wit_session_ga4.session_id | STRING | NULLABLE |
wit_session_ga4.session_id_group | STRING | NULLABLE |
wit_session_ga4.traffic_source.source | STRING | NULLABLE |
wit_session_ga4.traffic_source.medium | STRING | NULLABLE |
wit_session_ga4.traffic_source.campaign | STRING | NULLABLE |
wit_session_ga4.traffic_source.term | STRING | NULLABLE |
wit_session_ga4.traffic_source.content | STRING | NULLABLE |
wit_session_ga4.traffic_source.campaign_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.gclid | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.external_customer_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.account_descriptive_name | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.campaign_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.campaign_name | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_group_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_group_name | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.creative_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.criteria_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.gclid | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.user_list_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_network_type1 | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_network_type2 | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.criteria_parameters | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.keyword_match_type | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.page | INTEGER | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.slot | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.device | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.currency | STRING | NULLABLE |
wit_session_ga4.attribution_details | STRING | NULLABLE |
wit_session_ua_cross_device.session_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.source | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.medium | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.campaign | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.term | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.content | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.campaign_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.gclid | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.external_customer_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.account_descriptive_name | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.campaign_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.campaign_name | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.ad_group_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.ad_group_name | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.creative_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.criteria_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.gclid | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.user_list_id | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.ad_network_type1 | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.ad_network_type2 | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.criteria_parameters | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.keyword_match_type | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.page | INTEGER | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.slot | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.device | STRING | NULLABLE |
wit_session_ua_cross_device.traffic_source.google_ads_click_info.currency | STRING | NULLABLE |
wit_session_ua_cross_device.attribution_details | STRING | NULLABLE |
wit_session_ua_device.session_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.source | STRING | NULLABLE |
wit_session_ua_device.traffic_source.medium | STRING | NULLABLE |
wit_session_ua_device.traffic_source.campaign | STRING | NULLABLE |
wit_session_ua_device.traffic_source.term | STRING | NULLABLE |
wit_session_ua_device.traffic_source.content | STRING | NULLABLE |
wit_session_ua_device.traffic_source.campaign_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.gclid | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.external_customer_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.account_descriptive_name | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.campaign_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.campaign_name | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.ad_group_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.ad_group_name | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.creative_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.criteria_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.gclid | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.user_list_id | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.ad_network_type1 | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.ad_network_type2 | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.criteria_parameters | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.keyword_match_type | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.page | INTEGER | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.slot | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.device | STRING | NULLABLE |
wit_session_ua_device.traffic_source.google_ads_click_info.currency | STRING | NULLABLE |
wit_session_ua_device.attribution_details | STRING | NULLABLE |
Session tables - the module provides 3 session tables in the same schema for 3 models, i.e. Google Analytics 4, Universal Analytics Cross-Device and Universal Analytics Device
Expand to see the data schema
Field name | Mode | Type |
---|---|---|
date | DATE | NULLABLE |
session_id | STRING | NULLABLE |
user_pseudo_id | STRING | NULLABLE |
session_start_time | INTEGER | NULLABLE |
landing_page | STRING | NULLABLE |
stream_id | STRING | NULLABLE |
platform | STRING | NULLABLE |
totals.event | INTEGER | NULLABLE |
totals.page_view | INTEGER | NULLABLE |
totals.unique_page_view | INTEGER | NULLABLE |
totals.screen_view | INTEGER | NULLABLE |
totals.unique_screen_view | INTEGER | NULLABLE |
totals.engagement | INTEGER | NULLABLE |
totals.engagement_time_seconds | FLOAT | NULLABLE |
totals.visit_duration | INTEGER | NULLABLE |
totals.first_visit | INTEGER | NULLABLE |
totals.view_item | INTEGER | NULLABLE |
totals.add_to_cart | INTEGER | NULLABLE |
totals.begin_checkout | INTEGER | NULLABLE |
totals.add_shipping_info | INTEGER | NULLABLE |
totals.add_payment_info | INTEGER | NULLABLE |
totals.purchase | INTEGER | NULLABLE |
totals.purchase_revenue_in_property_currency | FLOAT | NULLABLE |
totals.shipping_value_in_property_currency | FLOAT | NULLABLE |
totals.tax_value_in_property_currency | FLOAT | NULLABLE |
totals.refund_value_in_property_currency | FLOAT | NULLABLE |
totals.event_count | STRING | NULLABLE |
totals.event_value | STRING | NULLABLE |
acq_traffic_source.name | STRING | NULLABLE |
acq_traffic_source.medium | STRING | NULLABLE |
acq_traffic_source.source | STRING | NULLABLE |
wit_session_ga4.session_id | STRING | NULLABLE |
wit_session_ga4.session_id_group | STRING | NULLABLE |
wit_session_ga4.traffic_source.source | STRING | NULLABLE |
wit_session_ga4.traffic_source.medium | STRING | NULLABLE |
wit_session_ga4.traffic_source.campaign | STRING | NULLABLE |
wit_session_ga4.traffic_source.term | STRING | NULLABLE |
wit_session_ga4.traffic_source.content | STRING | NULLABLE |
wit_session_ga4.traffic_source.campaign_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.gclid | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.external_customer_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.account_descriptive_name | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.campaign_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.campaign_name | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_group_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_group_name | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.creative_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.criteria_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.gclid | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.user_list_id | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_network_type1 | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.ad_network_type2 | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.criteria_parameters | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.keyword_match_type | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.page | INTEGER | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.slot | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.device | STRING | NULLABLE |
wit_session_ga4.traffic_source.google_ads_click_info.currency | STRING | NULLABLE |
wit_session_ga4.attribution_details | STRING | 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.browser | STRING | NULLABLE |
device.web_info.browser_version | STRING | NULLABLE |
device.web_info.hostname | STRING | 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.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 |
ecommerce.transaction_id | STRING | NULLABLE |
ecommerce.currency | STRING | NULLABLE |
ecommerce.total_item_quantity | INTEGER | NULLABLE |
ecommerce.purchase_revenue_in_property_currency | FLOAT | NULLABLE |
ecommerce.purchase_revenue_in_usd | FLOAT | NULLABLE |
ecommerce.purchase_revenue | FLOAT | NULLABLE |
ecommerce.refund_value_in_property_currency | FLOAT | NULLABLE |
ecommerce.refund_value_in_usd | FLOAT | NULLABLE |
ecommerce.refund_value | FLOAT | NULLABLE |
ecommerce.shipping_value_in_property_currency | FLOAT | NULLABLE |
ecommerce.shipping_value_in_usd | FLOAT | NULLABLE |
ecommerce.shipping_value | FLOAT | NULLABLE |
ecommerce.tax_value_in_property_currency | FLOAT | NULLABLE |
ecommerce.tax_value_in_usd | FLOAT | NULLABLE |
ecommerce.tax_value | FLOAT | NULLABLE |
ecommerce.unique_items | INTEGER | NULLABLE |
ecommerce.items.item_id | STRING | NULLABLE |
ecommerce.items.item_name | STRING | NULLABLE |
ecommerce.items.item_brand | STRING | NULLABLE |
ecommerce.items.item_variant | STRING | NULLABLE |
ecommerce.items.item_category | STRING | NULLABLE |
ecommerce.items.item_category2 | STRING | NULLABLE |
ecommerce.items.item_category3 | STRING | NULLABLE |
ecommerce.items.item_category4 | STRING | NULLABLE |
ecommerce.items.item_category5 | STRING | NULLABLE |
ecommerce.items.price_in_property_currency | FLOAT | NULLABLE |
ecommerce.items.price_in_usd | FLOAT | NULLABLE |
ecommerce.items.price | FLOAT | NULLABLE |
ecommerce.items.quantity | INTEGER | NULLABLE |
ecommerce.items.item_revenue_in_property_currency | FLOAT | NULLABLE |
ecommerce.items.item_revenue_in_usd | FLOAT | NULLABLE |
ecommerce.items.item_revenue | FLOAT | NULLABLE |
ecommerce.items.item_refund_in_property_currency | FLOAT | NULLABLE |
ecommerce.items.item_refund_in_usd | FLOAT | NULLABLE |
ecommerce.items.item_refund | FLOAT | NULLABLE |
ecommerce.items.coupon | STRING | NULLABLE |
ecommerce.items.affiliation | STRING | NULLABLE |
Attribution tables - the module provides 3 attribution tables in the same schema for 3 models, i.e. Google Analytics 4, Universal Analytics Cross-Device and Universal Analytics Device
Expand to see the data schema
Field Name | Type | Mode |
---|---|---|
date | DATE | NULLABLE |
session_id | STRING | NULLABLE |
journey_id | STRING | NULLABLE |
attribution_timespan | STRING | NULLABLE |
event_date | STRING | NULLABLE |
conversion_time_micros | INTEGER | NULLABLE |
conversion_time | TIMESTAMP | NULLABLE |
session_path_timestamp | TIMESTAMP | NULLABLE |
session_path_days_from_conversion | INTEGER | NULLABLE |
user_pseudo_id | STRING | NULLABLE |
event_name | STRING | NULLABLE |
event_value | FLOAT | NULLABLE |
path_length | INTEGER | NULLABLE |
path_length_days | INTEGER | NULLABLE |
path_step_number_reverse | INTEGER | NULLABLE |
path_step_number | INTEGER | NULLABLE |
session_path_traffic_source.source | STRING | NULLABLE |
session_path_traffic_source.medium | STRING | NULLABLE |
session_path_traffic_source.campaign | STRING | NULLABLE |
session_path_traffic_source.campaign_id | STRING | NULLABLE |
session_path_traffic_source.gclid | STRING | NULLABLE |
attribution_score.first_click | INTEGER | NULLABLE |
attribution_score.last_click | INTEGER | NULLABLE |
attribution_score.linear | FLOAT | NULLABLE |
attribution_score.position_based | FLOAT | NULLABLE |
attribution_score.timedecay | FLOAT | NULLABLE |
ecommerce.transaction_id | STRING | NULLABLE |
ecommerce.currency | STRING | NULLABLE |
ecommerce.total_item_quantity | INTEGER | NULLABLE |
ecommerce.purchase_revenue_in_property_currency | FLOAT | NULLABLE |
ecommerce.purchase_revenue_in_usd | FLOAT | NULLABLE |
ecommerce.purchase_revenue | FLOAT | NULLABLE |
ecommerce.refund_value_in_property_currency | FLOAT | NULLABLE |
ecommerce.refund_value_in_usd | FLOAT | NULLABLE |
ecommerce.refund_value | FLOAT | NULLABLE |
ecommerce.shipping_value_in_property_currency | FLOAT | NULLABLE |
ecommerce.shipping_value_in_usd | FLOAT | NULLABLE |
ecommerce.shipping_value | FLOAT | NULLABLE |
ecommerce.tax_value_in_property_currency | FLOAT | NULLABLE |
ecommerce.tax_value_in_usd | FLOAT | NULLABLE |
ecommerce.tax_value | FLOAT | NULLABLE |
ecommerce.unique_items | INTEGER | NULLABLE |
ecommerce.items.item_id | STRING | NULLABLE |
ecommerce.items.item_name | STRING | NULLABLE |
ecommerce.items.item_brand | STRING | NULLABLE |
ecommerce.items.item_variant | STRING | NULLABLE |
ecommerce.items.item_category | STRING | NULLABLE |
ecommerce.items.item_category2 | STRING | NULLABLE |
ecommerce.items.item_category3 | STRING | NULLABLE |
ecommerce.items.item_category4 | STRING | NULLABLE |
ecommerce.items.item_category5 | STRING | NULLABLE |
ecommerce.items.price_in_property_currency | FLOAT | NULLABLE |
ecommerce.items.price_in_usd | FLOAT | NULLABLE |
ecommerce.items.price | FLOAT | NULLABLE |
ecommerce.items.quantity | INTEGER | NULLABLE |
ecommerce.items.item_revenue_in_property_currency | FLOAT | NULLABLE |
ecommerce.items.item_revenue_in_usd | FLOAT | NULLABLE |
ecommerce.items.item_revenue | FLOAT | NULLABLE |
ecommerce.items.item_refund_in_property_currency | FLOAT | NULLABLE |
ecommerce.items.item_refund_in_usd | FLOAT | NULLABLE |
ecommerce.items.item_refund | FLOAT | NULLABLE |
ecommerce.items.coupon | STRING | NULLABLE |
ecommerce.items.affiliation | STRING | NULLABLE |
google_ads_click_info.external_customer_id | STRING | NULLABLE |
google_ads_click_info.account_descriptive_name | STRING | NULLABLE |
google_ads_click_info.campaign_id | STRING | NULLABLE |
google_ads_click_info.campaign_name | STRING | NULLABLE |
google_ads_click_info.ad_group_id | STRING | NULLABLE |
google_ads_click_info.ad_group_name | STRING | NULLABLE |
google_ads_click_info.creative_id | STRING | NULLABLE |
google_ads_click_info.criteria_id | STRING | NULLABLE |
google_ads_click_info.gclid | STRING | NULLABLE |
google_ads_click_info.user_list_id | STRING | NULLABLE |
google_ads_click_info.ad_network_type1 | STRING | NULLABLE |
google_ads_click_info.ad_network_type2 | STRING | NULLABLE |
google_ads_click_info.criteria_parameters | STRING | NULLABLE |
google_ads_click_info.keyword_match_type | STRING | NULLABLE |
google_ads_click_info.page | INTEGER | NULLABLE |
google_ads_click_info.slot | STRING | NULLABLE |
google_ads_click_info.device | STRING | NULLABLE |
google_ads_click_info.currency | STRING | NULLABLE |
Conversion table - this table contains the names of events marked in Google Analytics 4 as a conversion
Expand to see the data schema
Field Name | Type | Mode |
---|---|---|
name | STRING | NULLABLE |
eventName | STRING | NULLABLE |
streamId | INTEGER | NULLABLE |
createTime | TIMESTAMP | NULLABLE |
propertyId | INTEGER | NULLABLE |
# Configuration
# Before you start
Remember to set up Google Ads
If you want to link data about Google Ads campaigns with the Google Analytics 4 BigQuery module (highly recommended option), we suggest you first configure the Google Ads - link module. If this integration is not enabled, it will not be possible to analyze the campaign in detail.
Access to exported data in BigQuery via Google Analytics 4 and Google Analytics 4 Account required
In order to use this module, you must have access to the event table dumped to BigQuery by Google Analytics 4, and to the service in Google Analytics itself. WitCloud works on a copy of Google Analytics tables, so it is necessary to give the platform the appropriate permissions. Expand the sections below to find out how.
Copy the WitCloud project's Service Account
In the menu on the left, select "Settings" and then "Projects" (1). From the list of projects, select the right one and then expand the details by clicking on the gear icon (2).
In the details view, information about the Service Account address is available - the project's technical email address, used to authorize the WitCloud user on Google services. By clicking the icon you will copy the address to the clipboard.
Grant access to the Google Analytics 4 table in BigQuery via Service Account
In order to share data with Google Analytics 4, enter BigQuery, in the menu on the left, search for the correct dataset and then select the “share dataset” option.
Then, the following roles should be assigned to the email address generated by WitCloud (Service Account): BigQuery Data Editor, BigQuery User
# Start creating the module
From the menu on the left, select the Collect tab , then click the Add new Data Collect button .
From the list of available modules, select Google Analytics 4 BigQuery.
# Initial settings
In the first step, we have to complete:
Collect name - name of our module. It will be visible under this name elsewhere on the platform.
Campaign Timeout - The campaign timeout determines how long a given campaign or traffic source should be associated with a given user when calculating attribution for sessions and conversions. For Google Analytics 4, the default time is 90 days, for Google Analytics Universal it will be 180 days. It is worth remembering that a larger campaign time limit will affect the processing of more information in Google BigQuery. Users of the WitCloud platform, depending on the type of business, usually set here from 30 to 90 days.
In advanced settings we can set:
Connect Google Ads Accounts - a combination of campaign data with Google Ads. After creating the Google Ads module in the WitCloud panel, the name of this module should appear for selection from the list.
Attribution - Time Decay (days) - settings for the Time Decay attribution model. How it works: Gives more credit to traffic sources closer to the conversion time. Credit is distributed using a 7-day half-life. This means that a traffic source interaction 8 days before conversion receives half as much credit as an ad interaction 1 day before conversion.
Attribution - Position Based - settings for the position based attribution model. By default, the first and last traffic sources in the user's path will each receive 40% of the conversion value. The remaining 20% of conversion value is distributed to traffic sources in the middle of the conversion path.
# Authorization from Google Analytics
In the next step, we need to give the WitCloud application access to the Google Analytics service. This is necessary so that WitCloud can download the data and settings necessary for the module to work.
# Setting up a connection with Google BigQuery
In this step, we need to set up a connection to the selected dataset located in Google BigQuery.
Remember to grant access to your data in BigQuery
Authorization with Google BigQuery is carried out by granting access to the Service Account. This step is described in the "Before you begin" section. If you skipped this step, be sure to do it now, otherwise it will not be possible to configure the module.
In the case of correct authorization in BigQuery, after pressing the "Refresh" button, it will be possible to select the Project, BigQuery Dataset and Table containing Google Analytics 4 data.
# Historical data processing setting
The last step is to determine how many days back we should process the data - this setting depends on the date of starting data export in Google Analytics 4. At the beginning, we recommend processing fewer days. After checking the data in BigQuery or reports, there will be no problem to download the rest of the historical data by our Workflow module described here.
# Recommended further steps - All In One reporting
The Google Analytics 4 BigQuery module has been included in the "All In One" reporting, which you can read more about here. It is a ready-to-create report that collects all data downloaded by WitCloud in one place with visualization in Looker Studio.