1520_UMS_BAS_facebook_spend Transformation

Target Database Table


Script Description and Logic

Aggregates daily Facebook spend metrics down the hierararchy of an organization's social media spending, including advertising account, campaign, ad name, and status. Metrics include impressions, spend, clicks, and vendor reported and last click orders and revenue. Daily channel vendor spend metrics are also inserted into the master marketing spend staging table.

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

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

1520_UMS_BAS_facebook_spend Transformation

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(ai.name,'UNKNOWN')||':'||COALESCE(campaign_name,'UNKNOWN')||':'||COALESCE(ad_name,'UNKNOWN')||':'||COALESCE(ad_status,'UNKNOWN'))

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