How to Analyse Keyword Fluctuations

We published this a long time ago…

Some of the content in this post might be out of date, and some images and links may no longer work.

Discover who we are and how we may be able to help you today:

Excel is a much more powerful tool than we often give it credit for. In fact, with the tools and reporting that can be made with Excel, I think its uses far exceed its initial purpose.

So, how can we use Excel within Search? We’ve already shown one method in a previous post that showcases how we can utilise simple(!) stats to show whether rankings have increased, but what else can we do with Excel?

Investigate rankings within niches

Retrieve your keyword rankings for any two given days into an Excel workbook. Put each day’s rankings into a different sheet to be organised. Now, in a master sheet, have your master keyword list formatted as a table. Formatting as a table allows you to filter your table to investigate keyword categories, providing that you have categorised those keywords.

Add new columns to your table for the rankings. We can do this by simply inserting a new column to the right of the final column. Now transfer your rankings into the table using the VLOOKUP function. This function matches strings between tables so that we can link data together. In this case, we are matching keywords with their rankings. Your VLOOKUP function should look something like this:

=VLOOKUP([Keyword], [Table number], [Table Column Number], FALSE)

If you’re still having issues, ExcelJet is a good source of information for all the functions of Excel. This is the VLOOKUP example.

Add a new column to the table so you can calculate the differences in the rankings, or even traffic. Hopefully you’ve categorised those keywords, so you can investigate each niche. What we have now is a data source to analyse: use a PIVOT table! Insert your PIVOT table, using Insert > Pivot Table.

Click on the whole table to choose as the Pivot table source.

Drag Keyword Category into the Row box.

Drag your Change column into the Values box. Make sure you have this value as a Sum.

And there you have it, a handy table that shows which keyword categories have increased or decreased the most in rankings or traffic, or whatever change your looking for. A valuable, easy, and quick analysis we can all do in Excel. Category 5 giving us the greatest increases here!

Advanced Excel

If your keywords have secondary or multiple categories, or you have loads of data, you can be as granular as you want to be. Drag as many variables into the columns and rows for a more detailed comparison across as many niches as you are investigating!

Summary

Putting your data into a table means it can be filtered and referenced easier.
The VLOOKUP function makes linking data much easier.
Pivot tables make analysis of the data simpler and easier to gain insight.

Click here for more information on how we can help with your insights and analytics, and put more useful tools, including Excel functions like these, into practice.