Source Database Table
drp.daily_last_click_cpo_cpa
Script that Populates Database Table
4111_CST_BAS_daily_last_click_cost_per_order.sql
Script Description and Logic
Creates a daily level last click cost per order and cost per acquisition calculation across marketing channels and vendors for the last 30 days
Source Tables Used In Script
| Schema | Table (or Derived Table) Name | Table Type | Purpose |
| drp | daily_first_click_cpo_cpa | database | Primary |
| drp | master_spend | database | Primary |
| drp | order_channel | database | Primary |
| drp | order_status | database | Finding first orders |
| N/A | last_click | derived | Last click channel attribution dimensions |
| N/A | last_sync_date | derived | Finding max load date |
SQL Flow
Calculated and Derived Fields
| Target Column | Target Column Data Type | Source schema.table | Source Column | Transformation/Logic |
| last_click_orders | INT | drp.order_channel | N/A | COUNT(*) FROM drp.order_channel oc INNER JOIN drp.order_status os ON oc.order_id = os.order_id |
| new_customer_last_click_orders | INT | drp.order_channel | first_order_flag | SUM(CASE WHEN os.first_order_flag = TRUE THEN 1 ELSE 0 END) |
| cost_per_order | DECIMAL(20,4) | drp.master_spend | total_spend | CASE WHEN ms.total_spend IS NULL THEN NULL WHEN lc.last_click_orders = 0 THEN 0 ELSE ms.total_spend / lc.last_click_orders END |
| cost_per_acquisition | DECIMAL(20,4) | drp.master_spend | total_spend | CASE WHEN ms.total_spend IS NULL THEN NULL WHEN lc.new_customer_last_click_orders = 0 THEN 0 ELSE ms.total_spend / lc.new_customer_last_click_orders END |
All content © Daasity 2021. Do not copy, share or distribute.