Using Google Analytics Spreadsheet Add-on

The article was written by Markestic

2021-12

Automate your reports with Google Analytics Spreadsheet Add-on

If you’re one of those entrepreneurs or online marketing experts like me who:

  1. They don’t like to mine inside the Google Analytics interface every day
  2. They are more than happy to analyse, review data in excel or google sheets and create their own dashboards
  3. They like to automate their reports to save themselves time
  4. You may be monitoring data from multiple Google Analytics accounts and want to get an overview of the results
  5. Or they are reporting for others who either don’t understand Analytics or who don’t want to be given full access to a Google Analytics view,

then this tutorial on how to use the Google Analytics Add-on is for you.

If you know yourself or are just curious about a simple and great online tool that is used by nearly 600,000 users every day (and for good reason!), in this article we will go through the basic features of the Add-on and more complicated query options in detail.

By the end of this article, you’ll be able to create your first automated reports and dashboards yourself in just a few minutes.

So let’s get started!

What is Google Analytics Spreadsheet Add-on and what is it for?

Google Analytics Spreadsheet Add-on allows you to retrieve and export any data from your Google Analytics account to a Google Spreadsheet file of your choice.

2 prerequisites follow from this. First, you need to have access to a Google Analytics account. Secondly, to be open to using Google Spreadsheet.

Google Spreadsheet is perhaps the most serious competitor to Microsoft Excel today. Simple, fast, free and editable spreadsheet solution in an online environment.

If you haven’t used it yet, I highly recommend you try Google Spreadsheet

Personally, I only use Excel when dealing with spreadsheets of many 100 megabytes, because above a certain size the browser itself breaks down. Google Spreadsheet is perfect for everything else, and beats Excel in many features, such as online collaboration.

Google Analytics Spreadsheet Add-on Features

  1. Free of charge. Even without any functional limitations!
  2. It works in Chrome browser as an add-on (quasi a Google Spreadsheet extension).
  3. It does not require any programming knowledge.
  4. Can be automated.
  5. With a little routine, a more complex data request can be done in 3 minutes.

In short, if you regularly want to see similar data (e.g. changes in your conversion costs, your online revenue, your Google advertising costs), you can keep track of it in a simple and automated spreadsheet. All completely free of charge.

These are the very reasons why we at Markestic use a lot of automated reporting in our daily work. Both in-house and for our partners. It speeds up data analysis and helps everyone see the same statistics so there is no confusion.

Create your first step-by-step automated report

To create your first automated report, you need to go through 3 main steps.

1. Install the Google Analytics Spreadsheet Add-on

  1. Create and open a Google Sheet in which you want to create the report.
  2. Under Add-ons, click on Get Add-ons.
  3. Search for it, then add the Google Analytics Spreadsheet plugin. If you can’t find it, you can also find it at the following link: Spreadsheet Add-on
  4. If an access statement pops up, accept it. By doing so, you enable this add-on to retrieve your Google Analytics data.

You’re done with the installation. The Google Analytics Spreadsheet Add-on is now visible in your Google Spreadsheet add-ons.

2. Create and run a report

  1. Once you have installed the Add-on in your browser, you are ready to make your first query. All you have to do is click on the Add-ons tab in the menu bar. Then choose Google Analytics. Then click on Create new report. This will bring up a simple and intuitive interface, sidebar, where you can define the parameters of the query. Let’s see this through!
  2. Above all, you must give your report a name.
  3. You will then need to select the Google Analytics view from which you want to start the query. To do this, you must first select the Google Analytics account, then the property, then the view.
  4. The different metrics and dimensions are then selected. The interface allows you to make selections from a simple drop-down menu, but if you start typing a specific word (e.g. session), it will also be recognised. Don’t expect too much surface area. But it serves its purpose perfectly.
  5. You can then define different segments.
  6. When you are done, click on the Create Report button.

At this point, nothing happens other than a new tab is created in the spreadsheet. This is called Report Configuration. This should not be renamed, now or in the future, because the add-on will look for this tab by name when it runs queries, whether it is one or more different reports. If you can’t find it, you won’t be able to run.

Once you are satisfied with the parameters, all you have to do is start the query.

To do this, click on Add-ons / Google Analytics / Run reports in the menu bar. This will create a report in a new tab with the name we have given it (Test Query in our example).

A little further down, we’ll go through how each parameter can be modified and complicated for more complex queries, but let’s go step by step.

3. Report automation

Now that we have our first report, however complex, let’s see how we can automate it. Ergo, what do we need to do to make the report not show a static state at a given moment in time, but update automatically at regular intervals. So you can always see the most up-to-date data. Provided that this is our goal. Of course, it is also possible that we were only interested in a particular moment in time. In this case we are done and there is no need for an automatic report update.

To automate this, click on Add-ons / Google Analytics / Schedule reports. The pop-up window speaks for itself. Allow the report to run automatically, then select the intervals at which the report should be downloaded and updated, and click “Save”.

Congratulations! Your first automated query from Google Analytics is ready.

More sophisticated settings for Google Analytics queries

