Facebook Ads integration summary

Stitch’s Facebook Ads integration replicates ad, campaign, and adcreative data using the Facebook Marketing API.

Note: This integration does not currently support replicating data for reviews, pages, etc.

Refer to the Schema section for a list of objects available for replication.

Facebook Ads feature snapshot

A high-level look at Stitch's Facebook Ads integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release Status

Released

Supported By

Stitch

Stitch Plan

Free

Singer GitHub Repository

Facebook Ads Repository

DATA SELECTION
Table Selection

Supported

Column Selection

Supported

REPLICATION SETTINGS
Anchor Scheduling

Supported

Advanced Scheduling

Unsupported

Table-level Reset

Unsupported

Configurable Replication Methods

Unsupported

TRANSPARENCY
Extraction Logs

Supported

Loading Reports

Supported

Connecting Facebook Ads

Facebook Ads setup requirements

To set up Facebook Ads in Stitch, you need:

  • Verify your access in Facebook Ads. If the user who creates the integration has restricted permissions - meaning the user doesn’t have access to all campaigns or ads - Stitch may encounter issues replicating data.

    Even if you only intend to sync certain pieces of data post-setup, the user completing the initial setup should still have full access.

  • Pause all ad-blocking software currently in use. Because Facebook authentication uses pop ups, you may encounter issues if ad blockers aren’t disabled during the setup.

Step 1: Add Facebook Ads as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Facebook Ads icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch Facebook Ads” would create a schema called stitch_facebook_ads in the destination. Note: Schema names cannot be changed after you save the integration.

Step 1.1: Select an attribution window

An attribution window is the amount of time for attributing results to ads and the lookback period after those actions occur during which ad results are counted.

We recommend selecting the same attribution window you use in Facebook Ads to prevent discrepancies between Facebook’s UI and data replicated by Stitch. For example: If the attribution window in Facebook Ads is 7 days, you should define this setting as 7 days.

Then, during every replication job, Stitch will replicate the past seven days’ worth of data to account for result attribution. This will ensure that records updated during the attribution period are correctly captured by Stitch.

For more info, see the Attribution windows and data extraction section.

Step 1.2: Include deleted data

Check the Include data from deleted campaigns, ads, and adsets box to have Stitch replicate data for these deleted objects.

Note: Data for deleted campaigns, ads, and adsets will be included only in Core Object tables.

Step 2: Define the historical sync

The Sync Historical Data setting will define the starting date for your Facebook Ads integration. This means that:

  • For tables using Incremental Replication, data equal to or newer than this date will be replicated to your data warehouse.
  • For tables using Full Table Replication, all data - including records that are older, equal to, or newer than this date - will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Facebook Ads’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 3: Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Facebook Ads integrations support the following replication scheduling methods:

To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 4: Authorize Stitch to access Facebook Ads

  1. After clicking the Authorize button, a small pop-up window will display.

    You’ll be taken through a series of steps to allow Stitch to access data from your Public Profile, Facebook Ads, and related stats.

  2. Click Okay to advance through these steps.
  3. After you’ve finished authorizing Stitch, you’ll be prompted to select the Facebook Ad Account you want to pull data from. Select the desired account by clicking the checkbox in the Connect column.

    If you don’t see the profiles that you would expect to, verify your Facebook Ads permissions before reaching out to support.

  4. Click the Save Connections button.

After your credentials are validated, you’ll be directed back to Stitch (click the All Done button to wrap things up) and the Integration Details page will display.

Step 5: Set tables and columns to replicate

To complete the setup, you’ll need to select the tables and columns you want to replicate to your data warehouse.

Check out the Schema section to learn more about the available tables in Facebook Ads and how they replicate.

  1. In the list of tables that displays - or in the Tables to Replicate tab, if you skipped this step during setup - locate a table you want to replicate.
  2. To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.

  3. To track a column, click the checkbox next to the column’s name. A green checkmark means the column is set to replicate.

  4. Repeat this process for all the tables and columns you want to replicate.
  5. When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.

Note: If you change these settings while a replication job is still in progress, they will not be used until the next job starts.

Initial and historical replication jobs

After you finish setting up Facebook Ads, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


Facebook Ads Replication

Attribution windows and data extraction

When Stitch runs a replication job for Facebook Ads, it will use the value of the Attribution Window setting to query for and extract data for Incremental tables. An attribution window is a period of time for attributing results to ads and the lookback period after those actions occur during which ad results are counted.

For example: If set to 7 days, Stitch will replicate the past seven days’ worth of data every time a replication job runs. While Stitch replicates data in this way to account for updates to records made during the attribution window, it can have a substantial impact on your overall row usage.

In the sections below are examples of how attribution windows impact how Stitch extracts data during historical and ongoing replication jobs.

For historical and full re-replications of Facebook Ads data, Stitch will query for and extract data newer than or equal to the date defined in the Start Date field in the Integration Settings page.

The Start Date, in conjunction with the Attribution Window, will define the minimum date Stitch should query for when extracting historical data. This is calculated as:

Start Date - Attribution Window = Minimum Extraction Date

Example

During the initial set up, the Attribution Window and Start Date settings are defined as:

  • Attribution Window: 28 days (default setting)
  • Start Date: 07/03/2017, or 2017-07-03 00:00:00

To account for the Attribution Window, Stitch would calculate the Minimum Extraction Date value as: 2017-07-03 00:00:00 - 28 days = 2017-06-05 00:00:00

If you were to write a SQL query using this date for the ads table, it might look like this:

  SELECT *
    FROM facebook_ads.ads
   WHERE updated_time >= '2017-06-05 00:00:00'   /* Min. Extraction Date */
