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.

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

Guide:

• 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. www.site.com 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.

Strategy:

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.