A Comprehensive Guide on How to Upload Facebook Ads to BigQuery

Facebook is the most popular social network globally, with over 2.6 billion monthly active users. Not surprisingly, it has become a mandatory promotional channel for many businesses. More than 7 million advertisers promote their products or services on this platform. Moreover, Facebook owns the three largest social media platforms: Instagram, WhatsApp, and Facebook Messenger.

Connecting a Facebook Ads Account is crucial for extracting and analyzing data in BigQuery. This allows businesses to query data from the Graph API and send it to BigQuery for comprehensive analysis.

Another reason Facebook and Instagram are popular among advertisers is the low cost of reaching an audience and the ability to target audiences accurately.

Note: This post was originally published in August 2019 and was completely updated in July 2024 for accuracy and comprehensiveness.

Extracting data from Facebook Ads allows for more in-depth analysis, offering the chance to integrate this information into simple spreadsheet tools like Excel or Google Sheets.

Transferring Facebook data, whether manually or using OWOX BI, enables you to identify trends, assess the effectiveness of various strategies, and determine the most successful approaches for your business to get more customers.

If you are using Google Analytics 4 for marketing reporting, you can upload Facebook ad cost data and get ROAS reporting for non-Google ad campaigns in the familiar interface.

Facebook Ads data can be seamlessly integrated with reporting tools or other data visualization tools or platforms, including Tableau, Power BI, or Looker Studio. These tools assist in creating detailed, customizable dashboards.

Furthermore, exporting Facebook Ads data is beneficial for merging it with data from the other ad platforms, mapping with on-site analytics, conversions, and specific user information so you can make clear decisions about budget allocation and share reports with team members and stakeholders. Explore some of the top Looker Studio templates we’ve gathered for analyzing Facebook Ads data.

Facebook offers businesses and marketers tools for working with advertising campaigns: Facebook Ads Manager, Facebook Pixel, and Facebook Analytics. By default, Ads Manager provides information about Facebook ads insights such as coverage, views, clicks, ad costs, etc.

If you install the Facebook pixel on your website and set up event tracking, you can find out the behavior of users who go from the Google app engine to your website by clicking on an ad. You can track all conversions on any device your ad brings to your website and see how much a desired action costs. Facebook reports show you all leads, registrations, orders, purchases, revenue, etc.

However, data stored in Facebook's advertising profile can tell you about the effectiveness of only one source. But what if a user comes to you from organic traffic, returns to your website, clicks on a link on a Facebook ad, and makes a purchase after getting an email?

How can you evaluate the effectiveness of the Facebook channel in this case? You won't know how it interacts with other email marketing apps and sources and how they affect the business until you consolidate your data. In large companies, marketing is not limited to Facebook ads, and sales can be made online, offline, or by phone.

Therefore, it's necessary to combine data from your Facebook Ads account with the data from your website analytics system (such as Google Analytics 4 or OWOX BI Streaming), other advertising services, and your CRM so you can see the whole picture: how much money is spent on advertising, which advertising channels are more effective, and what the financial results are.

By uploading cost data from Facebook Ads to BigQuery, you can associate costs with post data, user activities on the website, calls to the call center, emails, and purchase orders from the CRM system. You can also blend advertising cost data to comprehensively view your advertising performance.

To get started, you must set up a Google Cloud Project to manage datasets and tables in BigQuery.

1. Blend data from multiple sources for a unified view of ad performance;
2. Attribute ad costs to sessions from the analytics system for a better understanding of the ads' efficiency;
3. Use data from advertising services uploaded to Google BigQuery to build your attribution models for advanced channel-performance analysis;
4. You can use the data in BigQuery to pass the calculations back to Facebook Ads for automatic bid management.
5. Also, in Google BigQuery you can create custom audiences based on combined data and the results of attribution calculations and then automatically send those audiences to advertising services.
6. Finally, you can use Facebook data collected in BigQuery to build any reports you need without any restrictions.
7. Export Facebook ads to Google BigQuery and Looker Studio to better visualize reports created from Facebook data.

Amazon Redshift, Snowflake, Google BigQuery, and Microsoft Azure are the most popular cloud platforms for data warehouses. They have common advantages over a conventional data warehouse:

• You don't need to maintain servers and connect new ones when the load grows. Cloud storage scales automatically.
• Cloud platforms are faster than traditional storage and automatically redistribute the load.
• You don't need to install a server on your computer to get into cloud storage. Just open your browser and log in to the cloud.

We've chosen Google BigQuery as a data warehouse and recommend it for the following reasons:

