Exporting Universal Analytics data: dimensions and metrics in the Google Analytics Spreadsheet Add-On

Want to export your Universal Analytics data using the Google Analytics Spreadsheet Add-On but not sure what dimensions and metrics to select? Let me 'break down' how the data will look in Google Sheets...

The Google Analytics Spreadsheet Add-On

From 1 July 2024, Google will start deleting Universal Analytics data. There are several ways to back up your data before then, one of the easiest being the Google Analytics Spreadsheet Add-On. This free tool enables you to export data from your Universal Analytics property to Google Sheets (which you can then connect to Looker Studio if you want to visualise it).

I won't go through the process of installing and running the Spreadsheet Add-On, as this has already been covered well elsewhere. Here's a great walk-through from Benjamin at Loves Data:

Instead I want to talk about what the exported data looks like in Google Sheets and how to work with it further there. This may influence what data you decide to export.

Dimensions and metrics in Universal Analytics

Whenever you create a 'Report' in the add-on, you choose various dimensions and metrics to query. A full list of Universal Analytics dimensions and metrics (along with the exact name values to use in the add-on) are available in the UA Dimensions & Metrics Explorer.

Metrics are things that can be measured - they have numeric values. So Users, Pageviews and Bounce Rate are all metrics. Dimensions, on the other hand, are attributes - they tend to have text values. Page Title, Hostname and City are all dimensions.

There are also a number of time-related dimensions such as Year, Month of Year and Week of Year. Although these have a numeric value (e.g. 2021), they are still classed as dimensions.

Metrics but no dimensions

For a report to run in the add-on, all you need is at least one metric. Dimensions are completely optional. So the simplest report configuration would be something like this:

One metric and no dimensions in the Google Analytics Spreadsheet Add-On

This gives you a report showing the total number of pageviews in your selected Universal Analytics View for the time period specified:

Google Sheets report generated by the Google Analytics Spreadsheet Add-On containing one metric and no dimensions

You could add more metrics to build up a summary - these results include Pageviews, Sessions and Users, for example:

Exported Universal Analytics data containing three metrics and no dimensions

Metrics and one dimension

But to start giving some context to this summary data, you'll need to add a dimension too. Here we can see those three metrics broken down by the dimension 'Default Channel Grouping':

Exported Universal Analytics data with three metrics broken down by one dimension

As long as your report contains one dimension and one or more metrics, your Sheets data will follow this very simple table structure and be easy to understand at a glance. Here, for example, I'm breaking down those metrics by the time-related dimension 'Month of Year' instead:

Exported Universal Analytics data with three metrics broken down by one time-related dimension

NB Don't confuse Month of Year (ga:YearMonth) with the very similar-sounding dimension Month of the Year (ga:month). The former, Month of Year, shows you the month AND the year - e.g. '202201' indicates January 2021. The latter, Month of the Year, only shows you the month OF the year - e.g. '1' indicates January. The same applies to 'Week of the Year' and 'Week of Year'.

Metrics and two dimensions

Often, you'll want to break your data down by more than one dimension at once. For example, you might want to see Sessions by month by Default Channel Grouping, in order to identify whether Direct traffic rose or fell over time. The report is easy to configure:

Exported Universal Analytics data with a single metric broken down by two dimensions

But the data in Google Sheets looks like this (I'm just showing a few of the rows):

Universal Analytics export results with data broken down by two dimensions

Column A is your first dimension (Month of Year), column B is your second dimension (Default Channel Grouping), and column C is your metric (Sessions). The values in column B repeat for each month. With this structure it isn't easy to see at a glance what happened to your Direct traffic over time.

Now in Google Sheets you could sort your data by column B, or filter column B to only show 'Direct'. But a better way of doing it would be to create a pivot table:

  1. Select all the breakdown data in the report (including headers)
  2. In the menu, go to Insert > Pivot table
  3. Insert to New sheet

In the pivot table editor:

  1. Click Add alongside Values and select your metric (e.g. Sessions)
  2. Click Add alongside Rows and select your time dimension (e.g. Month of Year)
  3. Click Add alongside Columns and select your other dimension (e.g. Default Channel Grouping)

Options in Google Sheets pivot table editor

This gives you a table structure like this:

Pivot table from exported Universal Analytics data

It's now easy to see the change in each channel grouping over time. It's also possible to add multiple metrics (Values) to your pivot table, though it might become less easy to understand at a glance if you do so.

Recreating a Universal Analytics report

If you want to recreate a report from Universal Analytics (in terms of the data at least), look at the dimensions and metrics it contains - then add one time dimension into the mix. For example, here's the 'Behaviour > Site Content > All Pages' report in the Universal Analytics interface:

All Pages report in Google Analytics Universal Analytics

You would recreate this in the add-on by including the metrics: 'Page Views', 'Unique Page Views', 'Avg. Time on Page', 'Entrances', 'Bounce Rate', '% Exit' and 'Page Value'. For dimensions, you would want 'Page' and then a time-based dimension such as 'Month of Year'. Then, once you have exported the data, pivot it in Google Sheets.

Adding multiple dimensions

Be aware, though, that adding multiple dimensions can quickly increase the number of rows in the data export to unmanageable levels. For example, if you have a website with 1,000 pages, and you want to see weekly data for a 10-year period (10x52), you could end up generating a report with 520,000 rows (assuming there's data for each page each month).

Some combinations of dimensions are safer to use than others from this perspective. An optional primary dimension on the All Pages report is Page Title. However, most pages (URLs) only ever have one page title, so adding both Page and Page Title is unlikely to dramatically increase the number of rows in the generated report.

(Even here there are exceptions. If you are not excluding URL query parameters in Universal Analytics, you might find that one Page Title has many different URLs. Facebook's fbclid parameter seems to be a particular culprit.)

I hope these tips have helped you understand how to choose and work with dimensions and metrics when building reports in the Google Analytics Spreadsheet Add-On. If you want any help exporting your Universal Analytics data, feel free to contact me (before the end of the month!).

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
James Clark
Hi! I'm James Clark and I'm a freelance web analyst from the UK. I'm here to help with your analytics, ad operations, and SEO issues.
0
What do you think? Leave a commentx
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram