1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Local Keyword Research In Less Than 10 Minutes [Short Guide]

Discussion in 'Local SEO' started by Jacob Sterbenk, Jul 16, 2013.

  1. Jacob Sterbenk

    Jacob Sterbenk Junior Member

    Joined:
    Sep 3, 2012
    Messages:
    144
    Likes Received:
    37
    Home Page:
    Dear BHW members:)

    I've been a member of BHW for almost a year and I must say that I have learned a lot here. I want to give something back to the forum - there are many (great!) SEO guides available on BHW, so there is no point of writing another one. But, there aren't many guides about keyword research, so let me present you my technique of looking for the most profitable keywords on Google with the help of Google Keyword Toll and MS Excel.

    This is the process I use when doing local keyword research and so far it worked extremely well. Those of you who already have your own local keyword research system, feel free to comment it and help me make it even better. And, those of you who don't have the system yet, you may want to test this one.
    I am going to talk about "local" keywords because it is much harder to find good local keywords than it is to find good "global" ones. Also, there are many marketers that try to rank for local keywords as well - including me.:)

    Why target local keywords?


    There are several reasons why people want to rank for local keywords. Mostly it's because they want to make money with it. :) In general, people want to rank for local keyword because they are offering a local service - their target market are people in that area and they are only interested in that keywords. Others want to rank for local keywords because their consider local niches as "micro niches". It's much easier to rank their and the traffic is more profitable because the visitors are more targeted.

    So, no matter what your reason is, keyword research is extremely important because you need to know which keywords are worth the trouble and which aren't (of course, this doesn't mean that we can't use them for the diversification purposes).

    The problem with the local keyword research

    The "problem" (challenge) with the local keyword research is in the fact that you need to filter many "global" keywords in order to find the best local ones. Google Keyword Tools suggests all potentially related keywords, regardless of the "targeted location". The process of filtering 800+ keywords manually can take up to 3 hours, but with the help of MS Excel it can be done in less than 10 minutes (without making compromises on the quality).

    We'll take the "dating Los Angeles" as an example niche. When I enter "dating Los Angeles" in the Google Keyword Tool (which is still the best keyword research tool, all other tools are getting data from it), I get the 800 results with the following on top:

    speed dating los angeles
    dating in los angeles
    speed dating in los angeles
    asian dating los angeles
    online dating los angeles
    dating service los angeles
    date ideas los angeles
    los angeles dating
    dating coach los angeles
    dating services los angeles
    free online dating sites
    dating los angeles
    online dating sites
    dating los angeles
    free dating sites
    dating los angeles
    dating sites free
    dating los angeles
    free dating websites
    dating los angeles
    best free dating sites
    dating los angeles
    dating websites free
    dating los angeles
    dating sites for free


    As you can see, many terms (at the beginning of results) are actually local terms - like "speed dating los angeles" or "dating in los angeles". However, there are also many terms that aren't locally targeted - like "dating websites free", "best free dating sites", "dating sites for free", etc.

    These keywords can be used when building links (you actually need to use them to diversify your link profile), but they should not be your main target, because your website content will probably target local people only, which means that people from other areas won't convert as well as local people.

    Filtering local keywords from global ones


    So, what we need to do next is to filter local keywords from global ones - for that, we need to export the list of keywords from Google Keyword Tool and open MS Excel. Before you export data, don't forget to set the "match type" from "broad" to "exact". This way, we will know how much traffic we can expect from it.
    You simply click the "download" button and select "CSV for Excel" format. Then click the "download" button in the pop-up. After the download is finished, open the file in Excel. Now, Excel will help us sort keywords. Basically, we need to find keywords that include "los angeles" (because these keywords are relevant to us) and then check which one of those keywords has the highest number of traffic.
    GKT.jpg

    So, to find all keywords with the "los angeles" included, select the column with the keywords (first column). Then, click on the "conditional formatting" under the "Home" tab. Under "conditional formatting", go to Highlight Cell Rules>Text That Contains and type "los angeles" in it. You can also select the color you want to mark your cells with. Then simply click the "ok" button and Excel will automatically color all cells with "los angeles" in it.

    Now, we need to filter keywords by color so we can remove the "unrelated" keywords. Now, select all fields (Ctrl+A) and click the "Data" tab. Then, click on the "Filter" button. You will now see a dropdown on the first cell of the column. Click on the dropdown and select Sort by Color>Sort by Cell Color and choose the select the color that you used for highlighting. Now, only colored cells (cells with "los angeles") will stay in Excel. After that, you can clear the formatting rules from the "Conditional formatting" drop down menu (which can be found under "Home" tab).

    So, we now have a list of local keywords. It is now time to go through the list and remove keywords that aren't related to our niche (or are not related well enough). This shouldn't take long, since most keywords were automatically filtered.

    Sorting keywords


    After we know that all keywords on our list are relevant, we need to discover which ones are the most important for us. Select all cells (Ctrl+A) and click the "Data" tab and select "Sort" option from the "Sort & Filter" panel. You will see a dialog box - in the "Sort by" field select "Local Monthly Searches". In the "Sort on" field select "Values", in the "Order" field select "largest to smallest". Then simply click the "OK" button and your data will be sorted. At the top, you will see the keyword with the highest number of local monthly searches, which is usually the keyword you should go after.
    excel.png

    What to do next?

    Well, the most logical step would be to check the SEO competition. You can't do this with the Google Keyword Tool, but you can easily import the list of keywords in tools like Market Samurai and check the competition there. You need to check the competition of all keywords that have enough traffic to be used as main keywords.
    What to do with the global keywords?

    As mentioned before, the list of global keywords can be used when building links - it does not look natural if all your anchor texts include local terms.

    Conclusion

    As you can see, there is nothing special about the local keyword research. All you need to do is use all the tools (GKT, Excel) properly and you will be done very quickly. Since I started using this system, it never takes more than 10 minutes for the whole research.

    I look forward to reading your comments and suggestions for further improvements!

    Jacob Sterbenk:)
     
    • Thanks Thanks x 7
  2. Nerevar

    Nerevar Jr. VIP Jr. VIP

    Joined:
    Jun 30, 2010
    Messages:
    426
    Likes Received:
    168
    Weird, great post but no comments.

    Anyway, this is especially useful in the case of thousands of keywords that you need to sort.
     
  3. Jacob Sterbenk

    Jacob Sterbenk Junior Member

    Joined:
    Sep 3, 2012
    Messages:
    144
    Likes Received:
    37
    Home Page:
    Maybe people are not working in local niches? From my experience, local niches are much easier to rank and the competition is much lower then in "global" niches.
     
  4. morgan456

    morgan456 Jr. VIP Jr. VIP Premium Member

    Joined:
    Nov 26, 2010
    Messages:
    2,120
    Likes Received:
    248
    Gender:
    Male
    Occupation:
    SEO company
    What you said is right...! Competing with 1000 people is much easier than Competing with 1,00,000 people.......!


     
    • Thanks Thanks x 1
  5. Jacob Sterbenk

    Jacob Sterbenk Junior Member

    Joined:
    Sep 3, 2012
    Messages:
    144
    Likes Received:
    37
    Home Page:
    And, because the traffic is more targeted, one visitor is worth more than in general niches. The only "challenge" is monetization (you need to sell leads in order to make money, Adsense is not the best option here).
     
  6. Andres1986

    Andres1986 Jr. VIP Jr. VIP

    Joined:
    Sep 14, 2011
    Messages:
    2,445
    Likes Received:
    531
    Gender:
    Male
    Occupation:
    Keyword Research and Content Marketing Expert
    Location:
    Keyword Research Land
    Good post about local keyword research. I would add that in most cases, in relation to competition, you'll also have to research how well optimized first page results are for G+ Local as very frequently such results will be present in the case of keywords with a geographical reference in them.
     
  7. Nuruddin

    Nuruddin Newbie

    Joined:
    Jul 17, 2013
    Messages:
    29
    Likes Received:
    4
    What you said is correct...! Competing with 1000 people is much easier than Competing with 1,00,000 people.......!

    Good post about local keyword research. I would add that in most cases, Thanks for sharing important info
     
  8. Jacob Sterbenk

    Jacob Sterbenk Junior Member

    Joined:
    Sep 3, 2012
    Messages:
    144
    Likes Received:
    37
    Home Page:
    That is true - however, Google doesn't show only "local" results on first pages. They only show 3-7 such results, others are regular results. In most cases, regular results are ranked above "local" results, so they are definitely worth the trouble.
     
  9. Izaguf

    Izaguf Jr. VIP Jr. VIP

    Joined:
    May 1, 2014
    Messages:
    418
    Likes Received:
    32
    Home Page:
    Thanks, saved it. Gonna try it. For me, work with keywords is the most boring part.
     
  10. Vesania

    Vesania Regular Member

    Joined:
    Nov 29, 2011
    Messages:
    315
    Likes Received:
    142
    Occupation:
    Full-Time Procrastinator
    Location:
    Santa Paula CA
    Home Page:
    Finding keywords may be boring, but filtering, testing & planning out is the most viable part of good returns.

    Great guide nonetheless! We use this site to generate a multitude of local keywords based off the seed keyword: http://5minutesite.com/local_keywords.php
     
  11. Ancillarium

    Ancillarium Newbie

    Joined:
    Oct 23, 2014
    Messages:
    17
    Likes Received:
    1
    Location:
    Buffalo, NY
    Question: How many monthly searches are required before you consider a keyword to be worth pursuing?