How to Perform a Competitive Link Analysis
Recently, there was a major shake up in the rankings on Google for our top priority keyword.
We had been making good progress and then all of the we sudden slid about 9 spots. At the same time, a competitor who had been consistently 2-3 spots away from us (either higher or lower) had jumped all the way to the 4th position. This was especially an anomaly because their site consists of almost no on-page optimization. The keyword we were trying to rank for is nowhere to be found on their site, yet somehow they rank #4 while we were barely clinging to page two. What kind of spells were they casting on Google to make this happen?
I stared at their link profile on Open Site Explorer until I nearly hallucinated, when I finally realized that the data I had gathered from SEOmoz’s masterpiece was not going to be enough to get me the answers I needed. So I outlined a process to really understand their link profile, and that process is what follows.
1. Collect Data
Start with your site, since its most familiar, and run a report in Open Site Explorer. Change the filters to show links from External Pages only and All Pages on the subdomain.
How to set your filter on Open Site Explorer.
This will keep out all the internal links and show you any links you have to deep pages. When your filter is set, download the links to a CSV file and open it in Excel. To make things easy to work with, select all of the data in that document and convert it to a table. Now you can sort or individually filter certain columns. Finally, you need to insert two extra columns. Add one column for link type where you’ll note whether the link is a directory listing, a blog comment, a job post, etc. The next column will be for the date the link was acquired. Not all links will give the date but when you can find the information, record it here.
This is how your table should look in Excel, only bigger.
2. Pick your links
At this point, we need to determine the sample we’re going to analyze. Some sites may have only 20 links. In that case analyze them all. Others may have nearly 1,000. If that’s the case, you can determine how many you want to analyze. Keep the sample numbers consistent for every site you analyze so data can be accurately compared. And remember, this process is tedious so saying you’ll tackle all 1,000 is more than a bit ambitious. For my analysis, I chose the 100 links from each site with the highest page authority.
3. Review every link
Now we’re getting down and dirty. In order to know the type and date of every link, you have to visit each individual page that hosts a link. The quickest way I found to do this was to highlight 20-30 links at a time in Excel and paste them into URL Opener. This opens all of the links at once in their own tab so you can easily move through them. Then to find the link if it doesn’t jump out at me immediately I just copy and paste the anchor text from excel into a browser find box and I’m taken right to it. At that point, I note the link type and the date in Excel, close the tab and move on to the next. If the link is nowhere to be found, I look at deeper pages if they exist until I find it. If I don’t find it, I delete it from the list and move on.
Yes, you have to go through EVERY tab.
4. Create Visualization
Now that you know the type and date of all of your links, you’re starting to see some patterns. I found a pie chart helped me understand the link diversity much better. To create a pie chart, first list each type of link. Then I used a formula to help me find how many occurrences there were for each variation of link type. The formula for this is as follows:
Note: This formula must be entered as an array formula, to do this, press CTRL-SHIFT-ENTER (⌘-Return on Mac).
Now you should have a simple two-column table that you can use as the data for your pie chart. To create a Pie Chart, select Insert → Chart. From there you can customize your pie chart. When its all set, you'll have something like this:
FACT: More colors = more impressive pie chart.
Now, you can simply repeat this for however many competitors site's you want to analyze. Then create another pie chart using the same formulas to visualize the different variations of anchor text that exist in your link profile and your competitors. Now you know what types of link work (i.e. blog comment vs. directory) and what keywords/anchor text have the best opportunity. While it seems like a lot of work from the start, it actually helps you generate a very specific action plan for your link building efforts and removes a lot of the guesswork. Now you can stop staring blindly at the screen wondering why your competitor is beating you. Dig in to the data, create a plan and take them down.