Google BigQuery
How to get the data from multiple ad platforms and Google Analytics in Google BigQuery.
Google BigQuery is Google's powerful and low cost data storage service. For most professional users, it is thé starting point of their reporting and data interpretation infrastructure. But more and more agencies are moving from Google Sheets to BigQuery. This article will show you why this is the case.
Or skip directly to the step-by-step instructions on how to set up Cervinodata for BigQuery.
Key benefits of BigQuery
All your data in one place
It allows you to bring all your data into one place, not spread around in Sheets.
Plug and play
It has native connections to a very large list of BI tool providers as well as Dashboarding and reporting tools.
Speed
It is based on the same technology and platform as Google Search, so speed is obvious
Low costs
It is free for a large group of users that stay below the threshold of paid plans.
All your data in one place
- With it's flexible table structure and options to add new tables to existing ones, it's possible to build a database structure to keep all your clients in one relational database structure.
- Designing a database structure and it's relations will require the proper knowledge in your team to make it work.
- Tools like Cervinodata can automatically create a table structuce for you.
Plug and play
Due to it's native integration with leading data visualisation tools like Klipfolio, Google's own Data Studio, Sigma Computing, Tableau and others it's easy to use Google BigQuery as the central data source.
- See how to connect it to Klipfolio
- See how to use Google Data Studio
- Watch the video how to connect it to Sigma Computing
- BigQuery Now also works with KNIME
Tools like Cervinodata allow you to connect to data-sources and help you bring all your data into BigQuery.
Speed
Recently added features include a newly built back end with 10x the streaming quota, the ability to query live from Cloud SQL datasets, and the ability to run your existing TensorFlow models. These new features are designed to help you stream, analyze, and model more data faster, with more flexibility.
Low costs
Google might charge for its services, this depends on how you use it. Although you have to use Google BigQuery quite extensively (i.e. large volumes of data) before you reach the limit of free usage it is wise to understand their cost structure.
- See how the cost structure is set up. Cervinodata has some optimalizations in place to reduce the workload / costs, so please read the article.
- Also, check out their Cost calculator.
Step by step instructions
To get started with Cervinodata for BigQuery, you need the following:
1. A Cervinodata account (start free trial here)
2. Access to one or more ad platforms or Google Analytics views
3. A BigQuery project (start here)
First, set up Cervinodata
Connect to platforms
In the Cervinodata interface connect to multiple ad platforms (and Google Analytics). It is even possible to connect to one platform using multiple logins. Give access once, and you do not need to look at it again.
- After you have given access to one or more platforms, you can decide which accounts you would like to sync to BigQuery. This is done to keep the costs for BigQuery as low and your data set as clean as possible.
Choose which accounts you want in BigQuery
Switch the accounts ON to start the data collection. The data collection will automatically start. This might take a couple of minutes or a bit longer, depending on the number of accounts and the amount of data.
- For the accounts that have been switched ON, you can determine the destination.
- In this case you choose BigQuery (but you could choose multiple).
Group by Organisation
Cervinodata makes it very easy to manage data from multiple accounts & platforms that belong to one brand, organisation, division, country, etc. This is done with the Organisation layer.
- Grouping by Organisations allow you to quickly add ad accounts, platforms or Google Analytics views to a particular organisation, without changing anything else.
- You will see how this works below.
Choose BigQuery as your destination
This takes only a few simple steps
- Connect to Google BigQuery with your BigQuery username and password.
- From the list of available projects, choose the project you wish to use for Cervinodata. Press "Choose this project".
- Cervinodata will now create the table structure in this BigQuery project and will sync the historic data from all the accounts you switched ON.
- After this, Cervinodata keeps your BigQuery project up to date on a daily basis.
That's it!
Cervinodata has created a relational data structure for you, will syncronise the historic data to BigQuery (for all the accounts you have switched on), and will keep it up to date for you.
Using Cervinodata
Cervinodata offers a few clever tools to help you build SQL queries (without having to write any code yourself). Also, there is a campaign grouping mechanism available that allows you to automatically add new advertising campaigns to a particular group (a campaign group). Both tools will save you a lot of time.
Build a campaign group
A campaign group is a very powerful feature for online marketers and agencies that wish to report on multiple ad platforms, but not all the campaigns within those platforms.
- A campaign groups allows you to select which ad platforms and which campaigns should be included
- The Auto-select allows you to automatically add newly created campaigns when the meet the criteria. For instance contains "FB0013" (your campaign number) or "Summer_2020" (your campaign name)
- The campaign groups are automatically added to BigQuery and can be included in the Query builder (so no coding needed).
Create a query (Cervinodata query builder)
In Cervinodata, create a query for BigQuery. You can use this as the basis for your Zoho Analytics data set.
- The query builder enables you to create a sophisticated query for BigQuery, with out writing any code.
- Simply, choose the organisation, data type, platforms, date granularity and a time range.
- Copy the created query. You can paste this as a custom query in Zoho Analytics.
- Note: The query builder is based on the Organisation layer. If you add an advertising account to that organisation, the data will be added on the next BigQuery refresh cycle.
Next steps
These are a few of your next steps to take
- Get a Cervinodata account (start free trial here)
- Get a BigQuery project (start here)
- Ask a support question: https://support.cervinodata.com
- Contact sales for more info: sales@cervinodata.com
Try Cervinodata for free
Start a free trial of Cervinodata. Get the right data in your BigQuery project without writing one line of code.