Unified Marketing Schema (UMS) Code

This page provides information on the transform code block that is needed to populate the Unified Marketing Schema (UMS) and what should be added to a Script Manifest file in order to execute the code

Workflows

Upstream Data Mart Dependencies

There are no Data Marts that need to be updated prior to UOS

The proper sequence of either a Workflow or position in a Script Manifest file to populate this Data Mart is highlighted in the Transform Code Sequence - Data Mart page

Script Manifest File (YML)

Transformation Code Requirements

Transformation code should be added to the UOS block below depending on the integration. Instructions on what code to add for each integration is available in the following sections:

• Amazon Ads

• Criteo

• Facebook Ads

• Google Ads

• Pepperjam

• Pinterest

• Snapchat

• Tiktok

• Twitter

The following code should also be added to the UMS block to make sure that all the Brand Supplied Data is included in UMS and additional updates to UMS are made

  #--------------------------------------------------------------------------------------------------------------------
  # SECTION: 1500_UMS
  # DEPENDENCIES: Marketing Integrations
  # OPTIONAL:
  #   - Integration: Amazon Ads
  #   - Integration: Criteo
  #   - Integration: Facebook
  #   - Integration: Google Adwords
  #   - Integration: Pepperjam
  #   - Integration: Pinterest
  #   - Integration: Rockerbox
  #   - Integration: Snapchat
  #   - Integration: Tiktok
  #   - Integration: Twitter
  # REQUIRED: No
  # PURPOSE: These SQL scripts transform data from the source schema into the UMS schema.  The code should be enabled
  # when the integration has been added
  #--------------------------------------------------------------------------------------------------------------------
  ums:
    integrations:
    scripts:
      - "github://platform-sql-shared/scripts/base/1500_ums/1510_UMS_BAS_initialize_spend.sql"
      - "github://platform-sql-shared/scripts/base/1500_ums/vendor_kpi/2510_UMS_BAS_initialize_vendor_performance.sq
      
  # INTEGRATION CODE GOES HERE
  
    ums_final:
    # - this block always needs to be at the end after the different integrations
    integrations:
    scripts:
      - "github://platform-sql-shared/scripts/pro/1500_ums/1580_UMS_BAS_mktg_spend_source.sql"
      - "github://platform-sql-shared/scripts/pro/1500_ums/1900_UMS_BAS_master_spend.sql"

Unified Marketing Schema

Add this code to populate all of the UMS tables and include data from the Brand Supplied Data section of the Daasity App

UMS

Add this code to initialize the marketing spend from the BSD and the Vendor KPI tables

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1500_ums/1510_UMS_BAS_initialize_spend.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1500_ums/vendor_kpi/2510_UMS_BAS_initialize_vendor_performance.sql

UMS Final

Add this code to complete the UMS transformation

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/1500_ums/1580_UMS_BAS_mktg_spend_source.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/1500_ums/1900_UMS_BAS_master_spend.sql