Load Facebook Ads data into BigQuery

You can load data from Facebook Ads to BigQuery using theBigQuery Data Transfer Service for Facebook Ads connector. With theBigQuery Data Transfer Service, you can schedule recurring transfer jobs thatadd your latest data from your Facebook Ads toBigQuery.

Connector overview

The BigQuery Data Transfer Service for the Facebook Ads connector supports the following options for your data transfer.

Limitations

Facebook Ads data transfers are subject to the following limitations:

• The minimum interval time between recurring Facebook Ads data transfers is24 hours. The default interval for a recurring data transfer is 24 hours.
• The BigQuery Data Transfer Service for Facebook Ads only supports a fixed setof tables. Custom reports aren't supported.
• Facebook Ads data transfers have a maximum duration of six hours. Atransfer fails if it takes longer than this maximum duration.
• Incremental transfers aren't supported forAdInsights andAdInsightsActionstables. When you create a data transfer that includesAdInsightsandAdInsightsActions tables, and you specified a date inSchedule options, all data that is available for that date istransferred.
• The BigQuery Data Transfer Service supports a refresh window of up to 30 days to theAdInsights andAdInsightsActions tables. The refresh window refers to thenumber of days that a data transfer will retrievesource data from. When you run a data transfer for the first time, thedata transfer retrieves all source data available within the refreshwindow.

• The long-lived user access token that is required for Facebook Adstransfers expires after 60 days.

  If your long-lived user access token is expired,you can obtain the new one by navigating to your data transfer details andclickingEdit. In the edit transfer page, follow the same steps inFacebook Ads prerequisites to generatea new long-lived user access token.

• To use a network attachment with this data transfer, you must firstcreate anetwork attachment by defining a static IPaddress.

• If your configured network attachment and virtual machine (VM) instance arelocated in different regions, there might be cross-region data movement whenyou transfer data from Facebook Ads.

Data ingestion from Facebook Ads transfers

ForAdInsights andAdInsightsActions tables, the table partition that thedata is loaded into corresponds to the date from the data source.

ForAdAccounts tables, snapshots are taken once a day and stored in thepartition of the last transfer run date. The refresh window does not apply totheAdAccounts table.

Refresh windows

Arefresh window is the number of days that a data transfer retrieves datawhen a data transfer occurs. For example, if the refresh window is three daysand a daily transfer occurs, the BigQuery Data Transfer Service retrieves all data fromyour source table from the past three days. In thisexample, when a daily transfer occurs, the BigQuery Data Transfer Service creates a newBigQuery destination table partition with a copy of your source table datafrom the current day, then automatically triggers backfill runs to update theBigQuery destination table partitions with your source table data from thepast two days. The automatically triggered backfill runs will either overwriteor incrementally update your BigQuery destination table,depending on whether or not incremental updates are supported in theBigQuery Data Transfer Service connector.

When you run a data transfer for the first time, the data transfer retrieves allsource data available within the refresh window. For example, if the refreshwindow is three days and you run the data transfer for the first time, theBigQuery Data Transfer Service retrieves all source data within three days.

To retrieve data outside the refresh window, such as historical data, or torecover data from any transfer outages or gaps, you can initiate or schedule abackfill run.

Before you begin

The following sections describe the steps that you need to take before youcreate a Facebook Ads data transfer.

Facebook Ads prerequisites

Ensure that you have the following Facebook Ads information whencreating a Facebook Ads data transfer.

To obtain aclientID andclientSecret, perform thefollowing steps:

1. Create a Facebook developer appwith the app typeBusiness.
2. In theFacebook App dashboard,clickApp Settings>Basic and findthe app ID and app secret that correspond to the app.

To obtain a long-lived user access token, also known as arefresh token,perform the following steps:

1. In the Google Cloud console, proceed with the steps tocreate a Facebook Ads transfer.

2. In theData Source Details section, copy the redirect URI listed aftertheRefresh Token field.

3. Click theFacebook App dashboard,then clickSet up in theFacebook login for Business section.

   

4. In theSettings page, enter the redirect URL in theValid OAuth Redirect URIs field and clickSave.

5. Return to the Google Cloud console. In theData Source Details section,clickAuthorize. You will be redirected to a Facebook authentication page.

   

6. Select the Facebook developer app to authorize the account that connects with the BigQuery Data Transfer Service.

7. Once complete, clickGot it to return to the Google Cloud console. Thelong-lived user access token is now populated in the transfer configuration.

Long-lived user access tokens expire after 60 days. For information on how toobtain a new long-lived user access token, seeLimitations.

Refresh token alternatives

Alternatively, you can provide a refresh token when youcreate a data transferif you have obtained one using one of the following methods:

• Generate a long-lived user access token using the Graph API.Theads_management,ads_read, andbusiness_management permissions arerequired for a valid token for the data transfer.
• Generate a system user token.A system user token lets you manually add assets, such as ad accounts, to beincluded in the data transfer. If a system user token is expired, you mustmanually update the transfer configuration with new credentials. You alsohave the option to create a token that doesn't expire when you create a systemuser token. For more information, seeSupported access tokens.

BigQuery prerequisites

