4410_MKP_BAS_marketing_performance Transformation

Source Database Table

drp.marketing_performance.

Script Description and Logic

Pulls marketing channel, marketing vendor, marketing activity date, vendor reported metrics and first click and last click related metrics for each unique combination of date, channel and vendor. Spend in all integrations are included in the primary source table, drp.master_spend, and reported by unique date, channel and vendor.

From drp.master_spend and drp.order_channel lookup are made to generate a list of vendors and channel with first and last click where there is spend, these lists are then joined together as channel. This channel list is then allocated to the daily level by joining to drp.calendar on the date of the spend and stored as daily_channels.

Spend is then rolled up by summing the metrics in drp.master_spend by data, channel and vendor. From drp.order_line_revenue a list of first click orders and a list of last click orders is generated by join order_status and order_channel by day, channel and vendor, these lists are stored as daily_channel_first_click_orders and daily_channel_last_click_orders. From daily_channels, a join is made to spend to allocate spend to the unique date, channel and vendor. daily_channels, daily_channel_first_click_orders and daily_channel_last_click_orders are then combined together and rolled up.

The unique combinations of marketing date, marketing channel and vendor are then placed in a staging table in preparation for incremental functionality. Incremental functionality is included such that only the records that require an update or are new get inserted into the final marketing_performance table. The records in staging are compared to those in the final table. Any marketing performance ids that match are removed from the final table and updated by way of insertion from staging.

The records that appear in staging, but not in the final table are inserted into drp.marketing_performance.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_channel database Primary
drp master_spend database Primary
drp calendar database Lookup
drp order_line_revenue database Primary
drp order_status database Lookup
N/A total_rollup derived Union of daily_channel_first_click_orders and daily_channel_last_click_orders
N/A daily_channel_first_click_orders derived Obtain valid orders where the order is a first click order
N/A daily_channel_last_click_orders derived Obtain valid orders where the order is a last click order
drp_staging marketing_performance database Pre-insert

SQL Flow

To View Larger Image, Right Click and Choose Open Image In New Tab.

4410_MKP_BAS_marketing_performance Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
total_spend DECIMAL drp master_spend.total_spend SUM(spend) attributed to the day, channel and vendor
total_clicks BIGINT drp master_spend.total_clicks SUM(clicks) attributed to the day, channel and vendor
total_impressions BIGINT drp master_spend.total_impressions SUM(impressions) attributed to the day, channel and vendor
vendor_reported_orders BIGINT drp master_spend.vendor_report_orders SUM(vendor) reported orders to the day, channel and vendor
vendor_reported_revenue DECIMAL drp master_spend.vendor_reported_revenue SUM(vendor) reported revenue to the day, channel and vendor
first_click_orders BIGINT drp order_channel.order_id COUNT(valid orders) per unique day, channel and vendor where order is a first click order
first_click_new_orders BIGINT drp order_channel.order_id COUNT(valid orders) per unique day, channel and vendor where order is a first click order and first_order_flag = TRUE
first_click_gross_sales DECIMAL drp order_line_revenue.gross_sales SUM(gross sales) per unique day, channel and vendor where order is a first click order
first_click_net_sales DECIMAL drp order_line_revenue.net_sales SUM(net sales) per unique day, channel and vendor where order is a first click order
first_click_gross_margin DECIMAL drp order_line_revenue.gross_margin SUM(gross margin) per unique day, channel and vendor where order is a first click order
last_click_orders BIGINT drp order_channel.order_id COUNT(valid orders) per unique day, channel and vendor where order is a last click order
last_click_new_orders BIGINT drp order_channel.order_id COUNT(valid orders) per unique day, channel and vendor where order is a last click order and first_order_flag = TRUE
last_click_gross_sales DECIMAL drp order_line_revenue.gross_sales SUM(gross sales) per unique day, channel and vendor where order is a last click order
last_click_net_sales DECIMAL drp order_line_revenue.net_sales SUM(net sales) per unique day, channel and vendor where order is a last click order
last_click_gross_margin DECIMAL drp order_line_revenue.gross_margin SUM(gross margin) per unique day, channel and vendor where order is a last click order


All content © Daasity 2021. Do not copy, share or distribute.