We love digital

Call 0845 485 1219

We love digital - Call and say hello - Mon - Fri, 9am - 5pm

Why Pivot Charts In Google Analytics Are Awesome, Including 10 Useful Examples

Anna Lewis

by Anna Lewis on 24th September 2013

Pivot TableIf you use Google Analytics but have never used the pivot chart, you’re missing out! There are some wonderful insights and cross-analysis opportunities that only a pivot can give you. Plus they’re actually not that hard to get your head round!

They are great for understanding the detail behind the data better, comparing performance, identifying trends and issues and making good decisions quickly.

To get started with Pivot Charts, navigate to a standard report then click the far right graph option: pivot chart option in google analytics

They look a little useless when you first click them as they show the same dimension on both axis, however, it’s easy enough to make it useful – just change the ‘Pivot by’ option to another dimension. Here’s an example of a nice simple one:

browser and os pivot chart in google analytics

Which Browser is used with which Operating Systems?

Note: it’s beneficial to click the line between the menu and the report in order to shrink it out of the way and give you more space to see the juicy reports in.

Let’s break it down:

  • Report: Audience > Technology > Browser & OS
  • Pivot by:  Operating System
  • Pivot Metric : Visits

There are additional things you can apply here but we’ll come to those once we’ve covered the basics.

As you can see, it shows the browser on the left hand side, just like normal. It then breaks down the visits for each browser by which operating system the user was on. We can see that Chrome is used on a range of operating systems whereas Internet Explorer is mainly used on Windows. This particular report could be used to help you identify platform combinations to test your website on.

Let’s look at another example. Say you have a website that offers its services in different cities and has a page for each location. In this example you can cross reference these pages against user locations, like so:

location pivot chart in google analytics

Are my location/store pages used by people with IPs in the same City?

Here we can see that the pages for a specific region do see a rise in visits from people in that location. However, it also highlights that although Birmingham is one of the cities sending the most traffic, the Birmingham page does not feature in the top viewed pages – this suggests that it is hard to find, doesn’t suit the users or perhaps the pages are split out into smaller areas around Birmingham which means the views are distributed across more pages.

You can also tell from this that people view pages about locations that they are not in. This is most likely due to the users’ IP being based in a different city to them but also the fact that people will look for services outside of their physical location in a number of situations. All reporting platforms will leave finding the reason for behaviour up to you, but at least this helps build up a picture so you can make the data come to life and depict user situations and identify how you can help improve the site for them.

Making It More Useful

So now that we’ve covered two nice simple examples, let’s take it up a level and introduce more data – if more data isn’t for you, skip to the bottom for some more examples to use. To make the most of this report you can add the following functionality:

  • Another metric
  • A secondary dimension
  • Advanced Segments
  • Filters
  • And you can even build them yourself in custom reports

Let’s take a look at each of these:

Two Metrics

Let’s start by adding another metric which will allow us to see two statistics for each column and row of the report. This is beneficial because one metric means very little on its own.  Introducing a second metric gives you a way of understanding the activity better, putting the first metric into context and spotting trends or anomalies.

In this next example the company has websites for a number of different countries, they each have their own Google Analytics account but we also use a master account to review the data for all the sites in one place. So to analyse whether or not the global strategy is working, we can use a report such as this one:

global domains location report pivot two metrics google analytics

Worldwide cross domain strategy review

  • Report: Audience > Demographics > Location > Country / Territory
  • Pivot by:  Hostname
  • Pivot Metric 1: Visits
  • Pivot Metric 2: Bounce Rate

Although I’ve blurred the actual numbers you can see the concept behind this report; you hope for high numbers for the correct location and good bounce rates when users land on the right domain for their country but always keep a look out for surprises! A good variation on this report is the conversion rate metric too. Just click Goals or Ecommerce at the top and select conversion rate as the second metric.

Whenever I talk about analytics I can’t stress enough the importance of using more than one metric. Comparing information really gives it context and makes it much more useful.  Would you compare house prices without putting the data alongside how many bedrooms each has? So why look at visits alone and assume it tells you enough?

Secondary Dimensions

The next way to add additional information is to apply a Secondary Dimension. Hopefully you’re used to using these in standard reports, but if you’re not, they’re great for breaking the dimension down. A great example is to look at a keyword report and apply a Secondary Dimension for landing page:

secondary dimension

An example of a secondary dimension

And to use it in a Pivot Chart would do a similar thing:

example - keyword and landing page v city - trans and rev

Which keyword and landing page combo works best in each city?

  • Report: Traffic Sources > Sources > Search > Organic
  • Pivot by:  City
  • Pivot Metric 1: Transactions
  • Pivot Metric 2: Revenue
  • Secondary Dimension: Landing Page

Keyword and Landing page ecommerce data by City. This helps us to see which keywords and landing pages work in different locations.

Another great Secondary Dimension is ‘Visitor Type’ which breaks out new and returning visitors, like so:

secondary dimension for visitor type

Are returning visits from certain keywords better than their new visit counterparts and where are the hidden keyword gems for successful new visits for each city?

  • Report: Traffic Sources > Sources > Search > Organic (advanced filter to remove not provided)
  • Pivot by:  City
  • Pivot Metric 1: Transactions
  • Pivot Metric 2: Revenue
  • Secondary Dimension: Visitor Type

The next step to make the two above examples even better is to introduce Advanced Segments…

Use Advanced Segments For Better Analysis

