I have a tool crush on Screaming Frog. I consider it one of the keys to scalability and I don’t know what I would do without it. Dan Sharp deserves a medal because his tool has saved SEOs countless hours of manual work. Screaming Frog can be used for a variety of things, but my new favorite use is for identifying internal link building opportunities.
Note: If the keyword is in the template of your site (i.e. on every page) this process will not work, this is optimal for long tail or secondary keywords.
Conductor Searchlight is awesome for this, you can find the KWs with the most search volume that are just shy of ranking well.
You need to run bulk look ups for each export, then once you export it from Majestic, delete all the data you don’t need and add a column with the internal link phrase in each export and combine all the data:
Clean up your data, if a page has zero links pointing towards it, maybe it isn’t worth your time to add that internal link. You need to use your best judgement, but if the page is lacking on link juice, I generally remove it from my data set.
Throw all this data into a pivot table and select just your most powerful pages for internal links. Going off of Trust Flow, Citation Flow and AC Rank metrics allow you to prioritize your internal link building. For those of you wondering how to set up and use pivot tables, you wont find that information here… go to Annielytics for that stuff, she makes data sexy. But if you want to see how I set it up, here is a Pivot Table of SEER Interactive’s internal link opportunities optimizing for the keywords: link building, audit, international and pay per click.