partner-logos-color-facebook-ads - Daniel Anthony

Overview

This integration handles Facebook Ads including delivery, conversions, and metadata refresh for accounts, campaigns, adsets, and ads. The data is imported daily to track ad performance, conversions, and maintain up-to-date metadata for analysis and reporting.

Granularity: Account → Campaign → Ad Set → Ad level

Purpose: Track delivery and conversion metrics (spend, impressions, clicks, video views, conversions, etc.) and enrich with related metadata for performance analysis and reporting.

Refresh Cadence: Daily

Update Modes: Append (for delivery and conversion data), Refresh (for metadata)

For more information on the Meta Ads integration please follow this link

Jobs Configuration

1. Facebook Ads - Delivery Import

Feed: facebook
Status: Active (2)
Job Label: Facebook Ads - Delivery Import
Lookback: Last 5 days
First Run Hour: 6 AM daily
Poll Frequency: Every 24 hours
Targets:
- Table: facebook_data_insights
- Description: Facebook Ads - Delivery Data
- Update Mode: Append new data daily
- Date Column: date_start
- Priority: 1

2. Facebook Ads - Conversions Import

Feed: facebook
Status: Active (2)
Job Label: Facebook Ads - Conversions Import
Lookback: Last 7 days
First Run Hour: 6 AM daily
Poll Frequency: Every 24 hours
Options:
- fb_attribution_windows (required, default: "7d_click,1d_view")
  - Defines attribution lookback windows for clicks and views.
Targets:
- Table: facebook_data_conversions
- Description: Facebook Ads - Conversion Data
- Update Mode: Append new data daily
- Date Column: date_start
- Priority: 1

3. Facebook Ads - Meta Refresh

Feed: facebook
Status: Active (2)
Job Label: Facebook Ads - Meta Refresh
Lookback: Last 1 day
First Run Hour: 6 AM daily
Poll Frequency: Every 24 hours
Targets:
- facebook_meta_account (priority 1, refresh mode)
- facebook_meta_campaign (priority 2, refresh mode)
- facebook_meta_adset (priority 3, refresh mode)
- facebook_meta_ad (priority 4, refresh mode)

Connection Details

Parameter	Description	Notes
Username	`{val_1}`	Facebook Ads API username
Secret	`{val_2}`	Facebook Ads API secret/token
Options	`fb_account_ids`	Comma-separated Facebook Ad Account IDs (required)

Dimensions

Dimension Name	Maps to Fields
Account [Meta]	`facebook_meta_account.name`
Adset [Meta]	`facebook_meta_adset.name`
Ad [Meta]	`facebook_meta_ad.name`
Campaign [Meta]	`facebook_meta_campaign.name`
Publisher Platform [Meta]	`facebook_data_insights.publisher_platform`, `facebook_data_conversions.publisher_platform`
Device [Meta]	`facebook_data_insights.impression_device`, `facebook_data_conversions.impression_device`

Base Metrics

Metric Name	Formula	Description
Spend [Meta]	`SUM({spend})`	Total ad spend
Clicks [Meta]	`SUM({clicks})`	Total clicks
Unique Clicks [Meta]	`SUM({unique_clicks})`	Unique clicks
Link Clicks [Meta]	`SUM({actions_link_click})`	Link clicks
Impressions [Meta]	`SUM({impressions})`	Total impressions
Post Shares [Meta]	`SUM({actions_post})`	Post shares
Photo View [Meta]	`SUM({actions_photo_view})`	Photo views
Post Comment [Meta]	`SUM({actions_comment})`	Post comments
Post Like [Meta]	`SUM({actions_like})`	Post likes
Post Reaction [Meta]	`SUM({actions_post_reaction})`	Post reactions
Page Engagement [Meta]	`SUM({actions_page_engagement})`	Page engagement
Video Views [Meta]	`SUM({actions_video_view})`	Video views
10-Sec Video Views [Meta]	`SUM({video_thruplay_watched_actions_video_view})`	10-second video views
Video Views Quartile - 25% [Meta]	`SUM({video_p25_watched_actions_video_view})`	25% video views
Video Views Quartile - 50% [Meta]	`SUM({video_p50_watched_actions_video_view})`	50% video views
Video Views Quartile - 75% [Meta]	`SUM({video_p75_watched_actions_video_view})`	75% video views
Video Views Quartile - 100% [Meta]	`SUM({video_p100_watched_actions_video_view})`	100% video views
Completed Views [Meta]	`SUM({video_p95_watched_actions_video_view})`	Completed video views

