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:

1. Highlighting initial prioritizations
2. 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:

1. 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.
2. 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.
3. 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.

1. Create a new column in each worksheet in column C titled Google Referrals
2. Create a new column label in Column D titled CTR (Clickthrough rate)
3. 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.
4. 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%.

5.  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.

6. Now we’ll fill in referral data.  Go to your analytics platform.
7. 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.
8. 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.
9. 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.
10. 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
11. Paste referral numbers for each term in the appropriate cell in the C column of your worksheet
12. 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.

Now here’s the stuff you’re getting paid to do.  Let’s find Gaps & Opportunities. 

FINDING GAPS:

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

1. Determined who our target markets and/or personas are and what they need
2. Discovered the terms they are searching for online
3. Found out where your site is not getting adequate traffic in search results
4. 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 – stay tuned!).  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?

Go to any of the 8 steps:

Step 1: Define Your Target Audience and Their Needs

Step 2: Categorized Keyword Research

Step 3: Finding Gaps and Opportunities

Step 4: Define Competitors

Step 5: Spying On (and Learning From) Your Competitors

Step 6: Customized SEO Strategy & Recommendations

Step 7: Must-have SEO Recommendations

Step 8: Prioritize and Summarize