Ontological Keyword Research

The Foundational Keyword Research is a great starting point for mapping out the keywords that your website is ranking for, however, our Ontological process takes things to an entirely different level.

We call it the Ontological Keyword Research because we adopt a recursive approach which will not only help you find all of the keywords that your website is ranking for, but importantly, groups these terms based on topics and user intent.

This ensures that you cover all bases of the sales cycle whilst highlighting the most important keywords that will convert by tackling each of the categories (or topics) for your website.

This is achieved by reverse-engineering the websites competing for all of the topics and subtopics that are discovered during the process. Instead of simply looking at each term on its own merit, we sort the newly discovered keywords by topic, subtopic and user intent. This provides a much more focused approach in structuring the content on your website.

The above example provides a breakdown of the entire Ontological Keyword Process.

The tools you’ll need are:

Prerequisites in order to complete the Ontological Keyword Research:

Glossary

Subject – A subject refers to a broad niche or subject matter which is generally too broad to be of value for our SEO purposes i.e. Money, Health, Fitness, Marketing etc.

Topic – A topic generally refers to a particular part of a subject i.e. an SEO website may have the following three topics:

Sub-Topic – A sub-topic is a topic that is part of the broader topic. For example, subtopics for Technical SEO might be:

User Intent Keywords – these are keywords that convey the context or intention behind the user’s search.

There are three main types of user intent:

1. Informational – keywords where the user is looking for information (usually a question) on a particular topic i.e. what is machine learning?
2. Navigational – keywords where the user has some notion of what they are looking for, this is usually a particular website/brand/service i.e. lawyers near me
3. Transactional – keywords whether the user intends to purchase a product or perform some sort of conversion i.e. buy running shoes

Step 1: Determining Seed Topics

We will start by determining the seed topics of the website.

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

General Rules:

1. Create a spreadsheet and name it [your site] Ontological Keyword Process.

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 1,000 (maximum) keywords.

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.

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

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

12. Create a new tab called Competitor Details.

13. In the first row of this tab, create the following titles for each column:

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

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

Checklist

1. Determine seed topics based on:
   1. What your site is currently ranking for.
   2. What you want your site to rank for.
2. Create a new spreadsheet labelled [site title] Ontological Keyword Process
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. Create a new tab called Competitor Details and add the header row.

Step 2: 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.

Competitor Discovery

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

2. 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.

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.

3. 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.

Collecting Competitor Keywords

Next, we are going to pull each of the keywords that these competing pages are ranking for.

1. In Ahrefs, click on Organic Keywords in the left-hand sidebar – this will display all of the keywords that the competing domain/page is ranking for.

2. Click Export on the top right-hand side of the page.

3. Select Full Export and under CSV Format, select Microsoft Excel and then Start Export.

4. Give your download a moment to finish before clicking the tray icon in the top right.

5. Click on the new file to start the download.

6. Repeat this process for the remaining 4 competing pages.

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

Configuring 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.

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. Highlight the first row and Bold the text.

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

10. Navigate to the end of your keyword list and select the first blank cell in column A.

11. Select File > Import again and repeat the steps to import the next keyword list.

12. Delete the header cell for the 2nd and all further files.

13. Repeat the same steps until you have a keyword list containing all 5 competitors’ keywords.

14. Delete all columns except Keyword, Volume, and Difficulty.

15. Hit Ctrl + A (or Command + A on Mac) to select all data within the sheet and copy it.

16. Navigate to this tool.

17. Input your copied data into the box and select Remove Duplicates.

The tool will show you how many lines (keywords) it has removed next to the S and C buttons.

18. Go back to your sheet and press the Delete key on your keyboard to erase the selected data.

19. Go back to Text Mechanic and copy the data in the box.

20. Navigate to the top of your sheet and paste the data on your clipboard. You will now have a spreadsheet that only contains unique keywords.

21. Highlight the three columns (Keyword / Volume / Difficulty).

22. Select Data > Create a Filter.

23. Next, select the filter bar next to Volume. Select Sort Z – A so the numbers are sorted from biggest to smallest.

