Using Excel Pivot Tables for SEO analysis

There are a number of ways to measure how your SEO competitors are performing in Google using Excel. You’ll need to have ranking data for both you and your competitors, as well as search volumes of your competitors. I’ll also cover how to do this to analyse link profiles.

How to create a pivot table

To create a pivot table, you need a regular table of data. Click within the table of data, then click Insert>Pivot Table.

Insert a pivot table

In the dialogue box that appears, ensure the correct data source is chosen. By default, this will be the table that your cursor was in before inserting the pivot table, but it can be changed.

Choose whether you want to place the pivot table within a new worksheet or in another worksheet and press OK.

Creating a pivot table

I’ve placed my pivot table within a new worksheet. You’ll see a list of fields that correspond to your columns, and some empty boxes.

Pivot table fields

On a generic level, drag and drop the fields you want where you want to see them. This post will go into some specific ideas for set-up later.

  • Filter means “how would I like to split filter this data”
  • Columns are whatever you’d like the columns of your new table to be
  • Rows are what you’d like the rows of your new table to be
  • Values are what you want to be calculated in the cells

It’s customisable with how you want things to be calculated (count, sum, average, etc.) and how you’d like your data to be displayed (for example, as a percentage or as raw numbers).

Rank spread tracking with Excel

To see the spread of keyword rankings, I’d recommend having source data with at least four columns: Keyword, Search Volume, Rank, and Website (where the Keyword – Website pairs are unique).

Create a pivot table with the following criteria:

  • Website as the column
  • Rank as the Rows
  • Count of Rank as the Values
    • Note: This may default to “sum”. To amend this, click where it says “sum of…” select “Value Field Settings” and change this to “Count”
  • Optional: You can add filters for things like keyword category or search engine if you have data regarding those

Selected pivot table fields

The table you create will be quite long and will show you the number of times a website ranks in a particular position.

Table showing the number of times a website ranks

A more useful way to see this may be grouped by page (i.e. number of times they rank on page 1, page 2, etc.). Right click your left-most columns and select “Group”.

Grouping pivot tables

To group by the first 3 pages, I would suggest Starting at 1, Ending at 30 and by 10. Amend as required.

Pivot table - grouping by the first three pages

This then groups the pivot as such:

Pivot table groups

Pivot Table graphs tend to be ugly and confusingly labelled, so I’d recommend copying your data to a different table and creating a graph from that.

Graph showing keyword ranking split

Traffic estimate from keyword rankings and search volume

If you use the same table as before (with keyword rankings and search volume) you can create another column that calculates the traffic received for that keyword. You will need a click-through model to do this. I use Netbooster’s model.

To calculate estimated traffic this is Search Volume * CTR. To get CTR you’ll need to do a vlookup to grab the CTR that matches the ranking.

With this new column, you can create a pivot table that shows how much traffic each competitor got (and in each category if you have categorised your keywords).

  • Rows should be set to Website
  • Values should be set to Sum of Traffic Est.
  • Optional: Set columns to Category

Selecting categories to create a pivot table

Pivot table showing competitor traffic

As usual, you can create a graph to compare each website:

Graph comparing competitor traffic

Link profile analysis with pivot tables

It’s possible to build link profile analysis graphs with pivot tables in Excel. However, due to the ability to disavow links, they may not be very accurate. With some link analysis tools, it’s possible to use only the most recent data and analysis of this kind may still be useful to you.

I’d suggest using one of the following:

If you’re still keen to see older data you can use the full historic index for any of these.

Download a CSV of the links for the website you’re interested in and create a pivot table based off this CSV with the following settings:

  • Two filters – one for follow/no-follow and one for Date Found
    • Optional – Use Follow/No Follow as the Columns if you want to easier see the difference between followed and nofollow links.
  • Row should be the TrustFlow, Domain Rating, or Domain Authority of the source link
  • Values should be the same metric that’s in the row
    • Note: This may default to “sum”. To amend this, click where it says “sum of…” select “Value Field Settings” and change this to “Count”

Value field settings when creating a pivot table

With this pivot table you can see the number of links that have a specified TrustFlow/Domain Rating/Domain Authority.

If you want to see this for unique domains rather than links, amend your source data to include source domains (a text to columns should do this with “/” as the delimiter) and remove duplicate domains. You can then filter for the most recent links, and if you want to filter for only followed links you can do that as well.

Pivot table showing number of links

As is, this isn’t very inspiring, but you can create a graph that shows you the spread of TrustFlow for that website. Pivot Table graphs tend to be ugly and confusingly labelled, so I’d recommend copying your data to a different table and creating a line graph from that. You can also add data from competitor websites for comparison.

TrustFlow graph

As well as seeing the spread of trustflow/etc. you can use pivot tables of link data to quickly show the top linked to pages and the top anchor texts used.

Pivot table of linked data

If looking at anchor text, you may want to make sure all your anchors are in lowercase before creating a pivot table to count variants.

More Excel for Search

There are a plethora of ways to use pivot tables and Excel for SEO. If you want some more Excel tips for SEO I’d recommend:

Excel for Search: How to analyse keyword fluctuations

Have your keyword rankings really increased? How to track volatility

How to estimate search volume for adult keywords

How to get awesome actionable data from your events in Google Analytics

How to calculate your true Click Through Rate with Google Webmaster Tools

The Dangers of using Excel for SEO

Related Posts