Bulk Analytics Configurations with Google Sheets

Google Analytics is known for its simple, turnkey approach to getting started with analyzing your traffic data. Just drop the snippet on your page and GA does the rest! Right?

…right?

Ok, fine. While there are plenty of Google Analytics users who manage just one property, a growing chunk of the analytics market is composed of large companies with complex account structures with dozens upon dozens of properties. Managing things like filters, channel groups, goals, and custom dimensions across all of these entities is far from trivial, and time-intensive at best.

Take custom dimensions, for instance. Imagine having to edit custom dimensions across, say, 20 properties. You’ll be moving from property to property, drilling into each dimension setting and waiting for the save to happen before moving to the next property. In short, you’ll be clicking around the Admin section of the interface for quite a while!

As it happens, most of the people in these large companies with complex requirements are often unable to get through the day without opening up a spreadsheet. In fact, more often than I’m comfortable admitting, people have told me that what they want isn’t necessarily a robust tool; they just want a tool that will allow them to get the job done, and ideally it would be in a spreadsheet because that’s where they live.

“But spreadsheet add-ons aren’t robust enough for enterprise software.”

Me: “Yeah, well, that’s just like – you know – your opinion, man.”

Look, enterprise data is messy. You have to manage it in a way that is sustainable and flexible, but getting it done is better than not. And for better or worse, in large organizations, people live in spreadsheets. By providing tools that let people get their jobs done with as gradual a learning curve as possible, larger organizations will be better positioned to use powerful features of Google Analytics in a way that would otherwise be too cumbersome to consider.

So, for this growing group of users, wouldn’t it be great if they could manage the configuration of their custom dimensions in a spreadsheet, copy and paste the configurations across multiple properties (maybe with some slight differences), and then upload the whole thing back into Google Analytics?

Google Analytics Management API and Custom Dimensions

The Google Analytics Management API can be used to manage many things, including common entities such as custom dimensions. The API can be accessed through a Google Sheet using Apps Scripts, and Google Sheets automatically handles authentication for Google APIs. This makes Sheets add-ons a convenient way to distribute functionality for important business processes such as listing and updating custom dimension information in the tabular form to which spreadsheet users everywhere are accustomed. In fact, the API was built with the expectation that users would develop their own ways of accessing and processing their data.

One of the configurations that the Management API enables you to manage from your own systems (as opposed to the Google Analytics interface) is the Custom Dimension feature. This is an important feature that allows you to add custom data to the information Google Analytics is automatically getting for you. For example, you can add a dimension to capture:

The type of users (silver, gold, platinum)
The level of engagement in the current session (maybe based on scroll percentage)
The name of the author on an article page

If you do not use this feature, take a look at these 5 questions.

But when you’re a marketing organization with limited engineering resources, who is going to write a robust tool to manage these entities at scale in a way that is easy to use and gets the job done?

Your friendly neighborhood Googler, that’s who!

Working with Custom Dimensions in Google Sheets

With that in mind, I rolled up my sleeves and started working on an add-on that would help users manage their custom dimensions in a more robust and organized way, while using an interface they are comfortable with. What I came up with can be found in the add-on store: GA Management Magic

Below I provide a step-by-step guide on how to use the add-on to manage your custom dimensions, but if you are the video type of person, you can also see me using the add-on in the following screencast.

1. Install the GA Management Magic add-on

The add-on is available through the add-on store

2. Listing Custom Dimensions

To list custom dimensions from a property, run the List custom dimensions command from the add-on menu (see screenshot below). Enter the property ID from which to list custom dimension settings into the prompt.

A new sheet will be added, formatted, and populated with the values from the property. You’re welcome!

3. Updating Custom Dimensions

To update custom dimension settings within 1 or more properties, run the Update custom dimensions command from the add-on menu (see screenshot above). Enter the property IDs (separated by commas) of the properties that should be updated with the custom dimension settings in your sheet.

The properties listed in the sheet will be updated with these values. Neat, right?

If you have not named the range(s) as described above, the script will format a new sheet for you into which you can enter your custom dimension settings. It is also recommended that you not update blank values into the property as it may result in undesirable behavior.