• Google is the leader in the number of native data sources for marketers: Ads, Analytics, Search Console, and YouTube. All these services seamlessly integrate with BigQuery.
• It processes large amounts of data fast.
• It's easy to get started with BigQuery without the help of database administrators. Just create a Google Cloud account.
• You only pay for what you process.
• There are ready-made templates available to prepare data for reporting in Google BigQuery without coding or developers.

Google BigQuery doesn’t have a built-in tool for importing data from the Facebook Ads Manager. You can solve this problem in several ways:

• by uploading data manually;
• writing your own custom scripts;
• Automatically upload with an ETL tool like OWOX BI.

Let’s take a closer look at each of these options for how to integrate and send Facebook Ads data to BigQuery and transfer Facebook Ads data to BigQuery:

You can download cost data from the Facebook ads data, transfer its advertising profile into separate files, and manually upload them through the BigQuery interface. Building a data warehouse for Facebook Ads reports without huge engineering resources or a large software budget is feasible.

Digital advertisers can utilize Google BigQuery, which allows the direct import of data from CSV files. This feature enables advertisers who download Facebook Ads to BigQuery as their data warehouse.

This manual approach is cost-effective. While uploading Facebook Ads to BigQuery is free, costs may arise if the Google-set thresholds for data querying and storage are exceeded. Regularly updating Facebook Ads data, especially daily and across various report types, can become challenging.

More importantly, digital advertisers must ensure their uploaded reports maintain a consistent data schema, including metrics, headings, and formats. Inconsistencies in these areas could quickly diminish the value of the data warehouse by a data validation system, leading to incomplete or incorrect data.

The disadvantages of this method of loading data are obvious: a lot of unnecessary work and no automation.

This involves automating the migration of data from Facebook Ads to Google BigQuery, which is done in three primary steps:

This step involves gathering relevant Facebook Ads data, which can be utilized for various business purposes. There are two primary methods to extract this data:

• Using APIs: Facebook's APIs can be accessed through its SDKs, supporting languages like Python, PHP, JavaScript, R, and Ruby. The Facebook Marketing API, a RESTful API, allows users to make specific requests to endpoints, including the Graph API for ads and statistics data and Facebook Insights for detailed ad statistics.
• Through Real-time Streams: This method involves setting up a real-time data infrastructure that streams live data feeds to your database. This keeps your data up-to-date with the latest information from Facebook Ads, providing detailed insights for analytics and reporting.

It's important to convert your Facebook page ads data for BigQuery accessible format. If your API provides data in XML, you'll need to transform it into either JSON or CSV format, as these are the types BigQuery supports.

Additionally, make sure to use data types that BigQuery recognizes, including:

• FLOAT
• RECORD
• TIMESTAMP
• INTEGER
• STRING

This preparation phase is not just about format conversion; it's also about aligning your data from Facebook ads to BigQuery to match its capabilities to facilitate smooth data integration.

Proper preparation is key to leveraging in which you connect integrating Facebook Ads data to BigQuery, which can significantly enhance the insights gained from your Facebook Ads data. This connection facilitates a deeper analysis and understanding of your advertising strategies, thereby maximizing the potential benefits of your data.

To load data from Facebook Ads to BigQuery, you can use:

• Google Cloud Storage
• Direct POST requests to BigQuery
• Google Cloud Datastore

For example, upload data directly from Facebook Ads to BigQuery through the console or via a JSON API using HTTP POST requests. After importing raw data back into Google Cloud Storage, a Load Job is created to import data from the cloud into BigQuery. This method involves specifying data source URLs in POST requests.

Creating custom scripts for this data transfer has limitations:

• Code Maintenance: Self-built code requires constant monitoring and updates, especially when Facebook modifies its API.
• Data Consistency: A system to ensure data integrity and prevent leakage is necessary. Poor data quality can lead to suboptimal solutions that will cost your business a lot.
• Real-time Data: For real-time data analysis, additional coding is required.
• Data Transformation: Transforming data, such as adjusting time zones or currencies, demands extra effort.

Other options for integrating a Facebook ads database into a big query database are using third-party data pipelines or connectors. The most widely known solution is OWOX BI. Connectors and pipes transfer Facebook advertising data to an internal database.

OWOX BI has direct data streams from Facebook Ads, Microsoft Ads (formerly known as Bing Ads), Twitter Ads, Google Ads, and LinkedIn Ads, as well as 150 other data sources, with which you can upload raw data on all your campaigns into BigQuery.

Data schemes contain 80 to 200 parameters, which ensures a high granularity of data for your reports.

You can also set up automatic cost import with OWOX BI from different advertising services into Google BigQuery, blend, transform into GA4-accessible format, and automatically upload Google Ads cost data into Google Analytics 4.

