This article explains how to get the aggregated data from Google Analytics to BigQuery, which data you need and how to organise your Google Analytics data in BigQuery.
From Google Sheets to BigQuery
Once you move beyond Google Sheets, you will likely consider Google BigQuery as the key alternative. BigQuery offers a cost effective, high capacity and flexible data storage solution. A key benefit compared to Google Sheet is that BigQuery brings all your data together in one place, where Google Sheets spreads this around in all separate sheets. This is particularly true for online agencies and larger organisations (i.e. multiple locations, brands). One of the first platforms you would want to move to BigQuery for, is Google Analytics.
Hit level data ga to BigQuery
For Google 360 users, there is a direct connection between the two platforms to get hit level data from Google Analytics to BigQuery. But 360 is very expensive. And even for Google 360 users, for reporting purposes, it makes sense to (also) get aggregated Google Analytics data to BigQuery. Aggregated data means a lot less data to process. And less data means faster reporting. Hit level data tends to be a whole lot of data.
Aggregated data from Google Analytics to BigQuery
To get aggregated data from Google Analytics to BigQuery you either need to build an API connection between Google Analytics and BigQuery yourself or use a third party tool to handle this for you. Tools like Supermetrics or Cervinodata will do the job for you (see Supermetrics vs Cervinodata for a detailed comparison between the two rivals).
Which Google Analytics data do you really need?
Based on the experience Cervinodata has with agencies and larger organisations, we have compiled a list of Google Analytics metrics & dimensions with the highest demand. These are available in a relational data model (see the details of Ads data and Google Analytics data in the data model here). As time passes, more metrics and dimensions will be added.
Basic reporting requirements vs Ad hoc analysis
For the basic reporting requirements, there is usually no need to get more metrics and dimensions in BigQuery than the ones below. But there are always ways to add metrics and / or dimensions on an ad-hoc basis, but there is no need to fully automate this.
Google Analytics metrics
- "All goal completions"
- All 20 individual goals
- Duration (this is session duration)
- New users (to calculate the returning visiting rate)
These metrics get added value when combining with one or more dimensions
Google Analytics dimensions
- By device type
- By source / medium
- By UTM campaign
- By default channel group
Combined Google Analytics dimensions
- By device type & channel group
- By campaign & source/medium
Organising Google Analytics data: context
- Customer: This dimension allows you to quickly group multiple Google Analytics views together. For instance to sum up the revenue of multiple views. This dimension has been created by Cervinodata and is manageable inside the Cervinodata interface (where it is called Organisation).
- Google Analytics view: This is the lowest of the three levels in Google Analytics (Account > Property > View)
- Google Analytics Property: This is an important dimension because sometimes the view is not easy to distinct (if you have multiple clients with a "Master view" or "Raw (total)" or similar). In Cervinodata, this is called "Container". This might be a bit peculiar, but it allows for similar contexts to be placed in the same column (for instance both Adform and Google Analytics have a similar midlevel dimension).
How to get GA the out of BigQuery, into a Looker Studio report?
To get specific GA data in a Looker Studio report, you need to create SQL statements. When you run a SQL query, the resulting table can be saved. Make sure you name the BigQuery table in a way that makes it easy to find in Looker Studio. Next, make sure the query is scheduled, so your Looker Studio dashboard has updated data when you login in (read this support article to schedule queries).
Making BigQuery queries
For those of you that do not know how to write SQL statements (that includes me), Cervinodata offers a BigQuery Query builder. When Cervinodata is connected to BigQuery, a relational data model is created (by Cervinodata). Based on that data model, the Cervinodata query builder allows you to create advanced SQL statements, without writing one line of code yourself.
If you want to get a better understanding of how Cervinodata brings your Google Analytics data to BigQuery (and your ad platforms too), fee free to try Cervinodata for free. This includes the relational data model and the BigQuery Query builder as mentioned above.