How to Load Facebook Ads to BigQuery With a Few Clicks?

Facebook Page Insights Field Reference logo

+

bigquery

Stream all your marketing data from Facebook Ads to Google BigQuery

Google Big Query is a helpful data warehouse for online marketers to analyze Facebook ads data. However, connecting the data from Facebook ads to BigQuery can be complicated and might require costly technical engineering. Thankfully, with the Windsor.ai connector, it is possible to do it in a few clicks.  

Facebook has become an excellent marketing channel for many businesses providing a great reach and targeting, thanks to nearly 2 billion daily active users on the platform. However, online advertisers prefer Google’s BigQuery over Facebook Ads Manager due to its features and ease of use, especially when handling several hundred accounts.

BigQuery allows other platforms’ data, including Facebook, to be integrated and stored in its data warehouses, making it easy to manage and analyze Facebook data. But how to send data from Facebook ads to BigQuery?

While there are a few methods to get the Facebook ads data to BigQuerythe process is often complicated and costly.

If you want to get Facebook ads data to BigQuery, stick around as we discuss some of the easiest ways to do just that. This article will also walk you through the basic process of preparing your Facebook ads data and will answer some of the important questions you might have.

So, let’s get going!

 

Table of content

 

 

 

 

Prepare your Facebook ads data for Google BigQuery

 

Before we uncover the details of the methods to get your Facebook ads data imported into BigQuery, it is important to prepare the data for the import. The data must be in the correct format, so there are no import issues.

In most cases, the data you pull from Facebook Ads Library API will be in an XML file incompatible with Google BigQuery. You will have to transform it to serialization understood by Google’s platform. Currently, BigQuery supports data in the following two formats:

 

  • CSV
  • JSON

 

Apart from the format, you will also need to ensure that BigQuery supports the type of data you leverage from Facebook. Currently, the following types of data are supported:

 

  • INTEGER
  • STRING
  • FLOAT
  • RECORD
  • TIMESTAMP
  • BOOLEAN

 

If you want to know more about the data conversion process and additional requirements, you can visit Google’s BigQuery Documentation page.

Once your data is successfully prepared in the correct format, it is time for the most important step – transferring the data into BigQuery.

 

3 methods to connect Facebook ads to BigQuery

 

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. Sign up for Windsor.ai. You can also take advantage of the free trial to get a feel of the platform and see its features.
  1. Once in your account’s dashboard, select Facebook Ads in the left column as a data source. Once you choose Facebook Ads, you don’t need to download the raw data from your Facebook Ads Manager. Windsor will do that for you in the next step.

  1. Once you select the data source, click the Next (Data Preview Button). Here, on the next screen, you can choose multiple BigQuery competitors for your Facebook ads data, including Google Sheets, Google Data Studio, and of course, Google BigQuery. Select Google BigQuery by clicking on the logo, as shown in the screenshot below.  

  1. On the next screen, click the API URL to show the connector URL.

  1. Clicking the API URL will open a dialogue box containing the URL of the connector. You need to copy it.

  1. In the final step, grant access to the user: bq-upload@windsor-ai-bigquery.iam.gserviceaccount.com and paste the connector URL you copied into BigQuery. That’s all!
  1. Once you go through these steps, you will see that the data is automatically populated into your BigQuery account.

As you can see, it is easy to analyze Facebook data with the BigQuery platform using the Windsor.ai connector. Sign up today for a 14-day free trial version or request a demo.

Our expert technicians will be happy to answer any questions you might have.

Here is a video explaining how to connect data to BigQuery with Windsor.ai.

https://youtu.be/2dsTEKTvfMc 

 

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 Google Data 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 Google. 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.

 

Frequently asked questions

 

1. How to send data from Facebook ads to BigQuery?

You can manually send data from Facebook ads to Google BigQuery using the following methods:

  • Manually copying the Facebook ads data into BigQuery
  • Writing custom code to transport the data
  • Using a connector like Windsor.ai to automate the data transfer from Facebook Ads Manager directly into BigQuery

 

2. Can I use custom code to import Facebook ads data into BigQuery?

 

It is possible to import data from Facebook ads data to Google BigQuery using custom scripts. However, it is time-consuming, and you will need additional resources to manage and maintain the infrastructure.

Conclusion

 

Google BigQuery is a great platform for businesses to streamline data analysis from several channels, including Facebook Ads. A reliable method to import data from Facebook Ads Manager to BigQuery is essential to simplify the process.

Instead of writing and maintaining custom code or manually entering the data, you can use Windsor.ai connector integration.

You can import analytics-ready Facebook ads data into BigQuery within seconds. Windsor.ai is a powerful tool that can automate your data integration so that you get the most out of your ad campaign.

Let your marketing executives harness all the power to analyze Facebook data and boost your ROI with Windsor.ai. Register today for a 14-day free trial!

Windsor.ai connector Integration

Now let’s see how easy it is to get Facebook Ads to Google BigQuery with Windsor.ai. 

 

  1. Select FB Ads as a data source
Select FB Ads as Data Source
Choose Facebook Ads

   2. Select BigQuery as destination

Select BigQuery as Destination
Select BigQuery as Destination

   3. Copy the connector URL

Copy the URL
You cannot connect FB Ads data to BigQuery without the connector URL.

   4. After you grant access to the user: bq-upload@windsor-ai-bigquery.iam.gserviceaccount.com you should paste the connector URL in BigQuery and the data will start being populated.

And if you don’t know enough about BigQuery, have a look at this Google Cloud resource

Windsor.ai: Easily connect Facebook Ads to Google BigQuery

See the value and return on every marketing touchpoint

data warehouse

Providing 50+ marketing data streams, we make sure that all the data we integrate is fresh and accessible by marketeers, whenever they want.

insights

Spend less time on manual data loading and spreadsheets. Focus on delighting your customers.

Start a free trial today.

Integrate all your data and measure what matters.