Here at Ten Thousand Foot View, we use up to a dozen different tools plus good old-fashioned dead reckoning to develop an SEO keyword strategy.
If you’re the do-it-yourself type and would like a quick and easy way to increase relevant organic search traffic to your site here’s a way to do with tools you likely already have at your disposal. This project will be performed in three easy steps and should take less than 30 minutes to complete.
What you will need:
1. Web Analytics
Google Analytics is ideal but any web analytics tool that provides search volume and pages per visit stats by keyword will do nicely.
2. Search Engine Rank Tool
There are many tools available for this but most free options aren’t great. This SEO project is a one-off so you might consider the Free edition of Rank Tracker from Link Assistant. It will allow you to get a ranking report for as many keywords as you wish but you won’t be able to save your work or get progress reporting. For about $100 you can upgrade to the Professional edition which provides these features.
3. Spreadsheet
We still like Excel as it’s just faster but if you don’t have it Google Apps free spreadsheet or any other tool will work just fine.
Step 1 – Gather Data
The following procedure is for Google Analytics, Excel and Rank Tracker. It will likely only need to be modified slightly for other tools.
Assuming you have Google Analytics pull up your organic search report. You’ll find this under the menu
Your report should now show the top 250 results and also which search engine the traffic came from. Note there often be duplicate keywords at this point, this is normal.
TIP – If you don’t have at least 100 keyword results consider increasing the reporting period to 60, 90 or 120 days.
Next click the export button and choose “Excel (.XLSX)” which provides nicely formatted numbers rather than text fields as with CSV.
Open the downloaded file and delete the (not provided) row and totals rows of data. Sort all the data by “source” and remove all rows of data except those from Google. This project will focus on Google but you could easily do this for Bing or any other search engine if you wish. You will likely still have around a few hundred results in your sheet.
Before the next step sort your data by keyword alphabetically.
Step 2 – Get Rankings
Startup Rank Tracking and start a new project for your website. Enter the URL for your website and set the search engine to Google US only.. Copy just the keywords from your spreadsheet and paste them into the keywords box when prompted. Click “next” and wait while Rank Tracker works it’s magic; note you might be prompted to enter some CAPTCHAs along the way. When done your results will be displayed in alphabetical order, no matter which order you provided the keywords in. This is why it is important to sort your keywords if you didn’t do it before importing no problem sort the spreadsheet now before the next step.
Right-click on the menu bar and you’ll see you can add and remove columns in the report. Add the “Google URL Found” column.
If you find that there are a high number of results not showing up in the top 100 spots try changing the Preferences/Rank Tracking Mode to check 1,000 instead of 100 results. Recheck the rankings… this may take a while but by doing so you will obtain the exact ranking for each keyword and the landing page URL.
Ok, now select all the rows of data from Rank Tracker, copy and paste them next to the existing columns in the spreadsheet. Quickly verify that the keywords are correctly matched from the first column.
Once done sort the sheet by visits from highest to lowest. You can now remove all the columns except Keyword, Visits, Pages/Visit, Google Rank and Google URL Found.
Step 3 – Number Crunching
Change all the (Not in top 100) or (Not in top 1000) rankings to = 100 or 1000 respectively to facilitate the super complicated and proprietary calculation to come. An easy way to do this is sort the list by each of these columns and globally paste “100” or “1000” into the appropriate cells.
Are your ready, here is super duper strategy #1:
Insert a column next to “Google Rank,” name it “opportunities” and input the following calculation for each row below:
= (visits) x (pages/visit) x (rank)
Ok, what? Here’s the rationale:
a) more visits equals more traffic and equals more opportunities for your website
b) more pages/visit equals a greater chance to convert that traffic
c) a higher ranking means more opportunity for improvement
Simplistic yes, but it works in most cases.
Once you’ve entered your formula sort by the “value” column and scan through the results. What do you see in the top 10 results?
What you should see is that you’re already getting a nice amount of traffic, visitors clicking through on those keywords are looking at quite a few pages on your site and there is ample opportunity to increase your ranking. You also have the exact page that is currently ranking highest for that keyword.
Here is super duper strategy #2:
First, save your work. Sort your list by Google Rank and delete everything except keywords that rank from 11-20 (this is page 2 of search results). Next sort the remaining keywords by Visits. Moving these keywords up from page 2 to page 1 can easily net 10 times as many visits. Since you often only need to move a keyword 3-4 spots to accomplish this it is truly low hanging SEO fruit.
Action Items
With this in hand go to each of the top 10 results (or more if you wish) of your chosen strategy and optimize those pages as appropriate. Please remember that you should never try to optimize a page for more than 3 keyword phrases. Next, do some white hat link building using the chosen keywords as anchor text.
In a few weeks or months, you should see highly relevant traffic spike nicely on your site.
Keep in mind that this is a fairly simple approach to building a keyword strategy. There are many additional techniques that we employ in our process such as prospecting of competitors’ keywords and deep subject matter keyword research.
Are you considering hiring an agency to manage your Search Engine Optimization? Check out what we offer right here.
