This post was promoted from YouMoz. The author’s views are entirely his or her own (excluding an unlikely case of hypnosis) and may not reflect the views of Moz.

If you’ve labored through building out categorized keyword lists in Step 2, you’ve done a lot of the grueling work for your SEO strategy. Give yourself a pat on the back (or more realistically, go get yourself a beer. I’ll be right here when you get back). You should also have a good idea of what’s popular and what’s not (based on what people are searching for).

In this step we’re going to look at two things:

Highlighting initial prioritizations

How to find Gaps and opportunities based on current performance (for existing sites only)

PART 1: HIGHLIGHTING PRIORITIZATIONS

Now that you know your keywords and their search volume well, highlight tabs/keyword groups (I usually give them a color) that have a lot of search volume, or tabs that you otherwise feel are important to pay attention to. For example, if I have 10 Excel tabs, each with a keyword category related to types of furniture I sell (living room sets, kitchen stuff, dining room sets, outdoor furniture, bedroom sets, etc) and I find that there are a heck of a lot of people searching for the living room furniture terms but comparatively few people searching for the outdoor furniture terms, I might highlight the living room furniture tab because I’ll want to prioritize that content in my strategy and on my site.

Within each of those tabs, you can also go through and highlight specific terms or sets of terms that you find important because they have high search volume or for any other reason.

You may want to mention these categories and/or specific terms directly in your strategy recommendations later on. We’ll revisit what you highlighted in Step 6 when we start really building out and organizing your recommendations.

PART 2: FINDING GAPS & OPPORTUNITIES

This is manual, and can be time-sucking, painstakingly laborious, but insightful. But the amount of detail you get into is also up to you – so don’t get your panties in a bunch just yet.

Finding Gaps and Opportunities basically means we’re going to look at estimated search volume, and referral volume to try to get a sense where we might be doing well and we might not be. Because search volume numbers that we have are estimates, I don’t suggest taking the actual numbers too seriously. There might even be times when your referrals are higher than the search volume. So you might not want to show the execs or clients the numbers (and have to answer for them), but rather show them what we’re going to learn from them instead.

GAPS: Are areas where there is content on the site for a term, but the referral volume is very low (especially compared to search volume) or none.

OPPORTUNITIES: Are areas where there is significant search volume for something relevant to your target markets (and what your company is offering), but there is no content on the site to attract those searchers.

Here’s how we find this.

Open your Excel sheet with all of your keyword lists. Pick a list. In column A you should have the title “Keywords” and the rest of the column should contain your keywords. In column B you should have the title “Search Volume” and the rest of the column should contain search volume numbers for each keyword in column A.

What we’re going to do first is go into your analytics system and pull out referral numbers for each keyword and paste them in.

More on this in a minute, but here’s what you’ll need to consider for now. If you’ve got an Excel sheet with several tabs and dozens or even hundreds of keywords in each tab, you will probably want to consider doing a small sample of only a few keywords per list. For example if my Erykah Badu keywords list has 1500 terms in it, I don’t want to have to look up each of those terms manually in my reporting system and copy and paste the referral numbers one by one, until its all done and I’ve wasted two weeks of my life… Instead I’ll choose a sample set of 20-50 terms per list.

Here’s three suggestions for ways you can choose a sample set:

Based on your target market needs: Did you take Step 1 to the level of building out Personas, where you identified your target markets and their needs? If so, take a sample set of terms from each list that most represents what they need and the questions they’re asking.

Based on high-volume (head) search terms: If you’re the competitive type and you just want to go for the gold (and why not?) then choose a sampling of the top search volume terms.

Sample high volume (head) and low volume (tail) terms: Tail terms are important, and it’s great to see how you’re doing on more competitive and less competitive terms. Depending how deep your lists are, you might be able to get a sense of this by taking a few terms from the top (head terms) and a few terms from the bottom, and making that your sample list.

Use your noggin. If there is a sampling of terms that makes more sense – this is your work of art. Do what feels right for you. BUT, don’t just choose terms that you know your site is doing well on. Choose a set of terms that your site should be doing well on. This way you can put your site to the test.

Feel free to choose a larger sample size, or even pull referral values for the entire list. If you have a way of pulling the data out of your analytics or just the terms in your worksheet (through APIs or other means), you’re golden. You can and should pull the referral data for all of your terms. As with all of the work we do in these Strategy steps, the more research and data pulling you do, the more insight you’ll have. Just be sure to manage your time wisely.

