Have you wondered how your competitors are getting a strategic advantage over others? One of the best ways you can overtake them is to take a look at your backlinks.

Required skill: Pivot Tables

Take a look at this video walkthrough if you do not know how to use pivot tables. This video will be able to teach you everything you need to know, and more.

By taking a look at the example to the table in the Excel spreadsheet, you can get an idea of how the data was pulled together in the raw data tab. Now you can see how the information was organized in the pivot table in that tab. But, you can always organize the data however you want based on how you feel is best. This should help provide a good jumping-off point for you.  The pivot table was also sorted by domain authorities in descending order. The information was then filtered out from wordpress.com.

You can download the Excel workbook from Dropbox.

Pulling The Data Together

Step 1: Paul your sites backlinks.  You can use Open Site Explorer, Majestic SEO, ahrefs, or whatevs to help you get the information you need. Then, pull them together into a formatted table.

Step 2: Now, add another column and call it “Site.”  Something that you can typically do is add the domain and double-click the bottom right corner of the cell. This will fill down to the bottom of the data sheet. Keep doing this each time you add another batch of backlinks. By the time you’re done, you’ll have a single table which has a mashup of backlinks.

Backlinks 1

Step 3:  Now that you have the backlinks available to you, extract the domains from the back link URL using the left and search functions. If you haven’t done this before, here is a video tutorial to demonstrate how you do this. Another way you can do this is to download the demo workbook from the post and simply copy the formulas.

Step 4:  You can now create your pivot table by using these settings:

pivot 1

Step 5: Some people don’t like how Excel indents rows, in the default, “Compact” pivot table format.  In the dataset we have, there are three different values pulled down in rows. These values are domain, site, and URL. Due to this, euros can get really congested. This makes it really difficult to do for you to differentiate them. Because of this you might want to use the Outline layout. You can check it out in the excel file download.

To set your pivot table to “Outline,” click on any cell in the pivot table and go to Pivot Table Tools > Design tab > Layout > Report Layout > Show in Outline Form (Mac: PivotTable tab > Design > Layout > Outline Layout).

Step 6:  You may want to pull individual observations into text boxes to the left of the pivot table. Text boxes were used for a couple reasons:

  • I didn’t want to mess up the heights of rows inside my pivot table.

  • I could attach links to the text boxes that linked to the individual cells in the pivot table.

Step 7:  now that you have the data you need, start reverse engineering strategy for your backlinks. There are quite a few more strategies you could use on the dataset you just pulled from your competitors backlinks.

In case you are more of a visual learner, here is a 10 minute video walkthrough of some of the key steps that can be taken to organize the data for analysis.