A guide for using Google Sheets and Ahrefs to extract a competitor’s best backlinks.
1) Log into the client’s link building Google account
2) Click here and select File > Make a copy, enter the client’s company name as the file name and save the file to My Drive
3) Identify client’s top 5 competitors. The easiest way to do this is to visit the client’s website, take note of the first keyword phrase listed in the home page title and enter it into a search engine (be sure to only include fellow competitor sites, rather than business directories that appear in the top 5 etc)
4) List the top 5 URL’s obtained in step 3 into the Google Sheet, in the area provided
6) Select the Click Here button for the top-ranked competitor site (be sure to have Ahrefs open)
7) Click Export
7) Select Full Export, click Start export and click Download
8) Open up the newly downloaded spreadsheet and copy the entire contents of the sheet (column A to column W)
9) Go back to the Google Sheet, click on the 2nd tab at the bottom (#2 – Data) and paste the contents of the competitor spreadsheet from step 8) into the area provided (ensure cell A2 is selected)
10) Repeat steps 6 – 9 for the remaining competitor URL’s; NOTE – in step 9, be sure to append the contents of each competitor spreadsheet to the next row beneath the last competitors – don’t override the previous data! (see below)
11) Once data has been added for all 5 competitors, click on 3rd tab at the bottom (#3 – Plan of Attack)
12) The links have already been arranged in order of value, so simply select the first 10; as the Google Sheet has already been saved, you can simply return to this file month after month to obtain another 10 links, without having to go through this entire process again.
If you have any questions with any of this, please email firstname.lastname@example.org.