Facebook Ads

Facebook Ads provides a history of the settings of your Ad Account, Campaigns, Ad Sets, Ads, and Ad Creatives. Facebook Ads also fetches Ad Insights data and Instagram Ads data.

See Facebook's Marketing API documentation for more information.

Features

Feature Name	Supported	Notes
Capture deletes		`AD_HISTORY`, `AD_SET_HISTORY`, `CAMPAIGN_HISTORY`, and `CREATIVE_HISTORY` tables.
History mode		All columns. We capture the delta of changes between syncs.
Custom data		`ACCOUNT_ATTRIBUTION` and `AD_SET_ATTRIBUTION` tables. All columns in these tables are custom except for primary and foreign key columns.
Data blocking		Column level
Column hashing
Re-sync		Connection and table level. Only fetches reporting data.
API configurable		API configuration
Priority-first sync		Pre-build and custom reporting tables.
Fivetran data models		Get the models: source / transform / creative history; Get the ad reporting model; Supports Quickstart data models
Private networking
Authorization via API

Supported deployment models

We support the SaaS and Hybrid deployment models for the connector.

You must have an Enterprise or Business Critical plan to use the Hybrid Deployment model.

Setup guide

Follow our step-by-step Facebook Ads setup guide to connect Facebook Ads with your destination using Fivetran connectors.

Sync overview

You can choose to configure your Facebook Ads connection in two ways:

Sync all accounts syncs all the accounts you have access to.
Sync specific accounts lets you select the accounts you want to sink.

Selecting Sync all accounts automatically adds and syncs any new accounts.

We retrieve the following tables and their related data during every sync:

all AD_ACCOUNTS
ADS
CAMPAIGNS
AD_SETS
AD_CREATIVES
CUSTOM_CONVERSIONS
REPORT

A sync may be interrupted due to requesting too much data, missing permissions, or a temporary outage of an external API service. When this happens, we use other methods to fetch the data. See our Sync Fallback Algorithms documentation.

We retrieve most metadata tables from Facebook during every sync.

However, we incrementally sync several tables, see our metadata tables documentation for more information.

Rollback sync

A rollback sync is a sync that automatically runs once a day. Rollback syncs capture changes that happen outside of the incremental sync time frame.

The rollback window for custom and pre-built reports depends on the maximum value between the Click Attribution Window and View Attribution Window settings. By default, we sync data from the last seven days during each sync. We perform a one-day rollback sync if you set the Click Attribution Window and View Attribution Window to DAY_1. We perform a 28-day rollback sync if you set both the Click Attribution Window and View Attribution Window to NONE.

The supported values you can set for the Click Attribution Window and View Attribution Window are:

DAY_1
DAY_2
DAY_7
DAY_28
NONE

Sync fallback algorithms

Skipping fields in metadata tables

Fields that require extra permissions or contain too much data may cause a metadata sync to fail. In such cases, we skip querying those specific fields. We display the following notification about this workaround in your dashboard:

Facebook Ads failed jobs warning

The following sections list some of these specific fields:

Business fields

Expand for details

The Account entity has a group of business fields:

business
business_city
business_country_code
business_name
business_state
business_street
business_street2
business_zip
owner

These business fields require the business_management permission granted to the user.

We check if this permission is granted during our setup tests and if it is not, we display the following warning before your connection starts the next sync:

Facebook Ads token setup test warning

Funding source details field

Expand for details

The funding_source_detailsfield of the Account requires the MANAGE task permission granted to the user.

We skip the funding_source_detailsfield if it is unavailable to the user. The skip will be mentioned in logs with the following message: "You are not granted the "MANAGE task" permission. We skipped the funding_source_details field. This is intended behavior and means the connector is working as expected.".

Other metadata tables' fields

Expand for details

Certain fields in various metadata tables can cause errors when they are included in API queries. We determine such fields dynamically. For every sync, we find a working set of fields that can be fetched for each entity and we use that set until the end of the current sync. In case a field is causing an error, we skip it.

Common source API errors, such as missing permissions or rate limits, may lead to us skipping the fields.

Error type	Facebook error code	Description
API Unknown	1	Unknown error due to downtime
API Service	2	Temporary issue due to downtime
API Method	3	Capability or permissions issue
Invalid parameter	100	Invalid parameter error

Fetching reports at different levels

We don't stop the sync process when we fail to fetch report data. Instead, we try to fetch the data using alternative methods offered by the Facebook API service.

