Connect Facebook Ads (Meta) to BigQuery

3 methods to connect Facebook Ads to BigQuery

While there are a few methods to get the Facebook Ads data to BigQuery, the process is often complicated and costly.

Unfortunately, Google’s BigQuery does not have a simple and quick built-in tool to import data directly from the Facebook Ads Manager. However, you can still import all your Facebook ads data into BigQuery using any one of the following three steps.

Method 1: Using Windsor.ai connector integration to move data from Facebook Ads to BigQuery

One of the simplest and most reliable methods to import your Facebook Ads data into Google BigQuery is using a powerful connector integration from Windsor.ai. Compared to other methods, it is the easiest and does not require writing a single line of custom code.

Here is a step-wise guide to integrating your Facebook Ads data into your BigQuery using Windsor.ai:

1. Register

Register or login if you already have an account.

2. Select your source

You need to select Facebook Ads as a Data Source and Grant Access to Windsor.ai.

3. Select Destination
Choose BigQuery as the destination.

4. Create a destination task
Click the Add Destination Task Button and fill out the necessary fields.

5. Sync your Data
In the final step, grant access to the user: [email protected]. That’s all!
Once you go through these steps, you will see that the data is automatically populated into your BigQuery account.

*Note: As a connector URL, you can use any URL providing a JSON. Either from the connectors or for example a URL with cached and transformed data.

Method 2: Upload data manually from Facebook Ads to BigQuery

Uploading data manually from Facebook Ads Manager to Google BigQuery is tedious and time-consuming. Although it is the cheapest way, it will require several steps.

If you want to know how you can manually enter data from your Facebook Ads into your BigQuery account, follow the steps given below:

• Sign up for Google Cloud Project and create a new project for your campaign.
• Go to your Facebook Ads Manager, select the data you want to import and download the reports. Make sure that you save the files in the .csv format.

• Go back to the project on BigQuery and click “Create Dataset” and name it, ensure that it is encrypted and click “Create Table.”

• Then in the source section, create the table form and select the file format to .csv.
• Then navigate to the destination and select the correct options.
• Go to “Partition and Custer Settings” and choose the option that suits you the most, and select field delimiter by going into the “Advanced Options.”
• Click create a table. Once you do this, your data will be populated in BigQuery with Facebook Ads data.
• Once the data is populated, you can go back to Google BigQuery and click on your dataset ID.
• You have multiple options for your data. You can either write SQL queries against your Facebook data or export them to Looker Studio or other third-party tools for in-depth analysis.
• Just repeat this process to extract all the Facebook data from your campaigns and import it into BigQuery.

As you can see, this process is quite lengthy and time-consuming. Imagine doing this over and over for multiple Facebook accounts you handle. It can get tiring and difficult to analyze Facebook data of multiple accounts.

However, following these steps will help you get your Facebook data into your BigQuery account if you must do it.

Method 3: Writing custom scripts to move data from Facebook Ads to BigQuery

If you have a technical team or expertise in writing code, you can also write custom scripts to pull data from your Facebook Ads Manager into Google BigQuery. Transferring data using a custom script involves the following two steps:

• Extracting the data from Facebook, and
• Loading it into BigQuery

You can extract the relevant ads insight data from Facebook through APIs or real-time streams.

You can access Facebook’s API using different SDKs that the platform offers. The platform natively supports Python and PHP. Facebook marketing API is RESTful, and you can interact with several frameworks and languages.

You can access all your ads data through the Graph API. You can also request additional data for specific ads through Facebook Insights.

If the process seems a bit complicated, we have you covered. Let’s take an example to help simplify things a bit. If you want to extract all the ads data from any given account, you can do that by executing the following script through curl:

curl -F ‘level=campaign’ -F ‘fields=[]’ -F ‘access_token=<ACCESS_TOKEN>’ https://graph.facebook.com/v2.5/<CAMPAIGN_ID>/insights

