Using the Google Analytics Spreadsheet Add-on

Automate your reports with the Google Analytics Spreadsheet Add-on

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

1. Don't enjoy digging around inside the Google Analytics interface every single day
2. Would much rather analyze and review data in Excel or Google Sheets, and build their own dashboards for it
3. Like automating their reports to save themselves time
4. Perhaps track data from several Google Analytics accounts and want a comprehensive picture of the results
5. Or build reports for others who either don't understand Analytics, or who they don't want to give full access to a Google Analytics view,

then this walkthrough of the Google Analytics Add-on is definitely for you.

If you recognized yourself, or you're simply curious about a simple, brilliant online tool that nearly 600,000 users rely on day in, day out (and not by accident!), in this article we'll go through the Add-on's core features and its more advanced query options in detail.

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

So let's dive in!

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

The Google Analytics Spreadsheet Add-on lets you pull and export any data you like from your Google Analytics account into a Google Spreadsheet file of your choice.

That comes with 2 prerequisites. First, that you have access to a Google Analytics account. Second, that you're open to using Google Spreadsheet.

Google Spreadsheet is arguably the most serious competitor Microsoft Excel has today. It's a simple, fast, free spreadsheet solution that you can edit in an online environment.

If you haven't used it yet, I definitely recommend giving it a try: Google Spreadsheet

Personally, I only use Excel when handling spreadsheets that are hundreds of megabytes in size, because above a certain size the browser itself falls over. For everything else Google Spreadsheet is perfect, and in countless respects it even beats Excel, for example with online collaboration.

Features of the Google Analytics Spreadsheet Add-on

1. Free. And with no feature limitations whatsoever!
2. It works as an Add-on in the Chrome browser (essentially a Google Spreadsheet extension).
3. It requires no coding knowledge at all.
4. It can be automated.
5. With a little practice, even a more complex data query can be built in 3 minutes.

In short: if you regularly want to see similar data (for example how your conversion costs are changing, how your online revenue is developing, or your Google advertising spend), you can track it continuously in a simple, automated spreadsheet. And all of it completely free.

That's exactly why at Markestic we use plenty of automated reports every day. Both in-house and for our partners. It speeds up data analysis and helps make sure everyone sees the same statistics, so there are no misunderstandings.

Build your first automated report step by step

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

1. Installing the Google Analytics Spreadsheet Add-on

1. Create and open a Google Sheet in which you want to build the report.
2. Under the Add-ons menu, click Get Add-ons.
3. Search for and add the Google Analytics Spreadsheet extension. If you can't find it, you'll also find it at the following link: Spreadsheet Add-on
4. If a permissions notice pops up, accept it. This is what allows the Add-on to pull your Google Analytics data.

And with that, you're done installing. The Google Analytics Spreadsheet Add-on is now visible among your Google Spreadsheet extensions.

2. Creating and running a report

1. Once you've installed the Add-on in your browser, it's ready for you to build your first query. All you have to do is click the Add-ons tab in the menu bar. Then choose Google Analytics. After that, select Create new report. This brings up a simple, intuitive interface, a sidebar in which you can define the parameters of your query. Let's walk through it!
2. First of all, you need to give your report a name.
3. Then you need to select the Google Analytics view you want to run the query from. Following the tree structure, you first choose the relevant Google Analytics account, then the property, then the view.
4. After that you select the various metrics and dimensions. The interface lets you make your selections from a simple dropdown menu, but if you start typing a specific word (e.g. session), the system recognizes that too. Don't expect a flashy interface. But it does its job perfectly.
5. After this we can also define various segments.
6. Once we're done with this, click the Create Report button.

At this moment, all that happens is that a new tab is created in the spreadsheet. Its name is: Report Configuration. Don't rename it, now or in the future, because the Add-on is built to look for this tab by name when it runs the queries, whether there's just one report or several different ones. If it can't find it, it won't even be able to run.

If we're happy with the parameters, all that's left is to run the query.

To do this, click Add-ons / Google Analytics / Run reports in the menu bar. With that, the report is created on a new tab with the name we gave it (in our example: Test Query).

A little further down we'll go through how the individual parameters can be modified and made more complex for more sophisticated queries, but let's take it step by step.

3. Automating the report

Now that our first report has been created, however complex it may be, let's look at how we can automate it. In other words, what we need to do so the report doesn't show a static snapshot from one moment in time, but instead refreshes automatically at regular intervals. That way we always see the freshest data in it. Assuming that's our goal. Of course it's also possible that we were only interested in one particular point in time. In that case we're done, and there's no need for automatic report refreshing.

If we do want to automate it, click Add-ons / Google Analytics / Schedule reports. The pop-up window speaks for itself. Enable the report to run automatically, then choose how often the query and refresh should run, and click the "Save" button.

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

