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).
Format the data to your liking and you’re ready to go!
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.
This is a very helpful post Will! :) I was wondering what other tools aside from Majestic and Ahrefs we can use for this?