How to report Keyword Ranking changes for Free
Hey all, I was debating what type of super expensive, paid rank checking software to get for a week or so. I needed something that would keep a running tab on how my search engine rankings were doing (up and down) and after thinking about it for a while I came to the conclusion that I could just use the free tool from SEOBook RankChecker and just put some equations into Excel. I thought this might help those of you that really want to track your SERP progress, but don't want to pay the $200-300+ for rank checking software.
1. Download SEO Book's RankChecker for Firefox: Rank Checker - Track Google, Yahoo! Search, & Microsoft Rankings Free
- This tool is actually very accurate, and is even more accurate then WebCEO for me because WebCEO must be running of datacenters that are not local, skewing the results that I would actually receive when I search for a kw on Google.
2. Make a master list of keywords
- I normally just go into my Google Analytics account and pull all the keywords from all the search engines, then add my keywords that I'm not ranking for yet from my keyword research. Take this master list and plug it into Excel and sort it from A-Z
3. Bulk Import your Keywords into RankChecker
- This part is a little tricky because you'll need to do this a few times if you've got more than 100 kw's you want to track. If you've got 1000's of keywords then you may be better off paying for some type of software that lets you scan that many kw's at once because otherwise you'll need to create 10+ RankChecker files to hold all those keywords.
- Copy your first 100 kws - Click Add Multiple Keywords - Paste and put your URL in. Then you need to save this list at list1 or whatever.
- Repeat this and save as list2
4. Scan your lists of kw's
- Once your scan is complete, export as a CSV, then copy the data and put it into a new Excel document. Scan the rest of your lists that should be in alphabetical order for ease, and past the results into the same spreadsheet vertically.
- Now you've got all your data from your 1st scan - good job! I usually delete al the url coloumns because they're unnecessary for me, but you can do whatever you want.
5. Scan Again at a later date
- The next time you scan your site, copy and paste the results into a new tab in Excel (you can paste into a new page as well, but I prefer a new tab). When all your data in the new tab or page is formatted exactly the same as your original, move on to the next step.
6. Use a formula to tell you your progress
- Now that you have to sets of data, one from date1 and one from date2, you can compare the two easily by using a simple formula. Just skip one coloumn, put the dates you're comparing and click the first cell in the next coloum.
You can either just put type a = in this cell and then click the corresponding kw cell in the older ranking spreadsheet, and then go back to the newer ranking kw or just copy the formula here:
='date1'!B2-B2
- I only had to do this twice, once in cell F2 and then F3 and then I was able to highlight those two cells, and drag the corner all the way to auto fill the rest of the data.
7. Color Code & Format
- The next thing to do is set your cell formating for the formulated data to "Number" with no decimal points, and then pick the red negative. Then highlight all the final data and make it a green font so you can tell when something is doing good or bad.
Here's what it looks like when your done, and remember, the next time you do it you can simply cut and past all this into a new tab and it will take you like 2 minutes. If you get a #VALUE you've either come or gone completely from the results. Hope this was useful!
1. Download SEO Book's RankChecker for Firefox: Rank Checker - Track Google, Yahoo! Search, & Microsoft Rankings Free
- This tool is actually very accurate, and is even more accurate then WebCEO for me because WebCEO must be running of datacenters that are not local, skewing the results that I would actually receive when I search for a kw on Google.
2. Make a master list of keywords
- I normally just go into my Google Analytics account and pull all the keywords from all the search engines, then add my keywords that I'm not ranking for yet from my keyword research. Take this master list and plug it into Excel and sort it from A-Z
3. Bulk Import your Keywords into RankChecker
- This part is a little tricky because you'll need to do this a few times if you've got more than 100 kw's you want to track. If you've got 1000's of keywords then you may be better off paying for some type of software that lets you scan that many kw's at once because otherwise you'll need to create 10+ RankChecker files to hold all those keywords.
- Copy your first 100 kws - Click Add Multiple Keywords - Paste and put your URL in. Then you need to save this list at list1 or whatever.
- Repeat this and save as list2
4. Scan your lists of kw's
- Once your scan is complete, export as a CSV, then copy the data and put it into a new Excel document. Scan the rest of your lists that should be in alphabetical order for ease, and past the results into the same spreadsheet vertically.
- Now you've got all your data from your 1st scan - good job! I usually delete al the url coloumns because they're unnecessary for me, but you can do whatever you want.
5. Scan Again at a later date
- The next time you scan your site, copy and paste the results into a new tab in Excel (you can paste into a new page as well, but I prefer a new tab). When all your data in the new tab or page is formatted exactly the same as your original, move on to the next step.
6. Use a formula to tell you your progress
- Now that you have to sets of data, one from date1 and one from date2, you can compare the two easily by using a simple formula. Just skip one coloumn, put the dates you're comparing and click the first cell in the next coloum.
You can either just put type a = in this cell and then click the corresponding kw cell in the older ranking spreadsheet, and then go back to the newer ranking kw or just copy the formula here:
='date1'!B2-B2
- I only had to do this twice, once in cell F2 and then F3 and then I was able to highlight those two cells, and drag the corner all the way to auto fill the rest of the data.
7. Color Code & Format
- The next thing to do is set your cell formating for the formulated data to "Number" with no decimal points, and then pick the red negative. Then highlight all the final data and make it a green font so you can tell when something is doing good or bad.
Here's what it looks like when your done, and remember, the next time you do it you can simply cut and past all this into a new tab and it will take you like 2 minutes. If you get a #VALUE you've either come or gone completely from the results. Hope this was useful!
Luxury Brand Marketing Blog