Google Analytics Pivot Tables – The Super Duper Guide

 

Pivot tables are hidden gems in Google Analytics reports. They are extremely powerful data summarization tools and are commonly used in spreadsheet programs like Microsoft Excel.  Through pivot tables you can quickly summarize data in desired format and detect data trends that you can’t determine otherwise. Even being so powerful, they are not the default table views in Google Analytics reports which is a shame.

 

This guide consists of following sections:

  1. Getting Started with Pivot Tables in Google Analytics.
  2. Five Components of Pivot Table
  3. Pivot Table Case Study
  4. Using Pivot Tables with Advanced Segments
  5. Using Pivot Tables with Filters
  6. Using  Pivot Tables with Custom Reports

 

Getting Started with Pivot Tables in Google Analytics

Pivot tables are available as ‘table view’ option in several reports in Google Analytics. There are some reports in which you can’t view data in pivot table format like conversion reports (Goals reports, E-Commerce reports & Multi-Channel funnel reports).

To see data in pivot table format, head to a report like ‘All Traffic’ report and then click on the ‘Pivot’ button:

pivot-table-google-analytic

 

Five Components of Pivot Table

1. Primary Dimension – It is the first column of your pivot table.  The primary dimension selected in the chart above is ‘source medium’.

2. Secondary Dimension – It can become the second column of your pivot table if you select a dimension from the ‘secondary dimension’ drop down menu. By default no secondary dimension is selected.


3. Pivot By
- By default Google pivot the data by the primary dimension you have selected. Since the primary dimension in our case is ‘source medium’, the table has also been pivoted by ‘source medium’. You can however pivot your data by several other dimensions like visitors, technology, traffic sources, content etc:

pivot-by-google-analytics

 

Pivot Metrics – By default only one pivot metric is used in the pivot table. However you can select up to two pivot metrics. There are three categories of pivot metrics available in Google Analytics Standard reports: Site Usage Pivot Metrics, Goal Pivot Metrics and E-Commerce Pivot Metrics.

a. Site Usage Pivot Metrics (like visits, pages/visits, avg. visit duration etc)

site-usage-pivot-metrics

site-usage-pivot-metrics

Note:  Site usage pivot metrics are available by default only when you are viewing your report in ‘site usage’ explorer in Google Analytics Standard reports:

site-usage-explorer

b. Goal Pivot Metrics (like goal conversion rate, per visit goal value etc). To see such metrics you first need to click on the ‘Goals’ tab.

goal-pivot-metrics

Note:  Goal pivot metrics are available by default only when you are viewing your report in ‘Goals’ explorer.

c. E-Commerce Pivot Metrics (like revenue, transactions, average value etc). To see such metrics you first need to click on the ‘E-Commerce’ tab.

ecommerce-pivot-metrics

 Note: E-commerce pivot metrics are available by default only when you are viewing your report in ‘E-commerce’ explorer.

 

4. View Columns Buttons – Through these buttons you can navigate to other columns of your pivot table. However you can view only 5 columns at a time:

 

5. View Rows Buttons – Through these buttons you can view up to 500 rows of your pivot table at a time. Its functionality is similar to the row buttons you normally use in your analytics reports.

view-rows-button

 

Pivot Table Case Study

The usage of pivot tables is extremely broad and depends upon the insight you want to get. Let us suppose that you run various campaigns on international level and you want to determine how all the marketing channels are performing in each country. Follow the steps below:

Step-1: Go to all traffic sources report and then click on the ‘pivot’ button.

Step-2: Select ‘country/territory’ from the ‘Pivot By’ drop down menu to pivot the table by ‘country/territory’

Step-3: Select first pivot metrics to ‘visits’ and second pivot metrics to ‘bounce rate’ to determine the quality of traffic of all the marketing channels for each country.

Step-4: Select ‘Visitor Type’ from the ‘secondary dimension’ drop down menu so that you can determine the behavior of new and returning visitors. You pivot table should now look like this:

pivot-table-case-study

You can now easily compare visits and bounce rate of all the marketing channels for each country and for each visitor type (new and returning visitors).  This can help you in understanding and comparing the volume and quality of traffic generated by different marketing channel for each of your international target market.  You can’t get such type of data summarization in Google Analytics reports without using pivot tables.

 

Using Pivot Tables with Advanced Segments

Advanced segments can add many more dimensions to your multi dimensional pivot table and thus can make your pivot tables more robust. For example in order to truly understand the performance of various international campaigns, we may need to know how many visits included conversions and how many visits included e-commerce transactions for each marketing channel and for each visitor type.  We can determine this by applying following two ‘default advanced segments’ to our pivot table: ‘Visits with conversion’ and ‘visits with transactions 

pivotTables-advanced-Segments

Now we can get better understanding of the quality of traffic generated by different marketing channels for each country and visitor type.

 

Using Pivot Tables with Filters

The various filters available on the reporting interface make it easier to analyze large data sets. For example if I just want to analyze the traffic from various Google properties (organic, paid, referral, images etc) then I can filter out such data through a filter:

pivot-table-filters

 

Using Pivot Tables with Custom Reports

Pivot tables become extremely useful when you use them in custom reports and apply filters and advanced segments. The biggest advantage of using pivot tables with custom reports is that you can choose the pivot metrics you really want in your pivot table.  I generally use pivot tables in custom reports to analyze the data.

pivot-tables-custom-reports

Such type of data reporting is not possible in Google Analytics standard reports.

Other Posts you may find useful:

 

Join over 5000 subscribers!
Receive an update straight to your inbox every time I publish a new article.

 

About the Author:



My business thrives on referrals, so I really appreciate recommendations to people who would benefit from my help. Please feel free to endorse/forward my LinkedIn Profile to your clients, colleagues, friends and others you feel would benefit from SEO, PPC or Web Analytics.

 

 

  • zubair

    Hey Himanshu…..great post…..simple yet quite effective in analysing site traffic…I would also love to see some posts on analysing social media sites…

    Cheers!!!!

    • http://www.seotakeaways.com/ Himanshu

      Thanks Zubair. I will cover social media analytics soon.

  • http://floridawebcoaching.com Russ Schon

    Thanks Himanshu,

    I never really understood pivot tables before and I had no idea that they were avaliable in Google Analytics. This information will allow me to examine more information in the reports for my clients.

    Keep up the good work,

    Russ

    • http://www.seotakeaways.com/ Himanshu

      Thank you for the kind words Russ.

  • http://www.msgseosites.in Sandeep Kumar

    Hi Himanshu, Your posts always very informative. I already subscribed with your blog and fan page, as you are expert in Google analytic so i don’t want to leave any post from you. You have good command on both analytic and blogging. Looking for your next post. Before this i don’t know about pivot tables in GA and their scope but i get it. Thank you very much.

    • http://www.seotakeaways.com/ Himanshu

      Thank you Sandeep. I am looking forward to write more about analytics.

  • http:www.cxfocus.com Tim Leighton-Boyce

    Thank you. This is really useful. Your point about using pivot table with Custom Reports came as a revelation to me. That’s something I need to explore. Very good tip.

    • http://www.seotakeaways.com/ Himanshu

      Thanks for the kind words Tim. Glad you find the post useful.

  • http://www.fijdesign.com Tom Andrews

    Excellent very useful, find things like this come into their own for retail sites with online stores where very detailed analysis is required.

    • http://www.seotakeaways.com/ Himanshu

      Thanks Tom.

  • http://www.gerardrathenau.nl Gerard Rathenau

    Thanks for sharing this knowledge. I have a question:

    Do you use pivot tables to analyze landing pages?

    • http://www.seotakeaways.com/ Himanshu

      yes sometimes.

  • http://www.searchmechaniks.co.uk Ava

    Great post once again, i have a suggestion for you to start a basic guide for newbies about Google Analytics(videos and screencasts would be great too), there is a lot of demand of basic guides, internet s just filled up with advanced tutorials.

    • http://www.seotakeaways.com/ Himanshu

      Thanks for your suggestion Ava.

  • https://twitter.com/mikegracen Mike Gracen

    Great stuff Himanshu!! As usual, reading just one of your posts gets my mind going in a hundred directions on possible implementations :-)

    • http://www.seotakeaways.com/ Himanshu

      Thanks Mike. Glad you like this post.

  • Graeme Stuart

    This is really insightful. Hadn’t even considered use of pivot tables with GA before. Thank you very much.

  • Diaz

    Best blog Ive found regarding Analytics…
    I spent all my evening reading your posts, just when Im about to finish I find another 3-5 interesting posts and I start again jeje

    Now THIS will improve vastly the reports I send to my clientes, thanks Himanshu!!

    Would you recommend any books about analytics, besides the ones from avinash?
    Have you thought about writing a book?

    • seohimanshu

      Thanks for the kind words. I would recommend reading this book: “Microsoft Excel 2010: Data Analysis and Business Modeling 3rd Edition”. Knowledge of statistics is essential in analytics. Also read this post: http://www.seotakeaways.com/beginners-guide-maths-stats-web-analytics/

      • Diaz

        Thanks, I’ve got decent statistics knowledge, and I’ve just got the book.
        So… that means you haven’t thought about writing a book I guess!

        • seohimanshu

          The problem with writing a book is that it gets outdated in few months. I would rather update my blog posts and keep them up to date.

  • stuart mcmillan

    Great post, thanks! Do you know if you can save the pivot table view as part of a custom report? It would be nice not to have to re-pivot every time I went to the report

    • seohimanshu

      I save pivot view of a regular/custom report as a shortcut report. Then you don’t need to re-pivot them every time. In fact you can save any settings/view as shortcut reports.

      • stuart mcmillan

        Thanks, too tip!

        Stuart

  • Mirage

    Thanks for your thorough explanation Himanshu. Really helpful!

    I have noticed that with exporting pivot tables using Google Analytics to 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!

    • seohimanshu

      If you can’t do something directly through the interface then you need to use the GA API. It provides lot of flexibility in terms of data extraction.