Your spreadsheet should now display all keywords with the highest search volume at the top.

Sorting The Keywords

Initial Pruning

We want to eliminate any keywords that are unrelated (to the website and topics that we have identified) as well as create new tabs for subtopics.

At this stage, we are making broad strokes and will refine the results in the next step where we will merge the topics with too few keywords into larger subtopics.

Here is an example:

Remove the keyword from the sheet if it:

If for example your site sells or affiliates products relevant to a brand, then the keywords with these products and/or brands are valuable and should be added to their own separate tab i.e. if you have a website selling running shoes, then you might want to create a new tab for “Nike”, “Adidas” etc.

Extracting User Intent Keywords

1. Highlight the Keyword Column by clicking on the letter of the column (It should be A)

2. In the menu, click on Format, then Conditional Formatting.

3. A box labelled Conditional Format Rules should be displayed on your screen. Click on the box labelled Format Cells If… and click on Text Contains.

4. In the box labelled Value or Formula input “how”

5. Go to the “Custom” box and click on the Fill Color paint can.
Choose a light red.
This will highlight all keywords with the word “How” red, making them easily identifiable.

6. Repeat the above steps for Who, What, Where, When, Why, Should, and Does.
This will catch most of the Question-based keywords that people use.

After you have done this, you will have highlighted the majority of the question keywords.

7. Create and place them in a new sheet called User Intent [Topic]

With the keywords that are remaining, perform a cursory check to see if there are any repeated keywords that are derivative of the seed topic and have enough search volume to warrant being its own sub-topic.

Then create a new tab and name it after the new sub-topic (or if one exists, add it to a pre-existing tab that is relevant)

For example, if we notice that the Seed Topic “Keto Recipes” returns multiple instances of “Keto Breakfast”, then it may be worth putting the latter into a new sub-topic called “Recipes”.

The last thing we want you to have to do is to restructure your entire site based on the keyword research, so use your best judgement in this case. Likewise, you don’t want to be overwhelmed in the future when you redo the process for the new sub-topics.

For example, if you are selling beard oil, you may target beard oil (as the primary topic for the homepage) followed by benefits of beard oil, organic beard oil, etc as subtopics.

Finalising Your First Seed Topic

1. When the Master File list is complete, right-click the tab and select Delete.

2. Review the remaining tabs that you have.

3. If you have any duplicates, merge them together.
For example, if we had two different versions of Finance Tips, we would merge them together as they are identical.
The same applies to Finance Tips and Money Tips from our example.

4. This is fine, you may wish to keep them as they are, or merge them with the most relevant subtopic.

Ideally, you want to have a minimum of five keywords to work with for each of your subtopics.

In the next few steps, we will be performing the process on these subtopics, which means we’ll be gathering even more keywords into these lists.

It’s important to highlight that we will also be performing the same process on the subtopics as well, so while it is important to make the distinction between two different subtopics, it is also important not to be too granular at this stage.

5. Right-click the tab labelled [topic] (Homepage) and select a primary tab colour.

6. Right-click on the remaining subtopics and select a lighter shade of that colour.

Later, we will be repeating this recursive process with other topics, so choosing lighter shades of the same colour will allow us to see the various groups of topics and subtopics more clearly at a glance.

Repeat for Each Seed Topic

1. Navigate back to your Seed Topics list and select the next topic. In our case, it is “Budgeting”

2. Perform a Google search of your seed topic and gather the URLs of the first five unique competitors as before.

3. Repeat the Reverse Engineering process for each seed topic.

Once you have done this, your Competitor Details list should look something like this.

Note: select a new colour set for each new topic, with a lighter version of that colour for your subtopics.

Checklist

Step 3: Reverse Engineering Competitors – Subtopics

Gathering Competitors and Keywords

1. Review the newly-created subtopics in your worksheet using the list tab in the bottom left corner of your spreadsheet.

2. Perform a Google search of this subtopic and as before, make a note of the first five unique, organic competitor URLs.

Be sure to follow the guidelines from the previous Competitor Discovery section when collecting these URLs.

3. Using Ahrefs to search the competitor under Exact URL, navigate to Organic Keywords > Export > Full Export > Start Export.

