Our Authors

Barry Loughran

Director of Performance Content

About this Author

Barry has worked in search for more than 10 years and is passionate about using Owned and Earned media to grow clients' traffic. He's always looking for the latest and greatest examples of digital content and re-working new angles in to client campaigns to get the best results.

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: Create the new columns for each area you need to report on (multiple days or competitors) and create your pivot table Go to Analyze then Fields, Items and Sets and click Calculated Field 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: 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! 0 3 min read

Barry Loughran

About this Author

> Visit our Global Website

North America

Latin America

Europe, Middle East and Africa

Asia Pacific

Barry Loughran

About this Author

Have you been reporting your averages wrong?

> Visit our Global Website

North America

Latin America

Europe, Middle East and Africa

Asia Pacific

The Pace of Progress | dentsu 2024 Media Trends