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

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

Assisted Orders

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

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

Create the final [dm_mkt.fct_assisted_orders] table

Vendor Marketing Performance

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

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

Create the final [dm_chn.fct_vendor_level_performance] table

Was this helpful?