How to load data from Facebook Ads to Google BigQuery

Extract data from Facebook Ads

You can pull any data out of Facebook Ads through the Ads Insights API. The Insights API provides access to the analytics and reporting functionality. The way you interact with the data you own is by requesting reports where you define the data and the required granularity.

As in the case of Google, Facebook also exposes a very rich set of APIs that you can use for every aspect of your advertising needs, from creating ads programmatically to see how your campaigns perform.

In this post, we'll focus only on how to extract data out of Facebook Ads; for further information on what else can be performed through the Facebook Ads related APIs, you can check the documentation of the Marketing API.

Before you start, do also have a read on how to activate and manage your developer account. And make sure that you understand the security-related concepts of the Facebook Marketing API. In general, access to the API happens mainly through the SDKs that Facebook offers. Officially, SDKs for PHP and Python are supported, while there are also a number of community-supported SDKs for languages like R, JavaScript, and Ruby. You can also find more if you do your research on platforms like GitHub.

The Facebook Marketing API is a RESTful web API and thus can also be accessed by performing requests directly to the appropriate endpoints. As a RESTful API, interacting with it can be achieved by using tools like CURL or Postman or by using HTTP clients for your favorite language or framework. A few suggestions are as below:

As with everything in Facebook, Ads and their statistics are part of the Graph API, which you can interact with also using the Graph Explorer, and there's a special edge that you can use to request your Ads' statistics; it's the insights edge.

Insights can be accessed from the following list of edges:

The response from each contains information belonging to the ad object for which insights are queried.

For example, let's assume that you would like to extract all stats related to your account. You could do this by executing the following request using 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

Data can be returned in either XLS or CSV format, and when the report is ready based on your request, you can access it from a URL like the following:

https://www.facebook.com/ads/ads_insights/export_report?report_run_id=<REPORT_ID>&format=<REPORT_FORMAT>&access_token=<ACCESS_TOKEN

Get real-time streams of your Facebook Ads stats

It's also possible to create a real-time data infrastructure for fetching from Facebook Ads and loading them into a data warehouse repository. You can do that by subscribing to real-time updates to receive API updates with Webhooks. With the proper infrastructure, you can have an almost real-time feed of data into your repository and ensure that it will always be up to date with the latest data.

Facebook Ads exposes a very rich API which gives you the opportunity to get very granular data about your accounting activities and use it for analytic and reporting purposes. This richness comes with a price, though, a large number of complex resources that have to be handled through an also complex protocol.

Prepare your Facebook Ads Data for Google BigQuery

Before you load data into BigQuery, you should make sure that it is presented in a supported format. For example, if the API you pull data from returns an XML file, you must first transform it into a serialization that BigQuery understands. Currently, two data formats are supported:

You also need to make sure that the data types you are using are the ones supported by BigQuery, which are the following:

STRING
INTEGER
FLOAT
BOOLEAN
RECORD
TIMESTAMP

For more information, please check the Preparing Data for BigQuery page on the documentation.

Load Data from Facebook Ads to Google BigQuery

If you want to load Facebook Ads data to BigQuery, you have to use one of the following supported data sources:

Google Cloud Storage
Send data directly to BigQuery with a POST request
Google Cloud Datastore Backup
Implement a streaming insert
App Engine log files
Cloud Storage logs

From the list of sources mentioned above, 5 and 6 are not applicable in our case.

For Google Cloud Storage, you first have to load your data into it. There are a few options for doing this; for example, you can use the console directly as described here and follow the best practices.

Another option is to post data through the JSON API. APIs play an important role in both the extraction and the loading of data into our data warehouse. It's just a matter of one HTTP POST request using a CURL or Postman tool in its simplest case. It should look like the following example:

POST /upload/storage/v1/b/myBucket/o?uploadType=media&name=myObject 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

If everything goes well, you should get a response from the server like the following:

HTTP/1.1 200 Content-Type: application/json { "name": "myObject" }

Working with CURL or Postman is good only for testing. If you would like to automate loading your data into BigQuery, you should write some code to send the data to Google Cloud Storage.

In case you are developing on the Google App Engine, you can use the library that is available for the following supported languages:

If you are using one of the above languages and are not coding for the Google App Engine, you can access Cloud Storage from your environment. Interacting with a feature-rich product like Google Cloud Storage can become quite complicated depending on your use-case. For more details on the different options that exist, you can check Google Cloud Storage's documentation. If you are looking for a less engaged and more neutral way of using Storage in the Cloud, you can consider a solution like RudderStack.

After you have loaded any data into Google Cloud Storage, you have to create a Load Job for BigQuery to load the data into it. This job should point to the source data in Storage in the Cloud that has to be imported. You can do so by providing the source URIs that point to the appropriate objects.

The previous method used a POST request to the Google Cloud Storage API for storing the data there and then loading it into BigQuery. Another way to go is to do a direct HTTP POST request to BigQuery with the data you would like to query. This approach is similar to how we loaded the data to Google Cloud Storage through the JSON API, but it uses the appropriate end-points of BigQuery to load the data there directly.

The way to interact with it is quite similar; you can find more information in the BigQuery API Reference and on the page that describes how to load data into BigQuery using POST. You can interact with it using the HTTP client library of the language or framework of your choice; a few options are:

The best way to load data from Facebook Ads to BigQuery

In this post, we only just scraped the surface of what you can do with BigQuery and how to load data into it. Things can get even more complicated if you want to integrate data coming from different sources.

Instead of writing, hosting, and maintaining a flexible data infrastructure manually, you can simply use RudderStack to handle the data routing and pipeline management automatically for you.

With one click, RudderStack seamlessly integrates with your sources or services, creates analytics-ready data, and syncs your Facebook Ads to Google BigQuery right away. It is the easiest way to automate powerful data integrations!

Help your marketing and executive team take ownership of the advertisement data that lives inside Facebook Ads to transform your performance marketing and boost your ROI.

Don't want to go through the pain of direct integration? RudderStack's Facebook Ads integration makes it easy to send data from Facebook Ads to Google BigQuery.