Neil Walk width=

Segmenting Data from link Profiles using Excel

Its been many month since I posted on my blog, but its been a hectic few months I attended Domain Fest in LA spoke at Traffic Dublin, Spoke at RIMC11 in Iceland (Awesome) I carried out a presentation at Internet World (Will push a blog out in the next week or so) and I did the SASCOn wrap up (Slight different presentation technique than I normally use), but after speaking with Dave Naylor, Rishil, Basvandenbeld ,Bary Adams, yoast, nichola stott, Pete Young, The hodge, Paddy Morgan amoung too many others I thought I really should start blogging more, so this post is about segmenting data from link profiles.

You may remeber this post on Blog Storm by Patrick Altoft about link profiles and exporting back link data from OSE (Open Site Explorer) to produce a profile graph.

Link Profile

Well I really liked this visual way of presenting data, but you can expand on this by segmenting the information further.


  • Carry out some keyword research and get yourself a good list of phrases 10-20 to concentrate on.
  • Next do a few quick searches on Google and check in an excel doc which sites appear for mulitple phrases.
    (The purpose for this is to find a general competing website and not a site competing for a specific phrase only.)

  • We can then choose two or three sites which appear under the most phrases “This is our competition.”
  • Go to Opensite explorer and download the data (you will need a PRO account)
  • Once you have downloaded all the data for your site and the competitors we start a little bit of manual work.
  • Add a column to you excel sheet after anchor text of: ” Anchor Type” then quickly label the anchor text as the following:
    – Brand (i.e. or branded phrases or redirecting sites)
    – Misc.  (Words that have no meaning i.e. Click here, website)
    – Keyword (Keyword influenced phrases.)
  • I also like to make two further columns
    – 1) Internal or Domain links
    – 2) Label the main site (help distinguish between profiles)

See image below:

Now once you’ve done this the fun starts 🙂

Or it means we can analyse the following:

  • Link profile graphs
  • Type of profile (Brand, Misc, keyword)
  • Top anchor texts for keyword
  • Anchor ratios (good for spotting drops in rankings)
  • What pages have the key links.
  • Internal (Deep) to Domain link profiles.
  • No. of anchor text variations

(To be honest, you can do some of this using Opensite explorer anyway, but adding in the brand element and more comopeitors makes it easier to choose a strategy of solve a problem.) – depending on the competition this will take about 20-30 minutes to carry out properly.

So now we have all the data together lets start constructing some useful information:

Profile Graphs:

To produce this graph make a pivot chart using Page Authority as the ‘row’ then website as the column and use a count function for no of links.

Anchor text Type:

Next change the pivot table by using site as the row and anchor type as the column. This shows us the following:

This is useful to be able to start to plan your link building efforts.

Top Anchor Texts:

Now use anchor type as the pivot filter, put anchor text as the row and site as the column, you can now view your comeptitors top anchor texts.

Domain versus Deep links

Create a table showing site by Domain/Deep Links:

Anchor Text variations:

Next change the pivot table to show anchor text as the row and website as the column, quickly count the number of rows whcih correspond to each site and make a table, this will then show you the numbner of anchor text variations used for each site, this shows you one of two things:

  1. How natural a link campaign is
  2. How large the competitor site is.

You will often note on sites that drop listings, the percentage ratio is high showing often a unnatural link profile.


Once the above has been carried out, you can undertake your SEO plan i.e. Which page you are going to build links for what anchor text you may use (Also carry out further keyword research).

I use the type of reserach when either:

  • Problem solving
  • looking for initial sales strategy.
  • Keyword research (using competitors)

I hope you find this article useful.

One comment on “Segmenting Data from link Profiles using Excel
  1. A lot of the data we build from monitoring our website is recorded and placed in to an excel document. On the whole it is perfect for representing what the website is doing in black and white and in a straight to the point way.

1 Pings/Trackbacks for "Segmenting Data from link Profiles using Excel"
  1. […] Let there be lightThe Real Cost of Social MediaSocial Media: Preparedness 101: Zombie ApocalypseSegmenting Data from link Profiles using ExcelAnd that’s it for this week’s weekly link roundup! Till next week RicardoVisit Authors […]

Leave a Reply

Take back control of your Digital Marketing

Call us on 07816897301
or fill in the form for free advice and information.

We’ll call you back to find out more about you. Then you can just sit back and relax while we create a proposal tailored to your needs.

We will ask you details about your business and talk you through the most beneficial and cost-effective digital options available to you.

Your Name

Your Email

Your Telephone

I'd Like To Discuss?

[wpgdprc "By using this form you agree with the storage and handling of your data by this website."]

Agree - Tick to hear about latest News, industry Insights and offers

Neil Walker SEO Manchester