Leverage BigQuery for Advanced Internal Link Analysis

Prerequisites

First, you’ll need a CSV containing all your internal links.

At the very least, your file should have a column for the source (origin of the internal link) and a column for the destination (where the link leads). However, if possible, include additional columns for the anchor, status code, and type of link (such as image, text, and hreflang) to enrich your analysis.

For example, I used data from my agency’s website. While it’s a small site with 1,678 pages (including redirects and erroneous pages), it contains 338,656 links when accounting for CSS, JavaScript, sitemaps, and more. Although manageable in a raw Excel sheet, applying custom formulas and filters could become challenging.

Below are my CSV columns:

• Type: Identifies whether the link is from a sitemap, hreflang, canonical, simple hyperlink, image, CSS, etc.

• Source: The page where the link is located.

• Destination: Points to the target page.

• Alt Text: If the link is an image, this column contains its alt attribute text.

• Anchor: The HTTP status code of the destination.

• Status: The status of the destination (e.g., canonicalized, non-indexable).

• Follow: Useful for determining if the link impacts SEO.

• Link position: Indicates if the link is in the navigation, head, content, or elsewhere. Ensure the tool settings are accurate.

• Link origin: Specifies whether the link is only present in the HTML or the rendered HTML post-JavaScript execution. This is helpful in troubleshooting JavaScript-rich websites.

With the file ready and a Google Cloud account set up, what’s next?

There are two options:

1. If your file is under 100 MB, upload it directly via the BigQuery interface

2. For larger files, use Cloud Storage.

Although the process is similar to the first option, I’ll explore the second option (as my file exceeds 100 MB).

Create a bucket and load the CSV

1. Return to the Cloud Hub and click “Cloud Storage” at the bottom left.