Using Google Analytic Pivot Table to Track Average Ranking

There are many features and tools within Google Analytic that is under-utilize, one of these hidden gem we want to talk about today is the Pivot Table feature.  Although this feature is useful on couple of different reports within Google Analytic, we found it to be the most useful for the Queries report within the Search Engine Optimization section.  We will show you how to setup and use the pivot table for the Queries SEO report in Google Analytics.

 

First, where is the pivot table feature?  It is the last button on the tool bar below the graph.

After clicking on it, you will notice your report gets a couple extra columns.  How does this help you exactly?  Let me first show you how the standard Queries report looks like.

This is a pretty good overview of what query is getting good impression, clicks, and average position.  But the impression, clicks, and average position is calculated by combining all of the Google Properties (web, image, mobile, and video).  This vanilla report does not show me how my SEO effort is helping or hurting my website’s performance in relation to each of the Google Properties.  

With the Pivot Table feature, we can organize the report into something like this.

This pivot table shows the impression and average ranking for the queries across all the Google Properties.  (Web Search, Image Search, Mobile Search, and Video Search)

And this pivot table shows the impression and clicks for the queries across all the Google Properties.  (Web Search, Image Search, Mobile Search, and Video Search)

 

To setup the Pivot Table –

1. Open the report you want to pivot (in this case the Queries report)
2. Click on the Pivot button on the tool bar

3. Set Pivot by Google Properties

4. Then select from one of the pivot metrics

This is extremely useful for cross channel optimization strategies and cross channel focused marketers.  We can quickly access what channel / keywords is doing well and which one needs more effort, we can also pull this report by month and track the changes over time.  Looking at the example we can see that we have no present in the video search, so putting together videos campaign and optimizing a Youtube channels could be a strategies we focus on for the next quarter!

 

______________________________
Contributed by Chad Tsang, Sr. eStrategist