Optimization Up

If you run ads across different channels like Google, Facebook, LinkedIn ..etc, you already know reporting consolidation is now a must. Having all of your ads data in one storage is the only way to get a holistic view of your marketing investment and understand which channel is more profitable to the business.

BigQuery is a cloud data warehouse that can allow you to bring all of your data into one place for large-scale analysis. Google BigQuery has native integration for importing data from Google’s own properties like Google Analytics and Google Ads. However, working with other data sources like Facebook Ads can be difficult and requires a different approach.

There are 3 ways to upload Data from Facebook Ads to BigQuery:

1- Using 3rd Party Connector

There are a few connectors out there like Supermetrics and Owox that can allow you to integrate BigQuery to Facebook Ads and fetch all ads data automatically. However, none of them are free, you will have to buy a subscription in order to use the service.

2- Upload Data Manually

You can download a report from Facebook Ads Manager and upload the CSV file to BigQuery. Nevertheless, this is not an efficient way and requires a lot of manual work.

3- Write a Script

You can write a script that connects to BigQuery API and upload the Facebook Ads data to the relevant dataset, this requires heavy dev resources. However, we have done the work for you and coded a Google App Script that can allow you to import Facebook Ads data into BigQuery automatically and for FREE.

How to use Google App Scripts to link Facebook Ads to BigQuery

1- Configure your BigQuery Project

1- Navigate to your BigQuery project

2- Create a New Dataset

3- Enter your Dataset ID. Keep this id handy, we’ll use it later

4- You should see the dataset on the left once created successfully

2- Prepare your Google Sheet

1- Make a copy of this Google Sheet

2- Add your Facebook Ads data to ‘FB data’ tab, you can use any sheet add-on like Supermetrics. Or check out this free solution on how to import Facebook Ads Data into Google Sheets

3- Configure the script

Project ID: This should be replaced with your Google Cloud Project ID. Here’s where you can get your cloud project id:

Dataset ID: should be replaced with the dataset ID we created above

Tabel ID: add a name to the table that will contain your FB data

6- Run your script, by clicking the button ‘Click to Upload Data to BigQuery’

7- Check if data has been uploaded successfully to BigQuery

3- Schedule the Script

1- To schedule the script, click Tools > Script Editor

2- Inside the script editor click ‘Triggers’

3- Click ‘Add Trigger’ and then select from the ‘Choose which function to run’ dropdown menu to uploadDatatoBigquery. The rest of the fields are set per your needs, like the type of the time trigger and the actual time.

Please note the script is appending the data to your existing data in the table. Therefore, it’s recommended to schedule the script at Midnight to run once per day when all your data is complete.