Advanced Keyword Analysis with Excel

My previous post was all about keyword analysis using a basic template. This one allows to analyse more data.

I have covered most the below from my previous post, but since repetition is the mother of knowledge, I am repeating the same information.

That data can be 50 or 50,000 keywords. However, let’s cut to the chase and analyse the columns:

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.

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<.

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.

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.

Column K: It is splitted into two categories (Brand vs Non Brand); it may sound a bit obsolete, but when you have a mega brand, like Coca Cola the use of a keyword that a brand can be doesn’t seem such a bad idea after all. With a combined use of IF, ISNUMBER, SEARCH you will get the right indication depending on the keywords that you have in front of you.

Column L: Imagine that you have a brand, like a hotel. In this case you need to measure your keywords’ performance in terms of rankings. Again with the use of the same combined functions IF, ISNUMBER, SEARCH you will be able to measure your keywords’ performance in terms of rankings and search volume and see which ones under-perform.

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.

 

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.

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.

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….

Download now the advanced data/keyword analysis speadsheet (.xls) and until next time happy exceling and analysing…

UPDATED — (1.10.2016)

If you want to know how to use SUMIFs and COUNTIFs with a little bit of VLOOKUPs then download this spreadsheet:

Download now the updated advanced data/keyword analysis spreadsheet (.xlsx)

 

Also read...

Comments are closed.