How to upload data from Facebook to Google BigQuery

Facebook is the most popular social network globally, with more than 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 services on this platform. What’s more, Facebook owns the three other 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 accurately target audiences.

Table of contents

What data Facebook collects

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. And 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 clicks, registrations, orders, purchases, etc.

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 Facebook, 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, and sales can be online and offline. Therefore, it’s necessary to combine data from Facebook Ads Manager with data from your website, 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 the financial results.

Why collect data from Facebook in Google BigQuery?

By uploading cost data from advertising services into Google BigQuery, you can associate costs with user activities on the website, calls to the call center, emails, and purchased orders from the CRM system. This helps you set up advanced analytics and assess the impact of all marketing efforts, both online and offline, on business performance.

Data from marketing services uploaded to Google BigQuery can be used to calculate your attribution model, and then BigQuery can pass the results to systems for automatic bid management. Also, in Google BigQuery you can create user audiences based on combined data and the results of attribution calculations and then automatically send those audiences to advertising services. Finally, you can use data collected in BigQuery to build any reports you need without restrictions.

Why Google BigQuery and not any other cloud storage?

The most popular cloud platforms are Amazon Redshift, Google BigQuery, and Microsoft Azure. 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 data sources for marketers: Ads, Analytics, Search Console, 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 the services you use.
  • There are ready-made services and solutions for uploading data to Google BigQuery without the help of developers.

How to import data from Facebook to Google BigQuery

Google BigQuery doesn’t have a built-in tool for importing data from Facebook Ads Manager. You can solve this problem in several ways: by uploading data manually, writing your own scripts, or using specialized services such as OWOX BI. Let’s take a closer look at each of these options.

1. Manually import data or use your own solution

You can upload cost data from Facebook’s advertising profile into separate files and manually upload them through the BigQuery interface. The disadvantages of this method are obvious: a lot of unnecessary work and no automation.

You can also write scripts that will upload the data you need from a marketing service. However, you’ll need to constantly monitor and support these scripts. You’ll also have to spend developer resources to combine data from different accounts and on different dates, check data quality, and quickly respond to possible changes in advertising service APIs. In addition, you’ll need to retrospectively update data if it’s changed in Facebook Ads, convert costs from all advertising sources into a single currency, etc. If you don’t do these things, poor quality data can lead to suboptimal solutions that will cost your business a lot.

2. Collect cost data into Google BigQuery with Google Analytics

With OWOX BI, you can set up automatic cost import from different advertising services into Google Analytics and upload cost data to cloud storage in one data stream. If you use this method, consider the limitations of Google Analytics:

  • A maximum of 90 MB can be loaded into a single dataset per day.
  • You can download a maximum of 50 files per day per resource.
  • Google Analytics may take up to 24 hours to process uploaded data and make it available in reports.
  • When uploading a large amount of data, there may be problems with the Google Analytics API.
  • You can’t upload more parameters than the cost dataset schema from Google Analytics can contain.

3. Import costs from advertising services directly into Google BigQuery

OWOX BI has direct data streams from Facebook, Instagram, and LinkedIn, with which you can upload raw data on all your campaigns into BigQuery. These data schemes contain 80 to 200 parameters, which ensures a high granularity of data for your reports.

What are the benefits of Facebook’s direct integration with Google BigQuery?

OWOX BI offers a complete solution out of the box — you don’t need to do anything manually. The service collects all the data you need in a convenient format and monitors its quality and relevance.

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

Since cost data importing occurs without the participation of Google Analytics:

  1. You get the data faster: Costs are uploaded directly to BigQuery, and you don’t have to wait an extra 24 hours for Google Analytics to process them.
  2. Data is downloaded without Google Analytics limits of 90 MB and 50 downloads per day.
  3. You can download statistics from multiple ad accounts into a single BigQuery table by simply specifying the same dataset when creating a stream.
  4. Data from the advertising source is compiled into a single-party table in Google BigQuery. This greatly simplifies the compilation of queries and analysis of downloaded data.
  5. Cost data is uploaded to BigQuery in two currencies. One field stores costs in the original currency from the advertising service. You can specify the other currency to convert cost data to when you create the flow. Converted costs are helpful for reports where data from different sources must be converted to a single currency.

How to set up cost data import from Facebook Ads to Google BigQuery using OWOX BI

  1. Make sure your advertising links have UTM tags.
  2. On the OWOX BI home page, click Create Pipeline:
Create Pipeline
  1. As the source, select Facebook Ads:
  1. 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.

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

Under no circumstances does OWOX BI 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.

  1. Select a connected account or grant access to a Google BigQuery account where you want to store the exported data:
  1. 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.

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

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

Key takeaways

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.