ORDER BY updated_time

For ongoing replication jobs, Stitch will query for and extract data using the last saved maximum value in the table’s Replication Key column and the Attribution Window setting.

Note: This applies to every replication job that takes place after the historical replication job.

Example

The last maximum saved Replication Key value for the ads table is 2017-10-01 00:00:00.

To account for the Attribution Window of 28 days, we’d subtract this from the last maximum saved Replication Key value:

2017-10-01 00:00:00 - 28 days = 2017-09-03 00:00:00

In this case, Stitch would query for and extract data that is newer than or equal to 2017-09-03 00:00:00 and older than or equal to 2017-10-01 00:00:00.

If this were a SQL query, it might look like this:

  SELECT *
    FROM ads
   WHERE updated_time >= '2017-09-03 00:00:00'
                              /* max Replication Key value - Attribution Window */
     AND updated_time <= '2017-10-01 00:00:00'
                              /* max Replication Key value from previous job */
ORDER BY updated_time

Attribution windows and row count impact

Due to the attribution window, a high Replication Frequency may not be necessary. Because Stitch will replicate data from the past N days during every replication job, recent data will be re-replicated and count towards your row quota.

To reduce your row usage and replicating redundant data, consider setting the integration to replicate less frequently. For example: every 12 or 24 hours.


Facebook Ads table schemas

There are two types of tables in Stitch’s Facebook Ads integration: Core Object and Insights.

  • Core Object tables contain foundational data that’s useful for analysis. These are the adcreative, ads, adsets, and campaigns tables. To learn more about how Facebook Ads data is structured, we recommend checking out their API guide.
  • Insights tables contain performance data for every campaign/adset/ad combination, segmented by day and demographics specific to each table. For example: The ads_insights_age_and_gender table is segmented by day, age, and gender.

Replication Method :

Full Table

Primary Key :

id

API endpoint :

adCreative - Reading

The adcreative table contains info about the creatives used in ads in your Facebook Ads account.

This is a Core Object table.

id
STRING

The ID of the creative.

Reference:

body
STRING

The body of the ad.

image_url
STRING

The URL for the image for the creative.

account_id
STRING

The ID of the account associated with the creative.

Reference:

actor_id
STRING

The actor ID (page ID) of the creative.

adLabels
ARRAY

Details about the ad labels applied to the creative.

id
STRING

The ad label ID.

Reference:

created_time
DATE-TIME

The time the ad label was created.

name
STRING

The name of the ad label.

updated_time
DATE-TIME

The last time the ad label was updated.

adcreative (table), adLabels (attribute)

applink_treatment
STRING

The deep link fallback behavior for dynamic product ads if the app is not installed.

branded_content_sponsor_page_id
STRING

The sponsor page ID of the creative.

call_to_action_type
STRING

The call to action button text and header text of legacy ads.

effective_instagram_story_id
STRING

The ID of the Instagram post used in the ad.

effective_object_story_id
STRING

The ID of the page post used in an ad.

title
STRING

The title for an ad link.

name
STRING

The name of the creative in the creative library.

image_crops
ARRAY

Details about the crop specifications (aspect ratios) for images in different ad placements.

100x100
ARRAY

The crop specification for 100x100.

value
INTEGER

The value of the crop specification.

adcreative (table), 100x100 (attribute)

100x72
ARRAY

The crop specification for 100x72.

value
INTEGER

The value of the crop specification.

adcreative (table), 100x72 (attribute)

191x100
ARRAY

The crop specification for 191x100.

value
INTEGER

The value of the crop specification.

adcreative (table), 191x100 (attribute)

400x150
ARRAY

The crop specification for 400x150.

value
INTEGER

The value of the crop specification.

adcreative (table), 400x150 (attribute)

400x500
ARRAY

The crop specification for 400x500.

value
INTEGER

The value of the crop specification.

adcreative (table), 400x500 (attribute)

600x360
ARRAY

The crop specification for 600x360.

value
INTEGER

The value of the crop specification.

adcreative (table), 600x360 (attribute)

90x160
ARRAY

The crop specification for 90x160.

value
INTEGER

The value of the crop specification.

adcreative (table), 90x160 (attribute)
adcreative (table), image_crops (attribute)

instagram_actor_id
STRING

The Instagram actor ID associated with the creative.

instagram_permalink_url
STRING

The Instagram permalink associated with the creative.

instagram_story_id
STRING

The ID of the Instagram post for creating ads.

link_og_id
STRING

The Open Graph (OG) ID for the link in the creative if the landing page has OG tags.

object_id
STRING

The ID of the promoted object or object that is relevant to the ad and ad type.

object_story_id
STRING

The ID of the page post that is used in the creative.

object_story_spec
OBJECT

Details about the specifications of a creative that are used to create a new unpublished page post.

instagram_actor_id
STRING

The Instagram user that the story will be posted to.

link_data
OBJECT

Details about the specifications for link or carousel ads.

page_id
STRING

The ID of a Facebook page.

Reference:

photo_data
OBJECT

Details about the spec for a photo page post.

branded_content_sponsor_page_id
STRING

The ID of the branded content sponsor page ID.

branded_content_sponsor_relationship
STRING

The branded content sponsor relationship option.

caption
STRING

The description of the image.

image_hash
STRING

The hash of the image in your image library with Facebook.

page_welcome_message
STRING

The welcome text from page to user on Messenger once a user performs a send message action on an ad.

url
STRING

The URL of an image to use in the ad.

adcreative (table), photo_data (attribute)

template_data
OBJECT

Details about the spec for a template.

additional_image_index
INTEGER

The index (zero based) of the image from the additional images array to use as the ad image for a dynamic product ad.

