Calculating Search Engine Visibility Percentage

Love it or hate it Excel is one of the most useful applications for SEO reporting. An essential function that we utilise it for at Bruce Clay Australia are for calculating “Search Engine Visibility Percentage” for client and competitor websites.

Search Engine Visibility Percentage

Search Engine Visibility Percentage (SEV%) is a very simple calculation that you can perform to see what percentage of your keywords are ranking in the top 10, or 20 or any other position you’d like to keep tabs on. We use SEV% as one mechanism for quickly visualising overall ranking movements. In its simplest form, it can be calculated like this:

Number of ranking keywords/Total number of keywords monitored

Unfortunately calculating SEV% like that doesn’t really give you any useful information. What would be more useful to know would be the SEV% for the number 1 rankings, number of rankings in the top 3 or number of rankings on the first page of Google as well as that of your competitors. Enter Excel and the “countifs” formula.

Calculating Simple SEV% for Top 10 Ranking Keywords

Let’s start out with one website to begin with. We extract our ranking data from our own SEO Tools however; ranking data comes in many forms so we’ll use a simple example to start. Here we have a list of 20 keywords and their respective rankings in Google.com.au, this example and all others are included in the spreadsheet linked to below, so if you understand the concepts of SEV% calculation you may just want to skip to the chase. Excel workbooks (for 2003 and 2007) are included at the end of the post.

To calculate the Top 10 SEV% of this keyword list in Excel we need to count the number of rankings in the 2^nd column that are between 1 and 10 (inclusive) and divide by the total number of keywords (20). Here’s how we do it:

Assuming your keywords are in Column A and your rankings are in Column B, add the following formula into Column D (you can put it anywhere you like, just not in Column B as you will create a circular reference).

=COUNTIFS($B:$B,">=1",$B:$B,"<=10")/20

The countif formula will look at the entire of Column B and count the number of cells that have a number greater than or equal to 1 and less than or equal to 10, then it will divide that number by 20, which is the total number of keywords. The result of this formula is a SEV% of 55% – fifty five percent of those keywords are in the top 10.

Getting more ranking brackets from the same data

We can easily change this formula to expand or reduce the SEV% brackets, here’s what top 3, 5, 20 and 30 would look like:

Top 3

=COUNTIFS($B:$B,">=1",$B:$B,"<=3")/20

Top 5

=COUNTIFS($B:$B,">=1",$B:$B,"<=5")/20

Top 20

=COUNTIFS($B:$B,">=1",$B:$B,"<=20")/20

Top 30

=COUNTIFS($B:$B,">=1",$B:$B,"<=30")/20

Top 1 is actually much easier, because we don’t have to look for number occurring between other values. Since there is only one condition to search for (numbers that equal 1), we can use a countif formula instead of countifs.

Top 1

=COUNTIF($B:$B,1)/20

Put it all together and you get something like this. If you’ve downloaded the example spreadsheet, refer to the Simple SEV% tab.

Here it is again in formula view.

Nice, so there you go, you’ve calculated top 1, top 3, top 5, top 10, top 20 and top 30 search engine visibility percentages for your website. Now you can repeat the process for your competitors…or do it all at once.

Repeating this process for your website and your competitor websites is a bit annoying, not to mention time consuming, especially if you need to do it on a regular basis so let’s delve a little deeper by adding some more conditions to the data – dates and competitors.

Calculating Competitor SEV%

We’ll use the same data as before but I’m going to reduce the number to 10 keywords so I can illustrate the process in screenshots effectively. You can use as many as you want in your version, there are no limits imposed by the formulas we’re going to use (only by Microsoft Excel).

Add a couple of columns in front of Column A so we can add dates and website names. We’ll also need to build another small table off to the right to calculate competitor’s SEV% as well as our own. We’ll need to check the SEV% for ranking data for this month and last month, so add dates to that new table too.

