This long and nerdy post isn’t a self-contained tutorial, but if you’ve learned the basics of the GA add-on and know the fundamentals of spreadsheet work you shouldn’t have any trouble following.
What is it?
The Google Analytics add-on for Google Sheets is an interface that lets you use the Google Analytics API to pull your site data into a spreadsheet. There’s a learning curve but it’s quite powerful.
The official add-on documentation is a good start, though it leaves a lot of holes. You can see a walkthrough of setting up reports on the Moz blog, including the optional parameters. If you use this a lot you’ll want the GA API documentation and the dimensions and metrics explorer, which will tell you the API name of whichever dimension or metric you want as well as telling you which ones are legal to use together. You’ll likely also have use for the Google Sheets function reference.
Let me warn you that while I have some examples below I’m assuming you’ve used both Google Sheets and the Google Analytics add-on to some extent, though much more so Sheets (or spreadsheets in general).
How can you use it?
Let me give you a few examples of specific analytics reports you might be interested in. These are ordered from simplest to most complicated. After the description you’ll find a table with the metrics, dimensions, sorting, and filtering for the report configuration.
Try to name your reports something descriptive but still brief; you may be typing their names multiple times.
- Missing pages: For some items you may just want to pull the data straight over to the summary tab. For example, for error reporting you can pull your 404s and put the top ten most-viewed into the summary. I like to grab unique views (as a more representative count) and the page path (since the title is always the same). If your report is called Top 404s, your summary’s first line would be
='Top 404s'!B16to grab the path and count, respectively. Select both boxes and drag the bottom right corner down to select 10 lines to easily get the top 10 404s.
- Slow pages: Likewise for your slowest pages; you might be interested in both how long they take and how many times they are viewed. In fact I would filter out the lowest unique view counts (what “lowest” means depends on your overall site traffic) to focus your efforts on more important pages and to reduce the effect of outliers.
- Category archives page by page: Suppose you have only a few categories on your blog and you’re interested in visitor engagement with the archives – are people digging deep in one of your categories? In other words, you want to compare every page of the archive separately. That’s easy in WordPress where every category archive page includes /category/ and (most likely) no other page does. You may also want to clean up the page titles for easier skimming. Here, I have “CSS Archives – rweber.net” and then “CSS Archives – Page 2 of 4 – rweber.net”, and both “Archives” and the site name are irrelevant. Here’s a function that will eliminate those:
=REGEXREPLACE('Top Categories'!A16, " Archives| - rweber.net", "")
It leaves “CSS – Page 2 of 4” or just “CSS”. Note that if you use pipes instead of dashes you’ll need to escape them:
" Archives| \| rweber.net"in the middle of the REGEXREPLACE if I had “[Page Title] | rweber.net”.
Alternatively, you can use a pivot table to put all pages of a particular category archive together. First, dynamically copy your data into a different page with a filter applied that takes the titles all the way down to just the category name. By “dynamically” I mean by inter-tab spreadsheet reference rather than by copy-paste, the same way we are pulling the data into the summary tab. I would use this function:
=REGEXREPLACE('Top Categories'!A16, " Archives| Page \d+ of \d+| - rweber.net", "")
and in the next column,
to get the pageviews.
Note that you have to start your data copying in row 2 and give each column a title; otherwise you won’t be able to make a pivot table. “Pivot table” is in the Data menu, and choosing it will create another tab. Make Rows your categories and Values the pageview counts, summarized by SUM. Then go back to Rows and sort them by SUM of pageviews, Descending order. Now you have the data you will copy over to your summary tab (so you’ll probably want to rename the tab from “Pivot Table 1”).
- Page titles linked to page: Finally, it might be convenient to have the page titles in your analytics summary link out to the actual pages. Suppose my page paths show up in GA as everything after the .net, with their titles structured as “[Page Title] – rweber.net”, and I have called the title first and then the path, as in the table below. Then to link the title (without site name) to the page, I can use this function:
=HYPERLINK(CONCATENATE("http://www.rweber.net", 'Top Pages'!B16), REGEXREPLACE('Top Pages'!A16, " - rweber.net", "" ))
Depending on how you have your permalinks structured and whether you have nested page URLs, it can be more or less difficult to filter down to just blog posts or just actual pages. The example in the table below is unfiltered.
|Report Configuration for Examples 1 & 2|
|Report Name||Top 404s||Slowest Pages|
|Filters||ga:pageTitle=@Page Not Found||ga:uniquePageviews>=10|
|Report Configuration for Examples 3 & 4|
|Report Name||Top Categories||Top Pages|
A Word About Date Ranges
Date ranges can be entered into the Report Configuration as either a pair of calendar dates or a length of time in days, which will be a span of time ending with the previous day to when the report is run. You can use Google Sheets’ date functions to get a span of time that doesn’t end “yesterday” but is fixed relative to “today”. The UK’s GDS Data blog has a post about just that: Using relative date formulas in Google Sheets to query Google Analytics data.
Perhaps you are the interface on top of the interface, though, setting up the summary tab for someone who isn’t familiar with the GA API or add-on but would like a flexible custom dashboard. In that case you can put date entries in the summary tab and pull them into the Report Configuration by cross-tab reference, and it will work just fine. For anyone, including yourself, this is a great way to have the ability to compare multiple reports in two different date ranges without having to keep track of which dates go in which columns of the Report Configuration.
A Little Supplementary Documentation
- “Last N days” takes precedence over “start date” and “end date” if all are present.
- You can have just a metric but you cannot have just a dimension.
- Multiple dimensions or metrics are separated by whitespace, any whitespace.
- You must sort by your metric if you want that – by default it will sort by dimension alphabetically.
- You don’t need to HTML-escape anything for the API; the add-on does it for you. This includes spaces.
- Only “meaningful” whitespace is allowed in the filter string. That is, whitespace within your filter condition is allowed, but the metric or dimension, operator (==, =@, etc), filter condition, semicolon or comma, and next filter command must all be touching.
- Custom definitions have simple API names: ga:dimension1 is your custom dimension with index 1, and likewise for metrics.
- Any legal way of writing a date will work for the start and end date entries (probably subject to your Google language settings, for day/month/year versus month/day/year).
- Getting the segment id may only be possible through an outside service or through reading a URL – if you select only the segment you’re interested in within GA, the URL of the page will include “useg%3Duser” followed by a string of numbers, letters, and dashes. That string is the segment ID, which goes after “gaid::” in the Segment line of the Report Configuration.
- Predefined segments, on the other hand, don’t have IDs you can use in the same way – you’ll see “builtin” instead of “user” in the URL. For those you’ll have to build a segmentation command in the Report Configuration. For example, users who’ve made a purchase would be “users::condition::perHit::ga:transactions>0”. This syntax is in the GA API documentation.
Advantages and Disadvantages
The most fair comparison for our custom Google Sheets dashboard tab is to a standard Google Analytics dashboard made from a combination of standard and custom reports. The main purpose of considering custom reports is full flexibility in filtration: looking at the standard Event Action report with a secondary dimension of Page Title, you cannot filter by Event Label, but in the spreadsheet or a custom report, you can.
- Date ranges: can be applied to every widget individually and determined by functions that update automatically. Likewise…
- Segmentation: can be applied to every widget individually, though you’ll have to combine data sets for individual segments to get comparisons. This combination can also give you…
- Computed values: overall percentages, aggregate metrics, the sky’s your oyster.
- Combining and cross-referencing data: GA widgets limit you to one dimension and two metrics, but here you can have as many as you’d like. You can also combine data in other ways – did you institute a filter in your GA that changed how your page paths or titles are recorded? Copy the report data to a separate tab (dynamically!), applying that filter to all of it, and then create a pivot table to combine the metrics for now-alike pages. It won’t be necessary for date ranges entirely on one side of the change but it won’t hurt anything either.
- Cleaning and relating data: spreadsheet functions allow you to remove extraneous text from page titles and link them to the page in question, among other things.
- Easy access to full set of data: just go to the relevant tab of the spreadsheet. With internal GA dashboards you can make it so the widget links to the report you drew it from, but the automatic link is to the full generic version of the report. That’s probably not an issue with a custom report, of course, where presumably you’ve made the filtration part of the report definition.
- Ability to combine data from different GA properties and views: do you have virtual page views or error reporting filtered to a secondary view? Do you have a different site you’d like to see data from together with your main site? In the spreadsheet you can do that.
- Access to all of Google Analytics: I recently learned there is a secret chunk of Enhanced Ecommerce data about Related Products that is only available through the API, and hence in the spreadsheet but not a GA dashboard.
- Unlimited number of widgets
- Unlimited number of widgets
- Setup is lengthier: any visualizations or graphs you might want are yet another step on top of what I’ve discussed here.
- Not as adapted to printing/PDF-making.
- More fragile: if your date range is fixed (absolutely or relative to the current day) this is not a problem – just schedule the report to run nightly and give other users read-only access. Otherwise, well, one of the reasons for having users enter dates from the summary/dashboard tab of the spreadsheet is to minimize the risk of them accidentally deleting something from the Report Configuration.
- Running reports is slow; if you can schedule them, that is best.
- You have access to unlimited mixing and matching of dimensions and metrics, but if you combine illegally, your report will generate an error upon being run.
Photo of cargo ship from Unsplash via Pixabay.