As I said before, there are ways to make the data even more granular, which can be done very well with advanced segments. They also present the data very clearly.

Combining the above two examples, we can review keyword and landing page ecommerce results per country, while comparing new and returning traffic for each, like so:

secondary dimension + advanced segment

Differences in new and returning traffic for source / mediums in different countries

  • Report: Traffic Sources > Sources > All Traffic
  • Pivot by:  Country
  • Pivot Metric 1: Transactions
  • Pivot Metric 2: Revenue
  • Secondary Dimension: Landing Page
  • Advanced Segment 1: New Users
  • Advanced Segment 2: Returning Users

The next example uses three advanced segments to show mobile, desktop and tablet traffic broken down by source / medium and landing page, to help you plan a mobile marketing and website strategies:

best landing pages per medium per device type

Mobile strategy planning

  • Report: Traffic Sources > Sources > All Traffic
  • Pivot by:  Landing Page
  • Pivot Metric 1: Transactions
  • Pivot Metric 2: Revenue
  • Secondary Dimension: None
  • Advanced Segment 1: Desktop Traffic
  • Advanced Segment 2: Mobile Traffic
  • Advanced Segment 3: Tablet Traffic

Now here’s an example of a similar report but with a secondary dimension for landing page and breaking the data down by City for location strategy planning:

full combo - medium by device and landing page and city

Local / National mobile strategy planning

  • Report: Traffic Sources > Sources > All Traffic
  • Pivot by:  City
  • Pivot Metric 1: Transactions
  • Pivot Metric 2: Revenue
  • Secondary Dimension: Landing Page
  • Advanced Segment 1: Desktop Traffic
  • Advanced Segment 2: Mobile Traffic
  • Advanced Segment 3: Tablet Traffic

While we’re on the topic of mobile analysis, if your website uses a mobile subdomain, this report is great for finding out whether or not mobile users on each device are actually happy using your mobile subdomain or whether they prefer to use the desktop version of the site:

mobile sub domain analysis

Does my mobile subdomain work well on all devices?

  • Report: Audience > Mobile > Devices
  • Pivot by:  Hostname
  • Pivot Metric 1: Visits
  • Pivot Metric 2: None (add bounce rate for more detail)

Using The Data

Once you’ve created your first pivot table you will realise that they are hard to manage within the interface itself. I definitely recommend using the export csv function in order to be able to manipulate the data to your heart’s content!

For even more information about Pivot Charts in GA, I found this super duper guide by Himanshu Sharma when I was halfway through the above post, so there’s extra tidbits of information on there if this hasn’t quenched your thirst for awesome reports enough yet.

What Do You Like To Use Pivot Charts For?

There are so many ways to combine data and different pivots suit different websites,  I can’t possibly come up with every combination so it would be great to hear the ones that work well for you. Please leave your examples in the comments for everyone to enjoy.

Anna Lewis

Anna Lewis

Our resident analytics specialist is Anna Lewis. Anna is unbelievably attuned to anything analytical and can fill you in on all the latest news, tips and advice to get ahead in this evolving market.

down arrow

Your Free Whitepaper

The Practical Guide To Google Analytics For Businesses (2nd Edition)

The Practical Guide To Google Analytics For Businesses (2nd Edition)

Download this whitepaper now and get a new one every month!

Download »

6 Comments

  • Thomas Jacquel 24th September 2013

    Great post Anna, I agree with you: Pivot Charts in GA give great insights! Tom

    Reply to this comment

  • Peter O'Neill 25th September 2013

    Hi Anna,

    Nice post but you missed out on the best GA pivot table (and my favourite report). The aim is to get:

    * Report: Entry Point (with pages grouped)
    * Pivot by: Medium
    * Pivot Metric 1: Entrances
    * Pivot Metric 2: Bounce Rate (or a conversion rate)

    It can be set up through a secondary Profile/View with page names grouped to their initial directory (e.g. product pages, homepage, blog posts, etc) or using a Custom Report with a first dimension of Page Path Level 1 (just realised you can do this with a custom report).

    The amount of insight into the intersection of traffic sources and landing pages based on both the quantity and quality (however defined) of traffic is incredible.

    Peter

    Reply to this comment

    • Anna Lewis

      Anna Lewis 25th September 2013

      Thanks for the suggestion Peter, that’s a great Pivot. Are there any others you use on a regular basis?

      Reply to this comment

      • Peter O'Neill 25th September 2013

        Thats the main one, like you say, the visualisation is not great within GA.

        It’s not a true pivot but my other similar approach is to use a horizontal funnel for a single dimension

        e.g. for each browser (or country, traffic source, entry point, etc)

        - visits
        - % get to product
        - % product => cart
        - % cart => checkout
        - % complete checkout

  • Samantha Noble

    Sam Noble 28th September 2013

    Love this post! I normally export data to excel and pivot from there. Didn’t realise you could do within GA. Learn something new everyday.

    My favourite quote:

    “Would you compare house prices without putting the data alongside how many bedrooms each has? So why look at visits alone and assume it tells you enough?”

    Reply to this comment

  • Mirage 2nd December 2013

    Thanks for your thorough explanation Anna. Really helpful!

    I have noticed that with exporting pivot tables using Google Analytics excel, it only exports the 5 columns which you have in your view at that moment. Do you know a method to export all columns in your pivot? Or maybe a work-around to get this done?

    Tnx!

    Reply to this comment

Subscribe To The Koozai Blog