We’re going to be pasting our ranking data and our competitor’s ranking data in the same columns, so we’ll need to make sure each value we paste in has an indication of who owns that ranking, that’s why we have a website name column. Each ranking will also need a date associated with it so that we can calculate rankings from a specific website on specific date. Now let’s add some data:

I’ve colour coded the competitor data here so we can see the differences easily. Notice that the entries I have used in Column B (Website Name) are exactly the same as the headings used in Column F. This is important as the formulas we’re going to use will require these to be the same. The date row also matters so make sure they match.

OK, time to add the formulas. Once again, Top 1 is easier to calculate so let’s start there. In cell G5, add the following formula to give you Top 1 SEV% for MySite:

=COUNTIFS($A:$A,G$4,$B:$B,$F$4,$D:$D,1)/COUNTIFS($A:$A,G$4,$B:$B,$F$4)

You’ll notice that this formula is a good deal more complex that what we have used previously. The reason for this is now we are dealing with multiple dates as well as competitor data being mixed with our own. To explain this formula a bit, it; looks in Column A and counts the number of cells that match the date in cell G4 (there are 20), then looks in Column B and counts the number of cells containing the text in cell F4 (this excludes the competitor values previously counted in the first part of the formula), next it looks in Column D and counts all cells with a value of 1.

The next part of the formula is basically the equivalent of the /20 in the simple SEV% calculations. In this example the number of keywords have been reduced to 10 per website, so we cannot divide by 20, we need to divide by 10. Since it’s logical to assume that the number of keywords we’re monitoring will change, we can get around having to manually update the formula each time the number of keywords increase or decrease by using this last half of the formula. So if we check 10 keywords this month and 20 keywords next month the SEV% calculation will be accurate, if we need to drop a keyword or two we can just delete them from the list and the formula will adjust.

Now let’s tackle top 3, which is a similar formula but with the added conditions of greater than or equal to 1 and less than or equal to 3.

Top 3

=COUNTIFS($A:$A,G$4,$B:$B,$F$4,$D:$D,">=1",$D:$D, "<=3")/COUNTIFS($A:$A,G$4,$B:$B,$F$4)

Now copy that formula down to your top 30 and modify the “<=3” to fit the ranking brackets we need:

Top 5

=COUNTIFS($A:$A,G$4,$B:$B,$F$4,$D:$D,">=1",$D:$D, "<=5")/COUNTIFS($A:$A,G$4,$B:$B,$F$4)

Top 10

=COUNTIFS($A:$A,G$4,$B:$B,$F$4,$D:$D,">=1",$D:$D, "<=10")/COUNTIFS($A:$A,G$4,$B:$B,$F$4)

Top 20

=COUNTIFS($A:$A,G$4,$B:$B,$F$4,$D:$D,">=1",$D:$D, "<=20")/COUNTIFS($A:$A,G$4,$B:$B,$F$4)

Top 30

=COUNTIFS($A:$A,G$4,$B:$B,$F$4,$D:$D,">=1",$D:$D, "<=30")/COUNTIFS($A:$A,G$4,$B:$B,$F$4)

Here’s what it will look like in formula view.

What we can do now is reuse these formulas for the competitor data. So, select the range from G5 to G10 copy and paste them at G14. At this stage the data is still “MySite” data because we are looking up on the cell $F$4. The quickest way to change this to competitor data is to find and replace $F$4 (TheirSite) with the cell where the competitor name is, $F$13.

Now we have the correct data for competitors we can simply drag the top 1 to top 30 values to the right to get the next month’s SEV%.

And here’s the finished product.

And that’s about it, as long as you make sure your website names and dates match up like they should you can add as many keywords and competitors as you need to.

Now that you have produced the raw SEV% data, you can use Excel’s charting functions to visualise and compare your websites SEV% against your competitors.

This is obviously just a start to how you can cut and visualise your ranking data and can be combined with other metrics to provide richer reports and additional insight into your website’s SEO performance.

Search Engine Visibility Calculations – Excel Version 2003

Search Engine Visibility Calculations – Excel Version 2007

See irobinson's author page for links to connect on social media.