How to undertake large-scale competitive link analysis

Competitive link analysis In Excel

As an initial step, you’ll want to identify your key competitors and download link data from your preferred tool. I’ve used .xls exports from Majestic and 30 different recommended agencies for SEO and Content Marketing. One link per domain is usually sufficient and will help keep your dataset manageable.

Next, move these workbooks into a new folder and open up a separate blank workbook. Load up the VBA editor (ALT + F11) and click Insert > Module. Paste in the following code, making sure you update the ‘Path’ with your own folder.

Sub GetSheets()
Path = "C:\LinkAnalysis\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
  For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
  Loop
End Sub

Click the green play button to run or press F5.

Let Excel chug away for a while and when it’s finished you should have a workbook with one domain per sheet.

Right click on the worksheet bar and select all sheets. On the sheet you’re already viewing, select column E (domain), then cut and paste it into column A. This should perform the action across all of the sheets.

Go back to the VBA editor, insert a new module and add the following:

Sub Combine()
  Dim J As Integer
  On Error Resume Next
  Sheets(1).Select
  Worksheets.Add
  Sheets(1).Name = "Combined"
  Sheets(2).Activate
  Range("A1").EntireRow.Select
  Selection.Copy Destination:=Sheets(1).Range("A1")
  For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
  Next
End Sub

Run the script which should create a new sheet at the beginning called ‘combined’.

View the sheet and then go to Data > Remove Duplicates. Only tick ‘Domains’. This will trim the dataset down so that only one example URL remains per domain; usually this is sufficient.

Add in a new column to check whether or not the domains already link to your site, using the following formula:
=IF(ISNUMBER(MATCH([@Domain],'My Domain'!A:A,0)),"Links to my site","No link")
Reference the domain column on your combined sheet and then the domains column of the worksheet for your site.

Finally, we’re going to check how many competitors each domain on the combined sheet links to. Create a new VBA module and paste in the following:

Function CountIfSheets(aRange As Range, xCriteria As String) As Double
  Dim rangeAddress As String
  Dim i As Long
  Application.Volatile
  rangeAddress = aRange.Address
  With ThisWorkbook
    For i = 2 To .Worksheets.Count
      CountIfSheets = CountIfSheets + Application.CountIf(.Worksheets(i).Range(rangeAddress), xCriteria)
      Next i
    End With
  End Function

Go back to the combined sheet, insert a new column and use the following formula, referencing column A of your first non-combined sheet again. The VBA will perform a count across all of the sheets except for the first (which should be your combined one).
=CountIfSheets('My Domain'!A:A,[@Domain])
Format the data to your liking and you’re ready to go!

FAQs

How many sites can this handle?

Assuming you have a decent machine, this should work fine with 30~ sites with reasonably large link profiles (~5K domains). Limiting it to one link per domain helps. Beyond this, it may be best to use SQL, R or Python.

Can I use exports in different formats?

Absolutely. The initial VBA could easily be tweaked to work with CSV’s instead.

Can I use a mixture of link metrics?

If you want to use link data from Majestic and then overlay a metric like DA, then just run the combined domains list through a tool like URL Profiler and pull the data into the sheet with a VLOOKUP.