Blog

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.

Supermetrics Sidebar

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.

Data source selection

Data source selection

Select views

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. 

Select views

Select views

Select Dates

The date selection will define the date range of the data extracted.

Select Metrics

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.

Select Metrics

Select Metrics

Split by

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.

Select Dimension

Select Dimension

Segment

This is where you can apply segment to your query.

Select Segment

Select Segment

Query Examples

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.

Querying Pageviews

Is the example below the query is returning the Total of Pageviews for the Last 3 months for the selected view.

Supermetrics query example

Supermetrics query example

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.

Supermetrics query example

Supermetrics query example

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.

Supermetrics query example

Supermetrics query example

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

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.

Final thought

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.

 

 

Was this post helpful?

Leave a Comment