You can see the query parameters in the Report Configuration tab in your spreadsheet. What may strike you at first is that certain lines, from line 14 to line 17, have been hidden. First of all, let’s make this visible by clicking on the arrows on the borders of the hidden rows, so we can get a complete picture of what parameters can be specified, what the exact input fields are.

Now let’s look at them in detail!

Report Name

It speaks for itself, but some additional thoughts may be useful.

The Report Name is the unique identifier for your query. If you change the name, a new tab will be created when you run the query. The tab with the previous name is then unaffected. It will not be deleted or overwritten.

In the Report Configuration tab you can define multiple queries side by side.

https://drive.google.com/file/d/1RIhTRPOPMVLs5k41lBtd2KlWOUcTgUsE/view?usp=sharing

You can enter any number of queries in a Google Spreadsheet. If two of them have the same name (this can happen if, say, you copy and forget to change it), only one will run. This means first from the left. Error message will also indicate that due to name duplication one could not run.

Finally, it is important to know that a query that has no name will not run. For example, if you no longer want to run a query in the future, you don’t have to delete the entire column. It is sufficient to delete the name. You can leave everything else untouched, because it won’t be able to run without a name.

You can achieve the same effect by setting the Skip Report line to TRUE. Then the query for that column is simply skipped and skipped by the plugin.

View ID

This is your Google Analytics view ID. When you log in to your Google Analytics account and look at the tree structure, each level has unique identification numbers in addition to the name. This identifier is a reference to the given view. For example, if you want to retrieve the same data from 2 different views, the quickest way is to copy the parameters of your existing column one by one, then change the View ID and, as described above, the report name.

Start Date and End Date

No big surprises! These define the start and end date of the request.

Date formats can be used – for example 01/01/2019. But you can also refer to specific days using a formula – for example =today(). This always takes the current day as the limit for the request.

If the goal of an automated report is to always show the most recent end date, then it is worth making the End Date a dynamic variable.

In addition to the concrete date or even a figurative definition, you can also use relative textual references. For example yesterday or 30daysAgo. These can also be interpreted by the Add-on.

Ergo, the same thing can be defined in several ways. For example, you can refer to yesterday with a function: e.g. =today()-1 , or you can specify yesterday. It’s a matter of taste. Choose the one that is simpler and clearer for you.

Metrics and Dimensions

If you are familiar with Google Analytics and know the names of the different metrics and dimensions, you can define them here. Or, if it’s easier for you, you can choose from the drop-down box on the right sidebar above when you create a report.

A few tips and additional thoughts on this:

  1. Google Analytics is built on JavaScript. Along the same lines, the naming and query definition follows JavaScript syntax. It may seem like a krixkrax at first, but don’t worry, they are quickly transparent.
  2. Metrics, dimensions and later segments and filters are named starting with “ga:”.
  3. This is followed by the name of the attribute – for example sessions, date, campaign and so on.
  4. Names consisting of several words are based on the camel case. What does it mean? In JavaScript, when defining variables, words are written as one, but word boundaries are marked with capital initials. Correctly, for example, ga:transactionRevenue or ga:sourceMedium. Not ga:Transaction Revenue or ga:sourcemedium.

Quite a few of the metric and dimension names are trivial, and feel free to try them on your own. But if you have any questions, you can find an accurate and complete list of all possible queries at Dimensions & Metrics Explorer.

Just to get you started, here are some of the more commonly used names (you can use these for other attribute columns – Segments and Filters, for example. See later):

  • ga:sessions
  • ga:avgSessionDuration
  • ga:source
  • ga:sourceMedium
  • ga:keyword
  • ga:adGroup
  • ga:adClicks
  • ga:goalCompletionsAll
  • ga:goalXXCompletions (where for XX, enter the ID of the goal. You can find this in Google Analytics in the “Goals” goal measurement settings for that view)
  • ga:deviceCategory
  • ga:transactions
  • ga:country
  • ga:city
  • ga:productCategory
  • ga:date
  • ga:week
  • ga:month
  • ga:year
  • ga:yearMonth (this differs from month in that it separates months by year. So if you are doing a multi-year report, ga:month will aggregate, for example, the months of January. ga:yearMonth will display the months separately on an annual basis)
  • ga:dayOfWeek

And so on. The list is virtually endless. It may seem daunting at first, but you’ll soon realise that you’ll actually be using 10-12 names on a regular basis. The rest can be found ad hoc in the Dimensions & Metrics Explorer linked above.

Order

This allows you to specify the order of the query. Decreasing or increasing order according to some criterion.

For example, if you want to get the monthly breakdown not in chronological order, but according to how each month followed the next in ascending order in terms of session number, you can define this with ga:sessions. In descending order, the same can be given as -ga:sessions.

Filters