More sophisticated setup options for Google Analytics queries

You'll see the parameters of the query on the Report Configuration tab in your spreadsheet. Something that might catch your eye at first is that certain rows, from 14 to 17, have been hidden. Before anything else, let's make them visible by clicking the arrows at the boundary of the hidden rows, so we get the full picture of what parameters can be set and what the exact input fields are.

Let's go through these in detail now!

Report Name

It speaks for itself, yet a few extra thoughts can still be useful.

The Report Name is the unique identifier of your query. If you change the name, a new tab is created as soon as you run the query. It doesn't touch the tab with the previous name. That one is neither deleted nor overwritten.

On the Report Configuration tab you can define several queries side by side.

You can define any number of queries in a single Google Spreadsheet. If two of them have the same name (which can happen if, say, you copy one and forget to change it), then only one of them will run. In terms of order, that means the first one from the left. An error message will also let you know that one couldn't run because of a duplicate name.

Finally, it's important to know that a query with no name won't run. If, for example, you no longer want to run one of the queries in the future, you don't have to delete the entire column. It's enough to delete its name. You can leave everything else untouched, because without a name it won't be able to run.

You achieve the same effect by writing TRUE in the Skip Report row. In that case the Add-on simply skips and leaves out the query belonging to that column.

View ID

This is the identifier of your Google Analytics view. If you log into your Google Analytics account and look at the tree structure, at every level there are unique identifier numbers alongside the name. This identifier refers to the given view. If, for example, you want to pull the same data from 2 different views, the quickest solution is to copy the parameters of your existing column exactly, then change the View ID, along with the report name for the reasons described above.

Start Date and End Date

No big surprise here! These define the start and end date of the query.

You can use date formats, for example 01/01/2019. But you can also reference specific days with formulas, for example =today(). This always takes the current day as the query's boundary.

If our goal with an automated report is for it to always show the freshest end date, then it's worth making the End Date change dynamically.

Alongside a specific date or a formula-based definition, we can also use relative text references. For example yesterday or 30daysAgo. The Add-on can interpret these too.

In other words, we can define the same thing in several ways. For example, we can reference yesterday with a function: e.g. =today()-1, or specify it as text: yesterday. It's a matter of taste. Choose whichever is simpler and clearer for you.

Metrics and Dimensions

If you're comfortable in Google Analytics and know the names of the various metrics and dimensions, you can define them here yourself. Or if it's easier for you, you can also pick them from the dropdown field in the right-hand sidebar mentioned above when you create the report.

A few tips and extra thoughts to go with this:

1. Google Analytics is built on JavaScript. In line with that, the naming and the way queries are defined also follow JavaScript syntax. It might look like gibberish at first, but don't worry, it becomes clear quickly.
2. The names of metrics, dimensions, and later segments and filters, start with "ga:".
3. This is followed by the name of the given attribute, for example sessions, date, campaign and so on.
4. Names made up of several words are built in camel case. What does that mean? In JavaScript, when defining variables we write the words together, but we mark the word boundaries with capital letters. So the correct form is, for example, ga:transactionRevenue or ga:sourceMedium. Not ga:Transaction Revenue or ga:sourcemedium.

Quite a few metric and dimension names are self-explanatory, so feel free to experiment on your own. But if a question comes up, you'll find an accurate and complete list of every possible query at the following link: Dimensions & Metrics Explorer.

Just to get you started, here are some of the more frequently used names (you can use these with the other attributes too, for example with Segments and Filters. See below):

