# Unified Marketing Schema (UMS) ## 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](https://lucid.app/documents/embedded/20d7b2c7-628a-44e1-959b-b00d8b53c422) 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](#vendor-performance) \[`ums.vendor_performance`] {% hint style="info" %} **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 {% endhint %} {% hint style="warning" %} **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 {% endhint %} ### **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 |