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 standard 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
get a report connecting your Online Advertisement campaigns with your ERP data
XPlenty has the advantage to support Twilio, but I don’t have an as good feeling as Stitch overall and it might be more expensive.
General thoughts:
...
, 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
This is the standard solution inside Google specially for Google BigQuery
Pros:
...
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
Probably provides Provides a good representations representation of the data (as Google offers it has a standard and it is for their own data), we you can have a feeling here with the example queries: https://cloud.google.com/bigquery-transfer/docs/adwords-transfer
Cons:
...
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:
Very Relatively affordable (https://www.stitchdata.com/pricing/), to be confirmed, but I assume 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 structure 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. Options 3:
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 structure 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
...
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
...
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.
...
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 Cons:
More expensive (bottom of the page here: https://supermetrics.com/pricing/supermetrics-for-bigquery)
1 data source & 1 account: $190/month
...
Doesn’t list Mandrill and MongoDB as a connector: https://supermetrics.com/connectors
Options 5: #5 - Hevo
Hevo is a no-code data pipeline as a service. Start moving data from any source to your data warehouse -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 programmer 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
Options 6: #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 in into GCP
...
Cons:
Expensive (didn’t find clear pricing, but found the information it’s betweek 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, MandrilMandrill, Mailchimp, Bing and Youtube
Options 7: 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/
Only option 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 mothly, 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, and Youtube