Daasity Knowledge Base
Daasity Knowledge Base
  • Start here
    • Daasity Overview
    • Important Concepts
      • Attribution Overview
      • What is a Daasity Valid Order?
      • Customer Householding
      • Timezones in Daasity
      • Ecommerce Glossary
      • Daasity Data Security Overview
    • Retail Analytics 101
      • Understanding the Basics: POS Data 101
      • How to use Syndicated Data
    • Product Changelog
    • Feature Requests
  • 🔌Extract
    • Extract your data with Daasity
    • All Integrations
      • Alchemer
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Algolia
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Amazon Advertising
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Where to Find Your Data
      • Amazon Seller Central
        • Validating Your Amazon Data in Daasity
          • Comparing Your Amazon Seller Central Orders to Daasity
        • Integration Setup
          • Configure Amazon Reports
          • Monitor Amazon Reports
        • Integration Specifications
          • Orders API
          • FBA Customer Concessions Reports
          • FBA Sales Reports
          • FBA Inventory Reports
          • FBA Removals Reports
          • Inventory Reports
          • Order Tracking Reports
          • Performance Reports
          • Returns Reports
          • Settlement Reports
          • Seller Retail Analytics Reports
        • Workflow Configuration Setup
          • Orders API
          • FBA Customer Concession Reports
          • FBA Inventory Reports
          • Performance Reports
          • FBA Sales Reports
          • FBA Removal Reports
          • Inventory Reports
          • Order Tracking Reports
          • Returns Reports
          • Seller Retail Analytics Reports
          • Settlement Reports
        • Transformation Configuration Setup
          • Orders API
          • FBA Customer Concessions Reports
          • FBA Inventory Reports
          • Performance Reports
          • FBA Sales Reports
          • FBA Removals Reports
          • Inventory Reports
          • Order Tracking Reports
          • Returns Reports
          • Seller Retail Analytics Reports
          • Settlement Reports
      • Amazon Vendor Central
        • Integration Setup
        • Integration Specifications
        • How to use
        • ASIN Mapping Configuration Setup
        • Workflow Configuration Setup
        • Transformation Configuration Setup
          • Vendor Sales & Inventory Report
          • Vendor Traffic Report
      • AppLovin
        • Integration Setup
      • Attentive Mobile
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Azure SQL Database
      • Back In Stock
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Big Commerce
        • Integration Setup
          • Connect Daasity From BigCommerce Marketplace
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • BigQuery Datasource
      • Bing Ads
        • Integration Setup
        • Integration Specifications
      • Circana
        • Integration Setup
        • Integration Specifications
        • Data Release Calendar
        • H-E-B Circana
      • Cordial
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Criteo
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • D​ata File (CSV, XLS)
      • Emplifi
        • Integration Specifications
      • Extensiv (Skubana)
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Facebook Ads
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Where to Find Your Data
      • Fairing V2
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Important Notes
      • Google Ads
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Important Notes
      • Google Analytics 4
        • API vs BigQuery
        • API Integration
          • Integration Setup
          • Integration Specifications
          • Workflow Configuration Setup
          • Transformation Configuration Instructions
          • Custom Reports
          • Validating GA4 Data in Daasity
          • Blending Universal Analytics & GA4 Data
        • BigQuery integration
          • Integration Setup
          • Integration Specifications
          • Workflow Configuration Setup
          • Transformation Configuration Instructions
          • Important Notes
          • Creating a Custom Report
      • Google Sheet
      • Gorgias
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Important Notes
      • Impact Radius (Beta)
        • Integration Specifications
      • Iterable
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Klaviyo Summary Statistics
        • Integration Setup
        • Integration Specifications
      • Klaviyo V2
        • Integration Setup
        • Integration Specifications
      • KnoCommerce
        • Integration Setup
        • Integration Specifications
      • Loop Returns
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Microsoft SQL Server Database
      • Mongo Database
      • MySQL Database
      • Narvar
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • NetSuite
      • Newstore
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Northbeam
        • Integration Setup
      • Okendo
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Ometria
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Order Desk
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Pepperjam
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Pinterest
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Postgres Database
      • Postscript (beta)
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Important Notes
      • Recharge
        • Integration Setup
        • Integration Specifications
        • Integration Specifications (2021-11 Beta)
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Recharge Data and Known Discrepancies
      • Retail Next
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Rockerbox
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Salesforce Commerce Cloud
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • SFTP
      • ShipBob
        • Integration Setup
        • Integration Specifications
      • Shipstation
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Shopify
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Shopify B2B Data
      • SKIO
        • Integration Setup
        • Integration Specifications
      • Snapchat
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • SPINS
        • Integration Setup
        • Integration Specifications
        • How to Use
        • Data Release Calendar
      • Stay Ai
        • Integration Setup
        • Integration Specifications
      • Target Plus
        • Integration Setup
      • Thankful
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • TikTok
        • Integration Setup
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Walmart Marketplace
        • Integration Setup
        • Integration Specifications
      • Whole Foods Market
        • Integration Setup
        • Integration Specifications
        • How to use
      • Yotpo
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Zendesk
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
    • Brand Supplied Data (BSD)
      • BSD Overview
      • Monitoring Your BSD Progress
      • Revenue Plan
      • Marketing Budget
      • Marketing Spend
      • RFM Values
      • Shipping & Fulfillment Cost
      • SKU Hierarchy
      • SKU Mapping
      • SKU Cost
      • Discount Code Mapping
      • Other Order Sources
      • Channel Mapping
        • Overview & Setup
        • Filling out Channel Mapping
        • Missing from GA & Missing from BSD
      • Configuration
  • 📊VISUALIZE
    • Dashboards
      • Dashboard Overview
      • Standard Dashboards
        • Daasity 101 Overview Dashboards
          • Flashes
          • Shopify Sales Report
          • Account Health
        • Digital Product & Revenue
          • Orders and Revenue
          • Amazon Settlement Report
          • Product Repurchase Rate
          • Product
            • Product Dimensions
              • Why is a Product Name NULL?
          • Inventory
          • Operations
            • Fulfilment Status Options
        • Retention Marketing Dashboards
          • Notifications (Email/SMS)
          • Retention
          • Customer Performance (Email/SMS)
          • Site Funnels
        • Acquisition Marketing Dashboards
          • Attribution Deep Dive
            • Marketing Attribution
              • Attribution Overview
              • Survey-Based Attribution
              • Discount Code Attribution
              • First Click, Last Click, and Assisted Attribution
              • Custom Attribution
              • Vendor-Reported Attribution
              • CPO, CPA, and CAC
              • Missing from GA & Missing from BSD
          • LTV & RFM
            • RFM
              • RFM in the Daasity App
              • Understanding RFM
              • Time on Books (TOB)
          • Marketing
          • Vendor-Reported Marketing Performance
          • Site Analytics & Attribution
      • Integration Dashboards
        • Amazon All Orders Report
        • Amazon Seller Central Business Reports
        • Gorgias
        • Klaviyo Campaign & Flow Performance
        • Loop Returns
        • Okendo Reviews
        • ShipBob
        • Shipstation
        • Subscription
        • Walmart Marketplace Account Sales Report
        • Yotpo
    • Explores
      • Explore Overview
      • Order Line Revenue vs. Transactional Sales Report
      • How to Access An Explore
      • Standard Daasity Explores
        • Current Inventory Level Explore
        • Customer Details Explore
        • Daily Company Metrics Explore
        • Daily Marketing Plan To Actual Marketing Performance Explore
        • Daily Plan to Actual Explore
        • Lifetime Value Explore
        • Marketing Attribution Explore
        • Multi-Channel Transaction Explore
        • Order and Order Line Revenue Explore
        • Product Page Explore
        • Shopping Stage Explore
        • SKU Level Repurchase Rates Explore
        • Traffic Explore
        • Transactional Sales Report Explore
        • UA vs G4 Explore
      • Integration Explores
        • Algolia
        • Amazon Settlement Explore
        • Fairing Survey Responses Explore
        • Gorgias Tickets Explore
        • Loop Returns Explore
        • Okendo Reviews Explore
        • Subscribers and Subscription Monthly Churn Rates Explores (Recharge)
        • Shipstation Explore
        • Yotpo
        • Zen Desk
    • Looker Guides
      • Building visualizations
      • Working with date dimensions & filters
      • Schedule a Report or Dashboard
      • Exporting Data
    • Looker Guides (Developer)
      • Daasity Hub & Spoke Introduction
      • Hub & Spoke File Structure
      • Working with LookML Dashboards
      • Customizing Base Daasity Code
    • Visualize using other BI tools
  • 🔀Transform
    • Transforming Data with Daasity
    • Daasity Data Models
      • Daasity Data Model Overview
      • Unified Data Schemas
        • Unified Schema Introduction
        • Unified Order Schema (UOS)
        • Unified Notification Schema (UNS)
        • Unified Subscription Schema (USS)
        • Unified Marketing Schema (UMS)
        • Unified Traffic Schema (UTS)
        • Unified Wholesale Schema (UWS)
      • Data Mart Models
        • Data Mart Introduction
        • Data Reporting Platform (DRP)
        • Channel Attribution Data Mart
        • Marketing Data Mart
      • Transform Code
        • Transform Code Introduction
        • Transform Code Sequence - DRP
        • Initialization Code
        • Householding Code
        • Order Status Code
        • Order Channel and Marketing Code
        • Daily Plan Code
        • Order and Order Line Revenue
        • Plan to Actual Code
        • Customer Code
        • Subscription Code
        • Reviews
        • Life-Time Value Code
        • Returns
        • Product and Inventory Code
        • Email & SMS
        • Transform Code Sequence - Data Mart
        • Unified Order Schema (UOS) Code
        • Unified Marketing Schema (UMS) Code
        • Unified Notification Schema (UNS)
        • Channel Attribution Data Mart Code
        • Marketing Data Mart Code
      • Source to Final Destination
    • Code Repository
      • Code Repository Overview
      • Getting Access to Your Code Repository
      • Code Repository Branches
      • Custom SQL Code
      • Custom Script Manifest Files
    • Script Manifest (YAML) Files
  • 🎵Data Orchestration
    • Workflows
      • Getting Started with Workflows
      • Creating Workflows
      • Managing Workflows
      • Workflow Metrics
    • Notifications
      • Introduction to Notifications
      • Enabling Notification Emails
      • Notifications UI
      • Managing Notifications
      • Authorization List
  • Audiences
    • Audiences Overview
    • Pushing Audience Data to Targets
      • Targets Overview
      • Attentive
        • Events Target
        • Subscribers Target
        • Target Endpoint Documentation
      • Facebook Ads
        • Facebook Target
        • Target Endpoint Documentation
      • Google Ads
        • Google Ads Target
        • Target Endpoint Documentation
      • Klaviyo
        • Events Target
        • Profiles Target
        • Target Endpoint Documentation
      • Sailthru
        • Events Target
        • Users Target
        • Target Endpoint Documentation
      • Tiktok Ads
        • TikTok Target
        • Target Endpoint Documentation
    • Building Audiences
  • warehouse
    • BigQuery
    • Redshift
    • Snowflake
    • Test Data Warehouse
      • Test Data Warehouse Overview
      • Create a Snowflake Test Warehouse
      • Create a Redshift Test Warehouse
  • —
    • Go to Daasity App
    • Contact Support
    • ✍️Feature Requests
Powered by GitBook
On this page
  • Overview
  • Entity Relationship Diagram (ERD)
  • Unified Traffic Schema Tables
  • GA Comparison
  • Multi-Channel Funnel
  • Product Page
  • Shopping Stage
  • Traffic
  • Transactions

Was this helpful?

  1. Transform
  2. Daasity Data Models
  3. Unified Data Schemas

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

PreviousUnified Marketing Schema (UMS)NextUnified Wholesale Schema (UWS)

Last updated 1 year ago

Was this helpful?

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 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

🔀
Click on this link to view