Keyword Analysis with Excel

Who said that data analysis  on spreadsheet cannot be science and art at the same? Now make  data (keyword) analysis like a boss.

We often have situations where need to simply analyse the performance of your SEO project by comparing two different date ranges.

It can often be a time-consuming process because it can be possible that you need to analyse 50,000 keywords for a big brand that you handle, which make the situation even harder. The proper use of Excel can allow you to speed up the process of analysing all these keywords and actually spend on the actual analysis, rather than having to spend it on how to create this spreadhsheet.

This spreadsheet that I have created allows you to spend all your time and effort on the actual analysis.

All you have to do is simply, copy and paste the data.

Download this Data/Keyword Analysis template (.xls) , that will speed up your work and help you provide interesting insights about the keyword performance of your site.

All you have to do is to create to separate sheets  and another sheet, that will be your Mastersheet.

Screen Shot 2016-04-30 at 11.58.43

Obviously, since I am not using real keywords, with actual figures I am using the =RANDBETWEEN function, which generates random numbers based on the limits that I have given so that the figures that you get are closer to an actual case study.

Screen Shot 2016-04-30 at 12.01.45

In the Mastersheet, you will find everything you need to segment, analyse and interpret data.

Column B,C, E, F:  With the use of VLOOKUP, you are able to get the data  (rankings from columns B,C and search volume from columns E, F) from another sheet or from another spreadsheet that may be stored somewhere within the server.

Screen Shot 2016-05-01 at 08.33.02

Column D: It allows you to compare the date ranges from columns B and C. In my case I have place arrows, if anything is 0> or 0 or 0<.

Screen Shot 2016-05-01 at 08.37.43

Column G, H: Imagine having to analyse not 50, but 50,000 keywords. And you need to make sense from the amount of data that you have to analyse. The best thing you need to do is to group these keywords, depending on the ranking position they are. You can group them based on the page they are; namely Page 1 (for keywords in ranking position from 1 to 10) and so forth for the other pages. How can you achieve that? With the use of multiple IFs you can group keywords.  It can be a bit tricky until you are used to the implementation of  multiple IFs.

Screen Shot 2016-05-01 at 08.36.55

Column I,G: The logic of the use of these columns resembles to columns G, H. Basically you use again multiple IFs, but not for Pages, but for Positions on Page 1 only. In this way you will be able to for which keywords you rank for on Page 1 , which ones have search volume, which ones you can boost to reach Position 1.

All the above columns have to do with the data collection and partially give you a rough idea about your keyword performance.

The next 7 tables simply segment your keyword data furthermore so that you make a more effective analysis.

You don’t need to do anything in these tables. All the figures/calculation are spitted out in these tables, so you don’t need to do anything.

The first 3 tables give you an idea of how many keywords can be found in each page and the total search volume per page, for the dates that you compare. The 3rd table (“Difference”), tells you how your keywords perform when you compare them. Through the use of SUMIFs and COUNTIFs the results are spit out for you.

Screen Shot 2016-05-01 at 08.40.15

The fourth table is a result from column D (Progress – Comparison) – it tells you how many keywords have remained static, improved or reduced number of keywords in terms of rankings and the sum of improved/reduced/static keywords, gives you the Overall Gain. How you achieve all these? Through the use of COUNTIFs and SUM functions.

Screen Shot 2016-05-01 at 08.40.54

The last three tables, in grey colour, allow you to  analyse the data exactly like the top 3 tables but this time you can complete the analysis for keywords that are in Positions 1-10. For the calculation of Difference you use basic formulas that will give the result.

Screen Shot 2016-05-01 at 08.41.31

 

This spreadsheet only scratches the surface of what you can actually do… there are so many other things to do in more depth with more VLOOKUPs and functions that will be covered in future posts. Until next time happy analysing and excelling….

 

Also read...

Comments are closed.