Blog

How to consolidate Google Analytics properties with Supermetrics for Google Sheets

There are many available options to consolidate Google Analytics properties. Here is a recipe using Supermetrics for Google Sheets and a Google Sheets formula. In this guide, I will extract data from various Google Analytics property in separate sheets using Supermetrics and consolidate them into a master sheet using a Google Spreadsheet formula. Before starting you will need to have Supermetrics installed and being familiar with it. If not, you can read my beginner’s guide. You will also need to know how to build queries for Google Analytics. You can learn about it here.

Organize your spreadsheet and collect data

We want to create queries for each one of the properties you want to consolidate and have our data organized in a specific way.

Step 1

Let’s create a sheet for each property you want to consolidate. You can create as many sheets as you want for as many property as you want.

Step 2

Run a query in the first property sheet. In this example I will run a simple query: Users, Sessions, and Pageviews split to rows by date for the last 6 months.

Step 3

Run the exact same query for your different properties in the different sheets

Consolidation

By now you should now have three or more identical property sheets. One sheet for each property you want to consolidate. In the Master sheet you will copy and paste the following formula:

=query(arrayformula({'Property#1'!A2:E;'Property#2'!A2:E;'Property#3'!A2:E}),
"select * where Col1 IS NOT NULL")

The array formula is collecting data from each property sheet adding data from each sheet one after another in the master sheet. The query function is here to clean ‘null’ values.

Final thought

On one side, Supermetrics for Google Sheets is super easy to use, super powerful and offers a lot of flexibility. One the other side Google Sheets is also powerful and also provides a lot of flexibility. As seen in this short guide, you can easily combine data from multiple Google Analytics properties using Supermetrics and some Spreadsheets formulas. Once you have your properties consolidated into one spreadsheet, you can quickly build a Google Data Studio report or run some analysis.

Resource

The spreadsheet used to illustrate this article is accessible here

Leave a Comment