Decoration Circle
Advanced SEO Textbook
2

Ontological Keyword Research

Our Ontological Keyword Research process adopts a recursive procedure which focuses on grouping keywords based on topical and user intent.

Topic Details
Clock icon Time: 60
Difficulty Intermediate
Topic Status
Boy image Enabled

Content hidden.

This module is not avalible yet.

Back to homepage

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.

Ontological Keyword Research

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

The tools you’ll need are:

  • Ahrefs
  • Google
  • Your Website

Prerequisites in order to complete the Ontological Keyword Research:

  • Foundational 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:

  • On-Site SEO
  • Off-Site SEO
  • Technical SEO

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

  • Site Speed
  • Structured Data
  • Mobile SEO

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:

  • Try to be as precise as possible when thinking of topics.
    For example, if you have a website about Injury Law then “Law” is far too broad because it doesn’t offer much value to the user.
  • Start small and aim for between one to three seed topics.
    Any more than three topics will be impractical at this stage of the process as you will end up covering too many keywords.
  • Ensure that the topics you select are relevant and focus on what the end-user is looking for.
    For instance, if you have a website that sells swimming costumes, then selecting topics like snorkels or swimming equipment will not offer as much value to your audience. Therefore, try to select topics that will ultimately achieve ROI for your business.
    For example, if you have a website that focuses on ginger, then instead of performing the recursive process for tangential topicals like turmeric or teas, we would focus on identifying keyword opportunities that users may be interested in, such as unique ways of incorporating ginger into their everyday lives.

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.

We are trying to combine as many topics as possible. In our example, “finance website reviews” and “loan website reviews” have been grouped into the Top-Level topic “Reviews”.

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.

12. Create a new tab called Competitor Details.

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

  • Keyword
  • Competitor
  • DR
  • Backlinks
  • Referring Domains
  • Organic Keywords
  • Organic Traffic

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.

We are interested in the specific web pages as opposed to the domains.

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.

  • DR (Domain Rating) – this tells you the strength of the target website’s backlink profile on a logarithmic scale from 0 to 100, where 100 is the strongest.
  • Backlinks – this tells you the total number of backlinks pointing to the web page.
  • Referring Domains – this tells you the total number of domains linking towards this web page.
  • Organic Keywords – this tells you the total number of organic keywords that the web page is ranking for.
  • Organic Traffic – this provides an estimate of the number of organic visits the web page receives each month.
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.

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.

Repeat these steps to import the keyword lists for all competitors you selected.

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:

  • Misspelt or incomplete
  • Too broad, i.e., finance or manage money
  • Irrelevant to the topic
  • Keyword Difficulty of 80+
  • Branded

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.

There may be other words that qualify for this step. For example: for an online educational site, you may want to filter keywords based on the word “classes” or “courses” as a means to identify other easy user intent keywords.

Likewise the word “is” could also be used for informational queries i.e. “is chocolate vegan”.

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.

Ensure that each new tab that you add is representative of a subtopic that you want to target.

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.

Helpful Hint 🆘

If you are unsure as to what type of keyword you’re looking at, perform a quick Google search to see what kind of results are displayed in the results. This will help you to determine if, and where, the keyword fits into what you’re looking for.

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.

You can access a different list view by clicking the Tabs icon, next to the New Tab icon.

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.

Note that some subtopics may not have enough keywords to justify having their tab.

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.

Keep the subtopics as general as possible.

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

  • Perform a Google search of your first seed topic
  • Collect the URLs of the top five unique, organic competitors
  • Add the URLs to the Competitor Details tab
  • Using Ahrefs, pull each competitor’s details to fill out the tab
  • Using Ahrefs, export each of the competitor’s keyword lists
  • Create a new tab and call it [topic] Master File
  • Import the keyword lists, remembering to append each one to the sheet.
  • Bold and freeze your header row.
  • Select all of the data within the sheet.
  • Navigate to Text Mechanic tool and input your copied data
  • Select Remove Duplicates
  • Go back to your sheet and press the delete key to remove the previous data
  • Go back to Text Mechanic and copy the data in the box
  • Navigate to the top of your sheet and paste the data from Text Mechanic
  • Select your three columns of data (Keyword / Volume / Difficulty)
  • Select Data > Create a Filter
  • Select the filter bar on the Volume header. Select “Sort Z-A” so the numbers are greatest to least.
  • Sort your keywords.
  • Right-click on the Master File tab and select Delete.
  • Review the tabs you have and merge any duplicates together.
    • If you have subtopics with too few keywords, look through other subtopics to find a suitable subtopic to merge with.
  • Right-click on the [topic] (Homepage) tab and select a primary colour
  • Right-click on the remaining subtopics and select a lighter version of that colour.
  • Navigate back to your Seed Topics list to reference the next seed topic.
  • Repeat the reverse engineering process until you have completed every topic in the tab.

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.

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

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

