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:
- 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
- Seamlessly combine your GA3 (Universal Analytics) and GA4 into a single schema to make it easy to report on analytics across platforms
- Compare overall conversion metrics across eCommerce, Amazon and Retail
- 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 |