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:
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?