View: facebook_spend

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp calendar database Primary
ums_staging facebook_spend database Pre-insert
ums_staging mkt_spend_source database Pre-insert
facebook_marketing ads database Primary
facebook_marketing adsets database Primary
N/A ads derived Pulls FB ad info and row number for dedup
N/A ads_unique derived Deduplicates on Ad ID
N/A adsets derived Pulls FB adset info
N/A campaigns derived Pulls FB campaign info
N/A last_sync_date derived Pulls last load date from drp.facebook_spend
N/A spend_rollup derived Pulls FB spend metrics
platform account_integrations database Primary

SQL Flow

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
total_spend DECIMAL(20,4) facebook_marketing.ad_spends spend SUM(COALESCE(spend,0))
total_clicks TIMESTAMP facebook_marketing.ad_spends cpc SUM(COALESCE(ROUND(spend/NULLIF(cpc, 0), 0)))
total_impressions BIGINT facebook_marketing.ad_spends cpm SUM(COALESCE(ROUND((spend * 1000)/NULLIF(cpm, 0), 0)))
vendor_reported_orders DECIMAL(20,4) facebook_marketing.ad_spends purchase_count SUM(COALESCE(purchase_count,0))
vendor_reported_revenue DECIMAL(20,4) facebook_marketing.ad_spends purchase_value SUM(COALESCE(purchase_value,0))
id CHAR drp.calendar calendar_date MD5(COALESCE(c.calendar_date::DATE,'2099-12-31')||':'||'Paid Social'||':'||'Facebook'||':'||COALESCE(sr.ad_account_id,'UNKNOWN')||':'||COALESCE(,'UNKNOWN')||':'||COALESCE(campaign_name,'UNKNOWN')||':'||COALESCE(ad_name,'UNKNOWN')||':'||COALESCE(ad_status,'UNKNOWN'))


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