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

ColumnDescription

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

ColumnDescription

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

ColumnDescription

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

ColumnDescription

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

ColumnDescription

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

ColumnDescription

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

Last updated