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.
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.