Datasource Joins

Data joins are performed to link insights and conversions tables to the respective meta data tables:

Join Field (LHS)	Join Field (RHS)	Source Table
ad_id	facebook_meta_ad.id	facebook_data_insights
adset_id	facebook_meta_adset.id	facebook_data_insights
account_id	facebook_meta_account.id	facebook_data_insights
campaign_id	facebook_meta_campaign.id	facebook_data_insights
ad_id	facebook_meta_ad.id	facebook_data_conversions
adset_id	facebook_meta_adset.id	facebook_data_conversions
account_id	facebook_meta_account.id	facebook_data_conversions
campaign_id	facebook_meta_campaign.id	facebook_data_conversions

Composite Metrics

Metric Name	Formula	Description	Round	Prefix	Suffix
CPC [Meta]	`{Spend [Meta]} / {Clicks [Meta]}`	Cost per click	2	£
CPM [Meta]	`{Spend [Meta]} / {Impressions [Meta]} * 1000`	Cost per thousand impressions	2	£
CTR [Meta]	`{Clicks [Meta]} / {Impressions [Meta]} * 100`	Click-through rate (%)	2		%
CPV [Meta]	`{Spend [Meta]} / {Video Views [Meta]}`	Cost per video view	2	£
CPCV [Meta]	`{Spend [Meta]} / {Completed Views [Meta]}`	Cost per completed view	2	£
VCR [Meta]	`{Completed Views [Meta]} / {Video Views [Meta]} * 100`	Video completion rate (%)	2		%
VTR [Meta]	`{Completed Views [Meta]} / {Impressions [Meta]} * 100`	Video thru rate (%)	2		%

Notes

All date references use the date_start column for data timestamps.
Update mode for delivery and conversion data is append to accumulate new data over time.
Meta data tables use refresh mode to ensure latest metadata is always available.
Attribution windows for conversion data can be configured via the fb_attribution_windows option.
Dimensions enable slicing data by account, campaign, adset, ad, publisher platform, and device.

Facebook Reach

📝 Summary

Facebook Reach reporting allows you to ingest and analyze estimated reach and ad recall metrics across the Campaign, Adset, and Ad levels. This dataset is an extension of the Facebook Ads integration and shares the same account connection. It leverages historical data with a 5-day lookback window and aligns with the meta tables for enriched reporting.

Reach data is particularly useful for brand awareness, lift studies, and understanding the unique audience reach over time.

⚙️ Job: `Facebook Ads - Reach Import`

Field	Value
Feed	`facebook`
Job Label	Facebook Ads - Reach Import
Status	2
First Run Hour	6
Poll Frequency	24 (hours)
Lookback Qty	5
Lookback Unit	day
Lookback Window	0

Options:

reach_mode
- Type: hidden
- Value: true
- Description: Facebook Ads - Reach mode flag

🎯 Targets

Table	Description	Update Mode	Date Column
`facebook_data_reach_ad`	Facebook Ads - Reach Data (Ad Level)	append	`bright_custom_date`
`facebook_data_reach_adset`	Facebook Ads - Reach Data (Adset Level)	append	`bright_custom_date`
`facebook_data_reach_campaign`	Facebook Ads - Reach Data (Campaign Level)	append	`bright_custom_date`

🧩 Dimensions

Name	Result Field
Account [Meta]	`facebook_meta_account.name`
Adset [Meta]	`facebook_meta_adset.name`
Ad [Meta]	`facebook_meta_ad.name`
Campaign [Meta]	`facebook_meta_campaign.name`

📊 Base Metrics

🔹 Reach [Meta]

Description: Final reported reach from Facebook across campaign, adset, and ad levels
Format: Number
Rounding: 0

Formula:

LAST_VALUE({facebook_data_reach_campaign.reach})
  OVER (
    PARTITION BY {facebook_data_reach_campaign.campaign_id}
    ORDER BY {facebook_data_reach_campaign.bright_custom_date}
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )

LAST_VALUE({facebook_data_reach_adset.reach})
  OVER (
    PARTITION BY {facebook_data_reach_adset.campaign_id}
    ORDER BY {facebook_data_reach_adset.bright_custom_date}
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )

LAST_VALUE({facebook_data_reach_ad.reach})
  OVER (
    PARTITION BY {facebook_data_reach_ad.campaign_id}
    ORDER BY {facebook_data_reach_ad.bright_custom_date}
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )

🔹 Recall Rate [Meta]

Description: Estimated ad recallers divided by reach
Format: Percentage
Rounding: 2
Suffix: %