Unified Marketing Schema (UMS)

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

Overview

The Unified Marketing Schema (UMS) is a core data model within the Daasity transformation module that helps accelerate development of analytical capability by normalizing all marketing data. The normalized schema was designed to support all types of marketing platforms: affiliate, display, marketplace, paid search and paid social

Entity Relationship Diagram (ERD)

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

Unified Order Schema Tables

• Vendor Performance [ums.vendor_performance]

TIP: Many of 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 UOS, our normalized order schema

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

NOTE: Legacy code will create two additional tables [ums.master_spend] and [ums.marketing_performance]. These tables will be deprecated in the coming months as new data marts are rolled out

Vendor Performance

Purpose: Enables you to normalize marketing spend and performance metrics from different marketing platforms into a single schema for standardized reporting.

Table Name: ums.vendor_performance

Column	Description
ID	Unique identifier for the each row in the table - comprised of date, integration_id, ad_account_id, integration_name, campaign_id, adset_id, ad_id and attribution_window
ACTIVITY_DATE	Date of the activity
STORE	Store linked to this marketing integration in the daasity app
CHANNEL	The type of ad platform, e.g.: Paid Social, Paid Search, Display
VENDOR	The ad platform name
SUBCHANNEL	Sub-channel associated with the marketing ad
MEDIA_TYPE	Media type of the marketing ad
AD_ACCOUNT_ID	The unique identifier of the advertiser account on the ad platform
AD_ACCOUNT_NAME	The name of the advertiser account on the ad platform
PROPERTY_NAME	Property where the ad is displayed
NETWORK_TYPE	Identifies the marketing platform for vendors that have multiple platforms (ex: Google Search vs. Youtube)
CAMPAIGN_ID	The unique identifer for the campaign
CAMPAIGN_NAME	The most recent campaign name for the campaign_id
CAMPAIGN_STATUS	The status of the campaign at time of the latest extraction
ADSET_ID	The unique identifier for the ad set. Note this may be labeled slightly different in some ad platforms, e.g.: ad group, ad squad. Google Performance Max campaigns will always have NULL ad set IDs
ADSET_NAME	The name of the ad set. Note this may be labeled slightly different in some ad platforms, e.g.: ad group, ad squad. Google Performance Max campaigns will always have NULL ad set names
ADSET_STATUS	The status of the ad set at time of the latest extraction. Note this may be labeled slightly different in some ad platforms, e.g.: ad group, ad squad
AD_ID	The unique identifier for the ad. Note that Google Performance Max campaigns will always have NULL ad IDs
AD_NAME	The name of the ad. Note Google Ads does not have ad names available
AD_DESCRIPTION	Description of the ad from the marketing platform
AD_URL	URL of the ad
AD_STATUS	The status of the ad at time of the latest extraction
TOTAL_SPEND	Total spend from the vendor
TOTAL_CLICKS	Total clicks from the vendor
TOTAL_IMPRESSIONS	Total impressions from the vendor
VENDOR_REPORTED_ORDERS	Total orders from the vendor based on the attribution window
VENDOR_REPORTED_REVENUE	Total revenue from the vendor based on the attribution window
KEYWORD	Populated if there is a keyword associated with the marketing
REVENUE_CLICKTHROUGH	Total click through revenue from the vendor
REVENUE_VIEWTHROUGH	Total view through revenue from the vendor
PURCHASES_CLICKTHROUGH	Total click-through purchases from the vendor
PURCHASES_VIEWTHROUGH	Total view-through purchases from the vendor
SEGMENT_1	Placeholder for custom segmentation
SEGMENT_2	Placeholder for custom segmentation
SEGMENT_3	Placeholder for custom segmentation
SEGMENT_4	Placeholder for custom segmentation
SEGMENT_5	Placeholder for custom segmentation
VIDEO_VIEWS	All video views
VIDEO_VIEWS_PCT_25	Video view count where user reached the 25% duration mark
VIDEO_VIEWS_PCT_50	Video view count where user reached the 50% duration mark
VIDEO_VIEWS_PCT_75	Video view count where user reached the 75% duration mark
VIDEO_VIEWS_PCT_100	Video view count where user reached the 100% duration mark
ORIGINAL_CURRENCY	Currency reported by the vendor
CURRENCY_CONVERSION_RATE	Rate used to convert monetary values from original_currency to target_currency
TARGET_CURRENCY	Currency of the Daasity account
ATTRIBUTION_WINDOW	Attribution window of the marketing ad
SOURCE_RANK	The rank of the data source for deduplication