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.
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.
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.
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
The table you create will be quite long and will show you the number of times a website ranks in a particular position.
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”.
To group by the first 3 pages, I would suggest Starting at 1, Ending at 30 and by 10. Amend as required.
This then groups the pivot as such:
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.
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
As usual, you can create a graph to compare each website:
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:
- Majestic’s Fresh Domain Backlink Analysis combined with Trust or Citation Flow
- AHREFS’ Fresh Index combined with Domain Rating
- Open Site Explorer’s Just Discovered Index combined with Domain Authority
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”
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.
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.
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.
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
