SEO Step-by-Step: Use Keyword Analytics to Calculate the Most Profitable Keywords

This is part three in my series, SEO step-by-step.

We’ve now covered the first two steps of optimizing your site for top search engine results:

  1. Start with your own initial list of keyword search phrases, thinking of what your customer would enter when looking for a site like yours
  2. Enter these search phrases into Google and analyze all the top results in order to get new keyword ideas and learn from your competition

For step 3 we will again turn to Google to not only get even more keyword ideas, but also to score each search phrase and calculate the best search terms to focus.

Google provides a keyword research tool as part of the their Ad Words service:

https://adwords.google.com/o/KeywordTool

The tool is free for anyone.  If you also sign up for a free Google Ad Words account then Google gives you additional keyword ideas and you don’t have to keep dealing with the CAPTCHA challenge, so I think it’s well worth signing up, even if you never do paid search.  The Keyword Tool has a search form that looks like this:

Google find keywords

You can enter multiple keywords into the “Word or phrase” box as long as each is on a separate line.  So you can take the entire list that you generated in steps 1 and 2 and paste it in and click search.  You can also enter each of the websites from you list of competitors, but you have to do that one website at a time.

After you click search and select the “Keyword ideas” tab you will see results like these:

GoogleFindKeywordIdeasResults

The results will have two sections:

  • Your original keyword list
  • Additional keyword ideas, up to 1,000 additional search phrases that Google derives from your list.

For each keyword search phrase it tells you the number of searches and the amount of competition.  There are “Global monthly Searches” and “Local Monthly Searches.”  These two numbers will be the same unless you go into the Advanced Options and select a particular country such as the United States.  Then it would show you world-wide and US search counts (or whatever country you selected).  The competition tells you have many websites already exist for each keyword phrase.

Your goal should be to target the relevant keywords with the highest amount of traffic and lowest competition.

You could page through this list manually, but I will show you how to automatically calculate your best keyword opportunities.

At the top of the results there is a Download button.  If you click it, select “All Search Results” and then “CSV for Excel” it will save all of your search results into a CSV file that you can load into Microsoft Excel.  When you do so the file looks like this:

GoogleKeywordsDowloadToExcel

Now you can use Microsoft Excel to help you find the best keywords to optimize your site for.  Remember, you want to target the highest number of searches and the lowest competition.  Notice that in Excel the competition column no longer says Low, Medium, High, but instead there is a decimal number: 0.59, 0.14, 0.38.  Google gives you the competition value as a number between 0 and 1 with 0 being low competition and 1 being high.

To calculate the best keywords we’re going to add a “Score” column that we can sort by.  In cell E1 go ahead and add the word “Score” as a column heading.  Then move down to E2 so you can enter the formula.

Remember, the lower the competition the better for you.  But Google gives you the value with lower competition having an smaller number.  So here’s the formula we will use to score each keyword:

=C2*(1-B2)

Cell C2 is the Global Monthly Searches.  B2 is the Competition.  If we take 1 minus B2 then we effectively flip the value around so that lower competition will have a higher number.

Now copy the formula in E2 to the rest of cells in column E.  Then choose Data, Sort, Sort by Score, Largest to Smallest.  This will put the keywords with the most traffic and the least competition at the top of your list:

GoogleKeywordScoreResults

You can now start at the top and work down, skipping any keywords that are not relevant to the market you are targeting.

In my next post I explain how incorporate the keywords you select into the webpages of your website.  Be sure to subscribe to my RSS feed so that you will be alerted whenever I add a new post.

Advertisements

