How to Transfer Facebook Ads Data into Google BigQuery
Since launching their ad platform in 2007, Facebook has challenged Google Ads for increasingly more control over PPC advertising dollars. Digital advertisers must regularly decide between the two ad platform powerhouses where to best allocate marketing dollars. Increasingly, the answer is both Google and Facebook.
As with Google Ads campaigns, Facebook data is necessary for digital advertisers to create complex advertising strategies, launch innovative campaigns, monitor trends, and optimize campaign performance. But while Facebook’s online interface, Ads Manager, has come a long way since it was launched, digital advertisers still complain regularly about difficulties they face accessing and reporting on Facebook data and implementing changes on the platform. This issue escalates quickly for digital advertisers working at agencies who manage tens or hundreds of Facebook clients.
Although BigQuery is run by Google, it remains a powerful tool for modern PPC marketers to access and analyze their Facebook Ads data at scale. BigQuery allows other ad platforms’ data to be ingested and stored in data warehouses, although it doesn’t necessarily make it easy to do so. Getting Facebook Ads data into BigQuery takes additional engineering and maintenance to ensure data is correctly loaded, stored, and made accessible.
We’ve outlined three ways to transfer Facebook Ads data into Google BigQuery. While more complex ways exist to automate Facebook Ads data transfers to BigQuery, these require complex and costly technical engineering. We believe that every digital advertiser can use the steps below to spin up their own Facebook Ads data warehouse.
- 1. Manual Facebook Ads Data Upload
- 2. Data Pipelines and Connectors
- 3. Shape’s Advertising Data Infrastructure
Manual Facebook Ads Data Upload into BigQuery
While very manual, it is possible for digital advertisers to build their own Facebook Ads data warehouses without engineering resources or expansive software budgets. Google BigQuery allows you to manually import data from a CSV file directly. This means that any digital advertiser that can download a Facebook Ads report can upload it to their very own data warehouse.
The cost of building this solution manually is affordable. Uploading Facebook Ads data is free although costs accumulate if a digital advertiser suprasses Google’s data querying and storage thresholds. Manually updating Facebook Ads data on a regular basis (ex: daily) across multiple different report types can get cumbersome. Likewise, when manually uploading Facebook data, digital advertisers want to be sure that their reports contain the same data schema (metrics, headings, and data formats) to prevent incomplete or missing data from quickly destroying the usefulness of their data warehouse.
Directions:
1. Generate a Google Cloud Project (free trial available).
2. You’ll be taken to a “Basic Checklist.” (You’ve already completed steps 1 and 2 listed.)
3. Navigate to Google BigQuery and find your new project. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
4. Login to Facebook Ads Manager. Navigate to the data you wish to query in Google BigQuery. If you wish to have daily data, ensure you have segmented your reports by day.
5. Download the data by selecting “Reports.” Click “Export Table Data.”
6. Export your data as a .csv file and save it to your computer.
7. Navigate back to Google BigQuery. Ensure your project is selected at the top of the screen and then click your project ID in the left-hand navigation.
8. Click “+Create Dataset”
9. Name your dataset. This should be representative of the entire dataset you plan to include (ex: “Client Name Facebook Ads”).
10. Ensure an encryption method is set then click “Create dataset.”
11. Click the name of your new dataset in the left-hand navigation and click “+Create Table”.
12. Navigate to the Source section.
- a. Create table from: Upload
- b. Select file: Find your Facebook Ads report you saved using the browse button
- c. File format: CSV
13. Navigate to the Destination section.
- a. Select “Search for a project.”
- b. Find your project name from the dropdown. Then select your dataset name (created in step 9).
- c. Enter a name for your table. This should be representative of the table of data your are uploading (ex: daily_campaigns_data_segmented_by_device)
14. Navigate to the Schema section.
- a. Click the checkbox to allow BigQuery to auto-detect schema OR
- b. Click “Edit as text” to manually name schema, set type (string, boolean, date, etc.), and mode (nullable, required or repeated).
15. Navigate to the Partition and cluster settings section.
- a. Choose “No partitioning” or “Partition by ingestion time” based on your needs. Partitioning divides your table into smaller segments that allow smaller sections of data to be more quickly and affordably queried.
16. Navigate to Advanced Options:
- a. Set Field delimiter: Comma
- b. If your data includes a header row, enter 1 in the “Header Rows to skip” field.
17. Click “Create table.” Your data warehouse will now begin to populate with Facebook Ads data. Check your Job History for the status of your data load.
18. Navigate to Google BigQuery and click your Dataset ID.
19. You can now start writing SQL queries against your Facebook data in Google BigQuery, or export your data to Google Data Studio and other third-party tools for further analysis.
20. Repeat this process for all additional Facebook data sets you wish to upload.
21. Repeat this process on a regular basis to ensure fresh data is available when necessary.
Google offers fantastic guides on manually uploading data to Google BigQuery or using JSON, as well as extensive support documentation.
Using Data Pipelines/Connectors to Get Facebook Ads Data into BigQuery
Another alternative to populate a Google BigQuery data warehouse with Facebook Ads data is to use a third-party data pipeline or connector. There are a number of solutions that exist such as Supermetrics, Funnel, and Improvado. With each of these solutions, the advertiser is responsible for launching, managing, and paying for the data warehouse. The connectors/pipelines transfer Facebook Ads data to the data warehouse on a preset basis. Depending on the solution, pricing may fluctuate based on the number of Facebook Ads accounts and/or data sources linked, the frequency or size of data transfers, or the level of support needed.
Although we can’t cover spin up directions for each connector individually, we’ve selected SuperMetrics for BigQuery given that Supermetrics is used by many PPC advertisers.
Directions:
1. Sign up for a free trial of Supermetrics for BigQuery (note: there is a free trial but access may not be granted immediately).
2. Add your Facebook Ads data sources to Supermetrics.
3. Generate a Google Cloud Project (free trial available).
4. You’ll be taken to a “Basic Checklist.” You’ve already completed steps 1 and 2. Click step 3: find “Product + API.” This takes you to the Google Cloud Platform Marketplace.
5. Search/select “Facebook Ads by Supermetrics.” Click Enroll.
6. Select the name of the project you generated in steps 3 & 4. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
7. Navigate to BigQuery and click your project name on the left-hand navigation.
8. Click “Create Dataset.” Fill out a dataset ID and location as needed. Save.
9. Using the left-hand navigation again, click Transfers. Select +Create Transfer.
10. You’ll be asked to configure the transfer form.
11. Set the Source to “Facebook Ads by Supermetrics”
12. Set Transfer Config Name = Name this something that indicates the job being run. (Ex: Shape Facebook Ads Transfer)
13. Set Schedule Options.
- a. Select what time you’d like data refreshed (Start Now or Start at set time)
- b. Select if you want data refreshed on a schedule or on-demand
- c. Select the start date and run time when data will be refreshed
14. Enter a Dataset ID = Choose (or create) a name for your dataset displayed in BigQuery.
- a. If creating a new Dataset ID, follow the steps on the “Create Dataset” form to add an ID name, data location, table expiration time, and encryption key. Give a clear indication of what’s included in the dataset (ex: Client Name Facebook Ads).
15. Under the Third-Party Connection section, click “Connect Source”. Follow steps to authorize Supermetrics as a third-party connector.
16. Using the Supermetrics pop up modal, sign in to Supermetrics for BigQuery. Authorize the Facebook Ads login as needed.
17. Select the ad accounts you wish to transfer data from and submit.
18. Your data warehouse will now begin to populate with data. Once the data has successfully been run, you can navigate back to BigQuery to access your data.
19. IMPORTANT: To backfill historical data, you’ll need to follow additional steps outlined in Supermetrics support documentation.
Using Shape’s Advertising Data Infrastructure to get Facebook Ads Data into BigQuery
We designed the Shape ADI to be the fastest and easiest way for digital advertisers to generate and populate a new BigQuery data warehouse with Facebook Ads data. Shape manages the Google Cloud Platform and dataset spinups, the data transfer, and even backfills data for 30 days (more can be requested). Everyone who spins up an ADI gets access to five campaign-level data views for free, and Shape handles the cost of data storage and queries. If advertisers want to enable premium views, pricing scales based on the number of views enabled (instead of on the number of data sources or ad accounts needed which can get expensive fast for agencies).
Directions:
2. Add your Facebook Ads data sources during the onboarding process.
- a. Optional: Create Clients and Budgets in Shape. You can combine campaigns into unique groupings to track spend and metrics in whichever combinations you choose.
3. On the main dashboard, click ADI. On the ADI page, click “Enable Warehouse.”
4. Shape will automatically generate a BigQuery data warehouse for you and enable five always-free campaign-level data views.
5. Once your data warehouse has been generated and 30-days worth of data backfilled, you’ll be taken to the Data Warehouse that lists all available data views.
6. Select a View Name to open that view directly in BigQuery OR click the “Open in BigQuery” to access your entire BigQuery data warehouse.
7. Your data warehouse is already populated and backfilled with data. You can query your data in BigQuery or export it to tools such as Google Data Studio and other third-party tools.
8. Optional: If you want to enable premium views, click the checkbox next to a premium view. Click the pencil that appears at the top of the table.
- a. On the “Edit View Status” screen, change the Status to “Enabled.”
For additional detail on Shape’s ADI including data warehouse support, check out Shape’s Help Center.
Getting Started with Shape’s Managed PPC Data Warehouse
We believe that every digital advertiser should have a powerful data PPC warehouse at her or his fingertips. To help make it easier to access and analyze your data, we have made campaign-level warehousing and API access free.
Looking for more data views or interested to learn more about what you can build with a data warehouse? Don’t hesitate to schedule a free consultation with our engineering team today before diving in.