How to measure your websites link spread graph

What does your websites link spread look 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!

**Chelsea Blacker is the Head of Client Delivery at Quaturo, a leading content marketing agency in London. Follow her on Twitter here.**

by Chelsea Blacker
  • If, like me, you were not sure about how to wrap an IFERROR around the VLOOKUP then this is the formula:

    Super post. Thanks a million for this.

    James, I asked you how to do it then decided not to be so damn lazy.. it is nearly midnight on a Friday so lazy is somewhat prevalent at this stage 🙂

    • Chelsea Blacker

      Thanks a lot Ralph for sharing this – it’s such a great improvement to the original formula. I’m impressed you’re doing this on a Friday, that’s true dedication!

      • Sorry, I also forgot to add that if you turn your external link data into a table by selecting it and hitting CTRL+T.

        If you then give your table a name in the design tab VLOOKUPs suddenly become a LOT easier, because you can just reference the table name in the formulae, e.g =VLOOKUP(A1,externalLinks,2FALSE) and not worry about absolute cell references.

        Tables are also dynamic so if you add or inset extra rows or columns they resize automatically. Formulae’s also auto fill in tables so this also saves you loads of time and you can pinpoint records by using the filters in the header row.

        Finally you can add the IFERROR statement to handle any #N/A errors. =IFERROR(VLOOKUP(A1,externalLinks,2FALSE))



    • Chelsea Blacker


  • You made my day. Great post Chelsea. Thx a lot.

    • Chelsea Blacker

      Cheers Eren – so glad you liked it! If you have any strange but cool graphs you like to create to visualise data don’t hesitate to share. Happy Tuesday 🙂

  • This just blew my mind. Thanks for the info and the step-by-step guide. I’m going to try this for a client this week. Fingers crossed I don’t mess up the VLOOKUP!

    • Chelsea Blacker

      VLOOKUPs definitely seem a bit intimidating, but once you understand how to do it – they are magical! Perhaps it’s just me, but I am very proud of myself when I learn a new Excel skill – I usually email my dad to show him what I’ve learned. Next up for me is… the pivot table! (freaking out… on the inside)
      Don’t hesitate to tweet me @ChelseaBlacker if you have any struggles with VLOOKUP. I’ve had many 🙂

  • Awesome post Chelsea. Thanks for sharing. Try wrapping an IFERROR formulae around the VLOOKUP to catch the #N/A’s and replace them with 0.

    • Chelsea Blacker

      Oooh, thanks for this Jonathan. That is a more efficient way to take care of the errors, I’ll be sure to try it out.

  • I love this stuff! If you really want to geek out, you can merge in crawl depth information as well as monthly natural search traffic to overlay on this data. This will help create a very clear picture that correlates how much traffic a page gets versus where it lives on the site and how much internal and external link love it gets.

    • Chelsea Blacker

      Myron, a man after my own heart! I was SO tempted to pull in the crawl depth data – but decided to keep to the links aspect for now.
      You’re totally right – the crawl depth can be easily attained in Screaming Frog, the column is called “Level” and should be in column W in the exported CSV.
      Pulling in some fun basic search traffic data can be fun too. I’ve yet to find a way to automate pulling in word count accurately – but I think this would also be fun to add in to better understand the optimal length for sticky content.

      • If you ever figure out how to pull in word count, please let me know … that would be *very* useful.

        Also read one of your responses below … if you’re jazzed about VLOOKUP, just wait until you start playing with Pivot Tables. Once you get comfortable with that, you’ll be turning everything into Pivot Tables – you’ll wonder how you ever lived without it, no joke.

  • Yasir

    Nice post indeed. Add data from Social Crawlytics against URLs to measure Social Spread along with Link Spread 🙂

    • Chelsea Blacker

      Thanks for suggesting Social Crawlytics Yasir, I’m definitely going to have a play around with it!

45 Leather Lane