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:
Calculate not only the revenue but the gross profit generated per sold product and factor in the delivery costs that you are bearing.
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.
Pros:
Very cost-effective way: https://cloud.google.com/bigquery-transfer/pricing
Provides a good representation of the data (as Google offers it has a standard and it is for their own data), you can have a feeling here with the example queries: https://cloud.google.com/bigquery-transfer/docs/adwords-transfer
Cons:
Only provides a limited quantity of data sources from Google and not from their competitors such as Facebook ads (list in pricing link above and details here: https://cloud.google.com/bigquery-transfer/docs/how-to)
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)
Provide a comparison chart (of course biased in their favor) with Bleno and Hevo: https://www.stitchdata.com/vs/blendo/hevo-data/
They give very clear information about the structure of the tables they provide: https://www.stitchdata.com/docs/integrations/saas/google-ads#schema
They cover most connectors you need: Google Ads, Facebook Ads, Mailchimp, MongoDB, Bing Ads
Cons
They list Google Ads, but not Google AdSense / Ad Manager, only Google Ads, and Google Campaign Manager: https://www.stitchdata.com/integrations/sources/
Missing connectors: Twilio, Youtube
#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:
I didn’t find the table documentations of what they generate in BigQuery
Could be expensive (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:
Quite affordable (https://www.blendo.co/pricing/), to be confirmed, but I assume you will not need more than the 500.- / month
They give very clear information about the structure of the tables they provide: https://docs.google.com/spreadsheets/d/1wRDf5aQQ_88g05R_p7sGPkCqfJikZfF6y-tgHlDRy6E/edit#gid=0
They cover most connectors you need: Google Ads, Facebook Ads, Mailchimp, Mandrill, MongoDB, Bing Ads
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:
Very active in online marketing and very focused on BigQuery and Data Studio (not a proof that the connectors are better though)
Provide many connectors (https://supermetrics.com/connectors , select BigQuery on the left to see the available connectors)
They cover most connectors you need: Google Ads, Facebook Ads, Mailchimp, Bing Ads, Youtube
Good documentation of the tables they will generate: https://supermetrics.com/docs/integration-google-ads-dwhtables/
Cons:
More expensive (bottom of the page here: https://supermetrics.com/pricing/supermetrics-for-bigquery)
1 data source & 1 account: $190/month
1 data source & 5 accounts: $490/month
so I assume you will need 5 accounts (as you have several shops) and several data sources, so probably 1-2K / month
Maybe you get a good deal if you request a quote though, hard to say
Doesn’t list Mandrill and MongoDB as a connector: https://supermetrics.com/connectors
#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:
Affordable (https://hevodata.com/pricing/), to be confirmed, but I assume you will be between 500.- and 1K / month
Provide good information about the table structures: https://docs.hevodata.com/sources/marketing-&-email/google-adwords/
Seem quite advanced for programmers (API, ...)
They cover most connectors you need: Google Ads, Facebook Ads, Mailchimp, Bing Ads
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:
Expensive (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
Add Comment