Unified Traffic Schema (UTS)

This page provides a detailed description of the Unified Traffic Schema (UTS) within the Daasity Data Model and defines each table and column in this schema

Overview

The Unified Traffic Schema (UTS) is a core data model within the Daasity transformation module that helps accelerate development of analytical capability by normalizing all traffic data from Google Analytics (Universal Analytics and GA4), Amazon and retail platforms like Retail Next and ShopperTrak.

The normalized schema was designed to help you in several key areas:

1. Make an effective transition from GA3 (Universal Analytics) to GA4 by allowing you to compare metrics across the two GA platforms and pick which source to use as your truth for additional analytics

2. Seamlessly combine your GA3 (Universal Analytics) and GA4 into a single schema to make it easy to report on analytics across platforms

3. Compare overall conversion metrics across eCommerce, Amazon and Retail

4. Compare product page performance across eCommerce and Amazon

Entity Relationship Diagram (ERD)

Click on this link to view the ERD for the Unified Traffic Schema (UTS) illustrating the different tables and keys to join across tables.

Unified Traffic Schema Tables

• GA Comparison [uts.ga_comp]

• Multi Channel Funnel [uts.multichannel_funnel]

• Product Page [uts.product_page]

• Shopping Stage [uts.shopping_stage]

• Traffic [uts.traffic]

• Transactions [uts.transactions]

TIP: All the tables contain the following fields which can be used to track the data flow from the source system to the integration schema within the database and then to UTS, our normalized traffic schema

• channel: the channel associated with the UTMs from the Daasity Channel Mapping process

• vendor: the vendor associated with the UTMs from the Daasity Channel Mapping process

• sub-channel: the sub-channel associated with the UTMs from the Daasity Channel Mapping process

• media_type: the media_type associated with the UTMs from the Daasity Channel Mapping process

• integration_id: the ID of the integration within the Daasity platform

• integration_name: the name of the integration within the Daasity UI

• __loaded_at: defines when the record was last loaded into this table

• __synced_at: defines when the record was last replicated from the source system into the integration tables

• __sync_key: defines a unique key that is created as part of the replication from the source system into the integration tables

GA Comparison

Purpose: Enables you to compare your GA3 (Universal Analytics) traffic to your GA4 traffic so you can understand how your metrics will change with GA4

Table Name: uts.ga_comp

Column	Description
id	Unique key for every row in the table
ga4_property_id	The property id for GA4
ga4_stream_id	The stream id for GA4
ua_view_id	The view id for GA3 (UA)
traffic_date	The date associated with the traffic
source	UTM Source
medium	UTM Medium
campaign	UTM Campaign
channel_grouping	GA default channel grouping
new_vs_returning	If the visitor is new or returning (user_type from GA3)
device_category	Type of device
country_id	ISO code for the country
bounces	Total bounces
duration	Total duration of the visits for the row
pageviews	Total pageviews
revenue	Total revenue
sessions	Total sessions
transactions	Total transactions
traffic_data_source	Source of the data - Google Analytics
traffic_data_source_detail	Additional detail on the source - GA3 or GA4
store	ID of the store associated with the traffic
store_name	Friendly name of the store associated with the traffic
store_type	Type of store - Ecommerce

Multi-Channel Funnel

Purpose: Enables you to have a single table regardless of data source (GA3, GA4, etc.) to track the individual site visits in the conversion path to a transaction

Table Name: uts.multichannel_funnel

Column	Description
id	Unique key for every row in the table
transaction_id	ID of the transaction event (usually the order code or order number)
sequence	The sequence of the site visit in the conversion path
ga4_property_id	The property id for GA4
ga4_stream_id	The stream id for GA4
ua_view_id	The view id for GA3 (UA)
transaction_date	Date of the site visit or transaction (depends on source)
source_medium_path	UTM Source / Medium
source_path	UTM Source
medium_path	UTM Medium
campaign_path	UTM Campaign
keyword_path	UTM keyword
basic_channel_path	GA default channel grouping
fbadid	Ad ID for Facebook put into the URL for tracking
gadid	Ad ID for Google Ads put into the URL for tracking
padid	Ad ID for Pinterest put into the URL for tracking
scadid	Ad ID for Snapchat put into the URL for tracking
ttadid	Ad ID for Pinterest put into the URL for tracking
traffic_data_source	Source of the data - Google Analytics
traffic_data_source_detail	Additional detail on the source - GA3 or GA4
store	ID of the store associated with the traffic
store_name	Friendly name of the store associated with the traffic
store_type	Type of store - Ecommerce

Product Page

Purpose: Enables you to compare product page performance across digital platforms (Ecommerce vs. Marketplace) by combining your GA3/GA4 with your Amazon data

Table Name: uts.product_page

