How to Measure Your Link Spread

Home / Blog
How to Measure Your Link Spread
09November

How to Measure Your Link Spread

About the Author

 

Comments

    Chelsea Blacker - October 9, 2012

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

      Chelsea Blacker - October 9, 2012

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

    Chelsea Blacker - October 9, 2012

    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 - October 9, 2012

      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.

        Chelsea Blacker - October 9, 2012

        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.

    Chelsea Blacker - October 9, 2012

    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 - October 9, 2012

      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.

    Chelsea Blacker - October 9, 2012

    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 - October 9, 2012

      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 :-)

    Chelsea Blacker - October 9, 2012

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

      Chelsea Blacker - October 9, 2012

      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 :)

    Chelsea Blacker - October 10, 2012

    I LIKE THIS

      Chelsea Blacker - October 10, 2012

      THANK YOU JOLIL

    Chelsea Blacker - October 12, 2012

    If, like me, you were not sure about how to wrap an IFERROR around the VLOOKUP then this is the formula:
    =IFERROR(VLOOKUP(A3,externallinks!$A$2:$B$139,2,FALSE),0)

    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 - October 16, 2012

      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!

        Chelsea Blacker - October 16, 2012

        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))