How to Automate Internal Link Building using Screaming Frog and Google Sheets

Internal linking is a crucial aspect of SEO, but let’s face it, manually building these links can be a tedious task. To streamline this process, We’ve developed a workflow using Screaming Frog (SF) and Google Sheets that automates internal link building. This method not only saves time but also enhances the efficiency and accuracy of your SEO efforts.

This workflow is a streamlined process designed to check for the presence of both a specific keyword and a specific internal link on a URL.

If the page you are checking contains the desired keyword and already has an internal link to the desired target URL, you know that you do not need to build an internal link from it. But, if the result is that there is no link present, this shows you an opportunity to build an internal link from the desired anchor text on that page without manually checking each one – saving hours!

This is an advanced guide intended for people who have good experience using Screaming Frog and a basic understanding of Python scripts.

The benefits of building internal links

Building internal links is an essential strategy in technical SEO, offering multiple benefits for both user experience and website performance. Firstly, internal links significantly improve user navigation by creating a well-connected network within the website. This interconnectedness not only makes it easier for users to find relevant content, enhancing their engagement and time on site but also helps search engines understand the site’s structure more effectively. Efficient navigation and clear structure are crucial for both user satisfaction and effective indexing by search engines.

Furthermore, internal linking plays a vital role in distributing page authority across the website. By linking from high-authority pages to those with lower authority, the SEO value is shared, boosting the ranking potential of individual pages. This distribution of link equity is key to making the site more competitive in search engine results. Additionally, the use of descriptive anchor texts in internal links reinforces the relevance of specific keywords and topics, aiding in improving search rankings for those terms.

So, onto the guide!

Step 1: Gathering URLs with the target keyword(s)

The first step involves collecting URLs that contain your desired keyword. This process is an automated version of using the ‘site:’ search operator in Google.

Setting up Screaming Frog:

1. Launch Screaming Frog and enter your website URL.
2. Configure your settings and navigate to ‘Configuration’ > ‘Custom’ > ‘Custom Search’.
3. Add a new field, select ‘contains’, ‘text’, and input your target keyword in the ‘content area’.
4. Adjust the area selection or custom link position rules if necessary to refine your search.

Running the crawl:

1. Execute the crawl and, upon completion, go to the custom search results.
2. Export this list for the next step.

Step 2: Utilising the Google Sheet script

The second step involves a script in Google Sheets that further processes your list of URLs that contain the keyword, this is to check if the internal link is present.

Preparing your Google Sheet:

1. Paste the list of URLs into column A of a new Google Sheet.
2. Access the script editor by clicking “Extensions” > “Apps Script”.
3. Replace any existing code with the script provided below, ensuring to replace the placeholder with your target URL.

The script

function searchForStringInURLs() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var data = sheet.getDataRange().getValues();
  
 for (var i = 0; i < data.length; i++) {
  var url = data[i][0]; // Assuming URLs are in column A (index 0)
   
  if (url) {
   var htmlContent = fetchUrlContent(url);
   var searchString = "INSERT YOUR URL HERE";
   var found = htmlContent.indexOf(searchString) !== -1;
    
   // Log "Yes" or "No"
   var result = found ? "Yes" : "No";
   Logger.log("URL: " + url + ", Found: " + result);
    
   // Write the result to Column B
   sheet.getRange(i + 1, 1).setValue(result); // Writing to Column B (index 1)
  }
 }
}

function fetchUrlContent(url) {
 var response = UrlFetchApp.fetch(url);
 return response.getContentText();
}

Running the script:

1. Save and execute the script, then close the editor.
2. Optionally, apply conditional formatting for a more visual result.

The result

In column B of your Google Sheet, you’ll now see ‘yes’ or ‘no’, indicating whether the URL was found within the HTML of the first URL containing the target keyword. This script checks if both the keyword and internal link are present on a page. If ‘no’, it signifies an opportunity to build an internal link from that page, eliminating the need for manual checks.

Scaling for larger websites or checking multiple target URLs

For larger websites, you can modify the script to check for links to different related pages simultaneously, exporting the results to columns C, D, etc. This scalability makes this workflow incredibly versatile for various website sizes.

Script example to check for multiple URLs

For example, to modify the script to check for links to three different related pages simultaneously and export the results to columns B, C, and D, you’ll need to adjust the script to include additional search strings and write the results to the respective columns.

function searchForStringInURLs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  // Define your URLs here
  var searchString1 = "INSERT YOUR FIRST URL HERE";
  var searchString2 = "INSERT YOUR SECOND URL HERE";
  var searchString3 = "INSERT YOUR THIRD URL HERE";

  for (var i = 0; i < data.length; i++) {
    var url = data[i][0]; // Assuming URLs are in column A (index 0)

    if (url) {
      var htmlContent = fetchUrlContent(url);

      // Check for each URL
      var found1 = htmlContent.indexOf(searchString1) !== -1;
      var found2 = htmlContent.indexOf(searchString2) !== -1;
      var found3 = htmlContent.indexOf(searchString3) !== -1;

      // Write the results to Columns B, C, and D
      sheet.getRange(i + 1, 2).setValue(found1 ? "Yes" : "No"); // Column B
      sheet.getRange(i + 1, 3).setValue(found2 ? "Yes" : "No"); // Column C
      sheet.getRange(i + 1, 4).setValue(found3 ? "Yes" : "No"); // Column D
    }
  }
}

function fetchUrlContent(url) {
  var response = UrlFetchApp.fetch(url);
  return response.getContentText();
}

Conclusion

This automated approach to internal link building is a game-changer for SEO professionals. It significantly reduces the time and effort required for this essential task, allowing you to focus on other strategic aspects of SEO. By integrating different tools and using them together in new ways, technical SEO becomes much more efficient.