Source Database Table
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|
|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|
To View Larger Image, Right Click and Choose Open Image In New Tab.
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.