A Comprehensive Guide on How to Upload Facebook Ads Data 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.

Another reason for the popularity of Facebook and Instagram among advertisers is the low price 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 December 2023 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

This simple process 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 are using Google Analytics 4 for marketing reporting, you can upload Facebook ads cost data and get ROAS reporting for non-Google ad campaigns in the interface you are familiar with.

Additionally, Facebook Ads data can be seamlessly integrated with reporting tools or data visualization 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 coverage, views, clicks, ad costs, and so on. If you install the Facebook pixel on your website and set up event tracking, you can find out the behavior of users who go to your website by clicking on an ad. You can track all kinds of conversions on any device your ad brings to your website and see how much a desired action costs you. 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, then 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 marketing sources and how they affect the business as a whole until you consolidate your data.

In large companies, marketing is not limited to Facebook Ads, and sales can be done not only online, but also offline or by phone. 

Therefore, it’s necessary to combine data from Facebook Ads 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 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 for a comprehensive view of your advertising performance.

This helps you set up advanced analytics and assess the impact of all marketing efforts, both online and offline, on business performance.

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 data collected in BigQuery to build any reports you need without any restrictions.
   

If you’re looking for a stable connector for transferring data to Google BigQuery try it for free right now. 

Amazon Redshift, Snowflake, Google BigQuery, and Microsoft Azure are the most popular cloud platforms. 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 and recommend it to our customers 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 send data from Facebook ads to BigQuery:

You can download cost data from Facebook’s advertising profile into separate files and manually upload them through the BigQuery interface. Building a data warehouse for Facebook Ads without significant 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 can download Facebook Ads reports to upload them into their data warehouse.

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

Furthermore, 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 leading to incomplete or incorrect data.

The disadvantages of this method 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 with BigQuery's capabilities to facilitate smooth data integration. 

Proper preparation is key to leveraging in which you connect Facebook Ads 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 into BigQuery, you can use:

• Google Cloud Storage

• Direct POST requests to BigQuery

• Google Cloud Datastore

For example, upload data directly through the console or via a JSON API using HTTP POST requests. After importing data into Google Cloud Storage, a LoadJob is created to import data from the cloud into BigQuery. This method involves specifying 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.

OWOX BI has direct data streams from Facebook Ads, Bing Ads, Twitter 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 together, transform into GA4-accessible format and automatically upload ad 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 data you need 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 every day.

2. You can download statistics from multiple ad accounts into a single BigQuery table by specifying the same dataset when creating a stream.

3. Data from the advertising source is blended into a single-party BlendedAdSpend table in Google BigQuery. This greatly simplifies the compilation of queries and analysis of downloaded data.

4. You can use OpenExchangeRates.org pipeline and convert all of the 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 from Facebook 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, BigQuery won’t let you upload data.

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:

• 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.

• 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 values 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 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 advertising service changes retrospectively, OWOX BI will update all data uploaded to BigQuery within the established update window.

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

If you have many touchpoints with customers, have offline stores, and want to see the entire path to purchase, however, then you should think about setting up advanced analytics and creating an automatically updated dashboard with all the metrics 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.