4. Download the keyword lists of all five competitors’ pages.

Importing Lists to Your Sheet

1. Move to the bottom of your subtopic and select the first blank cell in column A.

2. Select File > Import, then navigate to Upload and upload your keyword file for that subtopic.

3. Select Append to Current Sheet under Import Location.

4. Select Detect Automatically under Separator Type.

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

6. Select Import Data.

7. Import the keyword lists of all five competing pages that you gathered in the first stage.

8. As you import your data, remember to select the extra header rows and select Delete Row.

9. Copy the items under the Keyword heading of your newly-imported data.

10. Paste this data into Column A in the starting cell.

11. Copy and paste the Volume data in Column B, over the Keyword cell.

12. Copy and paste the Difficulty data in Column C, over the Position cell.

Again, ensure that the Difficulty data lines up with the Keyword and Volume data.

13. Select all of the remaining data within the sheet and press the Delete key to remove it.

Remove Duplicates

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. Navigate back to your sheet and press the Delete key to remove the old data.

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

Sort Keywords

1. Sort the keywords on your sheet according to the previous guidelines.

Note that many of the topics will already have a category. For example, in our case, we found some keywords that related to Credit Counselling.

Add these into the appropriate, pre-existing tab.

You may also come across new topics that are suitable to be added as subtopics. You should add a tab for these too.

2. Repeat this process for each of the new subtopic tabs.

The below example image is an overview of the entire Ontological Keyword Process.

Finalise Your New Tabs

1. If you have any tabs that contain duplicates, merge them together. For instance, in our example, if there were two copies of Credit Counselling, we would merge them together. The same applies for Credit Counselling and Credit Counsellors for example.

2. Change any new tab colours to match the colour-coding scheme that you set out for your topics (darker shade) and subtopics (lighter shade).

Checklist

Step 4: Reverse Engineering – Sub-Subtopics

Now that we’ve covered topics and subtopics, it’s time to look at the sub-subtopics. These are even more granular keywords groups that will further help outlines and structure your content.

1. Using the list tab in the bottom left corner, review the subtopics in your sheet.

The colour-coding system will help you identify which topics were created from the previous step (i.e. look for tabs with the lighter shades).

2. Perform a Google search for the subtopic and pick out the first five unique, organic competitor URLs in each subtopic search.

Remember to follow the guidelines from the previous Competitor Discovery step.

3. Using Ahrefs, search the competitor page under Exact URL, navigate to Organic Keywords > Export > Full Export > Start Export.

4. Download the keyword lists of all five of the competitors’ pages using the top-right tray icon.

Importing Lists to Your Sheet

1. Move to the bottom of your subtopic and select the first blank cell in column A.

2. Select File > Import, then navigate to Upload and import your keyword file for that subtopic.

3. Select Append to Current Sheet under Import Location.

4. Select Detect Automatically under Separator Type.

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

6. Select Import Data.

7. Repeat the data import for all five keyword lists that you downloaded from Ahrefs.

8. As you import your data, select the header row and click Delete Row.

9. Ensure that only the frozen header row in row 1 is present in the sheet.

10. Copy the items under the Keyword heading of your newly-imported data.

Copy all of the data by selecting your starting cell and then pressing the Ctrl+Shift+Down Arrow keys.

11. Paste this data into Column A in the starting cell.

12. Copy the Volume data and paste the data in Column B, over the Keyword cell.

13. Copy the Difficulty data and paste the data in Column C, over the Position cell.

Again, remember to ensure that the Difficulty lines up with the Keyword and Volume data to avoid any mismatched information.

14. Select all other data within the sheet and press the Delete key to remove the data.

Remove Duplicates

1. Copy all data in your sheet by pressing Ctrl+A then Ctrl+C on your keyboard.

2. Navigate to Text Mechanic and remove any duplicates.

3. Navigate back to your sheet and press the Delete key to remove the previous data.

4. Paste (Ctrl+V) the new data from Text Mechanic back into your sheet.

Sort Keywords

1. Review the new set of keywords and sort them in accordance with the previous guidelines.

