The SUSO Method: Local Keyword Research

We have adapted our Ontological Keyword Research process with a local approach.

This will help ensure that you are targeting keywords within your specific geographic area.

The tools you’ll need are:

Prerequisites in order to complete the Local Keyword Research:

Step 1: Determining Seed Topics

Start by determining the seed topics of your website.

Remember, the seed topics are made up of topics that the site is currently for and topics that you want your website to rank for.

1. Create a spreadsheet and name it [your site] Local KWR.

2. Rename the first tab to Seed Topics.

3. A great starting point for finding your seed topics is to look through your website. Your website’s main navigation or even the blog secession may help identify which topics you should focus on.

4. Add any topics that you have identified to a list on the Seed Topics tab – ensure that you don’t pick out any more than three.

5. From your Foundational Keyword Research, head over to the Keyword Pivot tab and select the first 500 (maximum) keywords.

If you haven’t created a Keyword Pivot, first complete the Foundational Keyword Research before continuing.

6. Create a new tab called Seed Keywords and copy and paste the selected keywords into that tab.

7. Looking through the list of keywords, make a note of any topics that catch your eye as being important to the website’s content or goals.

8. Add these into your Seed Topics tab.

9. From this list, identify the top-level topics that you want to focus on.

For example, “massachusetts law office” and “law office MA” would become a section called “Massachusetts law office”

10. You’ll end up with a short list of topics to use for your seed keywords – remember to remove any duplicates!

Note that the website that we have used in our example is large, so smaller websites will not require as many seed topics.

11. Right-click on the Seed Keywords tab and click Hide Sheet.

Checklist

1. Determine seed topics based on:

2. Create a new spreadsheet labeled [site title] Local KWR

3. Name the first tab Seed Topics.

4. Identify seed topics by browsing your website’s navigation and/or blog section.

5. Add any topics that you have found to the Seed Topics tab.

6. Copy keywords from the pivot sheet in your Foundational Keyword Research.

7. Create a tab called Seed Keywords and paste the pivot sheet keywords.

8. Skim through the keywords and note any potential topics.

9. Copy and paste the keywords from the Seed Keywords tab into the Seed Topics tab.

10. Create broad topics based on the list pasted into the sheet and remove any duplicate topics.

11. Hide the Seed Keywords sheet.

Step 2: Change Your Browser’s Geolocation

Skip this step if your browser’s geolocation is already set to your location i.e. if you are already searching from your core location.

However, if one of your target locations is in a different city or even country, it’s important to change your browser’s geolocation as this will provide more accurate details about your competitors.

Moreover, remember that search results change based on location, so it is possible that by not changing your geolocation, you are missing out on many local results.

To change your browser’s geolocation to your desired local area, you can use a browser extension or VPN (Virtual Private Network).

Here is a great Chrome extension that can help you do this.

Step 3: Reverse Engineering Your Competitors

The next step in this process is to reverse engineer your competing domains to identify any topics that you may not have covered on your site and to grab the keywords that they’re ranking for.

1. Create a new tab called Competitor Details.

2. In the first row of this tab, create the following titles for each column: Keyword > Competitor > DR > Backlinks > Referring Domains > Organic Keywords > Organic Traffic

3. Highlight the first row and Bold the titles – these are going to be the headings for each column.

4. To make it easier for you to navigate, keep the first row highlighted, and select View > Freeze > 1 Row.

5. Perform a Google Search for the first topic keyword in your Seed Topics list.

6.Grab the URLs of the top five competitors who show up organically (do not include any pages that appear in the ads section) and add these URLs into your Competitor Details tab under the Competitor column.

Note: we are interested in the specific web pages as opposed to the domains that are relevant to your niche.

Avoid capturing listings, social media sites, or authority sites like Forbes as these will not offer much value.

Ensure that the web pages are all from unique domains. If there are multiple pages from the same website, just add the first URL – this is likely the most important/relevant one.

7. Once you’ve selected the top five competitors for this keyword, pull the following metrics in the fields you created in the Excel sheet using Ahrefs’ Site Explorer tool.

Note: ensure “Specific URL” is selected from the dropdown before searching the competitor’s URL, otherwise the tool will display the metrics for the entire domain as opposed to the specific URL that we are interested in.

8. Repeat this process for the remaining topics in your Seed Topics tab.

9. Start with your first topic.

10. Using Ahrefs, input one of your competitor’s URLs at a time, selecting Specific URL from the dropdown before searching.

11. Navigate to Organic Keywords in the left sidebar.

12. Select Export > Full Export > Start Export.

13. Once your export has downloaded, click on the tray icon in the upper right corner of the window and click on your exported file to download it.

14. Repeat this process with the remaining 4 competitors for that topic.

By the end of this process you will have five different spreadsheets that contain all of the keywords that these competing pages are ranking for.

Checklist

Step 4: Compiling Your Keywords

Creating the Master File

1. Create a new tab in your spreadsheet and call it [topic] Master File.

2. Select File > Import.

3. Upload the first keyword .csv file you just downloaded by dragging it onto the uploader.

4. Select Append to Current Sheet under Import Location.

5. Select Detect Automatically under Separator Type.

6. Select No under Convert Text to Numbers and Dates.

7. Finally, click Import Data.

8. Repeat this process with the remaining 4 competitor keyword lists.

9. Highlight the first row and Bold the text.

