Looking to dazzle your colleagues with some mad spreadsheet skills? I have two words for you – pivot tables!
Pivot tables are tools that are used to help summarize data and identify and predict trends. Within Google Analytics, pivot tables are only available in the “table view” option in most reports (it’s not the defaul in every table view.
Before creating your first pivot table, it’s important to know all the features availale for selection and modification:
Components of Pivot Tables in Google Analytics
There are six components of a pivot table in GA; if you’re not familiar with this terminology do explore the Google Analytics glossary at Analyst.rocks.
The Primary Dimension is the first column of the pivot table. In the image below, the primary dimension is “Source/Medium”
The Secondary Dimension enables analysts to change the view of their data (pivoting) using a seperate dimension. This is available from the drop down menu (only available in the pivot table view). No secondary dimension is set by default however. This means you won’t be able to experience all the wonders if you don’t select a secondary dimension.
The Pivot By function enables you to pivot the data available in the table by the secondary dimension. Any dimension canbe chosen including Acquisitions, Behavior, Technology, Users, etc.).
Four categories of Pivot Metrics in Google Analytics are available, including:
- Summary pivot metrics (percentage of new sessions, goal completions)
- Site usage pivot metrics (bounce rate, session duration)
- Goal pivot metrics (goal conversion rates, per session goal value)
- Ecommerce pivot metrics (revenue, transactions, average order value).
View Column buttons enable analysts to navigate additional columns of their pivot table. Only five columns can be viewed at one time.
Finally, the View Row buttons provide control over how many rows can be viewed at one time. The maximum number of rows is 5000.
NOTE: Keep in mind that “views” in this context should not be confused with the property views. Property views are a set of specifications for tracking traffic on a domain.
Google Analytics Pivot Tables In Action
The components listed above are critical to being able to use pivot tables effectively. Become familiar and you will be able to interact with your data in very useful ways.
Start interacting with a pivot table in your own Google Analytics account by doing the following. First, open a report such as “Source/Medium” which is located under the Acquisition >> All Traffic. Then, click on the pivot table button.
Now that you’re in the right place, take a look at one use case that shows the potential in using pivot tables in GA.
Say, for example, that we have a client that has an international audience base. This clients wants to analyze marketing channel performance by country. In this instance, we would view the traffic sources report and change the table view to pivot table.
The next step would be to the select “country/territory” dimension within the drop down menu. This will give us a list of countries where the website received traffic). Then, we need to set our pivot metric. In this instance, let’s use “sessions” as the first pivot metric and then “bounce rate” in the second pivot metric. Combined, these data points will enable us to make an assessment of traffic quality for each country/territory. Finally, set the secondary dimension to “user type” so we can determine the behavior of new and returning users.
This setup outlined above will enable analysts to compare sessions and bounce rate of different marketing channels (or campaigns). This applies for each country and for both new and returning website visitors).
There’s a lot more that can be achieved by using pivot tables in Google Analytics. Let this serve as an beginners introduction to all that’s possible.