1520_UMS_BAS_facebook_spend Transformation

Target Database Table

ums.facebook_spend.

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'))