app_link_spec
OBJECT

Details about the native deeplinks attached to the post.

Stitch will replicate data for the following deeplink types:

attachment_style
STRING

The style of the attachment.

branded_content_shared_to_sponsor_status
STRING

The branded content shared to sponsor option.

branded_content_sponsor_page_id
STRING

The ID of the branded content sponsor page.

branded_content_sponsor_relationship
STRING

The branded content sponsorship option.

call_to_action
OBJECT

Details about the call to associated with the link object.

app_destination
STRING

The app destination type.

app_link
STRING

The deep link to the app.

application
STRING

The application related to the action.

event_id
STRING

The ID of the Facebook event which shows the event info.

lead_gen_form_id
STRING

The ID of the Lead Ad form.

link
STRING

The destination link when the CTA button is clicked.

link_caption
STRING

The caption shown in the attachment.

link_format
STRING

The link format of video attachments.

page
STRING

Teh ID of the Facebook page which the CTA button links to.

product_link
STRING

The Open Graph object URL for canvas virtual good ads.

adcreative (table), call_to_action (attribute)

caption
STRING

The link caption.

child_attachments
ARRAY

Details about the link objects required for carousel ads.

image_hash
STRING

The image hash of an uploaded image for the attachment.

link
STRING

The link of the attachment.

call_to_action
OBJECT

Details about the call to associated with the link object.

app_destination
STRING

The app destination type.

app_link
STRING

The deep link to the app.

application
STRING

The application related to the action.

event_id
STRING

The ID of the Facebook event which shows the event info.

lead_gen_form_id
STRING

The ID of the Lead Ad form.

link
STRING

The destination link when the CTA button is clicked.

link_caption
STRING

The caption shown in the attachment.

link_format
STRING

The link format of video attachments.

page
STRING

Teh ID of the Facebook page which the CTA button links to.

product_link
STRING

The Open Graph object URL for canvas virtual good ads.

adcreative (table), call_to_action (attribute)
adcreative (table), child_attachments (attribute)

description
STRING

The link description.

event_id
STRING

The ID of a Facebook event.

force_single_link
BOOLEAN

Indicates if the post will be forced to render in single link format.

image_crops
ARRAY

Details about the crop specifications (aspect ratios) for images in different ad placements.

100x100
ARRAY

The crop specification for 100x100.

value
INTEGER

The value of the crop specification.

adcreative (table), 100x100 (attribute)

100x72
ARRAY

The crop specification for 100x72.

value
INTEGER

The value of the crop specification.

adcreative (table), 100x72 (attribute)

191x100
ARRAY

The crop specification for 191x100.

value
INTEGER

The value of the crop specification.

adcreative (table), 191x100 (attribute)

400x150
ARRAY

The crop specification for 400x150.

value
INTEGER

The value of the crop specification.

adcreative (table), 400x150 (attribute)

400x500
ARRAY

The crop specification for 400x500.

value
INTEGER

The value of the crop specification.

adcreative (table), 400x500 (attribute)

600x360
ARRAY

The crop specification for 600x360.

value
INTEGER

The value of the crop specification.

adcreative (table), 600x360 (attribute)

90x160
ARRAY

The crop specification for 90x160.

value
INTEGER

The value of the crop specification.

adcreative (table), 90x160 (attribute)
adcreative (table), image_crops (attribute)

image_hash
STRING

The hash of an image in your image library with Facebook.

link
STRING

The link URL.

message
STRING

The main body of the post.

multi_share_end_card
BOOLEAN

Indicates if end cards that display the page icon should not be removed.

multi_share_optimized
BOOLEAN

Indicates if orders and links are automatically selected and ordered.

name
STRING

The name of the link.

offer_id
STRING

The ID of a Facebook native offer.

Reference:

page_welcome_message
STRING

The welcome text from page to user on Messenger after a user performs a send message action on an ad.

picture
STRING

The URL of a picture to use in the post.

retailer_item_ids
ARRAY

The product IDs provided by the advertiser for collections.

value
STRING

The ID of the product.

adcreative (table), retailer_item_ids (attribute)

show_multiple_images
BOOLEAN

Used with force_single_link (true) to show a single dynamic item in carousel format using multiple images from the catalog.

adcreative (table), template_data (attribute)

text_data
OBJECT

Details about the text page post used for the ad.

message
STRING

The text of the page post.

adcreative (table), text_data (attribute)

video_data
OBJECT

Details about the specifications for a video ad.

branded_content_shared_to_sponsor_status
STRING

The branded content shared to sponsor option.

branded_content_sponsor_page_id
STRING

The ID of the branded content sponsor page.

branded_content_sponsor_relationship
STRING

The branded content sponsorship option.

call_to_action
OBJECT

Details about the call to associated with the link object.

app_destination
STRING

The app destination type.

app_link
STRING

The deep link to the app.

application
STRING

The application related to the action.

event_id
STRING

The ID of the Facebook event which shows the event info.

lead_gen_form_id
STRING

The ID of the Lead Ad form.

link
STRING

The destination link when the CTA button is clicked.

link_caption
STRING

The caption shown in the attachment.

link_format
STRING

The link format of video attachments.

page
STRING

Teh ID of the Facebook page which the CTA button links to.

product_link
STRING

The Open Graph object URL for canvas virtual good ads.

adcreative (table), call_to_action (attribute)

force_single_link
BOOLEAN

Indicates if the post will be forced to render in single link format.

image_hash
STRING

The hash of an image in your image library with Facebook.

image_url
STRING

The URL of the image to use as a thumbnail.

link_description
STRING

The link description of the video.

message
STRING

The main body of the post.

