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

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