42 comments

  1. […] the next post in this series I will show you how to use Google’s Keyword Tool to find out how many people are searching […]

  2. […] Use Keyword Analytics to calculate the most profitable keywords […]

  3. […] Use Keyword Analytics to calculate the most profitable keywords […]

  4. Does your blog have a contact page? I’m having trouble locating it but, I’d like to shoot
    you an email. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it improve over time.

    1. Hi Lucia, I just updated my About page, https://leanbusinessstartup.com/about/, with more contact info.

  5. Stroller Depot Coupon Code · · Reply

    Tremendous things here. I’m very happy to peer your article. Thank you a lot and I’m taking a
    look forward to contact you. Will you kindly drop me
    a mail?

    1. Thanks Sylvester, I’m glad you enjoyed the post.
      Ray Wampler
      Ray@dbEasy.com

  6. Hey there! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My website looks weird when browsing from my iphone 4. I’m trying to find a template or plugin that might be able to fix this problem.
    If you have any suggestions, please share. Appreciate it!

    1. I’m in the process of learning jQuery Mobile to create mobile sites using code so that dbEasy can offer both web and mobile database application services. However, if you are not a programmer then there are services that will automatically convert any website to be mobile friendly, such as Mobilize Today: http://www.mobilizetoday.com/.

  7. […] you remember from my post, Use Keyword Analytics to calculate the most profitable keywords, this long phrase was carefully calculated as a composite of  several terms that Google Keyword […]

  8. Hi would you mind letting me know which web host you’re utilizing?
    I’ve loaded your blog in 3 completely different web browsers and I must say this blog loads a lot faster then most.
    Can you suggest a good web hosting provider at a honest price?

    Thanks a lot, I appreciate it!

  9. Whɑt’s up, I check your blogs like every week. Yoour humoristic style is awesome, keep it up!

  10. Its like you read my mind! You appear to know so much about this, like you wrote the book
    in it or something. I feel that you simply could do with some percent to
    drive the message home a bit, however instead of that, this is
    excellent blog. A fantastic read. I’ll definitely be back.

  11. Manufacturers have managed to reduce the size,
    weight and cost whilst improving display and resolution.

    With a screen response time of 4-milliseconds, this LCD HDTV will provide you with a high-end image
    fluidity that is beautiful to watch. Samsung LCD TV is one of the best-selling LCD TV Brands in
    India.

  12. Normally I do not read article on blogs, however I wish to say that this write-up very forced me to take a look at and
    do so! Your writing taste has been surprised me.
    Thanks, very nice post.

  13. Thanks for sharing your thoughts on game of thrones season 1.

    Regards

  14. This design is spectacular! You obviously know how to keep a reader entertained.
    Between your wit and your videos, I was almost moved
    to start my own blog (well, almost…HaHa!) Fantastic job.
    I really loved what you had to say, and more than that, how you presented it.
    Too cool!

  15. Hey excellent website! Does running a blog such as this require a
    large amount of work? I have absolutely no knowledge of computer
    programming however I had been hoping to start my own blog
    soon. Anyway, if you have any recommendations or tips for new blog owners
    please share. I understand this is off subject but
    I just needed to ask. Many thanks!

    1. Starting a blog is easy. Just go to WordPress.com or Blogger.com and sign up for free. Then you just have to write.

  16. I have been surfing online greater than 3 hours lately, but I by no means found any interesting article like yours.
    It is beautiful value sufficient for me. In my view,
    if all webmasters and bloggers made good content as
    you probably did, the net might be a lot more useful than ever before.

  17. I really like what you guys are usually up too.
    Such clever work and coverage! Keep up the superb works guys I’ve added you guys to my blogroll.

  18. Do you mind if I quote a few of your articles as long as I
    provide credit and sources back to your website? My website is in the very same area
    of interest as yours and my visitors would really benefit from a
    lot of the information you provide here. Please let me know if this alright with you.
    Appreciate it!

  19. Hey there I am so excited I found your blog page, I really found you by mistake, while I was browsing on Bing for something else, Anyhow I am
    here now and would just like to say kudos for a remarkable
    post and a all round enjoyable blog (I also love the theme/design),
    I don’t have time to browse it all at the minute but I have book-marked it and also added your RSS feeds,
    so when I have time I will be back to read more, Please do keep up the superb work.

  20. I see a lot of interesting content on your blog. You have to spend a lot of
    time writing, i know how to save you a lot of time, there is a
    tool that creates high quality, SEO friendly posts in couple of seconds, just search in google – k2 unlimited
    content

  21. I’m really loving tthe theme/design of your site. Do yyou ever run into any browser compatibility issues?
    A couple of my blog readers have complained about
    my site not operaating correctly in Explorer but looks great in Safari.
    Do you have any tips to help fix this issue?

  22. My spouse and I stumbled over here from a different
    page and thought I might check things out. I like what I see
    so now i’m following you. Look forward to finding out about your web page for a second time.

  23. Hi! Do you know if they make any plugins to assist with SEO?

    I’m trying to get my blog to rank for some targeted keywords
    but I’m not seeing very good gains. If you know of any please share.
    Appreciate it!

  24. I read a lot of interesting posts here. Probably you
    spend a lot of time writing, i know how to save you a
    lot of time, there is an online tool that creates
    unique, google friendly posts in seconds, just type in google – laranitas free content
    source

  25. Hi there, the whole thing is going well here and ofcourse every one is sharing facts, that’s actually fine,
    keep up writing.

  26. I drop a leave a response whenever I like a post on a site or if I
    have something to add to the discussion. It’s triggered by
    the passion displayed in the post I browsed.
    And on this post SEO Step-by-Step: Use Keyword Analytics to Calculate the Most Profitable Keywords | Lean Business Startup.

    I was actually moved enough to leave a thought 😉 I actually do
    have 2 questions for you if it’s okay. Could
    it be only me or does it appear like some of the remarks
    look as if they are coming from brain dead individuals?

    😛 And, if you are posting at additional social sites, I
    would like to keep up with everything new you have to
    post. Would you list every one of your communal sites like your
    linkedin profile, Facebook page or twitter feed?

  27. Everyone loves what you guys are usually up too.
    This type of clever work and coverage! Keep up the great
    works guys I’ve incorporated you guys to my own blogroll.

  28. Gemological Institute of America (GIA) is an international reference
    in the standardization of the measurement of diamond clarity.

    1) the shape: round, princess, radiant, emerald, pear, heart, oval,
    marquise. Very small differences occur from one grade to the
    next.

  29. Sin embargo, otros usuarios podrían apostar por el Peer to Peer TV para ver gratis el Mallorca-Atlético
    de la villa de Madrid. Por sí os interesa, cojo la opción de Canal+ mas por un motivo
    extra que no debe de ver con el fútbol y es poder mirar mi otra
    pasión deportiva, la NFL.

  30. Hurrah, that’s what I was looking for, what a information! present
    here at this website, thanks admin of this site.

  31. This article presents clear idea for the new visitors of blogging, that actually
    how to do blogging.

  32. Thank you a bunch for sharing this with all peoplle you actually understand what you
    are speking about! Bookmarked. Kindly additionally seek
    advice from my web site =). We may have a link alternatee arrangement among us

  33. The safety of your account is one amongst our greatest concerns
    and we now have never receifed any kind of messages abhout a peson gettihg banned
    from School oof Dragons as a consequence off
    utilizing the Faculty oof Dragons Gems aand Coins
    Hack.

  34. I like what you guys are up too. Such clever work and exposure!
    Keep up the awesome works guys I’ve added
    you guys to my personal blogroll.

  35. Não deixe de checar as restrições dos cupons Gulodice Beleza e as regras de cada um
    para se corrobar se desconto é verdadeiramente ajustável
    para os produtos que você está tentando adquirir na
    Gula Formosura com Desconto. http://www.est.hi-ho.ne.jp/cgi-bin/user/tatan/sunbbs.cgi?mode=form2&pass=

  36. I have been exploring for a little bit for any high-quality articles or blog posts
    in this kind of space . Exploring in Yahoo I eventually stumbled upon this website.
    Studying this information So i am satisfied to show that I
    have a very just right uncanny feeling I came upon exactly what I needed.

    I so much unquestionably will make sure to don?t put out of
    your mind this site and provides it a glance regularly. http://clasificadoschilecito.com/index.php/ads/create-a-monster-truck-games-you-can-be-proud-of/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: