Here's what you'll need
First, you're going to need access to an SEO tool that will allow you to both: check all the keywords in the top 100 that each of your website's pages rank for, and finally, a rank tracker to discover what other pages rank higher than the original for each of those keywords. Some of the better keyword checking options are Moz, Semrush, and Serpstat because they all offer free trials. Even Scrapbox's RankTracker will work for the rank tracking portion. However, for the purpose of this demonstration, we'll be using Serpstat for its affordability, ease of use for this unique purpose, and the fact that many marketers have access to a legacy or LTD account. It's important to note however that you may use whatever combination of tools at your disposal to get the job done, but if you don't already own one of these tools, then I'd suggest you wait until the end of this tutorial before you go out and make a purchase.
The second thing you'll need is access to basic spreadsheet software. For the purpose of this demonstration, we'll be using Microsoft Excel because almost everyone knows how to use it, but something like Google Sheets should work given you understand how to translate the formulas I'll be providing later in the tutorial.
Step 1.) Checking Your Website's Internal Linking Suitability
Before you commit to exploring this method any further, you need to check whether or not your website is a good candidate for this internal linking method. In short, if your website is ranking for hundreds of keywords then it's worthwhile to explore this further. Any well-established website with a blog or many various keyword-rich product pages will be great candidates for this internal linking method. The good news is that you can check your website's suitability using a free Serpstat account and navigating to the "Tree View" report for your domain, here's how:
On the left-hand menu click "Website Analysis" > "Domain Analysis" > "SEO Research" > "Tree-View," then at the top of the page change the grey drop-down menu from "With subdomains" to "Root domain," next, enter your domain in the white box above, and finally, click on the "Search" button. If you're in the right view then you'll see something like this:
What you're concerned with here is the number of result pages. Essentially, the more results you see here the more internal linking ideas you'll discover later on in the process. Note: your free account only allows you to see 10 results per page, which is why you only see your home page, so this doesn't mean there are only 10*600 results, in this case, there are at least 100*600 results found. Additionally, do not be concerned about what you see here because the first 10 results are from the non-https version of the home page, we'll need to dig further into the results pages and set up filters to get into the good stuff.
At this point in the tutorial, you'll need a premium Serpstat account to follow along. Once you've obtained premium access, repeat the first step to view the full report and move onto the second step.
Step 2.) How to properly filter and export the data
a.) In short, we'll be organizing our exports in order by search engine results page, up to SERP 10 to be exact. To do this you'll need to utilize the Tree view report's filter feature located directly above the keyword column. From there you'll see a drop-down menu labeled "Filter" where you'll need to select the following: "Domain's position for a keyword" > "Between" > 1 | 10, this will give us the results for the 1st SERP.
IMPORTANT note: At the very bottom right of the screen you'll notice you can display up to 500 results per page, but depending on your plan this will differ. I understand that many of you might still be using a legacy or LTD account, so be aware that you'll only be able to export 100 results. If this describes you, then what you need to know is that additional filtering may be necessary in order to ensure no results are skipped upon export. If you notice that the "Pos" column is only showing position 1, then this is an indication that there are likely more results for position 1 that are hidden from your view on the 2nd results page. To remedy this, you can set the filter to 1|5, then 6|10, or even 0|1, then 2|3, etc. Of course, this type of aggressive filtering will eat away at your credits but it's necessary if you want to use your LTD.
b.) Next, you'll need to export the data for SERP 1. For this, I'd recommend setting up a nice clean folder on your desktop and label it something like "Internal Linking for Website Name." Now, although Serpstat retains your exports for future download, I recommend you download load them one at a time after each export and rename each file by SERP as you go along. Why? Because of the fact that Serpstat's reports do not contain any identifiable filename information, it's very possible that you could lose track of what SERP you've downloaded. This will become even more evident if you're using an account with limited results per page as previously discussed, so don't say I didn't warn you! Anyway, to export the data simply click on the "Export" drop-down menu on the top right-hand side across from the "Filter" menu, and select "CSV." On the next screen, you only need to select the "URL," "Volume," and "Keyword Difficulty" checkboxes as these are the most useful for the purpose of internal link building (these settings will remain). Next, when you see the grey bar popup you may click the white download link, or alternatively, click on the green download icon just to the right of your profile picture. If you choose the latter, you'll need to download the query nearest to the top of the list.
c.) Repeat steps a-b for the next SERP. For example, to obtain the data for SERP 2 you'll want to set the same filter but with the range of 11-20. For SERP 3 you'll use 21-30, and so on and so forth until you've downloaded all 10 SERPs.
Step 3.) How to combine your data
Now that you have your internal linking folder full of various csv files, you'll want to head on over to merge-csv.com to merge them into just one csv. To do so, you can simply drag and drop all of the files into Merge CSV at once, but make sure you keep the setting "Keep header (index) only in first file" checked. Next, click on the grey button "Merge CSV-files" to merge and download your combined csv file. Note: you may need to go through your final csv file and delete some of the header text and blank lines if they were not removed properly during the merge. In the end you should be left with a single csv file with only headers in row 1.
At this point, I'd recommend saving the file as an Excel Workbook because this will allow us to retain our formatting moving forward.
Step 4.) Sorting and preparing the spreadsheet
a.) First, highlight row 1, click "Data," and "Filter" as shown here: