Conducting a Keyword Performance Audit

Tim Hopma 11 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.

Our website uses cookies for various purposes and to enhance the site’s functionality. This helps us understand how you use and interact with the website.

Settings Accept Cookies