name
STRING

The name of the link.

offer_id
STRING

The ID of a Facebook native offer.

Reference:

page_welcome_message
STRING

The welcome text from page to user on Messenger after a user performs a send message action on an ad.

retailer_item_ids
ARRAY

The product IDs provided by the advertiser for collections.

value
STRING

The ID of the product.

adcreative (table), retailer_item_ids (attribute)

targeting
ARRAY

The post gating for the video.

video_id
STRING

The ID of the video that the user has permission to or a video in the ad account video library.

adcreative (table), video_data (attribute)
adcreative (table), object_story_spec (attribute)

object_type
STRING

The type of object being advertised. Possible values are:

  • APPLICATION - For apps on Facebook.
  • DOMAIN
  • EVENT
  • INVALID - Returned when invalid object_ids are supplied or the authorizing user doesn’t have permission to see the object.
  • PAGE
  • PHOTO
  • SHARE
  • STATUS
  • STORE_ITEM - For iTunes and Google Play store destinations.
  • VIDEO

object_url
STRING

The destination URL for link ads not connected to a page.

product_set_id
STRING

The ID of the product set for the creative.

Reference:

status
STRING

The status of the creative. Possible values are ACTIVE or DELETED.

template_url
STRING

The tracking URL for dynamic product ads.

template_url_spec
OBJECT

Details about the template link specifications used to create ad creatives.

android
OBJECT

Details about template link specifications used on Android.

app_name
STRING

The name of the Android app.

package
STRING

The fully qualified package name for intent generation and store linking.

url
STRING

The custom URL scheme for the Android app.

adcreative (table), android (attribute)

config
OBJECT

app_id
STRING

The ID of the Facebook app where the deeplink information is stored.

adcreative (table), config (attribute)

ios
OBJECT

Details about template link specifications used on iOS.

app_name
STRING

The display name of the iOS app.

app_store_id
STRING

The app ID for the App Store.

Reference:

url
STRING

The custom URL scheme for the iOS app.

adcreative (table), ios (attribute)

ipad
OBJECT

Details about template link specifications used on iPad.

app_name
STRING

The display name of the iOS app.

app_store_id
STRING

The app ID for the App Store.

Reference:

url
STRING

The custom URL scheme for the iOS app.

adcreative (table), ipad (attribute)

iphone
OBJECT

Details about template link specifications used on iPhone.

app_name
STRING

The display name of the iOS app.

app_store_id
STRING

The app ID for the App Store.

Reference:

url
STRING

The custom URL scheme for the iOS app.

adcreative (table), iphone (attribute)

web
OBJECT

Details about template link specifications used on the web.

should_fallback
STRING

Indicates if the web URL should be used as a fallback. If false, the content is only meant to be viewed on a native app.

url
STRING

The web URL.

adcreative (table), web (attribute)

windows_phone
OBJECT

app_id
STRING

The app ID as a GUID for the app store.

app_name
STRING

The display name of the app.

url
STRING

The custom URL scheme for the Windows Phone app.

adcreative (table), windows_phone (attribute)
adcreative (table), template_url_spec (attribute)

thumbnail_url
STRING

The URL to a thumbnail used for the creative.

image_hash
STRING

The image has for an image used in the creative.

url_tags
STRING

The query string parameters which will replace or be appended to URLs clicked from page post ads, messages of the post, and canvas app install creatives.

video_id
STRING

The ID of the video in the creative.

link_url
STRING

Used to identify a specific landing tab on the page by the page’s tab URL.


Replication Method :

Key-based Incremental

Replication Key :

updated_time

Primary Key :

id : updated_time

API endpoint :

ad - Reading

The ads table contains info about the ads in your Facebook Ads account.

This is a Core Object table.

updated_time and querying

Because this table uses updated_time as part of the Primary Key, query results might return various versions of the same adgroup.

To reflect the latest state of the adgroup, use the latest updated_time timestamp.

Deleted ads

If the Include data from deleted campaigns, ads, and adsets box in the integration’s settings is checked, this table will include data for deleted ads.

id
STRING

The ad ID.

Reference:

updated_time
DATE-TIME

The last time the ad was updated.

account_id
STRING

The ID of the ad account that the ad belongs to.

Reference:

campaign_id
STRING

The ID of the ad campaign that contains this ad.

Reference:

adset_id
STRING

The ID of the ad set that contains this ad.

adLabels
ARRAY

Details about the ad labels applied to the ad.

id
STRING

The ad label ID.

Reference:

created_time
DATE-TIME

The time the ad label was created.

name
STRING

The name of the ad label.

updated_time
DATE-TIME

The last time the ad label was updated.

ads (table), adLabels (attribute)

bid_type
STRING

The bid type of the ad. According to Facebook’s documentation, possible values include:

  • CPC
  • CPM
  • MULTI_PREMIUM
  • ABSOLUTE_OCPM
  • CPA

bid_amount
INTEGER

The bid amount for the ad that will be used in auction instead of the ad set bid_amount, if specified.

bid_info
OBJECT

clicks
INTEGER

The clicks you placed on your bid.

actions
INTEGER

The actions you placed on your bid.

reach
INTEGER

The reach you placed on your bid.

impressions
INTEGER

The impressions you placed on your bid.

social
INTEGER

The social value you placed on your bid.

ads (table), bid_info (attribute)

status
STRING

The configured status of the ad. Possible values include:

  • ACTIVE
  • PAUSED
  • DELETED
  • ARCHIVED

creative
OBJECT

Details about the creative used by the ad.

creative_id
INTEGER

The ID of the creative used by the ad.

ads (table), creative (attribute)

created_time
DATE-TIME

