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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

OWOX BI Pipeline helps automate advertising data import from Facebook to Google BigQuery. With this pipeline, you can set up an easy-to-use Google Looker Studio dashboard for Facebook campaigns that helps track the basic KPIs of your advertising.

Take a look at the demo dashboard we’ve created for you. The dashboard is divided into four sections:

• Impressions
• Clicks
• Actions
• Top Campaigns

Below, we’ll take a closer look at each section: the insights it provides, and how to use these insights to optimize your campaigns.

This section helps monitor advertising costs in comparison with the number of impressions. If, for example, the number of impressions decreases, but the CPM increases, you can optimize your ads by changing such parameters as the image, the content or the advertising audience of your Facebook campaign. Also, you can compare the performance of advertising campaigns for different periods. This will help you understand which campaigns reach more viewers or which campaigns cost less.

In the example below, you can see days when the campaign reached more people at a lower cost. These dates are June 21-22nd and June 25-27th. Consider whether these days are the days when the majority of your target audience is online. If so, you can schedule your campaign to run on particular days and hours. Also, take a closer look at the campaigns you run on these days, especially the audience, creatives, and content of the ads. These components may be useful for launching more efficient advertising campaigns in the future.

The growth of impressions (red line) for June 23rd might be a result of launching an advertising campaign with a wider audience. The cost of the campaign (blue line) increased in direct proportion, because the CPA remained high. Narrowing down the audience will result in fewer impressions and lower CPA, so that you pay less for the same number of actions.

This section shows the statistics about clicks on your advertising. Here you can monitor such parameters as clicks, CTR and CPC. The chart below shows that clicks and CTRs significantly decreased. In this case, we can change the image, content, CTA and the audience of the advertisement.

You can also see the dates when the campaign received the most clicks. Try using this info when launching your next campaign. In our example, you can see that the majority of clicks was received on Friday, June 23rd and on Sunday, June 25th. Perhaps you should increase the advertising activity on these days of the week. To sum up, this chart helps you react to the changes of indicators and optimize your campaigns right on time.

This section shows how often and how exactly users interact with your ads. It’s also important to know how much you pay for a particular interaction with your advertising. You can filter this chart by actions, such as likes, shares, URL clicks, subscriptions, comments, etc.

You can see the statistics for a single action or for all of them at once. If video views are the key factor for you, use the "video views" as a filter. This will help you see such parameters as the number of views, the days with the most and the fewest views, and the cost per view. Red arrows indicate the decreasing KPIs. Pay closer attention to these KPIs to improve your advertising.

In the example below, the Engagement Rate factor is decreasing. If this factor is important for you, then you can try replacing the standard Facebook wordings with your own CTAs. Also, you can offer users to take action for a reward. For example, followers who commented on the post would get an e-book, setup instructions or a discount.

Use the Top Campaigns section to compare the basiс performance indicators for your advertising investments. This section helps get a view of how you can reallocate the budget in order to get more revenue. It’s worth investing in more effective campaigns: those with low CPA or CPC and with high Engagement Rate.

The chart cells with expensive campaign parameters are highlighted in brighter red, and the cells with cheaper parameters are less bright. This helps you see the full picture of advertising costs and understand how to optimize budget investments. The screenshot below shows that the “02 Lookalike — EU’17 Soft...“ campaign has the cheapest click and the cheapest CPA. This means that this campaign is more investment-attractive than the “Jupyter NY 2017″ campaign with a $1.81 more expensive click.

The dashboard described above is based on the Facebook data collected in Google BigQuery using the OWOX BI Pipeline. You can create a similar dashboard after setting up the Facebook➟Google BigQuery pipeline. After you get the first data in your dataset, you can start setting up the dashboard. Leave your email here and we’ll send you a detailed guide to setting up your own dashboard.

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.