Column	Description
id	Unique key for every row in the table
ga4_property_id	The property id for GA4
ga4_stream_id	The stream id for GA4
ua_view_id	The view id for GA3 (UA)
traffic_date	The date associated with the traffic
source	UTM Source
medium	UTM Medium
campaign	UTM Campaign
channel_grouping	GA default channel grouping
amazon_sku	SKU of the Amazon product page
asin_child	Child ASIN of the Amazon product page
asin_parent	Parent ASIN of the Amazon product page
product_name	Mapped name of the Product across platforms
product_sku	Mapped SKU of the Product across platforms
featured_offer	Total sessions where product had the buy box shown (Amazon only)
product_detail_views	Total number of times the product page was viewed
product_adds_to_cart	Total number of times the product was added to the cart
product_checkouts	Total number of time the product was in the checkout
purchases	Total number of purchases
revenue	Total revenue
traffic_data_source	Source of the data - Google Analytics or Amazon
traffic_data_source_detail	Additional detail on the source - GA3, GA4 or Amazon
store	ID of the store associated with the traffic
store_name	Friendly name of the store associated with the traffic
store_type	Type of store - Ecommerce

Shopping Stage

Purpose: Enables you to have a single table regardless of data source (GA3, GA4, etc.) to track the site visit data in the conversion funnel

Table Name: uts.shopping_stage

Column	Description
id	Unique key for every row in the table
ga4_property_id	The property id for GA4
ga4_stream_id	The stream id for GA4
ua_view_id	The view id for GA3 (UA)
traffic_date	The date associated with the traffic
source	UTM Source
medium	UTM Medium
campaign	UTM Campaign
channel_grouping	GA default channel grouping
new_vs_returning	If the visitor is new or returning (user_type from GA3)
device_category	Type of device
shopping_stage	Stage of the step for the site visit in the conversion funnel
sessions	Total sessions
traffic_data_source	Source of the data - Google Analytics
traffic_data_source_detail	Additional detail on the source - GA3 or GA4
store	ID of the store associated with the traffic
store_name	Friendly name of the store associated with the traffic
store_type	Type of store - Ecommerce

Traffic

Purpose: Enables you to compare product page performance across digital platforms (Ecommerce vs. Marketplace) by combining your GA3/GA4 with your Amazon data

Table Name: uts.traffic

Column	Description
id	Unique key for every row in the table
ga4_property_id	The property id for GA4
ga4_stream_id	The stream id for GA4
ua_view_id	The view id for GA3 (UA)
traffic_date	The date associated with the traffic
source	UTM Source
medium	UTM Medium
campaign	UTM Campaign
channel_grouping	GA default channel grouping
new_vs_returning	If the visitor is new or returning (user_type from GA3)
device_category	Type of device
country_id	ISO code for the country
bounces	Total bounces
duration	Total duration of the visits for the row
featured_offer	Total sessions where product had the buy box shown (Amazon only)
pageviews	Total pageviews
revenue	Total revenue
sessions	Total sessions
transactions	Total transactions
traffic_data_source	Source of the data - Google Analytics
traffic_data_source_detail	Additional detail on the source - GA3 or GA4
store	ID of the store associated with the traffic
store_name	Friendly name of the store associated with the traffic
store_type	Type of store - Ecommerce

Transactions

Purpose: Enables you to have a single table regardless of data source (GA3, GA4, etc.) to track the site information for each transaction

Table Name: uts.transactions

Column	Description
id	Unique key for every row in the table
transaction_id	ID of the transaction event (usually the order code or order number)
ga4_property_id	The property id for GA4
ga4_stream_id	The stream id for GA4
ua_view_id	The view id for GA3 (UA)
source_platform	The platform where you manage buying activity (GA4)
transaction_date	Date of the site visit or transaction (depends on source)
source	UTM Source
medium	UTM Medium
campaign	UTM Campaign
manual_ad_content	UTM Ad Content
manual_term	UTM Term
channel_grouping	GA default channel grouping
new_vs_returning	If the visitor is new or returning (user_type from GA3)
device_category	Type of device
referral_path	Path of the incoming URL
full_referrer	Full referrer URL
country	County name
country_id	ISO code for the country
city	City
metro	Metro
region	Region
latitude	Latitude
longitude	Longitude
browser	Browser
operating_system	Operating System
revenue	Total Revenue
first_source_platform	Source platform of the first touch
first_source	Source of the first touch
first_medium	Medium of the first touch
first_campaign	Campaign of the first touch
first_manual_ad_content	Ad Content of the first touch
first_manual_term	Term of the first touch
first_channel_grouping	Default channel grouping of the first touch
traffic_data_source	Source of the data - Google Analytics
traffic_data_source_detail	Additional detail on the source - GA3 or GA4
store	ID of the store associated with the traffic
store_name	Friendly name of the store associated with the traffic
store_type	Type of store - Ecommerce