As before, many of the subtopics will already have been categorised. Remember to add these to the appropriate, pre-existing tab.

Likewise, you may find new topics that are a good fit for sub-subtopics, if so, create a new tab for these sub-subtopics.

Below is an overview of the Ontological Keyword Process, we are now at the bottom layer of the hierarchy.

Finalize Your New Tabs

1. Merge any duplicate sub-subtopics together.

2. Change any new tab colours to match the colour-coding scheme of your topics, subtopics and sub-subtopics.

Checklist

Step 5: Topic Consolidation and Matching

Topic Consolidation

Whilst performing the ontological keyword research, it’s possible that you may have created two topics that are similar to each other – this is the stage that we will identify and merge these.

If any of your subtopics or sub-subtopics have some sort of overlap and are similar enough to be considered as one single topic, simply merge their keywords together.

For example:

In our example, we were identifying keywords for Personal Finance and we found a large subtopic called “Saving Tips.”

One of our other seed topics, however, is called Money Saving Tips.

Therefore, Saving Tips should be merged into Money Saving Tips.

Topic Matching

It may be the case that you come across a topic that might actually be better suited to an entirely different seed topic. In this case, move the subtopic to that category.

For example:

In our example, we were identifying keywords for Personal Finance, but we discovered a large subtopic called Credit Counselling.

However, we also have a different seed topic called Credit.

Since Credit Counselling is a better fit under Credit as opposed to Personal Finance, we move the tab to that section of the keyword research.

Remember to change the colour of the tab to the new topic.

Step 6: Identifying User Intent Keywords

Refresher: What Is a User Intent Keyword?

Remember, there are three main types of search queries:

1. Informational – queries that answer questions
2. Navigational – queries that allow the user to locate a specific website
3. Transactional – queries where the user intends to buy something

The heading for this section, for example, would be an informational keyword if you searched it in Google.

Transactional queries can also be categorised into High Commercial Intent queries like:

How to Identify User Intent Keywords

1. Navigate to the Seed Topics tab in your spreadsheet and right click so that you can rename it to “User Intent Keywords”.

2. Select the data within the sheet and press the Delete key on your keyboard.

3. Add “Keyword, Volume, Difficulty” in each column of the first row and Bold it to create your header row for this tab.

4. With that row still selected, navigate to View > Freeze > 1 Row to freeze your new header row.

5. Go through each of your keyword list tabs and pull all “User Intent Keywords” and place them in your User Intent Keywords tab.

Be sure to only COPY 📄 each row’s data instead of CUT ✂. This is because we want to preserve the data within each topic, but pulling all user intent keywords into one sheet will allow an at-a-glance view of what users are looking for.

Checklist

Step 7: Creating a Keyword Graph

The final step of the ontological keyword research process is to create a keyword graph – this will help you to visualise the search volumes for each of your topics which in turn, will help inform your content creation plan.

Preparing The Data

1. Create a new tab and name it Graph.

2. Go to each keyword list tab in your sheet and navigate to the bottom of the keyword list.

3. Highlight the first three columns in the blank row underneath your data.

4. Go to the paint bucket icon in your tool tray and select any colour.

This will be used as an indicator for the end of the keyword list.

5. Type in Total Volume into Column A.

6. Highlight the row and Bold it.

7. In Column B, insert the formula

=SUM(B2:Bx)

where x is the row number right above the coloured bar.

In our example, it is row 1974, so our formula is:

=SUM(B2:B1974)

8. 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.

9. Continue this process for each tab on your worksheet.

10. Navigate back to your Graph tab.

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

12. Select the row and Bold the selection.

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

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

15. Reference the new totals that we calculated earlier from each tab and input them into the sheet in the appropriate cell in Column B.

Setting Up The Graph

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

2. Select the Chart Type dropdown.

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

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

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

6. Click on the chart.

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

Checklist

Your Ontological Keyword Research is now complete 👏🏻🎉!

You will now have a solid blueprint of the keywords that you need to target based on topics, subtopics and sub-subtopics for your core landing pages.

This process can be repeated as many times as you need.