• ga:sessions
• ga:avgSessionDuration
• ga:source
• ga:sourceMedium
• ga:keyword
• ga:adGroup
• ga:adClicks
• ga:goalCompletionsAll
• ga:goalXXCompletions (where you replace XX with the number, the ID, of the given goal. You'll find this in Google Analytics under the "Goals" goal-tracking settings of the given 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 the months by year. So if you build a report spanning several years, ga:month will aggregate all the Januaries, for example. ga:yearMonth, on the other hand, will display the individual months separately by year)
• ga:dayOfWeek

And so on. The list is practically endless. It might seem intimidating at first, but you'll soon realize that in reality you'll use 10-12 names regularly. The rest you can look up as needed in the Dimensions & Metrics Explorer linked above.

Order

This lets you set the order of the query. Descending or ascending order according to some criterion.

For example, if we don't want the monthly breakdown returned in chronological order, but rather according to how the individual months ranked in ascending order by number of sessions, we can define this with the ga:sessions attribute. In descending order, the same thing is specified as -ga:sessions.

Filters

In this section we have the option to specify various filters. From the very simplest to the quite complex. Here too it's worth taking a somewhat closer look at JavaScript syntax.

1. == defines an exact match. E.g. ga:sourceMedium==google / cpc. That is, at the source / medium level we're interested in the Google Ads data.
2. != does not match it. E.g. ga:source==facebook;ga:medium!=cpc. That is, we're interested in all traffic that came from the facebook traffic source, except the part defined at the medium level as cpc. In other words, we've excluded facebook paid traffic from facebook as a whole. It's important to note here that separating with a semicolon defines an AND relationship. Whereas a plain comma indicates an OR relationship.
3. =~ begins with something. E.g. ga:campaign=~facebook_cpc_dat_. That is, every campaign whose utm_campaign tag begins with facebook_cpc_dat_.
4. =@ contains something. E.g. ga:city=@balaton. That is, every city that has the word balaton in its name. Google Analytics is case sensitive, and accordingly so is the Google Analytics Spreadsheet Add-on. Keep this in mind, and where necessary compare the result of the query the first time against the results you see in the Google Analytics interface, so that a definition error doesn't cause you to filter for different results than the ones you wanted.
5. !@ refers to "does not contain". E.g. ga:campaign!@remarketing. That is, we exclude campaigns tagged with the remarketing campaign tag from our query.

Segments

Here we can define it if we want to run the query by any kind of segment. For example by country ga:country, at the city level ga:city and so on.

Limit

Back in the day, the query limit was set to 1000 rows by default. What did that mean? That the maximum number of rows in a query could be 1000, and the Spreadsheet Add-on wouldn't pull more than that. In practice this meant, for example, that if we pulled 3 years with a daily breakdown, which exceeds 1000 (3 * 365 = 1095), then the Add-on would no longer list the 1001st data point for us.

Fortunately, this limit is much higher today. The Add-on can handle as many as 50,000 rows. If for any reason we want to limit the number of rows pulled, we can do that here. For example if we write 10,000, then it will only export a more complex, more detailed query up to and including 10,000 rows.

Spreadsheet URL

Here you can specify the URL of another, essentially external Google sheet. If we don't want to export the results of the query into a separate tab in the same Google Spreadsheet, but into a completely different sheet, we have the option to specify that here.

Skip Report

We already referenced this solution in half a sentence above. If we specify TRUE in this row, the query in the associated column won't run on refresh. It's a rarely used solution, but it's worth knowing about if for any reason we no longer want to refresh one of the queries in the future.

Finally, let's go through the extra parameters that were in a hidden state at the start.

Report Type

This is another rarely used feature. It's not by chance that it's hidden by default along with rows 15, 16 and 17. If, instead of the standard queries, we want to use a different report type, for example Google Analytics's Multi-Channel Funnels report, we can define that here. In this case we write: mcf in the given cell. Then, when running the query, the Spreadsheet Add-on calls a different Google Analytics API.

Sampling Level

With more complex reports, or ones covering a longer time span or larger data set, sampling can occur. The Google Analytics Spreadsheet Add-on also indicates this on the relevant result tab (Contains Sampled Data: No. Or else a percentage value indicating the extent of the sampling).

If we specify LARGE or HIGHER_PRECISION in this row, we can avoid sampling as far as possible. In that case, despite a slower query time, the tool will strive to minimize sampling. Of course there are no miracles. Even then we can run into sampling. In that case it may happen that we have to reduce the size of the query, and perhaps merge the results from 2 separate queries.

Use Resource Quotas

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

Exclude Empty Rows

And finally, the last parameter we can specify defines whether we want to list any empty rows as well. For example, if we pulled the facebook.com / cpc source / medium results with a monthly breakdown for the whole year, but our facebook paid campaigns simply didn't run in October and November, only during the other months, then the Add-on won't list October and November. To do this, specify the following value: exclude.

We generally don't recommend this feature. The reason is that with queries, the best approach is to collect all the raw data onto one or more tabs, then build the report or dashboard you need on a separate tab, carrying out any filtering there as needed. If we filter everything out at the query stage, whether deliberately or by accident, then if we still need to change something or would need some extra data, we'll likely have to touch the structure and formulas of the dashboard we built later on.

So as a general approach, we recommend pulling the empty rows too, not just the ones that have some non-zero figure.

Where to go next with the Google Analytics Spreadsheet Add-on?

At first this many attributes and input fields might even seem intimidating, but don't worry for a second. The good news is that we can't do any harm with these queries. This is just an export. The worst that can happen is that, due to some syntax error, one of the reports doesn't run, but in that case the Google Analytics Spreadsheet Add-on helps us with a precise error message about where the error is and what we need to change.

Go ahead and try this tool. Run a few queries. Modify them, specify increasingly complex filters and segments, and check in Google Analytics as needed that you really did export what you wanted.

Whether you're already planning to automate a report or not, from here on you have the know-how in your pocket to do it. We wish you lots of success and good work with it!

And if you get stuck anywhere, or you need more complex reporting automation, don't hesitate to reach out to us: Get in touch with Markestic.