All tutorials

Load data from Facebook Ads to BigQuery

Learn how to load Facebook Ads data to BigQuery using Airbyte Cloud.

No items found.

Facebook Ads is an advertising platform that allows businesses to place paid ads on Facebook, Instagram, Messenger and the Audience network. Facebook Ads provides basic reporting features. When marketing teams need to create customized reporting it’s common to move Facebook Ads data to a cloud data warehouse and then use a modern BI tools for reporting.

Furthermore, the day you need to combine Facebook Ads data with other marketing data sources (Analytics, Google Ads, LinkedIn Ads, …) you want to load allyour marketing data to a data warehouse. Google BigQuery is a popular data warehouse that integrates easily with other Google marketing data sources like Google Analytics and Google Ads. You can use Google BigQuery to build up your marketing data warehouse.

Airbyte Cloud makes it easy to move all your marketing data, including Facebook Ads data to cloud data warehouses. Airbyte is an open-source data integration platform that you can deploy yourself, or use directly through Airbyte Cloud. This tutorial will take you through the steps to set up Airbyte Cloud and copy over your Facebook Ads data to BigQuery.

Prerequisites

  1. A Facebook Business Manager account with Facebook ads.
  2. A Google Cloud Platform with access to Google BigQuery.
  3. An Airbyte Cloud account to do the data replication for you.

Step 1: Set up Facebook Ads as the Airbyte Source

For Airbyte Cloud to be able to extract data from Facebook Ads you will need to give Airbyte Cloud access to your Facebook account and find your Facebook ads account ID as specified in the Airbyte Facebook Ads source docs.

Go to the Facebook menu and search for Facebook Ad Manager. You will then be redirected to the Ad Manager site, where you will see all your accounts and their corresponding IDs.

Next, go to Airbyte Cloud and create a new source. Give the source a name and select Facebook Marketing as the source type.

Click on the Authenticate your account button. You will be prompted to sign in to your Facebook account in a popup. Once signed in, you will be asked to give Airbyte access.  Grant Airbyte Cloud the permissions to Facebook Ads.

Now enter the start and end dates for when Airbyte should copy the data. Also, enter the Account ID from Step 1 and click on Set up source.

Step 2: Set up BigQuery as the Airbyte destination

For Airbyte Cloud to be able to sync Facebook ads data to BigQuery you will need to create a BigQuery dataset, a service account and a service account key as specified in the Airbyte BigQuery destination docs.

Then create a new destination and select BigQuery as the destination type. Enter the project id, dataset id, and dataset location you configured while creating the dataset. Use the contents of the credentials JSON service account and click on Set up destination.

Step 3: Set up a Facebook Ads to BigQuery connection

Once the source and destination are configured, you can access your connection settings. You should be able to see the various data sources (Airbyte streams) from Facebook Ads. You can set the sync frequency to sync data from Facebook Ads to BigQuery and the sync mode for each stream. In this example, we will select the ads data.

You can also change the sync mode. In this case, we change it below to Incremental | Append sync mode for the ads steam. This mode will append only new data to the destination tables in BigQuery. You can also choose between using Raw Data or Basic normalization with normalization set by default. You can read more about these all connection settings in the Airbyte Cloud getting started guide. Once configured, click on the Set up connection button.

After creating a connection, you can start a sync by selecting Sync Now

Once the sync is complete, go to your Cloud Console BigQuery page. You should now be able to see the tables created by Airbyte Cloud in your dataset. You will find one table with raw data (_airbyte_raw_ads) in JSON, and other tables created by Airbyte normalization. When Airbyte finds JSON data inside a field, it will normalize it and split it into multiple tables with foreign keys to the ads table.

You can select the tables to view their schema. The following image shows the schema of the ads table.

You can test out the incremental sync for your table by adding another ad to your Facebook page and running another sync.

In this example, we added one more ad. After adding another ad, you can run the following query on BigQuery to see the count.

Conclusion

This tutorial illustrates how easy it is to set up a connection between Facebook Ads and BigQuery using Airbyte Cloud. You can then use other Airbyte marketing sources to create a marketing data warehouse and plug in a BI tool like Google Data Studio, Tableau or Metabase to create custom marketing reports.

You can get started moving data to your data warehouse in minutes with the Airbyte Cloud free trial.

Open-source data integration

Get all your ELT data pipelines running in minutes with Airbyte.

Similar use cases

Move data from Google Ads to Snowflake

Learn how to easily drive your Google Ads data into Snowflake where it can be combined with data from other sources to get a holistic view of your business.

Export Google Analytics data to BigQuery

Learn how to export Google Analytics data to BigQuery using Airbyte Cloud.

Build a data ingestion pipeline from Mailchimp to Snowflake

Learn how to easily build a data ingestion pipeline to load business data from Mailchimp to Snowflake.