Transferring Facebook Ads Directly to Looker Studio with BigQuery

Earlier this year, Google Cloud announced that BigQuery data warehouse supports automatic data transfer from Facebook Ads.

In its preview stage, this feature offers an alternative way to enhance analysis and insights by allowing the scheduling of data loading tasks. With this integration, the need for third-party tools or manual code execution is eliminated.

This integration includes a fixed set of tables that do not support custom reports. BigQuery automatically populates AdAccounts, AdInsights, and AdInsightsActions tables with a 24-hour transfer schedule.

Note: The minimum interval between repeated transfers of Facebook Ads is 15 minutes. The default interval for repeated transfers is 24 hours.

Moreover, marketers can leverage extensive analytical capabilities beyond the basic metrics provided by Facebook Ads Manager with BigQuery. They can thoroughly analyze the data, identify trends, and delve deeper into understanding campaign performance by creating custom dashboards.

The seamless data transfer process not only saves marketers time and effort but also ensures that the data in BigQuery remains continuously updated with automatic and scheduled updates.

In this blog post, we will explore how to set up this integration.

Facebook Ads Information

Before creating the Facebook Ads – BigQuery transfer, we need certain Facebook Ads information: clientID, clientSecret, and refreshToken.

You can find these details in the relevant App settings within Meta Developers. If you already have this information, you can skip this section. In our article, we will proceed by creating an app from scratch.

We are creating a Facebook developer app with the Business application type.

You can initiate the relevant setups at https://developers.facebook.com/apps.

Choose Create App -> Other -> Business options.

Creating our app name and email address, we proceed.

In the Facebook App panel, under App Settings > Basic settings, you can find the app ID and app secret values.

For now, we’ve completed the steps on the Facebook Developers platform; we will use the App ID and App secret information during setup. We’ll revisit the Developers panel in the future.

Creating Facebook Ads Data Transfer

Since we’re starting from scratch, we’ll create a new project. If you already have a project, you can proceed from there.

We determine our project name: “perfist – ads to bigquery”.

After creating our project, we move to BigQuery Studio. https://console.cloud.google.com/bigquery

As you can see below, if the BigQuery Data Transfer API is not enabled in your project, you need to activate the API through the link provided in the warning.

Click on Data transfers -> Create A Transfer to create a new transfer.

In the Source Type section, select Facebook Ads.

For the Refresh Token in the Data source details section, we need to go back to the Facebook Developers page. To obtain the Refresh Token value, we need to grant permission via https://console.cloud.google.com/bigquery/oauth URL through our Facebook Developers App.

Add the “Facebook Login for Business” product to our application within Developers -> Dashboard.

Paste the above URL into the “Valid OAuth Redirect URIs” section and save the settings.

After entering the Client ID and Client Secret values (Client ID = app ID, Client Secret = app secret), click the AUTHORIZE button. By logging in with your Facebook account, you automatically authorize and complete the Refresh Token value as shown below.

In the next section, we create the Dataset where our data will reside.

We specify a name for the Transfer config name and set the transfer schedule. We want the transfer to occur daily at 05:00.

If you have a Service Account, you can select it or create a new one. I recommend granting BigQuery Data Transfer Service Agent and Owner roles in the Service Account you create. Then, save.

If you do not have a payment method set up in your Cloud project, you will receive an error. Although the Facebook Ads transfer process is stated to be free, you need to have a payment method set up in your Cloud projects. You need to add a payment method and authorize it for your project.

Looker Studio

After completing the setups, we move to Looker Studio and create a new report. In the report we created, we click on the option to add new data. We select the relevant Project, Dataset, and Table via the BigQuery option.

Now, we can use our Facebook Ads data within our report.

I hope this article has been helpful; you can contact us for any questions or comments.