View: Intermediary DRP Table(s): drp.order_cpo_cpa

Source Database Table

drp.order_cpo_cpa

Script that Populates Database Table

4120_CST_BAS_order_marketing_cost.sql

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

SQL Flow

Calculated and Derived Fields

 N/A

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