Conducting a Keyword Performance Audit

Tim Hopma 6 years ago

There has been a lot of recent talk about the business of SEO and the implications of measuring or attempting to measure ROI from SEO.

So I thought it may be helpful to provide a quick process for spot checking keyword performance as it directly relates to search traffic. In this post I am going to go over a simple procedure for analyzing organic traffic performance versus organic traffic potential.

Once you’ve completed your keyword research and have started to acquire more traffic to your website via search, it is important to gauge where your SEO investments are working and also identify where there is greater potential for both engagement and additional traffic.

Get ready for some fun old fashioned excel and analytics analysis. Here we go.

Dive Into Google Analytics

As mentioned above, for the purposes of this post we are going to look only at organic traffic.


Go into your analytics account, select your profile of choice, and drill down to your organic traffic via Traffic Sources > Sources > Search > Organic

For this particular audit we are going to look at just the top 500 traffic driving keywords over a 30 day period, for deeper analysis into performance over longer periods of time simply adjust the time period within analytics.

Tip: Stick to calendar periods for analysis; 1 month, 3 months, 6 months, 12 months, etc.

Go down to the bottom right-corner of the screen and set it to show rows = 500


We will also want to take a look at the URL’s that are acquiring these visitors, so click the ‘Secondary dimension’ button and under Traffic Sources, click ‘Landing Page’


Now export to csv or excel (xlsx)


Crack open excel

In sheet 2, “Dataset 1,” take a few minutes to organise your spreadsheet for the analysis;

1.      Add Filters to Row 1 using Data > Filter

2.      Select the dropdown in Column A (Keyword), click ‘Select All’ to deselect all, and then click the checkbox for (not provided)

3.      Delete all of these rows (not provided) OR take some time to deal with them.

4.      Click the dropdown in column A once more and select “Clear filter from “Keyword”

5.      Delete columns E (Avg. Visit Duration) and F (% New Visits).

6.      Create a new column C (to the left of the existing column C) titled Potential Visits.

7.      Create a new column E (to the left of the existing column E) titled Performance.

At this point it should look like this:


If it does you’re on the right track - save yourself some potential frustration and click save.

Now we need to go out and get all of the search volume data for your keywords, so fire up the google keyword tool and copy and paste your list in the same order into the tool, be selecting all of the cells in Column A, take note of the count of keyword you are bringing into the keyword tool - since you removed your (not provided) data the count will no longer be 500.

Note: Make sure you change the match type from the default broad match to [exact] match to give you a better handle on the search potential within your geographic area.

Now export using the ‘download’ button at Download > All Search Results > CSV for Excel.

Open up your freshly downloaded data

Open up the file in excel and select the same count of cells as you pasted in from the top down (AdWords will bring along other suggested keywords but we are only looking for the search volume from our existing traffic driving keyword population).

Copy and paste these counts into C2 in your original analysis document.

Now in column E (your performance column) set cell E2 = D2/C2 and then click the % button to convert this to a percentage.

Select column x and set conditional formatting (specific) to highlight the highest percentages in green, then do the same for column x using the opposite to show the lowest numbers in green.

Notice anything strange?

Cells with #VALUE! means that you are getting traffic for keywords that Google does not return any search volume for, also there’s a good chance that you have over 100% of traffic for some of these keywords.

Does this mean that you are acquiring every single search for that keyword? Heck no, but I suspect this means that you are capitalizing on trends that are not constant enough to be reflected within Google’s keyword search volume estimates.

Again, this is just a hypothesis, but I did do some spot checking on a few of my top-performing keywords using Google Trends, and here is what they turned up:

For the keyword ios6.1.1:


The search volume is literally at the top of the trends index (100) starting right in the beginning of 2013, and then looking at the geographic area:


100% in Japan, showing that not only did this keyword gain search volume in the very recent past, but that this search volume is siloed within Japan only.

For the term: htc j butterfly htl21 ??? (HTC j Butterfly HTL21 is a cell phone, and ??? means ‘bug’)


Google Trends reports that there is “not enough search volume to show graphs,” which is surprising considering this term alone sent almost 1,000 visits (996) via Google to our website over the past 30 days. And it is safe to assume that we are not acquiring 100% of related searches for the terms, so the current search volume must exceed 1,000 local searches.

And then for the keyword: f-10d ??? (translated: f-10d bug, f-10d is a cell phone from DoCoMo)


Which makes a little more sense, since Google estimated 720 searches per month, even though we’ve acquire 1,479 visits in the past 30 days.

This leads me to believe that the Google keyword tool is inaccurate for estimating recent trends in search volume when they are contained within specific geographic regions.

Time for the analysis

To get started click the arrow in column E (Performance) and select ‘Sort Smallest to Largest’, this will show you where your SEO efforts are the weakest, and where the most opportunity lies.

To take this one step further, start at the top and begin scanning for low bounce rates )I recommend looking out for anything under 60%) as these represent your most effective content pages and should be used to inform your link building and promotional priorities.

