In part 1 of our niche keyword research tutorial we generated a raw list of keywords using the Google Keyword research tool. We then used a keyword research tool called iLovePage1 to gather all the Google title competition metric for each phrase/keyword.
For part two we are going to further refine this keyword rich list until we have the perfect set of keywords for our Amazon affiliate site.
This site is part of an Amazon niche website case study that is testing the validity of using ultra low competition keywords to create a niche website that will not require any back linking and should provide up to $500 of passive income after six months.
To recap, the niche that our Amazon affiliate site will be focused on are “water filters“.
The following steps assume that you are familiar with all the other steps in the previous tutorial.
Step 1. Create a Master CSV List & Remove Duplicates
If you have been following part 1 of this niche keyword research tutorial, we finished processing all our raw keywords list’s from the Google keywords tools through iLovePage1. We now should have multiple CSV files that contain the AllInTitle metric for each keyword phrase, focused on our example product niche “water filters”.
Each CSV should be centered around the main product niche, focusing on one of our seed/idea keywords.
Using iLovePage1 we also did some initial filtering to ensure that we are only grabing phrases with three or more words and the AIT (AllInTitle) competition is below 1,000.
In our example, I used “brita” and “best” seed words along with the main niche keyword that we are focusing on, “water filters”. I usually have 20-40 of these files by the end of this stage of research, for our example were just going to focus on these two.
Next we need to combine all these processed files into one master CSV file to remove possible duplicates. Open each CSV up individually and copy the results into the master CSV file. Select everything (CTRL + A) and then open up the “Data” tab and click on “Remove Duplicates”.
This will clean up our lists and make the next couple steps easier.
Step 2. Create a Copy of the “Keyword Strategy Matrix” Google Spreadsheet
The next step is to copy the master CSV file results into a Google Doc Spreadsheet. I created this keyword strategy matrix a while ago to help manage my keyword research and to facilitate additional filtering. It contains a bunch of formulas to help further whittle down keywords and to help manage what’s usable and what’s not.
I am going to link a copy for any who would like to use it: Google Spreadsheet Keyword Strategy Matrix Link!
When first opening this Google spreadsheet you will see THREE different tabs at the bottom. Tabs “Water Filter Example: Raw” and “Water Filter Example: Final” showcase the filtering process for this tutorial. The tab labeled “Keyword Strategy Matrix: Blank” to use as starting template for your own keyword research analysis.
Currently this doc is viewable only. To edit, you will have to create your own working copy. You can either download this as a new EXCEL spreadsheet, OR copy it to your own Google Docs account (recommended).
Click on FILE and select “Make a copy…”.
How the Keyword Strategy Matrix Works
KEYWORD: Placeholder for your keyword phrase
Category: This will either be a SUPPORT or PRODUCT depending on the keyword. If following the 80/20 rule, for every five product articles we will need a support article. You will have to go through each keyword and decide how to categorize it.
Search Vol: Search volume that Google lists for this keyword.
All In Title (iLovePage1): The AIT results from iLovePage1.
All In Title (Googles): I found this cool trick that you can use with Google Spreadsheets to calculate the AIT for a keyword.
It uses the following formula: =REGEXREPLACE(importxml(ʺhttp://www.google.com/search?q=allintitle:ʺ&A2,ʺ//div[@id=’resultStats’]ʺ), ʺ[^0-9]+ʺ, ʺʺ)
Basically what’s going on is we are telling this CELL to do an AIT search against the ‘A2’ cell. The results will be in XML, so with a little REGEX magic we are able to acquire only the resulting AIT number. What makes this AllInTitle trick so unique is that the call is being made from the Spreadsheet and not your IP (as far as I understand), so you never get the Captcha and its quite fast.
So why not just use this trick and not mess around with third party keyword research tools to get the keyword AIT?
A couple problems. It does not always work. I have found that sometimes I will get a “#NA” result, at which point I will have to delete the cell and recopy the formula. You may noticed this right away when first opening this spreadsheet. To fix this, after the initial calculation has been completed I will copy the column and then re-paste it using “Paste Special => Paste values only”. This removes the formula but keeps the results.
ALSO I am almost certain this is a hack on Google’s system and truthfully I’m not sure how long it will last. But if you cannot get ahold of a good third party tool to acquire the AIT for a keyword, this hack will do the trick.
RATIO (ilovepage1): We’re looking for a ratio under 1, the closer to 0 the better. To find this ratio we divide the AIT results against the search volume. This will be a key metric in deciding if this keyword is worth using or not.
RATIO (Googles): Like the iLovePage1 ratio, dividing the Google AIT against the search volume. Were looking for ratios under 1.
LongTailPro KC: IF you have LongTailPro, finding this metric and using it with the RATIO metrics will give you a good overall picture of how well you will be able to rank for a keyword.
Article Completed: Management. You can dicate when an article was published. This will help when managing what keywords you have used and what you have not.
Step 3. Keyword Strategy Matrix Processing
The first step is to copy the resulting master keyword AIT results from our iLovePage1 results into this spread sheet. See the tab “Water Filter Example: Raw” as an example. Make sure the Search Vol. & AllInTitle (iLovePage1) Results go into the right columns, the spreadsheet imbedded formula’s should do the rest of the work. You may have to do a little cut and pasting to get the columns in the right place. Here is a picture of what it will look like:
Once everything has been entered, sort based on the RATIO column so the ratio’s that are closer to zero show up first. If both iLovePage1 and Google’s ratios are RED, delete that row. If one is red and the other is not, its conflicting. Well handle that next. Using this method you will narrow down available keywords FAST. Out of the 134 results we initially had, after the additional filtering we were left with about 12 results.
Also at this point I will start to categorize each keyword. Some are better suited as product review articles, others are better as support “How To” type of articles.
You will notice some conflicting results for the AIT (AllInTitle) results between Google and iLovePage1. This is due to the proxies that were used within iLovePage1 when looking up the AIT results. AIT results are localized to where the calling IP address. This is one of the reasons why we want to do a second AIT search using the spread sheet formula.
To help further filter out between these conflicts I usually rely on LongTrailPro’s competive score calculation for keywords. This is a PRO feature which currently costs $17 a month on top of the standard $99 fee for the software (pricey yes, but very worth it imo).
Step 4. Get The Overall Keyword/Phrase Competitiveness with LongTailPro
At this point we have narrowed down our list to about 12 possible results. You could go with these, but I would feel better filtering those results a little more. Also If there are conflicting RATIOS the KC of LongTailPro is a good deciding factor.
About LongTailPro. This is an amazing keyword research tool and can do most of the stuff in this tutorial without having to bounce around between different keyword research programs.
At least at the time of this writing the one thing that it does not do is allow for proxies. Hence why we need to use iLovePage1 for AIT results. But the one thing that LongTailPro does well is calculate and overall competinevess score(KC) for the keyword that you are targeting. While not always important for the overall strategy we are employing for this affiliate site, its still good to know and can help further filter our results.
Open up LongTailPro. Click on ‘Find Keywords’ and then open the ‘Add My Own Keywords’. Then copy the 12 filtered keywords into this box. Then click on “Generate Keywords & Fetch Data”.
Next calculate the Average Keyword Competiveness for each of our keywords. This is a PRO feature.
Copy the results back into our keyword strategy matrix. The final list should look something like this:
Step 5. Final Keyword Filtering – Do we have enough keywords to support an Amazon affiliate site?
Were almost done. We need to do a final Human Eyeball Lookover our list to further refine it. This is where experience in doing these kinds of sites will kick in and unfortunately cannot be done through a formula. Always think like the searcher. We are trying to build an affiliate site, the middle man between Google and Amazon to help the customer with a purchasing decision. These articles cannot be fluff, they will need to be 600-100 words of quality content. These are some of the things to think about as you are doing your final review of this list.
Some things to think about for the final review:
- Searches are done with the intent of BUYING. Focus on keywords that have a ‘buying’ intent.
- Double up keywords if you can. Example: “Best Toasters” and “Toaster Reviews” == Best Toaster Reviews
- Don’t be afraid of going after searches that are < 20 a month or lower. This is our gold, as long as the AIT results is lower than the search volume.
- Keywords for AIT can be in any order.
- Remember you will need a total of 30 articles to start out with. You will publish one article a day for a month. Then one article a week for the rest of the year. That’s 74 articles a year. That means we will need at least 74 keyword phrases. So pick the best of the best.
- !! Very Important !! While looking over a keyword that could be turned into a product article page, make sure you search on Amazon first. Your goal is 3-5 product reviews per product article. If those products don’t exist on Amazon then that keyword is worthless to go after. Your creating an Affiliate site, not a PPC site.
In our example I used only one result from the CSV keyword lists to process. For actual sites I will usually start off with about 4k results in the Keyword Strategy Matrix. After filtering I will be left with 100-400 very good keywords that I can cherry pick from over the next year.
In the next post well be covering how to create the actual Word Press site. Stay tuned!
What do you think? There are a lot of different keyword strategies when it comes to affiliate sites. This is just one of them that complements our strategy. What are yours?