Target Database Table
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_channel||database||Ensure unique records|
|N/A||last_sync_date||derived||Obtain last sync date for each record|
To View Larger Image, Right Click and Choose Open Image In New Tab.
Calculated and Derived Fields
All content © Daasity 2021. Do not copy, share or distribute.