Google Analytics 4 BigQuery

• Introduction
  • Example of operation
  • Data schema
• Configuration
  • Before you start
  • Start creating the module
  • Initial settings
  • Authorization from Google Analytics
  • Setting up a connection with Google BigQuery
  • Historical data processing setting
  • Recommended further steps - All In One reporting

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

1. We configure the module in the WitCloud platform by granting access to Google Analytics 4 API and the table of exported data in BigQuery
2. 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.
3. 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.