Update (Jan 15, 2015): After you’re done reading this post, be sure to check out Han and Nate’s new keyword tool that makes it easy to scrape your competitor’s keywords and do bulk keyword difficulty analysis.
Hi again everyone. So I decided to combine both videos in one post this time. As usual, if you’re using IE9, please try Chrome or Firefox :)
Below is Part 2 of the video series showing how I do my keyword research.
In this video I go over:
- How I export GAKT data into a spreadsheet and use Macros to turn the data into an earnings calculator.
- What metrics to track in the calculator, and what my expectations are for each metric.
- Find a niche with high SERP CTRs and monopolize it!
- How to use this spreadsheet to determine your content needs, and keyword targeting on that content
- Download the Spreadsheet with Macros here (right click on link and save as). It comes with some other Macros I use often as well in case anyone wants them :)
- Note: Excel can have compatibility issues. In fact I’m entirely sure the Macros will not work for some people. I am no Excel expert I just created this using Record Macro, and anyone can recreate this. Perhaps if there is an excel expert out there, he/she can optimize these Macros. The reason I’m not turning this into an open office doc that works for everyone is because I am thinking of releasing a free scripted version for you that includes the PA data (see next video).
The next video is less instructional and more of a demonstration of how I built a more automated system to determine keyword competition. It shows how it works, discusses programming challenges you may run into if you try something like this, and talks about a way I could develop a free version of this for you.
For those that are curious, you can see the exact formula my tool uses to calculate competition in this post (and even copy and paste this to your programmer if you want to recreate our tool).
Personally, this is the video I would have really appreciated about a year ago, but I’m very curious as to how it is received, so please let me know! Also please let me know in the comments if you would like a script that will allow you to do bulk CMI searches using your own SEOMoz API Key. If you haven’t already signed up for the free trial of SEOMoz (aff. link). I get paid whenever anyone signs up for a free trial, you don’t have to purchase after 30 days. So if you think I deserve a tip from an 80 million dollar organization, use my affiliate link! :)
Update October 17, 2012: Google has made some changes and I have posted an update to my keyword research tactics here: https://www.nohatdigital.com/blog/how-to-choose-keywords-for-your-adsense-domains
Hayden “NoHat” Miyamoto