I recently came across an excellent post by Ryan Darani from a while back on LinkedIn that shows how to find internal link opportunities using Screaming Frog’s custom extraction feature and Excel formulas. In a nutshell, you use Screaming Frog to scrape your website’s title and body content. After which, you upload the data to Excel, where you use an advanced formula to scan the body text to return a ‘True’ or ‘False’ value if your keyword is found within the text.
After successfully completing the process, I decided to replace the ISNUMBER formula that Ryan uses in his analysis, which returns a TRUE or FALSE value, with a different formula that returns the number of times the keyword is found within the text.
By doing so, I can get a more accurate picture of how many times a specific keyword features within the text. This approach helps find internal link opportunities and conduct a content audit analysis.
Before we start, it’s worth mentioning that you can follow this guide using both Excel and Google Sheets. I will use Google Sheets as it’s free with any Google account.
For the purpose of this tutorial, I used 13 articles from searchenginejournal.com as our sample data.
Let’s get started.
Extract content from your website using Screaming Frog
To avoid repetition, you can follow Ryan’s fantastic guide on the Craig Campbell SEO blog that explains how to extract body content from your website using Screaming Frog’s custom extraction feature: Creating Internal Linking Opportunities Using Screaming Frog
Other SEO scraping tools can be used for this purpose, such as Sitebulb, Oncrawl and Deepcrawl.
Open your CSV file in Google Sheets
Once you’ve extracted your website’s content from Screaming Frog as a CSV, open the file as a Google Sheet. For the purpose of this tutorial, I only kept the Article Content column. However, in a real audit scenario, I would include URL, title (H1), Article Content, word count, and other columns depending on my requirements.
Add your keywords
Add each keyword that you want to identify as a separate column header.
Add the Google Sheet formula to check for keywords
Select the first empty cell under your first keyword (cell B2 in my example) and copy/paste the following formula into the formula bar:
You might have to change the referenced cells in the formula depending on your Google Sheet columns. Cell A2 refers to the location of the content, which we will search for our chosen keyword. Cell B1 is the reference to your first keyword. The $ signs make the formula scalable, so when you drag the cell across your sheet, it automatically adjusts the formula.
How does the formula work?
Google Sheets or Excel don’t have a native way of counting how many times a specific word is found within a cell. So, the formula above combines a bunch of functions to automate this count mechanism.
Using the SUBSTITUTE function, you remove the keyword from your selected cell. We also use the UPPER function to convert all keyword characters to uppercase to avoid case sensitivity issues.
LEN then calculates the length of your text string without your selected keyword.
Afterwards, both totals are subtracted (original text – text without keyword).
Lastly, we divide the total number by the length of the keyword, which tells us how many times your keyword appears in the text.
Drag across the rest of your columns
Drag your cell downwards to add the formula to the other cells in the column. Likewise, you can drag the cell (or full column) across to add the formula to the next column to search for other keywords.
Add color scale (optional)
You can add a colour scale to make it visually easier to identify the number of keywords in each piece of content or page. Select a single column and click on Format > Conditional Formating from the top-level menu. You’ll find the conditional Formating window appears on the right side of your worksheet. Select the Colour Scale tab and from the Format Rules, click on the colour preview box and select your preferred colour scale. I chose the ‘White to Red’ colour scale as I find it works best for the purpose of this task.
There you have it. Using this formula, you can easily visualise the number of times a given keyword is featured within each piece of content on your website. This forumla can automate your internal linking efforts or save a ton of time in doing content audits.
Bonus steps (optional)
You can add extra data layers to make this research even more meaningful. Here are some ideas:
- Match your content against your website’s sitemap. Screaming Frog have a solid tutorial just for that: How To Audit XML Sitemaps
- Connect Screaming Frog or other SEO crawlers to your Google Analytics account and match web traffic with each URL.
- Connect crawler to your Google Search Console account and add clicks, impressions, CTR and SERP position data to each URL.
- Get data from third-party SEO tools, such as Ahrefs or SEMRush, to get backlink data for each page using vlookup. Screaming Frog even offers an API to Ahrefs if you have an account.
- Got access to server log files? Excellent. Add log file data to your analysis to check when was the last time Googlebot visited each URL (and frequency).