Now that you have an idea of the work ahead of you for this step, let’s DO this.

Create a new column in each worksheet in column C titled Google Referrals

Create a new column label in Column D titled CTR (Clickthrough rate)

Create a calculation in the first cell in the CTR column that does this calculation (=referral#/volume#). To do that type this into the cell:
=C2/B2
and hit enter This should insert a number into the cell.

Format that cell to show the number as a percentage. To do this, right click on the cell and choose Format Cells. Choose the number tab, and choose Percentage. You should leave one or two decimal points because a lot of clickthrough rates are likely going to be under 1%.

Now we’re going fill that calculation into the rest of the cells in that column. Do this by hovering over the bottom right corner of Cell D2 where we just did our calculation. You’ll see your curser change to what looks like a plus sign.

When it does, grab that corner and drag it all the way down to the last row at the bottom of your keyword list. This will fill I the calculation for every cell, so when you add referral values into the C column, the CTR in the D column will calculate automatically.

Now we’ll fill in referral data. Go to your analytics platform.

Find referring keywords for the month coinciding with your search volume research. For example, if you did search volume research for the month of April, then make sure you’re looking at referral data for the month of April.

Also make sure you’re looking at Google organic keyword referrals. No PPC and no other search engines. This is because we’re comparing this to keyword research we did using Google’s Keyword Tool which shows estimated searches in Google. We want to try to compare apples to estimated apples.

And make sure you’re either looking at local or global referral data, depending on which search volume numbers you’re using from your Google keyword research.

Now, search for referrals to your site for each of your terms. The referral terms should match exactly. For example, if my term in my keyword worksheet is Erykah Badu, I need to find keyword referrals for exactly that phrase. Referrals for Badu Erykah, or Erica Badu or Erykah Badu music don’t count

Paste referral numbers for each term in the appropriate cell in the C column of your worksheet

You should see CTR numbers fill in automatically.

Remember that because search volume from Google is only an estimate, these CTR numbers are not going to be exact. We’re basically using them to look for areas for potential optimization or new content.

Once you’ve pasted all of your referral numbers, we’re going to re-sort the data by the CTR column and look for Gaps & Opportunities. Do this by selecting all 4 columns A, B, C and D (click on the A column label and drag your mouse to the right to the D column label (note – column label where it says A, B, C, D, etc., not column title where it says Keywords, Search Volume, etc.). You should see the rows all 4 columns completely selected.

Now sort by descending values in the CTR column. Do this by choosing Data > Sort while your columns are selected.

Make sure Header Row is selected at the bottom of the window, and in the first text box dropdown choose your CTR column. Check the radio box next to it to make it descending. Leave the other fields blank and hit ok.

This should have sorted your data so that all of the keyword value pairs stayed in tact row-by-row, but they are now all sorted by the ones with the highest CTR value to the lowest.

Remember Gaps are where we have content but don’t have a good CTR. What’s a good CTR you say? Well, we know that with all of the stuff to click on a search result page (including going back and refining the query instead of clicking on anything), a number one ranked search result rarely if ever gets a 100% clickthrough. It might be about 35%-40%, or even higher if your result contains images, videos or rich snippets. But if you’re getting 35% CTR you’re doing well. This is not a Gap. Most of the time you’re going to see a lot of CTR numbers that are 2% or less. You’ll probably want to zero in on those.

Lets look at some Gap examples in our Erykah Badu data.

There are several things we can tell by looking at this set of data. First of all, the site who’s data we’re looking at is doing fairly well for Erykah Badu videos and lyrics. This is good, but let’s assume this site also has contains Erykah Badu photos, and song downloads. The first Gap we can see are the songs. The two phrases [erykah badu songs] and [erykah badu song] collectively have over 73,000 searches in April but only 128 referrals. And the popular song titles like [erykah badu next lifetime], [erykah badu tyrone], [erykah badu love of my life] and [erykah badu bag lady] have little to no referrals, with the highest CTR at less than a half percent. This Gap might represent two types of pages – a high level Erykah Badu page for all of her songs and the landing pages for the actual songs individually (assuming there are landing pages – which oftentimes is the problem). Note this Gap in your notes. We’ll be pulling it out again in our set of recommendations.

Do you see another Gap? Gap #2 is pictures and photos.

Our site has photos of Erykah Badu, but overall pretty dismal traffic numbers. Note this Gap as well.

And lastly, the site does allow people to download the songs for a price. This is one way they’re making money, so it is an important action. Yet, the fairly popular phrase [erykah badu download] does not have any referral volume.

Add this Gap to your notes as well. Because we know it’s associated with a monetary conversion, we don’t want to forget it in our site recommendations.

Determining The Reason For Gaps.

Now that we know what the Gaps are, we’ll want to look at why the site is performing poorly. This where your SEO superpowers come in. There could be several reasons the site is getting low or no traffic for these terms; from not being indexed, to poor SERP displays, to bad architecture and so much more. I’ll leave it up to you to use the skills you’ve learned here at SEOmoz to dive into that part. Do your assessments for each and add them to your Recommendations section of your Strategy Document.

FINDING OPPORTUNITIES:

Opportunities are similar to Gaps, but are content your site doesn’t actually have and might want to consider. This is why we expand keyword research out in Step 2, so we can cast a wide net and compare against that. Never do Step 2 and 3 (creating keyword lists and finding Gaps and opportunities) using only the terms already being referred to your site. This limits you to what you’re already receiving traffic for, rather than looking at what people want and then benchmarking how well you’re making yourself visible to them in Search.

Here’s an example of a potential Opportunity for our Erykah Badu set of terms we’ve been looking at.

I can see here that people are searching for Erykah Badu concerts, her tour, and to buy tickets. The site currently doesn’t provide that type of information. But based on the search volume, and knowing that this is something my target market is interested in, I might consider it, especially if I can monetize it. This can be added to the Recommendations section of your document.

PERFECT YOUR ART

So far in Steps 1-3 we’ve

Determined who our target markets and/or personas are and what they need

Discovered the terms they are searching for online

Found out where your site is not getting adequate traffic in search results

Found out things our target markets are searching on that we aren’t even providing.

With these Excel worksheets we’re looking only at visibility in organic search. You can take this to other levels by adding in paths from search to conversion, comparing PPC, and more. I can’t give away the farm or else I’d be giving away some of the crop that fuels our consulting business (our partnership is to be announced within the next two weeks – staytuned!). So again, use that big smart brain of yours to determine ways in which you can expand on this worksheet that are valuable to you and your customers.

In the end you’ll have SEO assessments for specific Gaps and specific areas of Opportunity to put in the Recommendations section of your strategy document. This is one of the things that makes this different from a regular SEO Audit. You’re not just going through the site saying ‘optimize for this and that.’ Anyone can sell the SEO Basics these days. If you’re reading this you’re not just anyone. This stuff gives you or your clients specific areas to focus on that are directly related to their customers and their site. And we’re not done.

In the next two steps we’ll be defining competitors and doing some competitive research that will give you even more targeted strategy recommendations. Maybe it’s time you reconsider your salary/rates?

Get fresh SEO data, insights, and tracking

Comments
43

The post is really informative and straight forward. It looks like you are using broad match search count for this calculation. If this is the case then I would caution users on using the broad match search count and use exact match search count instead. The reason is the analytics shows exact match so using broad match search count to calculate CTR will not provide the correct data.

I think it could also be helpful to overlay rankings as part of the equation. In an instance where there is high deman, low CTR, but rankings are already strong, then we may have found any area that needs deeper understanding.

Also, comparing CTR on some of these known rankings may also add an extra layer of understanding. It still won't be exact, but you could then apply this logic to better understand what the site's typical CTRs are.

This type of anlaysis needs to happen more often than it does. Too often only part of the data is ever looked at, not surprisingly, the keyword demand data.

Then people run out and start optimizing wherever they can drop those high demand words in, without understanding how the site is currently performing, or even how the page they just reoptimized was performing...increasing the chances of optimizing for a term that might actually:

have less demand than what the page was previously optimized for.

already be performing elsewhere on the site.

have a lower CTR than the old optimization or other location.

have a lower conversion rate.

This is similar to doing too shallow of keyword research...you might find terms that have high demand out of your research set, but miss even higher demand terms that fall outside your limited view.

This hopefully reinforces, and reminds us all, that strong SEO requires more time up front, which should lead to not only less time on the back side, but greater results.

Truely great SEO is like running a mixing board...anyone can slide the volume control up and down, but true artistry comes when all the dials are set optimally to provide the best quality of sound, not just the loudest.

Talking about CTR, how much do think that a PPC campaign can help you in this part of the analysis?

I ask it because, especially for eCommerces (which usually have PPC running in parallels with SEO), I found useful all the sets of statistical data Adwords or Yahoo Search Marketing offers me in order to focus also the SEO campaign regarding which set or "panorama" of keywords are better performing.

PPC may give a quicker and stronger view into CTR based on report of impressions. But to factor it all in, we also have to keep in mind the potential intent differences we may see in natural results traffic versus paid, and whether PPC results are running tandem with natural results, or being used where natural rankings are unattainable?

I was guessing the same too, and that's why whenever I use the PPC CTR datas as an indicative potential metric (not as a metric to use exactly equal for organic searches too) I always differentiate ranking in Serps keywords from not ranking.

I agree about the search behaviour focus too, and that's something I will need to study better in order to apply it to mine "keyword effectiveness analysis".

I think the PPC data comes in more useful at the next stage, looking at how users engage with the site, and especially with e-commerce how they complete transactions, so that you can tweak content before closing any gaps.

I do find it useful, though, for estmating the kinds of clickthroughs you should be getting for a ranking on the first page - kind of an added extra to the main post.

One could simply export the data out of Analytics, then use a Vlookup to populate April referrals for hundreds of keywords+ at a time.

EDIT: Equally one could take their keyword referral traffic, then put them in large batches into Adwords, export it then use Vlookups from the Adwords data to spot any quick wins. Same thing just the other way around?

Great effort Laura. Too bad Google's latest "secure search" update has made our job lot more challenging to say the least... As organic keywords are no longer available, is there any other reliable way to conducting a Gap & Opportunity analysis?

I mean, much part of the process you describe is something that I realize too... but - honestly - you give me a reason to look at my process with attention in order to refinate it following your suggestions.

I think that once this series will end that SEOmoz should have to keep it out from the Blog section and re-edit all in one "Best Practices for SEO" manual in the Search Marketing Guides section of this site.

Again, not enough words exist to thank you for this great job.

I'll stay tuned for the big news about the next partnership.

(and now let's go all studying a strategy in order to raise our salary ;) )

Laura, Sorry I dont know how I missed #3 in this series. i must have been lost that week. Anyway, Your method of research is outstanding and everytime I read one of your articles I learn. And thats what its all about. Becomeing better and better at what we do. Thank You

Sorry for the late pickup on this. Just auditing my organisation's site and getting some ideas from your processes.

My problem is with the keywords tool. Depending on if I am logged into my Adwords account, or if I am not, it provides me with two completely different local monthly search volume results for each keyword. In one example:

246,000 - and...22.

I want to, but I can't really make recommendations to management with a variability of 245,978.

Great series..! One question if I may, knowing it is some time since these posts were published...

Is there a keyword tool, free or paid, which can compile keywords even when search volumes are low...or even very low? I work fora a niched e-commerce bio-tech company, and even our most popular keywords are not exactly sky rocketing - but it is business critical to understand more in this field than eg Google can provide...

laura one question. Why you use local/global search volume to calculate the CTR of a keyword? You are equating local/global search volume to number of impressions which i think is not correct. local search volume is the number of times a keyword is searched in a particular geo location and it is not necessary that every time the keyword is search your site will appear in serp or am i missing something.

Wow Laura. Talk about over delivering. Instead of your posts reaching a plateau, they just get better and better.

Honest to Pete, I've run out of superlatives to use!

The information was first class, and the step-by-step excel instructions excellent. For those of us that thrive when we have set routines to follow, this post(as well as the prior two)are gold mines of useful how-to's.

And I echo Gianluca, I look forward to hearing about your partnership details. I predict that your new business will come out of the chute guns a blazin'.

Have you thought about putting this out as an eBook when complete? I know SeoMoz frowns upon having content used in other places, but I would imagine even they would find this a valuable ebook for the SEO industry.

I think you can get a better picture of more immediate gaps that can be closed if you put as well a tab with volume of impressions your site got for that keyword too. This data can easily be obtained from Google Webmaster tools and it has calculated CTR for you there too.

Amazing effort! I've just done something like this (though less elaborate) on one of our SEO proposals. So I guess this is useful for both existing and future campaigns. The latest word already has a lot of nice visualization and coloring features that can highlight things for you quite nicely.

Very detailed painted. I had read the first two steps to understanding the whole picture. I would like to see at the end of the eight steps of the synthesis output with a description of their contents, so you can return to them as needed.