If you’re competing in a competitive industry then you need to keep a sharp eye on each backlink and whether your backlinks are increasing or decreasing in number and more importantly quality over time.
The big question – Is your link building efforts paying off?
You’ll have a visual representation of the
The links will be grouped by Page authority, Domain authority and Anchor text. a little further work will also allow you to group more metrics such as Geo Location etc.
Note: Open Site Explorer update their index approximately every 30 days so unless you have last month’s link data for you site, you’ll have to wait for the next update for real data.
Download a chosen website’s backlinks (in CSV format) from Open Site Explorer using the following options:
Note: an SEOMoz Pro Account is required to download up to 10’000 links.
If you already have your backlink data from last month or earlier, you can move onto step 2; otherwise you need to wait until the next Linkscape Update.
Once the update takes place, download another CSV using the same settings above so you will now have 2 sets of backlink data for your site – one being from the month before.
Open both spreadsheets in Excel and delete the first 6 lines in both:
Scroll to the bottom of last months CSV and add some hash’s (or #):
Copy all the link data from this month’s spreadsheet and paste it into last month’s sheet below the hashed line:
Highlight all the cells below the “URL” heading
Click “Conditional Formatting” > “Highlight Cells Rules” > “Duplicate Values”
Thereafter select “Unique” with a light red fill
Now the interesting bit. Click on the small arrow besides “URL” and filter by the colour above:
This list is the pearl amongst the stones. Let me explain.
All the links listed above our hashed line are those links which you had last month but have lost this month (according to Open SIte Explorer) and those below the hashed line are the new links you have gained which you didn’t have last month.
Next, we’re going to compare each link from last month to this month using various metrics such as Page Authority.
Before moving on, Delete the hashed line in your table.
To graphically compare the links, we’re going to use a Pivot table in Excel. To do this:
Highlight your table and click “Insert” > “Pivot Table” > “Pivot Chart” > “Click OK”:
Now set you Pivot with the following options (“Values” must be set to “Count” and not “Sum”):
Next change you chart type to either a “Line” or “Area” type. I’m going to choose a “Stacked area”:
And there you have it!
From the above, we can see the links we have gained in the new month are overall more weightier and better than those we have lost over the last month.
The beauty about Pivot Charts are that we can easily switch metrics so if I wanted to compare using “Domain Authority”, I only need to switch it:
Note: You can also compare using “Anchor text”, “Linking root domains”” etc. and can add more metrics to the list (if you can get your hands on them such as the geo location of each link).
Note: The data is only as accurate as Open Site Explorer is.
Note: I used made up data for the above