10. Whilst the first row is still highlighted, select View > Freeze > 1 Row.

11. Delete the extra header rows on your sheet.

Pulling Compound Keywords

A popular tool we like to use to help quickly generate “LSI keywords” is LSIGraph.

However, in our Latent Semantic Indexing chapter, we discussed how LSI keywords are not representative and can be misleading in how they are described in the SEO industry, which is why we call them Compound Keywords instead.

In this step, we will pull out compound keywords using this helpful tool.

1. Navigate to https://lsigraph.com/ and type in the following: [topic] [location]

2. Click Generate.

3. Highlight and copy all of the results that are generated by the tool.

4. Navigate to Ahrefs and click on Keyword Explorer.

5. Paste your results from LSI Graph into the keyword explorer box and click Search.

6. Select Export > Export.

7. Generate LSI keyword lists and export them using Ahrefs for several variations of your topic.

For example:

8. Navigate back to your keyword research sheet and select File > Import.

9. Import your LSI keyword lists.

10. Select Append to Current Sheet under Import Location.

11. Select Detect Automatically under Separator Type.

12. Select No under Convert Text to Numbers, Dates, and Formulas.

13. Select Import Data.

14. Delete the extra columns, ensuring that you leave: Keyword, Volume, and Difficulty.

Removing Duplicate Keywords

1. Press Ctrl+A then Ctrl+C on your keyboard to select and copy all of the data from your sheet.

2. Paste the selected data into the Text Mechanic tool to remove any duplicates.

3. Copy the new items in the text box.

4. Navigate back to your sheet and press the Delete key to remove the old data.

5. Paste the new data from Text Mechanic back into your sheet so that you now only have a list of unique keywords.

Repeat all of the steps in Step 4 for each topic in your Seed Topics list.

Checklist

Step 5: Sorting Keywords

At this stage, we want to sort the keyword data that we have collected from competitors and the LSIGraph tool alphabetically.

1. Select Column A, B, and C.

2. Click the Filter icon in the tool tray.

3. Select Difficulty and Sort Z – A.

4. Delete anything over 80 Difficulty.

5. Select the Volume filter and Sort Z – A.

6. Sort your results and remove any keyword from the sheet if it:

If you come across a keyword that is derivative of the seed topic (or a relevant new topic), either create a new tab or add it to a pre-existing tab if one exists).

Note that each new tab should be representative of a subtopic that you want to target.

Keywords that make good subtopics include terms like review, top, best, near me, local, etc.

In our example, we are targeting a personal injury lawyer in Boston, MA. Therefore, we would include personal injury lawyer, personal injury attorney, car accidents, medical malpractice, etc., all as subtopics.

To quickly check keywords (i.e. if you are not sure if a term is branded or non-branded etc), perform a quick Google search and evaluate the results. Based on the search results, you can determine whether the keyword fits into your subtopics or not.

Important: remember that the goal with this keyword research is to highlight good local keywords. For example, it’s better to keep [topic] [location] keywords than it is to keep [topic] keywords – because the latter, are not localised.

Each subtopic should typically have a minimum of five keywords. If you encounter a subtopic with fewer keywords, it may be worth seeing if you can merge these with another relevant subtopic.

In the next few steps, we will recursively perform the same steps on the subtopics, gathering more keywords into these lists.

Finalising Each Tab

Now that we’ve sorted the keywords based on the subtopics, the last thing we need to do is finalise each tab to gain important information such as the total search volume for each subtopic.

1. Navigate to the bottom of each tab and select the first three cells under your data.

2. With these cells highlighted, select the paint bucket icon in the tool tray and select a color.

3. Type in “Total Volume” into Column A.

4. Highlight the row and Bold it.

5. In Column B, insert the following formula =SUM(B2:Bx) where x is the row number right above the colored bar.

In our example, it is row 753, so our formula is: =SUM(B2:B753).

6. Press Enter.

This is going to give us the total search volume for each keyword tab.

Note that Google Sheets may return 0 despite the numbers being present.

If this happens, select the data you’re trying to call and navigate to Format > Number > 0.

7. Repeat this process for each tab in the worksheet.

8. Once your master file is empty and you have completed all subtopic tabs, right click on the tab and select Delete.

Checklist

Step 6: Creating A Keyword Graph

The sixth and final step is to present your keyword data as a graph – this helps illustrate and highlight the most “important” subtopics based on the total search volumes we calculated previously.

1. Right click and rename your Seed Topics tab to Graph.

2. Delete the data inside that sheet as you no longer need it.

3. In the first row, type Topics and Total Volume.

4. Select the row and Bold the selection.

5. Click on your paint bucket icon in the tool tray and select a color to indicate a header row.

6. Type the names of all tabs within the worksheet into Column A.

7. Reference the new totals in each tab and input them into the sheet in the appropriate cell in Column B.

8. Select Insert > Chart to bring up the Chart Editor.

9. Select the Chart Type dropdown.

10. Scroll down to select 3D Pie Chart under Pie.

11. The Chart Editor will automatically detect your headers and display the data from this tab.

12. If necessary, you can modify these items in the Chart Editor.

13. Click on the chart.

14. Use the handles to expand and move the image next to the data in your sheet.

Checklist

Your Local Keyword Research is now complete.

You will now have a solid blueprint of the keywords that you need to target based on your desired locations.

This process can be repeated as many times as you need for topics, subtopics and sub-subtopics.