What’s your site’s spread looking like?
It’s a question that, until recently, I hadn’t considered answering, mainly because I’ve not seen a link spread graph before. They aren’t featured in Google Analytics, and you can’t measure a link spread with Webmaster Tools or any other free site tool.
Link spread means measuring the number of internal links (in links) vs. external links across a site. This exercise opens up a number of opportunities and illustrates what content is prioritized by the site owners versus what content has caught the attention (and external links) of the wider Web.
Link Spread in a Graph
The X axis represents each page on the site. The Y axis illustrates the number of links pointing to any given page; the left Y axis measures the number of internal links, while the right Y axis measures the number of external links.
In this example above, we can identify a few pages that hold very few internal links (the blue line) but are linked to from external sources (the red bar) quite frequently. This suggests the pages are highly informative. If a page has a large number of external links, there is an audience seeking whatever information is available on that page. In contrast, if we turn our attention to the left hand side of the graph, we see several pages with a large number of in links (blue line) but very few external links (red bar).
With these findings in mind, we can drill down into the content and pull out those “stand out pages” that don’t have the visibility of other internal pages, and start to make alterations so the popular content is more easily accessible internally, too.
How To Make Your Own Link Spread Graph
You will need the following three tools:
Screaming Frog – Free “Lite” version crawls up to 500 pages. A subscriptions costs £99.00 for a year. We’ll be using the “in links” measurement from Screaming Frog’s report.
MajesticSEO – The free version allows you to pull data for only the site you own, so you should be able to pull a list of external links pointing towards your own pages. If you have a subscription to another link scraping tool, feel free to use that as a MajesticSEO substitute.
Microsoft Excel – I’m using Excel 2010.
Establishing Your Inbound Links
Run a report in Screaming Frog.
Export the data by clicking the “Export” button.
Open the exported spreadsheet and identify the columns entitled “Address” and “In Links.” For the screenshot below, I have hidden some columns of data.
Create a new Excel document. Label the first tab “internallinks”. Paste the two columns of data into the new document sheet “internallinks”. It should look like this:
Establish Your External Links
Run a domain-level report in MajesticSEO. From the resulting data, select the “Top Pages/Links” table, and click the “here” link at the bottom of the screen to view the more-detailed report. From this page, the bottom of the table will have an anchor text link which reads “Export report CSV.” Do the export.
From the exported CSV, identify the “URL” column (column A) and the “TotalBackLinks” column (Column C).
Create a new sheet entitled “externallinks.” Paste the data from the red columns into the new sheet. You should end up with a sheet that looks like this:
Merge the Inbound Links and External Links
Now, we must incorporate the number of external links from MajesticSEO and the internal link data from ScreamingFrog. To do this, we will be using the VLOOKUP command. Don’t be scared! It’s not nearly as intimidating as it looks!
For this merge, we are going to shift the data from the externallinks sheet into the internallinks sheet.
In the internallinks sheet, create a new row in column C entitled “External Links”. In cell C3, create the following VLOOKUP formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s break this formula down:
Lookup_value – the value we want to search for in the second sheet “externallinks”
Table_array – the table of data we want the lookup_value to be looked up in. Don’t hesitate to jump to the second sheet of data to select the table; the formula will jump between the sheets with you. After you have selected the table, be sure to hit F4 key – it creates the dollar signs in the formula and locks the table in place so the formula won’t scroll down.
Col_index_num – the column of data you want to extract from the externallinks sheet. For this, type in a “2.” Annoyingly, although we consider the data to be in column B, Excel requires a number value – so column B is the second (“2”) column.
Range_lookup – select False. We want to see an exact value–not a range–so we must put in a false.
Be sure to drag the formula down to the bottom of the sheet.
Clean Up the Formula
You may notice a number of cells reading “N/A” – non-applicable. This means the formula could not find a match for the URL on the second externallinks sheet. The reason it couldn’t find a match would be because there are no links to the page, so it wasn’t downloaded from the MajesticSEO CSV.
Create a filter and select “sort smallest to largest.” This will bring all the #N/A results to the top. Replace all Non-Applicable formulas with 0.
Filter the Inlinks, select “sort largest to smallest.”
Build a Graph
Select the data, then under Insert > Charts, select to create a line graph.
Now you have a line graph – congrats!
The external links are probably looking a little low.
We want to make the red line into a bar chart AND correlate those numbers to the Y axis on the right of the graph.
To make a bar chart, we need to select all the red points, as done in the image above. Then select Insert > Chart > Column > Clustered Column. Now, the graph has both bars and lines, but the bars are so small, they can barely be seen.
To create a secondary axis, we must select the red bars of data again. Then click Layout > Format Selection. Select the tick box for “secondary axis.”
And there you have it, your visualized Internal vs. External Link Spread, complete with two Y axes. One of the most useful points of this graph is to illustrate to senior decision-makers why certain content deserves precedence and isn’t receiving it under the current folder hierarchy approach.
What other unique graphs or visual aids can you suggest to measure the quality of content? Share in the comments below!