Bringing Ads Data (Google, Facebook, ...) to BigQuery

Owned by Sylvain Paillard
Last updated: Jul 01, 2022
4 min read

First, why is it important to bring these data to BigQuery?

Let’s ask a simple example question: how can you evaluate your Customer Lifetime Value?

In order to do that, you can start by building reports based on your transaction reporting your Customer Lifetime Revenue and they are great resources to do that (https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value), which Boxalino already include in its standardized report for the Customer Lifetime Value.

But in the word “Value”, there is something different than in the word “Revenue”, because what you might really want, is to know what was the net profit you generated from the first ad you paid to get the first visit of the customer to the very last marketing activity which affected this customer.

Factoring all costs is a difficult task, but you can start with a few concrete steps:

  1. Calculate not only the revenue but the gross profit generated per sold product and factor in the delivery costs that you are bearing.

  2. Integrate all your online advertisement costs and make them fully included in the calculation of your CLV.

This document addresses how you can do point 2, by bringing additional data (like Google and Facebook ads) to Google BigQuery, so they can be integrated in your reports.

General observation

Google doesn’t provide an option to have the raw data with great granularity, so a single visit cannot be connected to a single record from the ads. Therefore, it will only be possible to do an approximation of costs, like for example, the customer came from than ad that day and we had an average CPC of 1.- that day, so, therefore, we assume it was 1.- for him.

Option 1: BigQuery Data Transfer Service

As very well explained in this online article, there are quite direct ways to transfer your raw data from Google Ads to Google BigQuery. We recommend the approach with Data Transfer, but you can also investigate the other method using Google Ads Script.

https://towardsdatascience.com/how-to-upload-raw-data-from-google-ads-to-google-bigquery-4bf0f2565f18

MAKE SURE THAT YOUR BIGQUERY REGION IS “Multi-Region EU”
and not another one like “europe-west-6”


Pros:

 Cons:

Option 2: Use a 3rd party connecting platform

Here is a list of providers that can bring your Online advertisement data to BigQuery.

#1 - Stitch

Stitch is a cloud-first, open-source platform for rapidly moving data.

Pros:

  • Relatively affordable (https://www.stitchdata.com/pricing/), often you can even start with their free version, (can go over 1K CHF / month but for a hundred million records or more)

Cons

 #2 - XPlenty

The leading data integration platform for all. Create simple, visualized data pipelines to your data warehouse or data lake. 

Pros:

  • Quite complete features: https://www.stitchdata.com/vs/xplenty/

  • The only option listed here supporting Twilio

  • Support many connectors: Twilio, Google Ads, Mailchimp, Youtube, Facebook, MongoDB

 Cons:

  • We didn’t find the table documentations of what they generate in BigQuery

  • More on the expensive side (didn’t find clear pricing, but found indication of starting price of 1K monthly, billed annually: https://sourceforge.net/software/product/Xplenty/)

  • They list Google Ads, but not Google AdSense / Ad Manager, only Google Ads, and Google Campaign Manager: https://www.xplenty.com/integrations/

  • They don’t list connector for Mandril

#3 - Blendo

Blendo is the leading ETL and ELT data integration tool to dramatically simplify how you connect data sources to databases.

Pros:

Cons

  • They list Google Ads, but not Google AdSense / Ad Manager, only Google Ads, and Google Campaign Manager: https://www.blendo.co/integrations/

  • Missing connectors: Twilio, Youtube 

#4 - Supermetrics

Literally. Supermetrics picks up all the marketing data you need and brings it to your favorite reporting, analytics, or storage platform — whether that’s a spreadsheet, a data visualization tool, or a marketing data warehouse.

Pros:

 Cons:

 #5 - Hevo

Hevo is a no-code data pipeline as a service. Start moving data from any source to your data-warehouses such as Redshift, BigQuery, and Snowflake in real-time.

Pros:

Cons:

  • They list Google Ads, but not Google AdSense / Ad Manager, only Google Ads, and Google Campaign Manager: https://hevodata.com/integrations/

  • They don’t list connector for Twilio, Mandril, and Youtube

#6 - Alooma

Alooma brings your data sources together into BigQuery.

Pros:

  • Able to do much more than only collecting data (advanced technical functionalities also with AI)

  • Seem quite advanced for a programmer (API, ...)

  • Fully integrated into GCP

Cons:

  • More on the expensive side (we didn’t find clear pricing, but found the information it’s between 1K and 15K a month)

  • They don’t have a list of connections: https://www.alooma.com/docs/connect-your-data

  • They don’t list connector for Twilio, Mandrill, Mailchimp, Bing, and Youtube