Have you been reporting your averages wrong?

If you work in search and you’re reporting on average keyword rankings without taking search volume into account, then you have a problem.  I see this miscalculation a lot, from fresh graduates to experienced search marketers; and whilst it’s an easy mistake to make, it’s an easy one to solve too.

The Problem

Firstly let’s clarify what we mean with an example. Using the information below, work out what you think the average rank is for yesterday and today:

If you said 15.5 and 8.5 then you’re doing yourself a massive disservice when you’re reporting to your client or boss.

Why?

Let’s have a look at the results; the  biggest volume keyword in your keyword group has dipped in ranking yet you’ve seen an overall improvement?  It doesn’t make sense.

The problem normally arises when we forget to consider search volume. When  reporting your averages you should be looking at your rank on a ‘per search’ level and then working out your collective average rank.

The better answer is in fact Yesterday: 5.3 and Today: 6.  This makes more sense given your two biggest keywords are now at position 6.

Here's how to fix it:

Create a column to the right of each ranking column and multiply the keyword’s ranking by its search volume:

The total up the search volume column along with your two new columns:

To get a more reflective average rank you need to divide your new columns by the total search volume column.  This will give you a weighted average rank which now takes the search volume of each keyword into account.

You can use this to understand average rank of competitors across multiple keyword groups in a super accurate and clear methodology.

Use a Pivot Table for Keyword Sets:

If you report on multiple keyword sets against lots of competitors, then the above manual method gets very clunky very quickly.  So, to help you scale this I will explain how to carry this over to a pivot table in Excel so you can repeat this over as many variables as you need to.

Follow these steps:

1. Create the new columns for each area you need to report on (multiple days or competitors) and create your pivot table
2. Go to Analyze then Fields, Items and Sets and click Calculated Field
3. Now call your new field something relevant like Yesterday avg Rank and in the Formula field you will need to divide Yesterday’s Rank x Volume by Search Volume, like below:
4. Repeat for any other averages you need to find out

All data will be carried over to your pivot table so you can now filter away!