7-MINUTE READ · By Paul Koks on July 4, 2017

In the past I didn’t like building reports, but with reporting automation everything changed in a positive way.

Five years ago I was still exporting a lot of data from Google Analytics. You probably know this export CSV functionality in Google Analytics. Oh man, I can still get a headache when I think back of that time.

A long story short, I have extensively experimented with Google Sheets Add-Ons and the Google Analytics API and use them almost on a daily basis to support my (reporting) needs.

In this post I want to show six tips for using Supermetrics with Google Analytics more effectively.

1. Use the avoid data sampling feature

Sampling is a pain in Google Analytics and not everybody running a website with large traffic volumes can afford it or is willing to invest in Google Analytics 360. Happily Supermetrics offers the option to “try to avoid sampling”. No third-party can guarantee unsampled results.



You can run a query – where Supermetrics breaks this query in smaller chunks – to try to avoid sampling. This is a great feature that can come in handy now or in the future! A few additional tips that might be very helpful for you.

  • You don’t have to modify each query separately to tick this box. Simply go to your SupermetricsQueries sheet and fill in [“AVOID_SAMPLING”] for all queries in the column “Special settings”.


  • I often use Google Sheets as a data storage place before I create visualizations in Google Data Studio. If that’s your goal as well, you should refrain from ticking the box “Add note to query results showing whether Google has used sampling”. As there is a huge chance this will impact your data import in Data Studio in a negative way. Instead, review column K on your SupermetricsQueries sheet “Results contain sampled data”. You’re ok as long as it says “FALSE”.


  • For non-user related queries you can check the validity of your data by analyzing and aggregating smaller chunks of data in Google Analytics. And after comparing it with your data of Supermetrics/Google Sheets.

The outcome might not be 100% “perfect”, but I have found this to be a great way to get more confidence on segmented data for high-traffic clients without Google Analytics 360.

2. Use advanced settings

Supermetrics allows you to fill in advanced settings for each of your queries. Sometimes you might want to add a query that doesn’t return any data right now. When you run this query, you’ll see a “Your query returned no data” error and result on your sheet.


You can avoid this by adding “RETURN_NO_DATA_RESULT” to the advanced settings as shown below:


In this case my previous query would generate this result:


Keep in mind that all special/advanced settings are aggregated in the “Special settings” field of the SupermetricsQueries sheet. This is important once you start modifying the queries sheet directly.


3. Use external input fields

You might want to run queries and change them every now and then. For example, this could be a change in the metrics, dimensions or filters that you select. A great way to save time and leverage Supermetrics further is by using external input fields or modifying the SupermetricsQueries sheet directly.

External input fields in action

You need to have a solid knowledge of Google Sheets/Excel formulas if you want to implement this properly. Here is simple example:



In this sheet you can see the sentence Top 10 Products in 2017. The first sentence is static and the second one is dynamic as you can see below:



Now I change field “A1” into 2016:



And there you have it! In the same way you can build complex formulas and input fields to change particular queries. This is how one of my fields looks like: [{“field”:”entrancebouncerate_perc”,”operator”:”>”,”value”:”70″,”combineToPrev”:”;”}] The input value “70” refers to a segment of sessions with an entrance bounce rate greater than 70%. You could make this a dynamic formula so that users can change this value of “70” into a different value and outcome.

Change SupermetricsQueries

Much easier, but in some cases less effective, is by directly changing the input values on the SupermetricsQueries sheet. This is very simple: [{“field”:”entrancebouncerate_perc”,”operator”:”>”,”value”:”80“,”combineToPrev”:”;”}] I have now updated the query from bounce rate 70% to 80%. Once you get more accustomed in using Supermetrics you will often use one of these two options to modify your queries. This instead of searching for your queries where your data resides.

4. Schedule your refreshes

Supermetrics is fast, but it still might take some time to run all your queries. This is where Supermetrics triggers really come in handy.


Here are three reasons why you want to refresh your queries automatically and at your preferred time.

1. Serve your audience on time

By setting up the triggers at your desired time interval, you are able to perfectly serve the needs of your audience. You might create your visualizations directly in Google Sheets, or you use external applications like Tableau or Google Data Studio. Supermetrics can serve as a central data hub in that case. It doesn’t really matter where you build your dashboards as you always want to update your reports and dashboards automatically with the latest data.

2. Save time for other tasks

Manually updating your queries will take time. The more queries and advanced settings (e.g. avoid sampling) are in place, the longer you will have to wait. My simple rule: automate all recurring, brainless tasks whenever possible.

3. You forget about it

Especially if others are dependent on you and the query refresh, you don’t want to risk that you forget to update the report on Monday morning. Enough reasons to set up triggers in Supermetrics! Keep in mind that you need the paid version of Supermetrics for Google Sheets to configure triggers on your queries.

5. Learn about the SupermetricsQueries Sheet

This tip is most relevant by the time you have built more advanced queries and reports and are growing your experience. Many people use a tool without knowing what is going on behind the scenes. In my opinion this is far from optimal. You can usually get more out of a tool if you know how it works (processes and outcome). This is why I recommend to explore the SupermetricsQueries sheet after you have built more advanced queries. Here is a list of fields you might want to start with. Explore them and try to find the best shortcuts to build your queries and reports faster and more accurate.

  • Sheet name
  • Range address
  • Last status
  • Results contain sampled data
  • Date range type
  • Start date
  • End date
  • Compare to
  • Comparison value type
  • Accounts/views
  • Metrics
  • Dimensions
  • Filters
  • Sort
  • Max rows
  • Special settings
  • Refresh with user account

I promise that you will get much quicker in modifying or creating queries when you know how these fields work and how the values are populated.

6. Use different sheets for your Google Analytics dashboard

I usually work in three stages when I build dashboards for my clients. This is especially true if I build dashboards directly in Google Sheets. Although I most often prefer to work with external tools, this is not always possible. And Google Sheets provides you with a ton of different customization options some external tools can’t offer. Here is my three step plan:

  1. Create different sheets for my “raw data”.
  2. Create an aggregated “staging” sheet.
  3. Create a dashboard (either in Google Sheets or in an external application).

Personally I have found it much more easy to connect each “raw data” query to an individual sheet. You will keep a good overview of your work. The “staging” sheet serves the simple purpose to prepare the data for my dashboard later on. It’s easy to make some changes if you need to. This is how it usually looks like in Google Sheets:


Three steps to build a dashboard in Google Sheets


One exception to the rule: use one sheet if you want to build a report template for
Supermetrics’ template gallery
. This is it from my side. I hope these tips help you to better leverage the power of Supermetrics and Google Analytics. Happy to hear about your experience and best practices!

About Paul Koks

Paul Koks is an Analytics Advocate at Online Metrics and a guest writer for Supermetrics. He is a contributor to industry leading blogs including Kissmetrics, SEMRush, Web Analytics World and Online Behavior and the author of Google Analytics Health Check. Paul helps companies to capture valuable insights from simple data. You can find him on Twitter.

Try Supermetrics for free

Get full access to Supermetrics with a 14-day free trial.
No credit card required.