In this post, we describe how Boxalino process, consolidates, and extend the data provided by the Google Analytics, Google Merchant Center, and Google Ads data transfers with the other data of your website.

If you didn’t already transfer your data to Google BigQuery, do it first by following our step-by-step guides:

Core data: Raw vs Enriched

Conceptually, the data transfer provides the core data (here-after the Raw Data) in your own project and dataset, so you can simply consider that the core data are there.

However, there are many challenges with using the Raw Data (especially for the case of Google Analytics) and this is why Boxalino provides additional core tables (here-after the Enriched Data).

ga_events_enriched

This table has the same granularity and the same columns as the original GA events table as described here: https://support.google.com/analytics/answer/7029846?hl=en

Instead of using the table format events_YYYYDDMM it is using a partition table on the field ‘date’.

The table is updated daily with the data available at that time (to avoid a day gap, make sure your Boxalino data process starts after the data are available in GA4 for the prior day)

additional fields

field	type	mode	comments	possible values

field	type	mode	comments	possible values
ga_session_start	INTEGER	NULLABLE	the first event_timestamp of the session	int timpestamp
session_id	INTEGER	NULLABLE	the google analytics session id	unique int
session_number	INTEGER	NULLABLE	the number of the session compared to prior visits made by the same visitor	number from 1 to n
source_medium_session	STRING	NULLABLE	the session attribution (complex logic) of the source_medium	concat(source,' / ', medium)
campaign_session	STRING	NULLABLE	the session attribution (complex logic) of the source_medium	the name of a campaign
full_referrer	STRING	NULLABLE	retrieval of the referrer of the first sesion event	a URL
default_channel_grouping_session	STRING	NULLABLE	the session attribution (complex logic) of the categorization of the traffic source (complex rules to map the possible values)	Organic Search, Paid Search, Email, Direct, Affiliates, Display, Referral, …
source_medium_user	STRING	NULLABLE	the user attribution (simple logic as provided by GA4 in the traffic_source) of the source_medium	concat(source,' / ', medium)
campaign_user	STRING	NULLABLE	the user attribution (simple logic as provided by GA4 in the traffic_source) of the source_medium	the name of a campaign
default_channel_grouping_user	STRING	NULLABLE	the user attribution (simple logic as provided by GA4 in the traffic_source) of the categorization of the traffic source (complex rules to map the possible values)	Organic Search, Paid Search, Email, Direct, Affiliates, Display, Referral, …
gclid	STRING	NULLABLE	search for the gclid in the session event parameters with proper attribution of all following session events since the gclid is detected (switch from one gclid at the beginning of the session to another one later on is managed)	a gclid string
date	DATE	NULLABLE	the date field used for the partitioning of the table (use it to avoid querying and be charged for all the data)	the day date of the ga_session_start

Do you need to alter your data before they make their way to the core table ga_events_enriched table? no problem, just extend the view : ga_events_enriched_config in the views dataset which is set to be a transparent view by default:
select * from ACCOUNT_views.ga_events_enriched

ga_session

This table consolidates sessions that are both coming from Google Analytics and/or Boxalino tracking.

Analytics data

The Google Analytics and Google Ads data have been integrated through an attribution model into several important standard analytics tables in the ‘reports’ dataset:

order_primary_analytics

This table has one row per transaction (not per transaction and per product, see order_product_analytics below for that other case)

This table extends the table order_primary with more fields, including a repeating record field called ‘attribution’ coming from Google Analytics and Google Ads and with the following children fields:

field	type	mode	comments	possible values

