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...
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.
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.
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:
This gives you a report showing the total number of pageviews in your selected Universal Analytics View for the time period specified:
You could add more metrics to build up a summary - these results include Pageviews, Sessions and Users, for example:
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':
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:
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'.
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:
But the data in Google Sheets looks like this (I'm just showing a few of the rows):
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:
In the pivot table editor:
This gives you a table structure like this:
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.
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:
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.
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!).
Buy me a coffee