Overview

This import pulls data for a given Facebook Ad Account into a database on Civis Platform.

It supports a number of tables that correspond to different Facebook Ad objects that can be created, as delineated below, specifically in the Output Tables section of this guide.

Create a new script by clicking here, or in Platform, navigate to Code → Scripts → More Script Templates and search for "Facebook Ad Insights" or “41088”.  This import is still in beta, if you’re interested in getting access, please email support@civisanalytics.com.

If you need access to this tool, please ask the Client Success team to share you on templates 41088 and 40500.

Once you specify your parameters and trigger a job, a Civis workflow will be created but not run.  You can find your workflow ID in the script's logs pane. Specifically, near the bottom, you should see logging like Created a new Facebook Ads default workflow with Workflow ID 12345​.

The workflow will include a script to generate each output table listed below. Additionally, users can add or edit jobs in the workflow in order to specify additional desired data aggregations (ex: importing lifetime ad insights data as well as daily and hourly). See “Advanced Configuration” section below.

Adding a Facebook Custom Credential To Platform

Use this Service to create a Facebook Custom Credential to Platform. Note that the Facebook account you link to the credential must have admin permissions on the Facebook page you'll be importing data from. An existing Facebook group administrator on that page can give you the proper permission.

If you encounter any issues with the Service please reach out to Support@CivisAnalytics.com

Script Parameters

The following parameters can be specified at the workflow level and will apply to each of the tables that are created or updated.

• WORKFLOW_NAME: Name of the Platform workflow created by this script. Note the job will create a new workflow each time the script is run, the resulting workflow_id can be found in the job output and success email log.
• FB_CREDENTIAL*   A Custom Credential on Platform with the Facebook Ad Account ID as the username, and a valid Facebook Access Token as the password. See Adding a Facebook Custom Credential to Platform above for instructions to create one.

• AD_ACCOUNT_ID ID of the Facebook Ad Account that holds the ad insights data, required if the fb_credential used has an App ID as the username rather than Ad Account Id. Should start with “act_” 

• SCRIPT_MODE*: One of ‘run’, ‘test_connection’, or ‘view_config’. ‘run’ will execute a full workflow. ‘test_connection’ will check if your credentials can pull data from Facebook. ‘view_config’ will return a configuration file of the tables that are created.

• DAYS: Number of days in the past to pull records from. For example, if this is 7, the job will update any active Ad or Campaign, and all Ads/Campaigns created in the past 7 days. It’s best to start with a small value and increase if necessary.
• START_DATE: Specify the earliest date to pull records from (formatted 'YYYY-MM-DD'). Not needed if DAYS is specified.
• END_DATE: Specify the latest date to pull records from (formatted 'YYYY-MM-DD'). The update will pull all Ads, Campaigns, etc. where the created time is between the START_DATE and END_DATE. Not needed if DAYS is specified. Required if START_DATE is specified.
• DATABASE_NAME: Name of the database that your tables should be created in. Required.
• SCHEMA_NAME: Name of the schema that your tables should be created in. Required.
• TABLE_NAME_PREFIX: This import will create/update multiple tables, for campaigns, ads, etc. This optional prefix can help identify the tables that were created from this import.
• IF_EXISTS: Action to take if the destination tables already exist. The options are 'fail', 'append', 'wipe', 'drop', or 'upsert'. The recommended value for ongoing imports is 'upsert'.
• MAX_ERRORS: The number of rows the import will skip because of an error before failing.
• CUSTOM_BREAKDOWNS: Ad Insights data can optionally be pulled with additional breakdowns. This import will create one table for each breakdown specified, separated with a semicolon. i.e. ‘age,gender;region’ will create one table with age and gender columns, and a second table with a region column. See https://developers.facebook.com/docs/marketing-api/insights/breakdowns/ for valid breakdowns.
• INCLUDE_ALL_ACTIVE_ADS: Can be set to false on ongoing updates, if older data is already populated. If checked, the update will pull data on all active Ads (and associated Adsets/Campaigns), regardless of the date range specified. The main purpose of this flag is when tables are initially being filled -- as an alternative to backfilling an entire 1-2 year period, a user can backfill a recent time period with Ad Insights data, and any dependent Campaigns or Ad Attribute rows should also populate, even if created further in the past.

• JQ FILTER: A jq filter string to parse the `targeting` field. jq is used to process JSON fields, see documentation here: https://stedolan.github.io/jq/. 

  Example using a jq sandbox: https://jqplay.org/s/b34ScCy32S

        More in depth example: https://jqplay.org/s/yAm3PDSZWq

