How to pull Google Analytics data to Google Sheets with Supermetrics
This article is a simple guide on how to pull Google Analytics data into Google Sheets using Supermetrics for Google Sheets. Firstly I will detail how to create a simple query step-by-step. Secondly, I will share a few query examples. Finally, I’ll give some tips to help you use Google Analytics correctly when extracting data. I will consider that you have Supermetrics already installed and you’ve already created your first Supermetrics Queries. If you don’t please read my complete beginners’ guide and come back to this article later.
How to create a simple Google Analytics query step-by-step
To create a Google Analytics query with Supermetrics you need to define some parameters like the view you want to query, the date range of the query, the metrics, and dimension, etc… Parameters will be determined using the various menu from the Supermetrics sidebar.
Select Data Source
In the Data Source menu, you can select the source you want to query. Select Google Analytics. If this is your first query with Google Analytics you will be redirected to a Google Sign In window so you can Authorize Supermetrics to access your Google Account and allow Supermetrics to view your Google Analytics data. You should then be redirected to the Spreadsheet with Google Analytics selected as a source.
Google Analytics is organized into Account, Property, and views. The ‘Select views’ drop-down menu is organized into accounts and properties listed with the following format “Property name: view name”. Select the view(s) from which you want to extract data. When you have view with a similar name use the property name to differentiate them.
The date selection will define the date range of the data extracted.
This is where you need to have some Google Analytics expertise to build your query. Are you looking for extract sessions, pageviews, users? You can select one of multiples queries.
This is not the most intuitive name even though it makes sense. In the Split by section you will choose the dimension you want to use for your queries. In Google Analytics dimensions are for example date, source/medium, device etc… You can select one or multiple dimensions.
This is where you can apply segment to your query.
Here are some examples of Queries you can build. Each example is presented with a screenshot. On the right, the Supermetrics Sidebar allowing to see the parameters used to build the query. On the left, in the spreadsheet, the output of the query.
Is the example below the query is returning the Total of Pageviews for the Last 3 months for the selected view.
Querying pageviews with date dimension
In the example below the query return the total pageviews split to rows by date for the last 7 days.
Querying pageviews with date and source/medium dimension
In the example below the query return the total pageviews split to rows by date and source/medium for the last 3 days.
Google Analytics subtleties
Supermetrics is super easy to use, Google Analytics is not. When you have issues extracting data, it is usually not because of Supermetrics but because of Google analytics. Here are a few things you need to consider.
Extracted data are limited to the selected date range
Google Analytics data are stored in some kind of database. Probably not a random SQL database but still a database. The Google Analytics API (thanks to Supermetrics) will extract data from this database. As you have probably understood it by now you need to define specific parameters to query the API. One of these parameters is the date range. Data will only be available for the selected date range. This means that if you want to access data from another date range you will need to build another query. If you choose a big date range like over the past two years, your query will be probably slow to run and will return hight sampled data. Make sure to wisely define the date range of your query before starting anything.
Sampling is a well-known limitation of Google Analytics. The more data you want to extract from the data, the higher will be the sampling. Supermetrics offer a way to avoid sampling in the Options menu, however this not bulletproof and it will make your query very slow. Always consider this when validating your data.
Don’t combine metrics and dimensions of different scopes
Scopes are defining the underlying organization of Google Analytics data collection. There are four different scopes:
- Hit data
- Session data
- User data
- Product data
When combining metrics and dimensions, you always have to ask yourself if it makes sense according to the way Google Analytics is collecting data. For example, you can’t use sessions as a metric when you have pages as a dimension.
Supermetircs allows you to extract Google Analytics data into a spreadsheet easily. It will give you a lot of flexibility and a great way to analyze your data and automate your reporting. However, it is essential to know Google Analytics well to avoid any mistake. At best, your queries won’t return any values. At worst, you can deliver reports and/or analyses with flawed data without even noticing it. Always check your Supermetrics data against your Google Analytics interface and some other dataset you own.