You can quickly copy all of the data by selecting your starting cell and then pressing the Ctrl+Shift+Down arrow keys.

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.

To avoid any mismatch with the keywords, ensure that the Volume data lines up with the Keyword data.

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.

Ontological Keyword Research

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

  • Review the newly-created subtopic tabs.
  • Google your subtopic and capture the first 5 unique, organic competitor URLs.
  • Export their keyword lists.
  • Navigate to the bottom of your subtopic tab.
  • Import all 5 new keyword lists, appending them to the bottom of the tab.
  • Copy data under the newly-imported Keyword header.
  • Paste that data into Column A under your previous keywords.
  • Copy data under the newly-imported Volume header.
  • Paste that data into Column B under your previous volume count.
  • Copy data under the newly-imported Difficulty header.
  • Paste that data into Column C under your previous difficulty count.
  • Ensuring the data lines up, delete excess columns in the sheet.
  • Navigate to Text Mechanic and remove duplicate items from your sheet.
  • Delete previous data in the sheet.
  • Paste the new data with duplicates removed.
  • Sort the new keywords.
    • Many subjects you find in these keywords will already have a tab elsewhere in your sheet by now. Instead of creating a new tab, add it to a pre-existing tab.
  • Repeat this process for each of your new subtopic tabs.
  • Merge any duplicate topics together.
  • Change the tab colours to the topic’s colour scheme.

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.

Remember to ensure that the Volume lines up with the Keyword data to avoid any mismatched information.

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.

Ontological Keyword Research

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

  • Review the newly-created subtopic tabs.
  • Google your subtopic and capture the first 5 unique, organic competitor URLs.
  • Export their keyword lists.
  • Navigate to the bottom of your subtopic tab.
  • Import all 5 new keyword lists, appending them to the bottom of the tab.
  • Copy data under the newly-imported Keyword header.
  • Paste that data into Column A under your previous keywords.
  • Copy data under the newly-imported Volume header.
  • Paste that data into Column B under your previous volume count.
  • Copy data under the newly-imported Difficulty header.
  • Paste that data into Column C under your previous difficulty count.
  • Ensuring the data lines up, delete excess columns in the sheet.
  • Navigate to Text Mechanic and remove duplicate items from your sheet.
  • Delete previous data in the sheet.
  • Paste the new data with duplicates removed.
  • Sort the new keywords.
    • Many subjects you find in these keywords will already have a tab elsewhere in your sheet by now. Instead of creating a new tab, add it to a pre-existing tab.
  • Repeat this process for each of your new subtopic tabs.
  • Merge any duplicate topics together.
  • Change the tab colors to the topic’s color scheme.

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:

  • Buy Now keywords i.e. Buy, Discount(s), Deal(s), Coupon(s), Free shipping etc.
  • Product keywords i.e. Branded searches (brand-name goods), Specific products (“iPhone 6” etc.), Product categories (“summer dresses”, “insect repellant”, “beach accessories” etc.), Affordable/Best/Cheapest, Comparison, Review, etc.

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

  • Navigate to your Seed Topics tab.
  • Rename it User Intent Keywords.
  • Select the data inside this sheet and delete it.
  • In the first row, add Keyword, Volume, and Difficulty in their own columns.
  • Select the row and Bold it.
  • With the row selected, select View > Freeze > 1 Row.
  • Go through each of your tabs.
  • Pull all “user intent keywords” and place them in the tab, accompanied by Volume and Difficulty data.
    • Be sure to COPY each row’s data, not cut. We want to preserve the data in individual sheets but provide an at-a-glance user intent sheet as well.

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

  • Create a new tab and name it Graph.
  • Move to each keyword list tab in your sheet.
  • Navigate to the bottom of the list.
  • Highlight the row underneath your data and fill it with a colour.
  • Type “Total Volume” into column A.
  • Highlight the row and bold it.
  • Type “=SUM(B2:Bx)” into Column B.
    • x is the row number right above the now-coloured bar or row.
  • Repeat this step for each keyword list tab.
  • Navigate back to your Graph tab.
  • In the first row, type Topics and Total Volume in their own columns.
  • Select the row and Bold the selection, filling it with a header colour.
  • Type in the names of all tabs into Column A.
  • Reference the new totals in each tab and input them into the sheet in the appropriate cell in Column B.
  • Select Insert > Chart.
  • Under Chart Type, select 3D Pie Chart.
  • Click on the chart and use the handles to expand and move the image next to the data in your sheet.

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.

🎉 Did you finish the article?

Mark this topic as complete