field	type	mode	comments	possible values
name	STRING	NULLABLE	the name of the attribution model	a string with a name for the attribution model user can select in a drop down (Traffic Source - Session (GA), Google Ads - Last Click, …)
channel	STRING	REPEATED	the name of the attributed channel	Paid Search, Direct, … or Untracked
order_sys_cd	STRING	REPEATED	opitional parameter to set a different sys code for the order
source	STRING	REPEATED	optional parameter to set a specific different source
sourcePlatform	STRING	REPEATED	optional parameter to set a specific different source platform
medium	STRING	REPEATED	optional parameter to set a specific different medium
sourceMedium	STRING	REPEATED	optional parameter to set a specific different source medium
AccountName	STRING	REPEATED	optional parameter to set a specific account name
CustomerId	STRING	REPEATED	optional parameter to set a specific customer id
CampaignName	STRING	REPEATED	the name of the campaign as per the attribution model
CampaignId	STRING	REPEATED	the name of the campaign id as per the attribution model
AdGroupName	STRING	REPEATED	optional parameter to set a specific google ad group name
AdGroupId	STRING	REPEATED	optional parameter to set a specific google ad group identifier
AdName	STRING	REPEATED	optional parameter to set a specific google ad name
AdId	STRING	REPEATED	optional parameter to set a specific google ad identifier
AdNetworkType	STRING	REPEATED	optional parameter to set a specific network type
keywordText	STRING	REPEATED	optional parameter to set a specific referral keyword
query	STRING	REPEATED	optional parameter to set a specific referral query
OfferId	STRING	REPEATED	the offer id in systems like google shopping
item	RECORD	REPEATED	the details of the item id like for google analytics view_item	repeated record matching GA4 format
parameters	RECORD	REPEATED	speicifc parameters and values often set with available attribution values

order_product_analytics

This table has one row per transaction and per product and has the same additional attribution infromation as the order_primary_analytics table above.

This table extends the table order_product with more fields, including a repeating record field called ‘attribution’ coming from Google Analytics and Google Ads (see above).

gads_campaign_analytics

In this table we apply Google Ads attribution models (a highly inspired but different model considering Google Ads as base data and not your overal orders)

field	type	mode	comments	possible values

field	type	mode	comments
date	DATE	NULLABLE	date - PK
Device	STRING	NULLABLE	device - PK
Campaignid	INTEGER	NULLABLE	campaign id - PK
CampaignName	STRING	NULLABLE	campaign name - PK
cost	FLOAT	NULLABLE	total costs
clicks	INTEGER	NULLABLE	total clicks
conversions	FLOAT	NULLABLE	total conversions
conversionvalue	FLOAT	NULLABLE	total conversion value
impressions	INTEGER	NULLABLE	total impression
interactions	INTEGER	NULLABLE	total interactions
attributions	RECORD	REPEATED	same structure as for prior tables

gads_campaign_offer_analytics

This table is similar to gads_campaign_analytics but provides the attribution per advertised products (and therefore only consider Google Shopping Ads and not Google Search Ads).

field	type	mode	comments	possible values

field	type	mode	comments
…			all the fields of gads_campaign_analytics
products_group_id	STRING	NULLABLE	the identifier of the products group as per your doc_product data
title_en	STRING	NULLABLE	title of the product group in English
title_de	STRING	NULLABLE	... German
title_fr	STRING	NULLABLE	… French
title_it	STRING	NULLABLE	… Italian
exact_margin	FLOAT	NULLABLE	the exact margin made based on your doc_order margin data
rounded_margin	FLOAT	NULLABLE	same but rounded
exact_price	FLOAT	NULLABLE	the exact sales price based on your doc_order data
rounded_price	FLOAT	NULLABLE	same but rounded
product_owners	STRING	REPEATED	configured product owners
sku	STRING	NULLABLE	product sku
ean	STRING	NULLABLE	product ean
gtin	STRING	NULLABLE	product gtin
article_id	STRING	NULLABLE	product article id
product_properties	RECORD	REPEATED	additional generic properties
product_id_properties	RECORD	NULLABLE	additional named properties (brand, category, …)

micro_conversions_purchases

This table analysis the micro conversions from GA4.

Boxalino Public Knowledge Base

Leveraging Google Analytics and Google Ads data