OWOX BI offers a complete solution — you don't need to do anything manually. The service conveniently collects all the needed data and monitors its quality and relevance.

If historical data changes in Facebook Ads, OWOX BI automatically updates the data uploaded to BigQuery. In addition, if necessary, you can upload your historical data for the last two months. This will help you evaluate campaign dynamics.

1. You get the data faster: Costs are uploaded directly to BigQuery daily.
2. When creating a stream, you can download statistics from multiple ad accounts into a single BigQuery table by specifying the same dataset.
3. Data from the advertising source is blended into a single-party BlendedAdSpend table in Google BigQuery, greatly simplifying the compilation of queries and analysis of downloaded data.
4. You can use OpenExchangeRates.org pipeline to convert all costs into a single currency. Converted costs are helpful for reports where data from different sources must be converted to one currency.

1. Make sure your advertising links have UTM tags.

2. On the OWOX BI Workspace page, click + NEW Pipeline:

3. As the source, select Facebook Ads:

4. As the destination, select Google BigQuery:

If you don't have a project in Google BigQuery, learn how to start working with cloud storage and create a dataset.

5. Select a Facebook account (or grant access to a new Facebook account) from which you want to export ad cost data:

OWOX BI would never change the settings of your account or advertising campaigns!

For most advertising platforms, OWOX BI requests access only to read data. This level of access is enough for us to get statistics and information about link markup in your ads. But not all advertising services have such a level of access or work as we expect. For example, to import raw data and connect Facebook ads into Google BigQuery using OWOX BI, you need ads_management permission.

6. Select a connected account or grant access to a Google BigQuery account where you want to store the exported data:

7. Select a Google BigQuery project and a dataset you want to upload your data to (or create a new one):

Note! To set up data collection, your Google account must be granted both BigQuery Data Editor and BigQuery User roles for the destination project. Otherwise, your BigQuery account won't let you upload data to new project.

To check/grant these permissions, go to the Identity and Access Management page in your Google Cloud Platform project. Read more in the Google documentation.

8. Specify the settings for your pipeline:

1. Select the date from which you want to upload cost data. You can set up either a future or past date. If you're choosing a past date, learn about the limitations on historical data import.
2. Choose the source/medium you want to apply to the imported data by default. The default values you specify for the UTM source/channel will be written to the cost data tables only if OWOX BI doesn't receive the actual UTM parameter values. For more information on why you should specify default values for UTM source/channel in the pipeline settings, refer to the Help.

Important! At any time, you can change the source/medium settings on the pipeline page to any value except google/organic. The specified values will be applied to new data imported after making changes and historical data within the update window.

9. Click Create Pipeline.

Done!

Data for the previous day will appear in the table in the specified dataset daily at 00:00 (UTC). Depending on the amount of advertisement data and the features of the advertising service API, importing data can take up to 24 hours. See this article for the structure of the table.

If the information in an ad service changes retrospectively, OWOX BI will update all data uploaded to BigQuery within the established update window.

Recently, Google introduced a native connector - Facebook Ads Data Transfer, which allows you to retrieve advertising data directly from your Facebook account into your Google BigQuery project.

In this article, we will compare OWOX BI Fully Managed Pipeline Meta Ads (Facebook+Instagram) to Google BigQuery.

Before diving into the comparison, we would like to draw your attention to a crucial point: the native connector from Google provides data from additional Facebook data sets exclusively for the previous day

Consequently, this affects the setup as each day's import will overwrite the previous day's metrics, leaving only the data for the most recent day. Additionally, this makes it impossible to update metrics and extract retrospective data.

Below is a comprehensive list comparing OWOX BI solution versus Google's:

As a result, you can see that currently, the tools available in Facebook Ads to transfer data will be insufficient to build a comprehensive report such as the CMO dashboard, which would display up-to-date data for the entire period.

Therefore, we recommend familiarizing yourself with the data scheme of OWOX BI pipeline Meta Ads (Facebook & Instagram) → Google BigQuery, which has already considered all the aforementioned points.

You can analyze the basics of advertising performance in the Facebook advertising profile and web analytics systems. For example, you can analyze CTR, CPC, CPA, CR, sessions, viewing depth, bounce rate, RPC, and ROAS. This is enough to analyze data for small businesses with a small number of advertising channels.

If you have many touchpoints with customers, have offline stores, and want to see the entire path to purchase, however, you should think about setting up advanced analytics and creating an automatically updated dashboard with all the metrics and pull data that interest you.

This will allow you to always have up-to-date information at hand, conduct a comprehensive assessment of advertising effectiveness, and make important decisions faster.