The time the ad was created.

name
STRING

The name of the ad.

effective_status
STRING

The effective status of the ad. According to Facebook’s documentation, possible values include:

  • ACTIVE
  • PAUSED
  • DELETED
  • PENDING_REVIEW
  • DISAPPROVED
  • PREAPPROVED
  • PENDING_BILLING_INFO
  • CAMPAIGN_PAUSED
  • ARCHIVED
  • ADSET_PAUSED

targeting
OBJECT

Details about the targeting specs applied to the ad.

age_max
STRING

The maximum age of the targeted audience.

age_min
INTEGER

The minimum age of the targeted audience.

app_install_state
STRING

The app install state of the targeted audience.

ads (table), targeting (attribute)

targeting
ARRAY

Targeting specs are ad set attributes that define who sees an ad.

Stitch may create subtables named ads__targeting__[spec_name] for each targeting spec type that is applied to the ad set. For example: ads__targeting__life_events

If you have many targeting specs applied to ads, a large number of subtables may be created in your destination.

id
STRING

The ad ID.

Reference:

updated_time
DATE-TIME

The time the ad was last updated.

behaviors
ARRAY

ID/name pairs of the behavior targeting specs applied to the ad.

id
STRING

The ID of the behavior.

name
STRING

The name of the behavior. Ex: All travelers

ads (table), behaviors (attribute)

connections
ARRAY

ID/name pairs of the connection targeting specs applied to the ad.

id
STRING

The ID of the connection. Ex: fans of your Page.

name
STRING

The name of the connection. Ex: Stitch Data

ads (table), connections (attribute)

custom_audiences
ARRAY

ID/name pairs of the custom audience targeting specs applied to the ad.

id
STRING

The ID of the audience.

name
STRING

The name of the audience. Ex: Outbound Email Leads

ads (table), custom_audiences (attribute)

education_majors
ARRAY

ID/name pairs of the education major targeting specs applied to the ad.

id
STRING

The ID of the education major.

name
STRING

The name of the education major. Ex: Computer Science

ads (table), education_majors (attribute)

excluded_connections
ARRAY

ID/name pairs of the excluded connection targeting specs applied to the ad.

id
STRING

The ID of the excluded connection. Ex: Target people who aren’t fans of your Page.

name
STRING

The name of the excluded connection. Ex: Stitch Data

ads (table), excluded_connections (attribute)

excluded_custom_audiences
ARRAY

ID/name pairs of the excluded custom audience targeting specs applied to the ad.

id
STRING

The ID of the excluded custom audience.

name
STRING

The name of the excluded custom audience.

ads (table), excluded_custom_audiences (attribute)

family_statuses
ARRAY

ID/name pairs of the family status targeting specs applied to the ad.

id
STRING

The ID of the family status.

name
STRING

The name of the family status.

ads (table), family_statuses (attribute)

friends_of_connections
ARRAY

ID/name pairs of the friend of connections targeting specs applied to the ad.

id
STRING

The ID of the friend connection. Ex: Friends of people who are fans of your Page

name
STRING

The name of the friend connection.

ads (table), friends_of_connections (attribute)

generation
ARRAY

ID/name pairs of the generation demographic targeting specs applied to the ad.

id
STRING

The ID of the generation demographic.

name
STRING

The name of the generation demographic.

ads (table), generation (attribute)

geo_locations__cities
ARRAY

Details about the city demographic targeting specs applied to the ad.

key
STRING

The city’s key.

distance_unit
STRING

The unit used to measure radius.

region
STRING

The region associated with the city.

name
STRING

The name of the city.

country
STRING

The country associated with the city.

region_id
STRING

The ID of the region associated with the city.

radius
INTEGER

The radius around the city that is included in the targeting. For example: if radius is 10 and distance_unit is mile, an area of 10 miles outside the given city will be targeted.

ads (table), geo_locations__cities (attribute)

geo_locations__location_types
ARRAY

Details about the location types included in the targeting specs applied to the ad.

value
STRING

The type of location included in the targeting specs. Possible values are:

  • country
  • country_group
  • region
  • city
  • zip
  • geo_market
  • electoral_district - Only applicable in the United States.
ads (table), geo_locations__location_types (attribute)

geo_locations__regions
ARRAY

Details about the region demographics included in the targeting specs applied to the ad.

name
STRING

The name of the region.

country
STRING

The name of the country associated with the region.

key
STRING

The key of the region.

ads (table), geo_locations__regions (attribute)

geo_locations__zips
ARRAY

Details about zip codes included in the targeting specs applied to the ad.

name
STRING

The name of the zip code. For example: 90210

country
STRING

The country associated with the zip code. For example: US

key
STRING

The key of the zip code. For example: US:90210

primary_city_id
STRING

The ID of the city associated with the zip code. For example: Beverly Hills

region_id
STRING

The region associated with the zip code. For example: California

ads (table), geo_locations__zips (attribute)

geo_locations__geo-markets
ARRAY

Details about the geo market demographics included in the targeting specs applied to the ad.

key
STRING

The key of the geo market.

name
STRING

The name of the geo market.

ads (table), geo_locations__geo-markets (attribute)

home_ownership
ARRAY

ID/name pairs of the home ownership demographic targeting specs applied to the ad.

id
STRING

The ID of the home ownership demographic.

name
STRING

The name of the home ownership demographic.

ads (table), home_ownership (attribute)

home_type
ARRAY

ID/name pairs of the home type demographic targeting specs applied to the ad.

id
STRING

The ID of the home type demographic.

name
STRING

The name of the home type demographic.

ads (table), home_type (attribute)

household_composition
ARRAY