Levels overview

Expand for details

Each report table has three types of specified metrics that are used to group data: Breakdowns, Action Breakdowns, and Fields. Each of these metrics belongs to one of the grouping levels (listed from the highest to the lowest): Account, Campaign, Ad Set, Ad, or None.

Breakdown	Level
`product_id`	`Ad`

Action Breakdown	Level
`action_target_id`	`Ad`

Field	Level
`ad_id`	`Ad`
`ad_name`	`Ad`
`adset_id`	`Ad Set`
`adset_name`	`Ad Set`
`campaign_id`	`Campaign`
`campaign_name`	`Campaign`
`objective`	`Campaign`

All metrics not listed in the above table have the Account level.

The lowest level of any of the selected metrics is the overall level of the report.

Example:

A custom report table has the following configuration:

Facebook Ads custom report config

Every metric here has the Account grouping level, except for the product_id breakdown, which has the Ad grouping level. Therefore, the overall report grouping level is Ad.

Regardless of the overall report level, we try to fetch all the data at the Account level first:

    https://graph.facebook.com/<API_VERSION>/<AD_ACCOUNT_ID>/insights

Query failures by level

Expand for details

If the query for the Account level fails, we try to fetch data at the next lower level. In this example, it is the Campaign level.

We fetch all campaign IDs for the current account and fetch reporting data for each campaign:

    https://graph.facebook.com/<API_VERSION>/<CAMPAIGN_ID>/insights

If the query for the Campaign level fails for any reason, we try to fetch data at the Ad Set and Ad levels in the same way.

The level at which we successfully fetch data does not affect the resulting composed values in the destination. However, the row count may differ.

If queries fail for all levels, the sync process stops, and we display the following warning in your dashboard:

Facebook Ads failed jobs warning

We don't try to fetch data at lower levels for reports with the highest Account and the lowest None levels.

This workaround may impact the sync's duration if the custom report contains too much data. We recommend doing the following to mitigate such an impact:

Don't add too many metrics to a single report. We recommend splitting a complex report into several simpler ones when possible.
Lower the custom report's overall level. A report with a lower overall level should have fewer number of metrics.
Use metrics with the same level inside a single report.

Schema information

This schema applies to all Facebook Ads connections.

To zoom, open ERD in new window.

Bid and budget (spend) values are stored at the currency's minimum denomination level. For example, US dollar values are stored as cents. Learn more in Facebook's Budgets documentation.

Metadata tables

The *_HISTORY tables contain metadata and capture versions of the objects as they are updated. These tables only contain records from the date of a connection's creation because Facebook does not retain change history for these resources. Use SORT BY id and ORDER BY updated_time to query the *_HISTORY tables when available.

Metadata tables do not contain the full change history of the objects within an account. Almost all selected metadata is retrieved from Facebook during every sync, except the tables listed below.

Incremental update of metadata tables

We incrementally update the following metadata tables:

CAMPAIGN_HISTORY
AD_SET_HISTORY
AD_HISTORY

We store the latest updated_time for these entities after each sync. During the next sync, we only fetch the entities with an updated_time value greater than the cursor value.

This behavior differs from reporting tables. Incrementally updated metadata tables can be re-synced on the Schema tab, but the re-sync does not restore the entity's change history, it only saves the latest version.

Schema notes

The *_HISTORY tables capture versions of the objects as they are updated. This isn't a perfect record of every change; rather, it captures the delta of changes between syncs. To query them, use SORT BY id and ORDER BY updated_time.

The bid_info_*, global_*, and placement_specific_* columns in the AD_HISTORY table can change their names depending on the source data, which is stored in the KEY-VALUE format. For example, depending on the source data, bid_info_* would have bid_info_KEY as its name, and VALUE as its value. For more information regarding the global and placement_specific fields, see Facebook's Adgroup documentation.

Report schema information

We support every field in the Facebook Marketing API v19.0. See Facebook's Insights API documentation for more information.

We provide two types of reports:

For either kind of report, we deliver a main table named after the schema name of the connection. The table will contain the fields selected within the setup form.

New fields added to an existing custom report only appear in the report schema after the connection completes a sync.

Secondary tables

If you select an action breakdown and field(s), Fivetran creates secondary tables only for fields of list<AdsActionStats> and list<AdsHistogramStats> types listed in Facebook's field documentation.