In this section we have the possibility to set different filters. From the simplest to the most complex. Here too, we should take a closer look at the syntax of JavaScript.

  1. == defines an exact match. Pl. ga:sourceMedium==google / cpc . That is, at the source/medium level, we are looking at Google Ads data.
  2. != does not match it. E.g. ga:source==facebook;ga:medium!=cpc . That is, we want to see all traffic from facebook traffic sources except traffic defined as cpc at the medium level. Ergo, we excluded facebook paid traffic from facebook as a whole. It is important to note here that the exact comma delimiter AND defines a relationship. While a single comma defines an OR relationship.
  3. =~ starts with something. Pl. ga:campaign=~facebook_cpc_dat_ . That is, any campaign with a utm_campaign tag that starts with facebook_cpc_dat_.
  4. =@ contains something. E.g. ga:city=@balaton . In other words, any city that has the word Lake Balaton in its name. Google Analytics is case sensitive, and so is the Google Analytics Spreadsheet Add-on. Pay attention to this and, if necessary, check the results of the query against the results you see in Google Analytics the first time, so that you don’t filter for different results than you would have liked because of a definition error.
  5. !@ refers to “not included”. For example, ga:campaign!@remarketing . That is, campaigns with the remarketing campaign tag are excluded from our query.

Segments

Here you can define if you want to query by any segment. For example, by country ga:country, by city ga:city and so on.

Limit

By default, the query limit was 1000 lines. What did it mean? That the maximum number of rows in the query was 1000, and the Spreadsheet Add-on did not retrieve more than that. In practice, this meant, for example, that if we requested 3 years per day, which exceeded 1000 (3 * 365 = 1095), then 1001. data is no longer listed for us by the add-on.

Fortunately, this limit is now much higher. The Add-on can handle up to 50,000 lines. If for any reason you want to limit the number of lines you can retrieve, you can do so here. For example, if you enter 10,000, it will only export a more complex, detailed query up to 10,000 lines.

Spreadsheet URL

Here you can enter the url of another, quasi external google sheet. If you do not want to export the results of the query to the same Google Spreadsheet in a separate tab, but to a completely different sheet, you can specify this here.

Skip Report

We have already referred to this solution in half a sentence above. If TRUE is specified on this line, the associated column will not be retrieved on update. Rarely used, but worth knowing about if for some reason you don’t want to update a query in the future.

Finally, let’s look at the extra parameters that were hidden at the start.

Report Type

This is also a rarely used feature. It is no coincidence that it is hidden by default along with lines 15, 16, 17. If you want to use a different type of report instead of the standard queries, for example the Google Analytics Multi-Channel Funnels report, you can define it here. In this case, write: mcf in the given cell. When queried, the Spreadsheet Add-on calls another Google Analytics API.

Sampling level

Sampling may be required for more complex reports or reports covering a larger time span or data set. This is also indicated in the Google Analytics Spreadsheet Add-on (Contains Sampled Data: No. Or, more precisely, a % value indicating the extent of sampling).

If you specify LARGE or HIGHER_PRECISION on this line, you can avoid sampling as much as possible. In this case, the tool will try to minimize sampling despite slower time sampling. There are no miracles, of course. You may also run into sampling. In this case, you may need to reduce the size of the query and possibly combine the results from 2 separate queries.

Use Resource Quotas

This could be exciting for users who use GA360 (Analytics 360). For them, the sampling threshold may be even higher. If you use GA360 in your company, you can push the sampling limit even further by setting TRUE here.

Exclude Empty Rows

And finally, the last parameter you can specify defines whether you want to list any empty rows. For example, if you have monthly facebook.com / cpc source / medium results for the whole year, but you didn’t run any paid facebook campaigns in October and November, only during the other months, then October and November will not be listed in the Add-on. To do this, set the value to exclude .

This feature is generally not recommended. No, because the best direction for queries is to collect all the raw data in one or more tabs, and then build the necessary report, dashboard in a separate tab, filtering as needed. If we filter out everything at the time of query, intentionally or accidentally, if we need to modify something or need some extra data, we will probably need to access the structure and formulas of the dashboard later.

So as a general approach, we suggest that you also request blank lines, not just those with some non-zero number.

How to proceed with Google Analytics Spreadsheet Add-on?

It might seem daunting at first with so many attributes and input fields, but don’t worry for a minute. The good news is that we can’t do any harm to you in the process. This is just an export. The worst that can happen is that a report doesn’t run due to some syntactic error, but in this case the Google Analytics Spreadsheet Add-on will help us with a precise error message, where the error is and what we need to change.

Feel free to try this tool. Run a few queries. Modify it, add more and more complex filters, segments, and check in Google Analytics if necessary to make sure you’ve exported what you wanted.

Whether you’re already planning to automate a report or not, you now have the knowledge in your pocket on how to do it. We wish you good luck and good work!

And if you get stuck at any point or need more complex reporting automation, feel free to contact us at Markestic.

What else might interest you…

Cool TikTok tips for businesses

Cool TikTok tips for businesses

TikTok is an increasingly popular social media app, especially among young people. The platform has been dubbed "the new Snapchat", and it's easy to see why. TikTok allows users to make short videos...

Why set up GA4 now?

Why set up GA4 now?

Just a ten-minute setup can save you a lot of headaches. "In 2023, we will start to retire Universal Analytics" Remember the emails you've received from Google in recent months about Universal...