4110_CST_BAS_daily_first_click_cost_per_order Transformation

Target Database Table

drp.daily_first_click_cpo_cpa.

Script Description and Logic

Creates a daily level first 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 first_click derived First click channel attribution dimensions
N/A last_sync_date derived Finding max load date

SQL Flow

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

 4110_CST_BAS_daily_first_click_cost_per_order Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
first_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_first_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 fc.first_click_orders = 0 THEN 0
ELSE ms.total_spend / fc.first_click_orders
END
cost_per_acquisition DECIMAL(20,4) drp.master_spend total_spend CASE
WHEN ms.total_spend IS NULL THEN NULL
WHEN fc.new_customer_first_click_orders = 0 THEN 0
ELSE ms.total_spend / fc.new_customer_first_click_orders
END


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