Secondary tables are named after the main table, with the field name included as a postfix (e.g. <table_name>_cost_per_action_type). The action breakdowns are columns in the table, and the field value is the number of actions to occur.

Every primary table and its secondary tables have the _fivetran_id column, which contains unique hashed values for each distinct breakdown combination. This column is useful for joining parent and child tables when you select more than one breakdown.

In the following examples, both the parent and child tables have the impression_device and publisher_platform breakdowns selected:

Parent table

`AD_ID`	`DATE`	`IMPRESSION_DEVICE`	`PUBLISHER_PLATFORM`	`_FIVETRAN_ID`
1	2023-01-01	`device_1`	`platform_1`	`hLi+wMCbTOFzzPIqv+ocBvJbvtg=`
2	2023-01-01	`device_2`	`platform_`2	`4h5CjCt2fOZHwChM+dBOZRI3tXk=`
1	2023-01-01	`device_1`	`platform_2`	`qJQCe+C93HLnPQ9XtNYF1RTOd+8=`
2	2023-01-01	`device_2`	`platform_1`	`sGNAOLEHU/Gf1d7/fGEdzdBUijs=`

Child table

`AD_ID`	`DATE`	`_FIVETRAN_ID`	`VALUE`
1	2023-01-01	`hLi+wMCbTOFzzPIqv+ocBvJbvtg=`	`value_1`
2	2023-01-01	`4h5CjCt2fOZHwChM+dBOZRI3tXk=`	`value_2`
1	2023-01-01	`qJQCe+C93HLnPQ9XtNYF1RTOd+8=`	`value_3`
2	2023-01-01	`sGNAOLEHU/Gf1d7/fGEdzdBUijs=`	`value_4`

The action data is hierarchical: a simple sum of all actions will not equal the field total_actions. See Actions you can measure in Facebook Ads Reporting for more information.

Query levels

Depending on the breakdowns, action breakdowns, and fields you selected while configuring the connection, we query at different levels:

Ad level: If you select the ad_id and/or ad_name fields, and/or product_id breakdown, and/or action_target_id action breakdown.
Adset level: If you select the adset_id and/or adset_name fields.
Campaign level: If you select the campaign_id and/or campaign_name and/or objective fields.
Account level: If you selected none of the fields listed above.

Primary key and composite key

The primary key of the parent table is the composite of the _fivetran_id column, the date (date, week, or month, depending on aggregation level) column, and the breakdown columns. The composite key of the child tables also includes the index column.

If you selected fields that correspond to more than one level, we query at the most granular level. For example, if you select both the ad_id and adset_id fields, we query on the Ad level.

Missing data

In the request used to query Facebook's Insights API, we specify the report configuration defined during the connection setup.

The report returned as a response may not contain all the fields you selected. Certain data may be missing due to Facebook's API limitations.

When Facebook introduced changes related to Apple's iOS 14 updates, some metrics became unavailable with certain breakdowns selected. For example, offsite conversion events do not support the delivery and action breakdowns. This includes demographic breakdowns such as age, gender, and region. See Apple's iOS 14 changes in Facebook API for more information.

API configuration options

You must provide specific values for certain parameters if configuring your Facebook Ads connection using our REST API.

See our REST API documentation for Facebook Ads for more information about using the Fivetran REST API to configure your Facebook Ads connection.

Expand for details

fields

account_currency
account_id
account_name
action_values
actions
ad_id
ad_name
adset_id
adset_name
attribution_setting
buying_type
campaign_id
campaign_name
canvas_avg_view_percent
canvas_avg_view_time
catalog_segment_value
clicks
conversion_values
conversions
cost_per_10_sec_video_view
cost_per_action_type
cost_per_conversion
cost_per_estimated_ad_recallers
cost_per_inline_link_click
cost_per_inline_post_engagement
cost_per_outbound_click
cost_per_thruplay
cost_per_unique_action_type
cost_per_unique_click
cost_per_unique_inline_link_click
cost_per_unique_outbound_click
cpc
cpm
cpp
ctr
estimated_ad_recall_rate
estimated_ad_recallers
frequency
full_view_impressions
full_view_reach
gender_targeting
impressions
inline_link_click_ctr
inline_link_clicks
inline_post_engagement
instant_experience_clicks_to_open
instant_experience_clicks_to_start
instant_experience_outbound_clicks
labels
location
mobile_app_purchase_roas
objective
outbound_clicks
outbound_clicks_ctr
purchase_roas
reach
relevance_score
social_spend
spend
unique_actions
unique_clicks
unique_ctr
unique_inline_link_click_ctr
unique_inline_link_clicks
unique_link_clicks_ctr
unique_outbound_clicks
unique_outbound_clicks_ctr
video_10_sec_watched_actions
video_30_sec_watched_actions
video_avg_percent_watched_actions
video_avg_time_watched_actions
video_p100_watched_actions
video_p25_watched_actions
video_p50_watched_actions
video_p75_watched_actions
video_p95_watched_actions
video_play_actions
video_play_curve_actions
video_thruplay_watched_actions
website_ctr
website_purchase_roas

