Marketing Data Mart Code

This page provides information on the transform code block that is needed to create Marketing Data Mart and what should be added to a Script Manifest file in order to execute the code

Workflows

Upstream Data Mart Dependencies

This data mart and code block is dependent on the following data marts being updated in order to update with new data:

• UOS

• UTS

• UMS

• Channel Attribution Data Mart

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

The following code should be added in the Script Manifest file

  assisted_orders:
    integrations:
      - google_analytics
    scripts:
      - "github://platform-sql-shared/scripts/datamart/14_marketing/001_MKT_BAS_table_creation.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/002_MKT_BAS_create_uts_tables.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/100_MKT_BAS_stg_mcf_channel_vendor_no_last_touch_direct.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/101_MKT_BAS_stg_mcf_channel_no_last_touch_direct.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/102_MKT_BAS_stg_mcf_vendor_no_last_touch_direct.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/110_MKT_BAS_stg_mcf_sales_report_summary.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/120_MKT_BAS_stg_channel_assisted_orders.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/130_MKT_BAS_stg_vendor_assisted_orders.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/140_MKT_BAS_stg_channel_vendor_assisted_orders.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/150_MKT_BAS_fct_assisted_orders.sql"

  fct_vendor_level_performance:
    integrations:
      - google_analytics
    scripts:
      - "github://platform-sql-shared/scripts/base/1500_ums/2580_UMS_BAS_bsd_marketing_spend.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/152_MKT_BAS_stg_channel_vendor_update_list.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/153_MKT_BAS_stg_attribution_orders_update_list.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/154_MKT_BAS_stg_uos_first_order.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/160_MKT_BAS_stg_ums_vendor_performance_summary.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/170_MKT_BAS_stg_mkt_order_revenue_first_click.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/180_MKT_BAS_stg_mkt_order_revenue_first_ad_click.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/190_MKT_BAS_stg_mkt_order_revenue_first_mktg_click.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/200_MKT_BAS_stg_mkt_order_revenue_last_click.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/210_MKT_BAS_stg_mkt_order_revenue_last_ad_click.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/220_MKT_BAS_stg_mkt_order_revenue_last_mktg_click.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/230_MKT_BAS_stg_mkt_order_revenue_self_reported.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/240_MKT_BAS_stg_mkt_order_revenue_attribution.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/250_MKT_BAS_stg_mkt_daily_spend.sql"
      - "github://platform-sql-shared/scripts/datamart/14_marketing/260_MKT_BAS_fct_vendor_level_performance.sql"

Marketing

Implement all of the Assisted Orders and Vendor Level blocks to create the following tables:

• [dm_mkt.fct_assisted_orders]

• [dm_chn.fct_vendor_level_performance]

Assisted Orders

Enable this code to create the final tables for analysis and any dependent tables:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/001_MKT_BAS_table_creation.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/002_MKT_BAS_create_uts_tables.sql

Enable this code to determine the assisted order view of marketing performance:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/100_MKT_BAS_stg_mcf_channel_vendor_no_last_touch_direct.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/101_MKT_BAS_stg_mcf_channel_no_last_touch_direct.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/102_MKT_BAS_stg_mcf_vendor_no_last_touch_direct.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/110_MKT_BAS_stg_mcf_sales_report_summary.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/120_MKT_BAS_stg_channel_assisted_orders.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/130_MKT_BAS_stg_vendor_assisted_orders.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/140_MKT_BAS_stg_channel_vendor_assisted_orders.sql

Create the final [dm_mkt.fct_assisted_orders] table

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/150_MKT_BAS_fct_assisted_orders.sql

Vendor Marketing Performance

Ensure any marketing spend that is capture in the BSD is incorporated into the marketing performance

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1500_ums/2580_UMS_BAS_bsd_marketing_spend.sql

Determine all the vendor / channel combinations and calculate the marketing performance for each attribution method

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/152_MKT_BAS_stg_channel_vendor_update_list.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/153_MKT_BAS_stg_attribution_orders_update_list.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/154_MKT_BAS_stg_uos_first_order.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/160_MKT_BAS_stg_ums_vendor_performance_summary.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/170_MKT_BAS_stg_mkt_order_revenue_first_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/180_MKT_BAS_stg_mkt_order_revenue_first_ad_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/190_MKT_BAS_stg_mkt_order_revenue_first_mktg_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/200_MKT_BAS_stg_mkt_order_revenue_last_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/210_MKT_BAS_stg_mkt_order_revenue_last_ad_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/220_MKT_BAS_stg_mkt_order_revenue_last_mktg_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/230_MKT_BAS_stg_mkt_order_revenue_self_reported.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/240_MKT_BAS_stg_mkt_order_revenue_attribution.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/250_MKT_BAS_stg_mkt_daily_spend.sql

Create the final [dm_chn.fct_vendor_level_performance] table

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/14_marketing/260_MKT_BAS_fct_vendor_level_performance.sql