4111_CST_BAS_daily_last_click_cost_per_order Transformation

Target Database Table

drp.daily_last_click_cpo_cpa.

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

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

4111_CST_BAS_daily_last_click_cost_per_order Transformation

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.