Track backlinks historically to compare each link lost and gained (Excel)

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?

Outcome of this analysis

You’ll have a visual representation of the

• Number of links lost in the last month (or a number of months if you have the data collected)
• Number of new links gained
• Lost and Gained link comparison
• Month Comparison between you and your competitor

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.

How are we going to achieve this?

• Excel and Pivot tables
• Open Site Explorer
  Why? Because we know when an update takes place unlike other services.

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.

Step One – The 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.

Step Two – Organise

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:

Next:

• Create a new column (called “Month”) on the right of “Target URL”
• Fill the “Month” column with the month you took your data for. e.g. before the hash’s, “January” and after the hash’s “February”
• Delete the “Title” Column.
• Format all the data as a Table

Step 3

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.

Step 4 – Pivot

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

How can this data be used

• Measure your efforts
• Spy on competitors and discover the source of their new links
• Compare your links with that of a competitor using the same Pivot chart (I might post something on this as a follow-up to this article)