Skip to content
English
Submit a Request Sign in
Go to daasity.com
  • There are no suggestions because the search field is empty.

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

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.