SEO – Extreme Keyword Analysis
Extracting phrases and common words to better format your pages

At some point, if you are a serious SEO your gonna want to dig a little deeper into data to see how your web page efforts are going.

The process I’ve used actually requires 2 bits of free software
01- Google Analytics account (with adequate amount of data to analyze)

02- A FREE piece of software called “Primitive Word Counter”

and Excel

Now I will say that upon looking for keyword extraction software I had almost given up. Though at the zero hour I found exactly what I needed to make a SEO overview of one of my clients sites.

So lets get into the process:

___________________________________

GOOGLE ANALYTICS PORTION:

01- Open your Google Analytics account (of website you are analyzing)
02- Go into “traffic sources–>Keywords
03- Change the date range to at least a year’s worth
(this will be under the drop down the upper right “date” are of GA)

Now at this point you’ll want to make note of how many total keywords were used- write this number down
04- Go to the top URL and change the portion that says

=average#

to

=average&limit=20000#

(this allows GA to process up to 20,000 keyword entries, from which what I can tell is the max it will output into Excel at one time)

– Hit Return
– Now go back and reset the date again to a year (as many times it jumps back to weeks worth)

Go up to “Export” (under Keywords” title)

– export a CSV

– then open in Excel

(You’ll want to delete alot of the “top data” to get your excel spreadsheet trimmed down to just the keywords and their count numbers)

Note: if you have more than 2000 keywords over a year, then you may have to do 20,000 blocks at a time
Procedure for that?
scroll to the bottom of the page and change the setting where it says “Show Rows” to 500 (if your site is larger, you may have to do this a few times)

Select the keyword row and select all

NEXT SOFTWARE:
01- Download – Primitive Word Counter

02- Paste all your keywords into the left side
(you may need to paste in a text editor first and copy back to get the Excel formatting off)

03- set the min word length to 4 (gets rid of small common words like “the”)
04- hit GO

This now gives you 2 sets of data to look at:
01- Your excel spreadsheet (from and which phrases were most common
(the B column will reveal your highest keyword phrases used to find your site)

02- By using the data in “Primitive Word Counter” you can narrow down which were the “common words” or “combinations of words” that were most frequent in your data
Note: this is different  than Google Analytics  as it looks for frequency of words used through out all data rather than strictly entrance phrases

This is not only a great SEO technique but an “extreme” insight into your data

for instance on one of my accounts it revealed that the words “2011” and “2010” were used alot, it revealed that the word “movie” was used more than “movies”

Taking this into consideration, I can fine tune some of my titles to be more formatted to searchers mind set and in return drive higher targeted traffic

Hope this helps,