ID/name pairs of the home composition targeting specs applied to the ad.

id
STRING

The ID of the household composition type.

name
STRING

The name of the household composition type. Ex: Children in home

ads (table), household_composition (attribute)

income
ARRAY

ID/name pairs of the income demographic targeting specs applied to the ad.

id
STRING

The ID of the income demographic.

name
STRING

The name of the income demographic.

ads (table), income (attribute)

industries
ARRAY

ID/name pairs of the industry targeting specs applied to the ad.

id
STRING

The ID of the industry type.

name
STRING

The name of the industry type.

ads (table), industries (attribute)

interests
ARRAY

ID/name pairs of the interest targeting specs applied to the ad.

id
STRING

The ID of the interest.

name
STRING

The name of the interest. Ex: Movies, Music

ads (table), interests (attribute)

life_events
ARRAY

ID/name pairs of the life event targeting specs applied to the ad.

id
STRING

The ID of the life event.

name
STRING

The name of the life event. Ex: Recently moved

ads (table), life_events (attribute)

moms
ARRAY

ID/name pairs of the mother demographic targeting specs applied to the ad.

id
STRING

The ID of the mom demographic.

name
STRING

The name of the mom demographic.

ads (table), moms (attribute)

net_worth
ARRAY

ID/name pairs of the net worth demographic targeting specs applied to the ad.

id
STRING

The ID of the net worth demographic.

name
STRING

The name of the net worth demographic.

ads (table), net_worth (attribute)

office_type
ARRAY

ID/name pairs of the office type targeting specs applied to the ad.

id
STRING

The ID of the office type.

name
STRING

The name of the office type.

ads (table), office_type (attribute)

politics
ARRAY

ID/name pairs of the political demographic targeting specs applied to the ad.

id
STRING

The ID of the political demographic.

name
STRING

The name of the political demographic.

ads (table), politics (attribute)

user_adclusters
ARRAY

ID/name pairs of the user adcluster targeting specs applied to the ad.

id
STRING

The ID of the user adcluster.

name
STRING

The name of the user adcluster. Ex: Cooking, Small Business Owners

ads (table), user_adclusters (attribute)

work_employers
ARRAY

ID/name pairs of the work employer targeting specs applied to the ad.

id
STRING

The ID of the work employer.

name
STRING

The name of the work employer. Ex: Microsoft

ads (table), work_employers (attribute)

work_positions
ARRAY

ID/name pairs of the work position targeting specs applied to the ad.

id
STRING

The ID of the work position.

name
STRING

The name of the work position. Ex: Contractor

ads (table), work_positions (attribute)

locales
ARRAY

The locale targeting specs applied to the ad.

value
STRING

The locale to be targeted. Ex: en

ads (table), locales (attribute)

geo_locations__countries
ARRAY

Details about the country demographic targeting specs applied to the ad.

value
STRING

The country to be targeted.

ads (table), geo_locations__countries (attribute)

geo_locations__country_groups
ARRAY

The country group targeting specs applied to the ad.

value
STRING

The country group code.

ads (table), geo_locations__country_groups (attribute)

messenger_positions
ARRAY

The Messenger position targeting specs applied to the ad.

value
STRING

The Messenger position to be targeted. Ex: sponsored_messages

ads (table), messenger_positions (attribute)

instagram_positions
ARRAY

The Instagram position targeting specs applied to the ad.

value
STRING

The Instagram position to be targeted. Ex: stream, story

ads (table), instagram_positions (attribute)

audience_network_positions
ARRAY

Audience network position targeting specs applied to the ad. Facebook’s Audience Network feature allows the serving of ads on other publishers’ iOS and Android apps and mobile websites.

value
STRING

The audience network position to be targeted. Ex: instream_video

ads (table), audience_network_positions (attribute)

education_statuses
ARRAY

Education status targeting specs applied to the ad.

value
STRING

The education level to be targeted. Facebook uses integers to represent each education level. For example:

  • 1 = HIGH_SCHOOL
  • 2 = UNDERGRAD

Refer to Facebook’s documentation for the full list.

ads (table), education_statuses (attribute)

publisher_platforms
ARRAY

Publisher platform targeting specs applied to the ad.

value
STRING

The publisher platform to be targeted. Ex: facebook

ads (table), publisher_platforms (attribute)

device_platforms
ARRAY

Device platform targeting specs applied to the ad. This is the type of device (mobile, desktop) someone who views your ad has.

value
STRING

The device platform to be targeted.

ads (table), device_platforms (attribute)

facebook_positions
ARRAY

Facebook position targeting specs applied to the ad. The position is the location on Facebook where the ad is served. Ex: in the newsfeed or a suggested video.

value
STRING

The Facebook position to be targeted. Ex: feed

ads (table), facebook_positions (attribute)

user_os
ARRAY

User operating system targeting specs applied to the ad.

value
STRING

The user operating system to be targeted.

ads (table), user_os (attribute)

user_device
ARRAY

User device targeting specs applied to the ad.

value
STRING

The user device to be targeted.

ads (table), user_device (attribute)

excluded_publisher_categories
ARRAY

Excluded publisher category targeting specs applied to the ad.

value
STRING

The publisher category to be excluded.

ads (table), excluded_publisher_categories (attribute)

targeting_optimization
ARRAY

The targeting optimization specs applied to the ad.

value
STRING

The targeting optimization spec.

ads (table), targeting_optimization (attribute)

relationship_statuses
ARRAY

Relationship status targeting specs applied to the ad.

value
STRING

The relationship status to be targeted.

ads (table), relationship_statuses (attribute)

interested_in
ARRAY

The targeting specs applied to the ad regarding topics users are interested in.

