Yesterday evening I was reading a post by Adrian Vender about Competitive Backlink Analysis with Open Site Explorer.  While this isn’t necessarily a new strategy (Adrian links to some other really great posts in his article), I was happy to see someone willing to put forth the effort to pull and analyze backlink data across a wide variety of competitors at the keyword level.  To recap the main points of Adrian’s strategy:

  • Identify your top keyword phrases and the sites ranking for each of these phrases
  • Run an Open Site Explorer export for each of these competitors
  • Combine into one master list and sort by Page Authority or Domain Authority (depending on your preference)

Right on, Adrian!  I’m a huge fan of this process and one I’ve been using for quite some time. However, I’d like to add my own tips and tricks for evaluating backlink profiles en masse to identify linking themes for your competitives.

Adjust OSE Settings Before Exporting

While this very well may be the strategy Adrian uses as well, I definitely recommend adjusting the Open Site Explorer settings to ensure you’re getting the most opportunities available.  Filtering out nofollow and internal links and including links to the entire domain, for example, will help you to better identify what link generation strategies have been pursued by your top competitors. 

Note: If you are analyzing a large competitor with a number of product or service lines, such as Amazon or even Wikipedia, including all links to that domain may be inappropriate.  For these competitors, you should consider exporting only those backlinks to the targeted page or exclude these URLs entirely.

Eliminate Duplicate Domains

I commented on this in Adrian’s post but one of the first things I do after I’ve created my master list is remove duplicate domains.  This is accomplished in 4 easy steps:

  1. Create a new column in your master list
  2. Apply the following formula to your newly-created column, assuming A1 references the linking URL: =LEFT(A1,FIND(“/”,A1,8))
  3. Sort in descending order of Page Authority.  This will ensure that the page with the highest Page Authority will be listed first
  4. Select your table and remove duplicates of the newly-created Root Domain column

Once you have done this, you’ll be left with a list of the top linking pages on each linking domain, allowing you to dive deeper into the overall backlink profile as part of your competitive research.

Create a Pivot Table

One of the easiest ways to identify low hanging fruit in linkbuilding is to find where multiple competitors are being linked.  Once we have our master list, this becomes increasingly easy to do with a quick pivot table in Excel.

First, make sure that you remove duplicate linking domains for each competitor individually; this way, you won’t have multiple links for the same competitor artificially inflating your numbers.  Next, combine these competitors into one master list and apply the root domain formula provided above to the Target URL column.  This will leave you with only the domain URL for each competitor (Note: this step is only necessary if you’ve selected all links to the domain rather than links to the homepage).

Next, simply set up a Pivot Table that pivots the Root Domain of the Linking URL against the Count of the Root Domain of the Target URL.  If you then sort the Count column in descending order, you can see what sites are linking to the largest number of your competitors, which should indicate that these are exceptionally relevant sites with a high likelihood of linking to your client as well.

Lastly, in order to prevent analyzing sites that are already linking to you, you may considering removing all links to your site before performing any of this analysis.  I recommend exporting your site’s Open Site Explorer backlinks and using a VLOOKUP function in the Master list to identify and remove those Root Domains are already linking to your site.

Pro Tip – Open URLs in Bulk to Expedite your Analysis

Using the URL Reviewer Tool from Ontolo Tools, you can open 20-30 tabs all at once in the same browser window (this is not the official limit fo the tool, but I’ve found it significantly slows my browser if I try and open more than 30).  This saves you a surprising amount of time because you can just right into the analysis and evaluation of these linking pages rather than wasting time opening them individually.

Have another way to apply Open Site Explorer for competitive backlink analysis?  Let us know in the comments below!


One Response to Remove Duplicate Domains for Competitive Backlink Analysis

  1. Will the ways to analyze backlinks ever stop? Ha ha.

    Awesome post. Simple, yet very effective to analyze tons of links for possible worthy prospects.

Leave a Reply

Your email address will not be published. Required fields are marked *