breakdowns

ad_format_asset
age
app_id
body_asset
call_to_action_asset
country
description_asset
dma
gender
frequency_value
hourly_stats_aggregated_by_advertiser_time_zone
hourly_stats_aggregated_by_audience_time_zone
image_asset
impression_device
link_url_asset
place_page_id
device_platform
product_id
publisher_platform
platform_position
region
skan_conversion_id
title_asset
video_asset

action_breakdowns

action_carousel_card_id
action_carousel_card_name
action_canvas_component_name
action_destination
action_device
action_reaction
action_target_id
action_type
action_video_sound
action_video_type

sync_mode

AllAccounts
SpecificAccounts

aggregation

Day
Week
Month

config_type

Prebuilt
Custom

prebuilt_report

ACTION_CANVAS_COMPONENT
ACTION_CAROUSEL_CARD
ACTION_CONVERSION_DEVICE
ACTION_PRODUCT_ID
ACTION_REACTIONS
ACTION_VIDEO_SOUND
ACTION_VIDEO_VIEW_TYPE
BASIC_AD
BASIC_AD_SET
BASIC_ALL_LEVELS
BASIC_CAMPAIGN
DELIVERY_PLATFORM
DELIVERY_PLATFORM_AND_DEVICE
DELIVERY_PURCHASE_ROAS
DEMOGRAPHICS_AGE
DEMOGRAPHICS_AGE_AND_GENDER
DEMOGRAPHICS_COUNTRY
DEMOGRAPHICS_DMA_REGION
DEMOGRAPHICS_GENDER
DEMOGRAPHICS_REGION

action_report_time

impression
conversion
mixed

click_attribution_window

NONE
DAY_1
DAY_7

view_attribution_window

NONE
DAY_1
DAY_7

engaged_view_attribution_window

NONE
DAY_1_EV

Ads action statistics

We create a separate table for each field of the AdsActionStats list type. These fields are listed in Facebook's documentation.

Expand for details

The created child tables contain the following main fields:

action_type - The type of action taken on your ad, Page, app, or event after your ad was displayed to the user, even if they didn't click on it. Action types include Page likes, app installs, conversions, event responses, etc.
value - The conversion metric value for the default attribution window (7d_click and 1d_view).
1_d_click - The conversion metric value for the "1 day after clicking the ad" attribution window.
7_d_click - The conversion metric value for the "7 days after clicking the ad" attribution window.
1_d_view - The conversion metric value for the "1 day after viewing the ad" attribution window.
7_d_view - The conversion metric value for the "7 days after viewing the ad" attribution window.
inline - The conversion metric value for the default attribution window that corresponds to all conversions that occur on the ad, like 'link_click', 'video_view'.

Conversions

Depending on the type of your conversion, Facebook maps the values into the following AdsActionStats fields:

actions
action_values
conversions
conversion_values

To get the complete conversion information using the Facebook Insights API, you need to:

Include all the fields listed above in the report.
Combine them downstream of your destination using the JOIN operator or other methods.

Rate limit troubleshooting

Facebook API rate limits control how many requests can be made within a given time frame. If your connection exceeds the allowed quota, syncs may slow down or fail with an error.

Rate limit issues can occur for several reasons, including:

Your access token makes a high number of requests.
You have a low number of active ads.
Multiple processes share the same access token and run in parallel.
A single connection is configured to sync multiple ad accounts.
Your connection schema includes metadata tables, which increase API usage.

To learn more about common causes of rate limiting and how to avoid them, see How Can I Reduce Facebook API Rate Limit Errors?.

UTC conversion

We don't convert source timestamps to Universal Time Coordinated (UTC). Instead, we use the Facebook Ads account's time zone to store the data in your destination.