value
STRING

The topic to be targeted.

ads (table), interested_in (attribute)

excluded_user_device
ARRAY

Excluded user device targeting specs applied to the ad.

value
STRING

The device to be excluded.

ads (table), excluded_user_device (attribute)
ads (table), targeting (attribute)

last_updated_by_app_id
STRING

The ID of the app that last updated the ad.

recommendations
ARRAY

Details about the recommendations for the ad, if there are any.

code
INTEGER

The recommendation code.

blame_field
STRING

Field to associate the recommendation with.

confidence
STRING

Indicates how reliable the recommendation is. Possible values are HIGH, MEDIUM, and LOW.

importance
STRING

Indicates how important the recommendation is. Possible values are HIGH, MEDIUM, and LOW.

message
STRING

The content of the recommendation message.

title
STRING

The title of the recommendation.

ads (table), recommendations (attribute)

tracking_specs
ARRAY

Tracking specs are actions taken by people interacting with the ad. Note: tracking specs only track - they don’t optimize or charge based on that action occurring.

value
STRING

The value for the tracking spec.

ads (table), tracking_specs (attribute)

conversion_specs
ARRAY

Conversion specs allow Facebook to surface the ad to users most likely to perform a desired decision. For example: adding to a shopping cart, viewing a particular page, or completing a form.

value
STRING

The conversion spec.

ads (table), conversion_specs (attribute)

Replication Method :

Key-based Incremental

Replication Key :

date_start

Primary Key :

ad_id : adset_id : campaign_id : date_start

Official docs :

Official Docs

The ads_insights table contains entries for each campaign/set/ad combination for each day, along with detailed statistics.

Note: Data for deleted ads, adsets, and campaigns will not appear in this table even if the Include data from deleted campaigns, ads, and adsets option in the integration’s settings is enabled.

Segmented insights data

To analyze data that’s been segmented by various characteristics, consider tracking some of the other ads_insights tables in this integration. The following tables contain the same fields as this one (ads_insights), but include additional dimensions to segment the data:

ad_id
STRING

The ID of the ad.

Reference:

adset_id
STRING

The ID of the ad set. An ad set is a group of ads that share the same budget, schedule, delivery optimization, and targeting.

Reference:

campaign_id
STRING

The ID of the campaign. Campaigns contain ad sets and ads.

Reference:

date_start
DATE-TIME

The start date.

date_stop
DATE-TIME

The end date.

ad_name
INTEGER

The name of the ad.

adset_name
STRING

The name of the adset.

campaign_name
INTEGER

The name of the campaign.

clicks
INTEGER

The number of clicks on your ads.

website_ctr
ARRAY

The percentage of times people saw the ad and performed a link click.

action_target_id
STRING

The ID of the destination where people go after clicking on the ad. This could be your Facebook Page, an external URL for your conversion pixel, or an app configured with the Facebook SDK.

action_type
STRING

The kind of actions taken on the ad, Page, app, or event after your ad was served to someone, even if they didn’t click on it.

Action types include Page likes, app installs, conversions, event responses, and more.

Note: As of July 2018, Facebook has deprecated the following action types:

  • app_custom_event
  • mention
  • tab_view

value
NUMBER

The metric value of the default attribution window.

action_destination
STRING

The destination where people go after clicking on the ad. This could be your Facebook Page, an external URL for your conversion pixel, or an app configured with the Facebook SDK.

ads_insights (table), website_ctr (attribute)

unique_inline_link_click_ctr
NUMBER

The percentage of times people saw the ad and performed a link click. Inline click-through rate uses a fixed 1-day-click attribution window.

frequency
NUMBER

The average number of times each person saw your ad.

account_id
STRING

The ID number of your ad account.

Reference:

account_name
STRING

The name of your ad account.

canvas_avg_view_time
NUMBER

The average total time, in seconds, that people spent viewing a Facebook Canvas.

unique_inline_link_clicks
INTEGER

The number of people who performed an inline link click.

cost_per_unique_action_type
ARRAY

Details about the average cost of unique actions.

value
STRING

The metric value of the default attribution window.

action_type
STRING

The kind of actions taken on the ad, Page, app, or event after your ad was served to someone, even if they didn’t click on it.

Action types include Page likes, app installs, conversions, event responses, and more.

Note: Facebook has deprecated some values for this field. They are as follows:

In July 2018:

  • app_custom_event
  • mention
  • tab_view

In October 2018:

  • app_custom_event_fb_mobile_add_to_cart
  • app_custom_event_fb_mobile_add_to_wishlist
  • app_custom_event_fb_mobile_initiated_checkout
  • app_custom_event_fb_mobile_search
  • app_custom_event_fb_mobile_complete_registration
  • app_custom_event_fb_mobile_achievement_unlocked
  • app_custom_event_fb_mobile_add_payment_info
  • app_custom_event_fb_mobile_content_view
  • app_custom_event_fb_mobile_level_achieved
  • app_custom_event_fb_mobile_purchase
  • app_custom_event_fb_mobile_rate
  • app_custom_event_fb_mobile_spent_credits
  • app_custom_event_fb_mobile_tutorial_completion
ads_insights (table), cost_per_unique_action_type (attribute)

inline_post_engagement
INTEGER

The total number of actions that people take involving the ad. Inline post engagements use a fixed 1-day-click attribution window.

relevance_score
OBJECT

Details about the relevance score of the ad.

status
STRING

The status of the ad’s relevance score.

Note: Relevance scores are shown after ads receive more than 500 impressions. In addition, relevance scores are only applicable to ads and will not appear for ad sets and campaigns.

score
NUMBER

A 1-10 rating that estimates how well the target audience is responding to the ad.

