View: facebook_spend

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

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