• Verify that you have completed all actions required toenable the BigQuery Data Transfer Service.
• Create a BigQuery datasetto store your data.
• If you intend to set up transfer run notifications for Pub/Sub,ensure that you have thepubsub.topics.setIamPolicy Identity and Access Management (IAM) permission. If you only set up email notifications, Pub/Sub permissions aren't required. For moreinformation, seeBigQuery Data Transfer Service run notifications.

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information, seeGrantbigquery.admin access.

Create a Facebook Ads data transfer

Select one of the following options:

bqmk--transfer_config--project_id=PROJECT_ID--data_source=DATA_SOURCE--display_name=DISPLAY_NAME--target_dataset=DATASET--params='PARAMETERS'

bqmk--transfer_config--target_dataset=mydataset--data_source=facebook_ads--display_name='My Transfer'--params='{"connector.authentication.oauth.clientId": "1650000000",    "connector.authentication.oauth.clientSecret":"TBA99550",    "connector.authentication.oauth.refreshToken":"abcdef",    "connector.authorizedAdAccountsOnly":true,    "connector.actionCollections":["Actions", "Conversions"],    "connector.genericBreakdowns":["PublisherPlatform", "PlatformPosition"],    "connector.actionBreakdowns":["ActionDevice", "ActionType"]}'

To manually run a data transfer outside of your regular schedule, you can startabackfill run.

For information on how your transferred data maps to Meta API fields, seeFacebook Ads report transformation.

Action collections

Action collections are objects that specify the different types of actionspeople have taken in response to your ad. You can specify action collectionswhen youset up your transfer configuration.

Action collections represent the fields of thelist<AdsActionStats> typethat are present in theAd Account, Insights endpoint response.

When a transfer completes, these action collections are populated in theAdInsightsActions table.

The following is a list of action collections supported in a Facebook Adsdata transfer:

• ActionValues
• Actions
• AdClickActions
• AdImpressionActions
• CatalogSegmentActions
• CatalogSegmentValue
• CatalogSegmentValueMobilePurchaseRoas
• CatalogSegmentValueOmniPurchaseRoas
• CatalogSegmentValueWebsitePurchaseRoas
• ConversionValues
• Conversions
• ConvertedProductQuantity
• ConvertedProductValue
• CostPer15_secVideoView
• CostPer2SecContinuousVideoView
• CostPerActionType
• CostPerAdClick
• CostPerConversion
• CostPerOneThousandAdImpression
• CostPerOutboundClick
• CostPerThruplay
• CostPerUniqueActionType
• CostPerUniqueConversion
• CostPerUniqueOutboundClick
• InteractiveComponentTap
• MobileAppPurchaseRoas
• OutboundClicks
• OutboundClicksCtr
• PurchaseRoas
• UniqueActions
• UniqueConversions
• UniqueOutboundClicks
• UniqueOutboundClicksCtr
• UniqueVideoView15_sec
• Video15_secWatchedActions
• Video30_secWatchedActions
• VideoAvgTimeWatchedActions
• VideoContinuous2SecWatchedActions
• VideoP100_watchedActions
• VideoP25WatchedActions
• VideoP50WatchedActions
• VideoP75WatchedActions
• VideoP95WatchedActions
• VideoPlayActions
• VideoPlayCurveActions
• VideoPlayRetentionGraphActions
• VideoTimeWatchedActions
• WebsiteCtr
• WebsitePurchaseRoas

Combining breakdowns

Facebook Ads has restrictions on what columns can be selectedtogether. Using these restricted combinations will cause the data transfer tofail.

For more information about what breakdowns can be combined, seeCombining Breakdowns.

Troubleshoot transfer configuration

If you are having issues setting up a Facebook Ads data transfer, try thefollowing troubleshooting steps:

• Check if your user access token has expired using theFacebook Access Token Debugger. Long-lived user accesstokens expire after 60 days. If your long-lived user access token has expired,navigate to your transfer details then clickEdit to modify your transferconfiguration. In the edit transfer page, follow the same steps inFacebook Ads prerequisites to generate a new one.

• Check that the long-lived user access token is generated with the requiredpermissions -ads_management,ads_read, andbusiness_management. You cancheck the permissions on your long-lived user access token by entering thefollowing link into your browser:

  https://graph.facebook.com/me/permissions?access_token=TOKEN

  WhereTOKEN is the value of the long-lived user access token.

  If you don't have the required permissions, generate a new long-lived useraccess token by following the steps inFacebook Ads prerequisites.

• Check theRequired Actions tab on theFacebook App dashboard for any itemsthat require attention.

You might encounter the following error messages related to Meta API rate limiterrors:

Error:There have been too many calls from this ad-account. Wait a bit and try again.

Resolution: Check that there are no parallel workflows using the same appsor credentials. If these errors persist, try upgrading your permissions toAdvanced Accessto get more rate limiting quota. For more information, seeMarketing API Rate Limiting.

Common monitoring metrics messages

You can also check theBigQuery Data Transfer Service monitoring metricsto determine the cause of a data transfer failure. The following table lists somecommonERROR_CODE messages for Facebook Ads data transfers.

Pricing

For pricing information about Facebook Ads transfers, seeData Transfer Service pricing.

What's next

• Learn more about theBigQuery Data Transfer Service.
• Learn more aboutworking with transfers,such as viewing configurations and run history.
• Learn how toload data with cross-cloud operations.