4120_CST_BAS_order_marketing_cost Transformation

Target Database Table

drp.order_cpo_cpa.

Script Description and Logic

Pulls first click cost per order, first click cost per acquisition, last click cost per order, last click cost per acquisition, order id, customer id, shop id and total featured items at the order level. Orders from all integrations are included in the primary source table, drp.order_status, and reported by unique order id, customer id and store combination. From the order_status table, a join is made to drp.order_channel to return a list of orders with tracked order channels.

The daily_first_click_cpo_cpa and daily_last_click_cpo_cpa tables are then joined into this list to pull in first and last click cost per order, and first and last click cost per acquisition. The unique combinations of order id, customer id, store id, first and last click cost per order, and first and last click cost per acquisition 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 drp.order_cpo_cpa table. The code looks back 30 days from last_load_dt to get that list of orders and then holds them in a staging table.

The records in staging are compared to those in the final table. Any orders 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.order_cpo_cpa.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_status database Primary source
drp_staging order_cpo_cpa database Pre-insert
drp daily_first_click_cpo_cpa database Lookup
drp daily_last_click_cpo_cpa datebase Lookup
drp order_channel database Ensure unique records
N/A last_sync_date derived Obtain last sync date for each record

SQL Flow

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

 4120_CST_BAS_order_marketing_cost Transformation

Calculated and Derived Fields

 N/A.

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