Facebook’s documentation states that: “10 means we (Facebook) estimate the ad is highly relevant and 1 means we (Facebook) estimate it’s not very relevant.”

ads_insights (table), relevance_score (attribute)

inline_link_clicks
INTEGER

The number of clicks on links to select destinations or experiences, on or off Facebook-owned properties. Inline link clicks use a fixed 1-day-click attribution window.

cpc
NUMBER

The average cost for each click (all).

cost_per_unique_inline_link_click
NUMBER

The average cost of each unique inline link click.

cpm
NUMBER

The average cost for 1,000 impressions.

cost_per_inline_post_engagement
NUMBER

The average cost of each inline post engagement.

inline_link_click_ctr
NUMBER

The percentage of time people saw your ads and performed an inline link click.

cpp
NUMBER

The average cost to reach 1,000 people.

cost_per_action_type
ARRAY

Details about the average cost of a relevant action.

value
STRING

The metric value of the default attribution window.

action_type
STRING

The kind of actions taken on the ad, Page, app, or event after your ad was served to someone, even if they didn’t click on it.

Action types include Page likes, app installs, conversions, event responses, and more.

Note: Facebook has deprecated some values for this field. They are as follows:

In July 2018:

  • mention
  • tab_view

In October 2018:

  • app_custom_event_fb_mobile_add_to_cart
  • app_custom_event_fb_mobile_add_to_wishlist
  • app_custom_event_fb_mobile_initiated_checkout
  • app_custom_event_fb_mobile_search
  • app_custom_event_fb_mobile_complete_registration
  • app_custom_event_fb_mobile_achievement_unlocked
  • app_custom_event_fb_mobile_add_payment_info
  • app_custom_event_fb_mobile_content_view
  • app_custom_event_fb_mobile_level_achieved
  • app_custom_event_fb_mobile_purchase
  • app_custom_event_fb_mobile_rate
  • app_custom_event_fb_mobile_spent_credits
  • app_custom_event_fb_mobile_tutorial_completion

  • app_install
  • mobile_app_install
  • onsite_conversion.add_to_cart
  • onsite_conversion.view_content
ads_insights (table), cost_per_action_type (attribute)

unique_link_clicks_ctr
NUMBER

The percentage of people who saw the ad and performed a link click.

spend
NUMBER

The estimated total amount of money spent on the campaign, ad set, or ad during its schedule.

cost_per_unique_click
NUMBER

The average cost of each unique click (all).

unique_clicks
INTEGER

The number of people who performed a click (all).

social_spend
NUMBER

The total amount spent so far for the ad showed with social information. Ex: Stitch Data likes this.

reach
INTEGER

The number of people who saw the ad at least once.

reach is different than impressions, which may include multiple views of the ads by the same people.

canvas_avg_view_percent
NUMBER

The average percentage of the Facebook Canvas that people saw.

objective
STRING

The objective selected for the campaign. This reflects the goal you want to achieve with your advertising.

impressions
INTEGER

The number of times the ad was on screen.

unique_ctr
NUMBER

The percentage of people who saw your ad and performed a unique click (all).

cost_per_inline_link_click
NUMBER

The average cost of each inline link click.

ctr
NUMBER

The percentage of times people saw your ad and performed a click (all).


ads_insights_age_and_gender

Replication Method :

Key-based Incremental

Replication Key :

date_start

Primary Key :

ad_id : adset_id : campaign_id : date_start : age : gender

Official docs :

Official Docs

The ads_insights_age_and_gender table contains entries for each campaign/set/ad combination for each day, along with detailed statistics, segmented by age and gender.

This table contains the same fields as the ads_insights table, with the exception of age and gender.

Note: Data for deleted ads, adsets, and campaigns will not appear in this table even if the Include data from deleted campaigns, ads, and adsets option in the integration’s settings is enabled.

ad_id
STRING

The ID of the ad.

Reference:

adset_id
STRING

The ID of the ad set. An ad set is a group of ads that share the same budget, schedule, delivery optimization, and targeting.

Reference:

campaign_id
STRING

The ID of the campaign. Campaigns contain ad sets and ads.

Reference:

date_start
DATE-TIME

The start date.

age
INTEGER, STRING

The age by which the data is segmented.

gender
STRING

The gender by which the data is segmented.

date_stop
DATE-TIME

The end date.

ad_name
INTEGER

The name of the ad.

adset_name
STRING

The name of the adset.

campaign_name
INTEGER

The name of the campaign.

clicks
INTEGER

The number of clicks on your ads.

website_ctr
ARRAY

The percentage of times people saw the ad and performed a link click.

action_target_id
STRING

The ID of the destination where people go after clicking on the ad. This could be your Facebook Page, an external URL for your conversion pixel, or an app configured with the Facebook SDK.

action_type
STRING

The kind of actions taken on the ad, Page, app, or event after your ad was served to someone, even if they didn’t click on it.

Action types include Page likes, app installs, conversions, event responses, and more.

Note: As of July 2018, Facebook has deprecated the following action types:

  • app_custom_event
  • mention
  • tab_view

value
NUMBER

The metric value of the default attribution window.

action_destination
STRING

The destination where people go after clicking on the ad. This could be your Facebook Page, an external URL for your conversion pixel, or an app configured with the Facebook SDK.

ads_insights_age_and_gender (table), website_ctr (attribute)

unique_inline_link_click_ctr
NUMBER

The percentage of times people saw the ad and performed a link click. Inline click-through rate uses a fixed 1-day-click attribution window.

frequency
NUMBER

The average number of times each person saw your ad.

account_id
STRING

The ID number of your ad account.

Reference:

account_name