Pivot Google Analytics Data to Create User-Facing Views using Google Sheets

Learn to aggregate, sort, and transform data from Google Analytics into the format your team prefers for reporting.

All Tutorials


Requirements Not Met

Before continuing, you'll want to or with Blockspring.
Otherwise none of this is going to be any fun. Let us know if you need help in the chat window below.

Overview

Now that you can get up-to-date Google Analytics data directly in your spreadsheets, your attention can turn to how the data will be used. In this tutorial, we'll show you how to aggregate, pivot and transform your data into the the format your team needs for reporting. You will go through the following two steps:

  • Aggregate your live Google Analytics data and use it in a Weekly Funnel report
  • Pivot and filter your data to get exactly what you need for reporting

Once finished, you'll have the tools to clean up and present your Google Analytics data to the rest of the team, however they prefer to consume it.


Step 1: Aggregate your live Google Analytics data and use it in a Weekly Funnel report

Let's say you want to add some of your web analytics data to an existing report. Here's a template to try; you can add your own live data by replicating the Analytics Report tabs and learn by example.

In this example, we have a Weekly Funnel Report showing a simplified user acquisition path of a webpage. While the first step uses Google Analytics data, the "Accounts Created" and "Weekly Active Users" data comes from another source. You'd currently have to paste the other data in (or use another Blockspring function), but now you don't have that problem with the Google Analytics data - you can simply link the output in the Analytics Report tab to a cell in your existing report. Changes to the report configuration (e.g. altering metric definitions or date ranges) can be made without disrupting the look and feel of the existing report.

Since the Analytics Report that we set up above used the Source Dimension, we need to use the “=SUM()” formula to get the value we want into cell B4 (or for advanced users, remove the Dimensions input value to ungroup the output). The use of the "=IFERROR()" function helps provide a reminder to log into Blockspring, since the Google Analytics data won't load otherwise.


Basic reporting aggregation


Step 2: Pivot and filter your data to get exactly what you need for reporting

You can get the specific data you need for reporting by pivoting your Google Analytics report output using the built-in Pivot Table functionality of your spreadsheet. In the example, we illustrate how you can determine the most popular keywords by landing page for a webpage. Simply run an Analytics report including Keyword and Landing Page as Dimensions, and whatever you'd like to use as Metrics. Then do your pivoting in a new Pivot Table tab, and use cell references to produce a clean table for presentation in the main example report tab.

Pivot source data

Intermediate pivot

Pivot reference

You can also apply built-it pivot table functionality like sorting values by a metric or the dimension, or applying filters. This can be useful if you want to drive multiple parts of a report off a single data pull.

Next Steps

You've learned how to aggregate, pivot and transform data to produce the format that your team needs for reporting. With this knowledge, here are some ideas on what to do now:

  • Anywhere you are using multiple Analytics Reports, look for opportunities to use the same data to reduce the number of individual tabs (and data pulls). In many cases, adding additional metrics or dimensions, and using aggregation can keep you under API limits and reduce other complexities.
  • Pivot Tables are a great way to explore your data - try filtering, sorting and grouping by different dimensions to look for interesting trends you'd like to investigate further.


That's a Wrap

Thanks for using this tutorial series - we'd love to hear your feedback!

  • Did something not make sense at all or think you have a better way to explain a concept?
  • Maybe you want some more information about how to make Blockspring work for you?
  • Or perhaps you use other tools or services that you’d like to see Blockspring explore?

Let us know in the chat window below or join the Blockspring Community Slack