curl -G -d ‘access_token=<ACCESS_TOKEN>’ https://graph.facebook.com/v2.5/1000002

curl -G -d ‘access_token=<ACCESS_TOKEN>’ https://graph.facebook.com/v2.5/1000002/insights

Once the script is ready, all the requested data will be returned into a .csv format, and you can access it through an access URL.

Another way of pulling data from Facebook is by creating a real-time data structure. You will need to subscribe to the real-time update to do this.

You can get a stream of real-time data from your database using the right structure. While Facebook Ads have a tremendously rich API, and you can extract even the smallest piece of data regarding your account and target audience, things can get complicated quickly.

You will need a more complex protocol to handle large amounts of data from multiple accounts.

The data you pulled from Facebook can come in several formats. However, if you want to use it in BigQuery, you must be mindful of the formats that BigQuery supports.

Once you extract the data from your Facebook Ads Manager, the next thing is to load it into Google BigQuery. There are a few ways of getting this done. Perhaps, the simplest one is through the console.

Another way to enter the extracted data from Facebook Ads to Google BigQuery is using JASON API. You can also use tools like curl to request HTTP POST requests to lead data. Here is a sample POST request to load data into BigQuery:

POST /upload/storage/v1/b/myBucket/o?uploadType=media&name= TEST HTTP/1.1 Host: www.googleapis.com Content-Type: application/text

Content-Length: number_of_bytes_in_file

Authorization: Bearer your_auth_token your Facebook Ads data

To automate the import process, you must write custom codes and send your Facebook Ads data to BigQuery You can use any one of the languages to write the script.

• Python
• Java
• PHP
• Go

While building a custom code for your data transfer to Google BigQuery might seem an attractive solution, the process has some serious drawbacks as well:

• You will need to maintain your custom code, which will require additional resources
• You will also have to set up a data validation system so that there is no data leakage
• The method we discussed above works for importing the data from Facebook ads. If you want to analyze Facebook data, you will need additional code on top of that.

How do you get Facebook data into BigQuery?

1. Getting the data from Facebook Ads Insights

Before you can get started here you will need to have a Facebook ID with access to a Facebook Ads account (for this example).

Facebook has a well documented Marketing API with SDK’s in PHP and Python. There is an active community building and maintaining SDK’s and wrappers for R (https://github.com/daroczig/fbRads), Java Script and many more.

But let’s do a simple example here to explain how to query data from the Graph API.

The following request queries your account with an attribution window of 7 day post view for the time range between the 19/06/2020 and the 25/06/2020 and provides the fields account id, ad id, campaign id and ad-set ID on an ad-set level:

To modify and create your own queries I recommend using Postman and start with pasting the link above.

If you refer to the marketing API link above you can start creating your own reports easily.

2. Pushing the data into BigQuery

Before you get started here you will need to have write access to BigQuery.

The easiest way to upload data to BigQuery is the HTTP POST method. You can refer to the BigQuery API documentation for more information about it.

To get started I recommend creating an own table for the Facebook Ads data in BigQuery and then uploading the data using a Cron job every 24hours or more often (depending on the data volume and how often you run your reports).

Important note here:

The refresh token from Facebook expires, so it’s good to check logs or schedule yourself a notification in case the token expires, as this will break the data loading.

FAQs

What is Facebook Ads?

Facebook Ads is part of the Meta Ads platform, a service that enables you to create and display ads on Facebook and its associated platforms, such as Messenger, Instagram, WhatsApp, and Audience Network. You may create carousel ads, sideshow ads, images, and video ads. The servicer also offers targeting options, ranging from demographics to interest and behavior. You can also choose campaign objectives based on your needs.

What is BigQuery?

BigQuery is a serverless database service offered by Google. The provider undertakes the management of the database and its underlying infrastructure while users manage the data. BigQuery offers several data analytics and querying tools and connects users to other Google Cloud tools like Looker Studio and Workspace for data visualization and collaboration.