Quickly Bulk Export and Merge Ahrefs Backlink Reports From Multiple Domains

This morning I read a great post by Jon Cooper over on the PointBlankSEO blog on how to prioritize link opportunities in Excel based on competitor's link profiles. Jon has laid out a fantastic process here, but opening all competitor link profiles in Ahrefs and then clicking export for each one looks like it is going to take a lot of time (particularly if you have a lot of competitors to look at). Jon mentioned a way of automating this process, which I'd love to hear about. In the meantime I have a little process to speed this up too :) p.s. Ahrefs, I'd love to use your API for this but it is way too expensive!

Notes:

  • You will need a paid Ahrefs account to use this process.


  • The number of links you can export depends your Ahrefs plan. In this example I'm using the professional plan so you only get 5000 URLs per domain backlink report.


  • Jon recommends using a one backlink per domain report from Ahrefs, which allows you to see only the most authoritative link for each domain. This would also mean you could cover more linking domains per 5000 URL report. Unfortunately, the report used in this process doesn't allow for URLs to be filtered this way.


  • The professional plan allows you to run 100 backlink reports per month, so if you want to use this process a lot and/or need to run 100 competitors or more you are going to need a bigger Ahrefs plan (unfortunately the main Ahrefs Site Explorer tab generates reports asynchronously, so this process won't work there.)


  • I use the RegexpReplace function in Excel to manipulate URLS. This function is not native to Excel and is actually a function of the SeoTools add-in by Niels Bosma. Beyond the very useful RegexpReplace function, I highly recommend installing this plugin for a million reasons that I'll perhaps cover another day.


The Process



  1. Head over to Ahrefs and visit the Backlinks Report tab. This area of Ahrefs is intended to be used for backlink reports that you want to update on a monthly basis. (Update: This is now found under the Labs menu and the Backlinks Report sub-menu.)


  2. Select "Create Standard Report".


  3. Here's the time saver click "Add A Batch".

    Ahrefs backlink report


  4. Paste all of your competitor domains in to the next window. Feel free to add a tag if you are doing this for multiple clients and need to filter out competitor domains per client.


    Ahrefs backlinks report batch


  5. Ahrefs will now run your domain backlink reports, go grab a coffee, it shouldn't take too long.


  6. Once all of your reports have completed, we are going to use Link Clump (same Chrome extension Jon recommended). Select all the URLs and copy them to the clipboard. Note you will need to go to the Link Clump extension options and select "Selected links should be...Copied to Clipboard" for the "Activate by Right mouse button" action.

    Ahrefs domain reports


  7. Now head over to excel and paste all of your report URLs into the spreadsheet. Column A will shows the domain name, and Column B will show the Ahrefs report URL. We are going to use Excel to change our copied Ahrefs report URLs to point to the backlinks section of the report and then add a parameter to the end of these URLs to get us directly to the report download.


  8. In Column C of your Excel spreadsheet add the following formula (ensuring you first have the SeoTools Excel add-in installed and active).


    =CONCATENATE(RegexpReplace(B1,"\/view\/index.php\?","/view/backlinks.php?"),"&export_type=3&charset=utf-16")



  9. Now populate this formula down for every row in your spreadsheet that contains data (you can do this quickly in excel by pointing to the right corner of the cell that contains the formula and double clicking once you see a crosshairs type icon.)

    Ahrefs domain reports excel


  10. Download Sample Spreadsheet



  11. Column C should now contain all of your Ahrefs report download links. Now we need to open them all to bulk download the reports. Head on over to a URL opener tool such as http://www.urlopener.com/, paste all of your report URLs and click open all. If you find only one URL is opened you will need to make sure your browser isn't blocking pop-up windows from urlopener.com (or whichever service you choose to use.)

    Ahrefs urlopener



  12. Now your browser will go crazy opening pop-up windows and downloading Ahrefs csv reports. Depending on how many reports you are downloading you may want to break up your list of URLs into manageable chunks to stop your browser melting down 😉


  13. Now you can follow the rest of Jon's link prioritization process!



Bonus Tip


Jon mentioned how to combine all of your CSV files on a Mac and mentioned that for PC users there are some tools available online. Well, look no further than Excel. The following version of the Ahrefs backlink report spreadsheet has a CSV file merger Macro attached. Open the spreadsheet, make sure to click "Enable Content" on the Macro warning dialog in Excel, and then click on the CSV file merge button in the spreadsheet.

All being well the Macro should now ask you which folder your CSV files are located in and then go about merging all CSVs in to one sheet. The Macro will save the combined CSV file in your default Excel folder, which may be your Documents folder. The Macro will tell you where the file is saved.

Download Sample Spreadsheet with Macro

The Macro is copied straight from this great post from Ron de Bruin, with one small change (Under 'Open the TXT file in Excel Tab:=False is changed to Tab:=True).

That's it. Enjoy bulk exporting link profiles from Ahrefs without a whole lot of clicking!

email

Tags:

Related posts:

2 Pingbacks/Trackbacks