For example, in my data set, the keyword ???? receives 22,000 exact local searches per month in Japan, yet over the past 30 days we have only acquired 42 of them, but the average bounce rate here is 38%, meaning that we are doing a good job of engaging visitors that land on this page.

The takeaways

Create a new sheets in excel and select both your stars and your duds in terms of keywords, recreate the same headers in the file and make sure when you are copying you select all of the cells in the row that contain data.

What you are doing here is creating a repository of both your top performing keywords, the ones with the highest performance percentages, looking specifically for high bounce rates representative of opportunities for content and experience improvement.

The second sheet is the opposite, where you are looking to suss out your pages that have low bounce rates and low performance scores, which represent the pages that are strong on content and experience but weak from an SEO perspective. These are your prime candidates for additional link-building or to be used as landing pages from other inbound channels such as email or social.

Stay in touch with the Zazzle Media family

Sign up for our monthly newsletter and follow us on social media for the latest news.

  • simonpenson

    Great tactical piece nick_eubanks and thanks for taking the time to pen it. What would your fuller keyword review look like? Or to put it another way what would your next move be after pulling this data?

    • @simonpenson A more in-depth review would look at what the specific organic sources were that were sending the most qualified traffic and then look to improve both the link profiles and content engagement on those pages. What is immediately available to you after finishing this quick process it a short-term road-map for which pages are doing well at setting expectations from search (seen through low bounce rate) as well as which pages need to be improved, especially if they are top or middle of the funnel conversion pages.

      • simonpenson

        @nick_eubanks makes sense. Do you ever tie conversion data into that mix?

        • @simonpenson Yup, but when looking deeper at traffic related to conversion data I use an approach more similar to the one I discuss here;

  • Hey Nick, under the “Open Up Your Freshly Downloaded Data” section you refer to column D as your performance column, and then divide c2 by b2 – D is actually the Visits Column, not the Performance column, and b2 is the landing page, not a value column.I might be wrong, but I don’t think so..

    • @ChristopherM Good eye Christopher, thank you. Fixed!

      • @nick_eubanks No problem, Nick. Really great article, some simple tactics cleverly combined to offer some really useful insights. I applied it immediately to a website I’m working on and found some useful areas to expand by looking at the actual traffic vs the potential traffic relative to the current ranking and what an increase in rankings would do to the actual traffic – you get my drift.Cheers mate, one of the more actionable and useful articles I’ve read lately.

        • @nick_eubanks You should also review “Select column x and set conditional formatting (specific) to highlight the highest percentages in green, then do the same for column x using the opposite to show the lowest numbers in green.” – Were X’s placeholders when you wrote the post, and just forgot to change to the actual columns? 🙂

  • Slava Rybalka

    Just tried this method – revealed some pages that need some link love. Thank you for a useful walkthrough!!!

    • @Slava Rybalka My pleasure Slava, glad to hear you found it useful!

  • hyderali_

    This was fantastic Nick!
    I just tried it & found some pages which were having thin content & high bounce rate. Also, due to this I found out the keywords on which my site is ranking. Yes, there were some visits on some keywords which were having very low or zero search volumes & vice versa. One more thing we can do is that we can remove the brand keywords from the keywords column & concentrating mostly on targeted keywords.
    One thing i didn’t understand is “Google trends” were you checking worldwide or the country you were targeting. I’m targeting india, so it was not feasible to me to check for worldwide & there is not a mention of the visits month wise in google trends, so how you were checking monthly visits from trends?
    Thanks for the post.

    • @hyderali_ Hey Hyderali – Thanks so much for the comments.In this instance I’m using Google Trends purely to spot check against traffic from the AdWords keyword tool. I found it to be very strange that I was receiving upwards of a thousand visits per month in some cases where G’s keyword tool reported no average monthly search volume. 
      What Trends told me was that in some cases the search volume was so recent or so geographically-focused that it may not be included in the AdWords tool. It’s really just a small shred of validation that the AdWords tool is not always representative of keyword traffic potential.

  • Marius Fermi

    Woah, I’m about to go into data overload! Just got myself involved with a Buzzstream account and now this! Thanks @nick_eubanks you rock!

  • Nick Davison

    Thanks for this.  This is insightful.  However, when I ran this exercise for my website, I noticed my organic visits had a lot of keywords stating “(not provided)” – 45% actually had this.  
    Thus, the performance metric could be very misleading because I might be getting a lot more visits for a specific keyword but it’s been cloaked under the “(not provided)” label.  I’m thinking I would need to include the “(not provided)” keyword into the evaluation.
    Any thoughts about this?

  • Nick Davison Hi Nick. (not provided) has wreaked havoc on keyword data but there are some interesting ways around it. It will never be 100% accurate again, but it will provide you more actionable data than you had previously.
    One of the best things to do is to look at the landing pages associated with (not provided) queries. Under “Organic Search Traffic” you’ll want to click on (not provided) and in the “Other” dropdown select landing pages. Granted you may see mostly the homepage as the landing page, but you will be able to pull some more helpful data out of there. At least to knock that 45% down to maybe 35 or 40%.
    Some other good tips here: