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
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
Calculated and Derived Fields
N/A
All content © Daasity 2021. Do not copy, share or distribute.