Getting Started

The recommended way to set up your import is as follows.

1. Use START_DATE and END_DATE parameters to pull older ads data, if desired. 
• For instance, if you have been running ads for multiple months or years and want older Ads data to be pulled into your tables, then you will likely need to run several imports with rolling 1-3 month windows, depending on your ad volume.
• Use ‘upsert’ mode so that your older data does not get overwritten.
• When backfilling older data, uncheck the INCLUDE_ALL_ACTIVE_ADS option to avoid re-pulling additional, recent data each time.
2. Once your data is backfilled, use DAYS instead of START_DATE and END_DATE to keep your data refreshed.
• If you are running a daily job, start out with DAYS = 7, or another smaller value. With larger values for DAYS, this import can potentially pull a lot of data for heavy users of Ads, leading to slow performance.
• Make sure INCLUDE_ALL_ACTIVE_ADS is checked for ongoing imports. This way, Ads, Campaigns, etc. that were created before your DAYS threshold will still update if they are currently active.

Output Tables

The default workflow imports a number of tables that correspond to different Facebook Ad objects that can be created, as delineated below.

• Accounts: High-level data on the ad Account you are pulling data for. This table will only populate with a single row, unless you set up multiple imports for different Facebook ad Accounts that you control.
• Campaigns: High-level data for each Campaign in your account. Example columns are name, status, and budget_remaining

• AdSets: High-level data for each AdSet in your account. Example columns are name, status, and bid_strategy. Each AdSet belongs to a single Campaign, denoted by campaign_id.

• Ads: High-level data for each Ad in your account. Example columns are name, status, and bid_amount. Each Ad belongs to a single AdSet and Campaign, denoted by adset_id and campaign_id. Note that detailed performance data is found in the Ad Insights tables.

• Ad Insights - Daily: Daily performance data for each Ad in your account. One row represents a specific Ad and Date (date_start). Example metrics are impressions, reach, clicks, and conversions. Each row contains an ad_id, adset_id, campaign_id, and account_id so that performance can be tracked at higher levels of granularity.

• Ad Insights - Hourly: Similar to Ad Insights Daily, this table tracks performance at the hourly level. This can support monitoring of trends for different times of the day, etc.

• Ad Insights - Custom: Optionally, one or more tables can be created with custom breakdowns. This will include ad performance (at the daily level of granularity) with additional columns. One common example is age/gender. It is up to the end user to specify the specific breakdowns they want to save, since there are dozens of combinations. A full list and explanation can be found at https://developers.facebook.com/docs/marketing-api/insights/breakdowns/. Additionally, customized time parameters can be specified, as explained in the Advanced Configuration section.

• Ad Set Insights / Campaign Insights / Account Insights - Custom: Insights can also be pulled at the Ad Set, Campaign, and Account level, with the same options available as with Ad Insights. Jobs at this level are not created in the default workflow, but can be added if desired by updating the workflow configuration.

Advanced Configuration

Any Ad, Ad Set, Campaign, or Account Insights jobs can be duplicated or edited to accept additional parameters in the YAML, updated. See an example YAML with these advanced configurations here.  Accepted parameters below:

• custom_date_preset: a relative time range, ex: “lifetime” or “last90d”. See all supported values here. 

• This is calculated separately from the start/end date. For example, if the start date for an Ad Insights job is 7 days ago, and custom_date_preset is “last90d”, then the job will first pull every Ad created/active in the past 7 days, and then pull the last 90 days of data on each of those ads.

• If this is left blank, insights data will be pulled strictly between the start/end date. In this case, if the start date for an Ad Insights job is 7 days ago, the job will first pull every Ad created/active in the past 7 days, and then pull the last 7 days of data on each of those ads.

• custom_time_increment: either “monthly”, “all_days”, or integer less than 90. Specifies the desired data aggregation - ex: if “monthly” is selected data will be grouped by month within the “days” or between the “start_date” and “end_date” provided.

Parsing AdSet Targeting

The AdSet output table includes a targeting field that includes the Facebook JSON object. You may want to extract certain information, for example included audience_ids, from the targeting object. Here’s sample code to demonstrate how to parse the JSON in Redshift:

select json_extract_path_text(first_aud_for_adset, 'id', true) as custom_audience_id
from(
  select json_extract_array_element_text(json_extract_path_text(targeting, 'custom_audiences', true), 0, true) as first_aud_for_adset from schema.table
  )

Note that the targeting field is subject to database character limits, and will be truncated if the JSON is longer than the database limit. You may want to make use of the JQ Filter parameter to limit results to information of interest for this reason.