Daasity Data Model: Unified Traffic Schema (UTS)

This article provides a detailed description of the Unified Traffic Schema (UTS) within the Daasity Data Model. This article will help you understand the purpose of each table and column in this data model.

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)

The embedded diagram houses the ERD for the Unified Traffic Schema (UTS) illustrating the different tables and keys to join across tables.  Hover over the embedded diagram to reveal controls to zoom and scroll.

Unified Traffic Schema Tables

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