Import Ahrefs Data to Google Sheets

In this guide, I’ll get you started on pulling data from the awesome Ahrefs SEO tool suite into Google Sheets, using the API Connector add-on for Sheets. Targeted around backlinks, you can use the Site Explorer, Content Explorer, Keywords Explorer, Rank Tracker, and Site Audit to compare your website to competitors. All in all, it’s a pretty sweet tool and we can integrate its native API with Google Sheets! Let’s jump in and see how to get started plugging out data in.

Note: Ahrefs is not a free tool. Further, if you want to access the Ahrefs API, you have to pay an additional subscription fee. You can read more about that here: https://ahrefs.com/api/subscription.

CONTENTS

PART 1: CONNECT TO THE AHREFS API

If you are using API Connector’s OAuth2 connections manager (paid feature), you can skip this entire section. Instead of getting an API key, just click Ahrefs on the Manage Connections screen and go directly to Part 2 of this tutorial. You won’t need to include an API key in any of your requests.
ahrefs-img8

Once you’ve signed up and have an account, you’ll be asked to put in a website to start a project on. Go ahead and put in your website’s address here:

ahrefs-img1

Just hit continue and go through the rest of the advanced steps if you don’t know what you are doing (like me). After finishing the setup for your selected website, you should be presented with a screen like below. Go ahead and click on More -> Ahrefs API.

ahrefs-img2

Now check out the left corner and click on Your API Profile.

ahrefs-img3

Go ahead and generate that API token!

ahrefs-img4

Great! Let’s check out the documentation and see what kind of data we can get from our website.

PART 2: CREATE YOUR AHREFS API REQUEST URL

Ahrefs is nice enough to avoid a lot of the headache of piecing together an API request URL by providing us with this API request builder tool. You can access it by clicking “API Request Builder” in the left sidebar.

ahrefs-img5

From here, it’s super easy! Setup what data you want to get in the From drop-down, set your Limit, and copy the API URL Request output below:

ahrefs-img6
https://apiv2.ahrefs.com?token=YOUR_TOKEN&target=ahrefs.com&limit=25&output=json&from=backlinks&mode=subdomains

PART 3: PULL AHREFS API DATA INTO SHEETS

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create Request tab, enter the Request URL we just created
  3. If you’re connecting through OAuth2, choose Ahrefs from the dropdown menu. Otherwise leave Authentication set to None.
  4. Click Run!
ahrefs-img7

PART 4: MORE EXAMPLE API URLS

As previously mentioned in the last part, Ahrefs provides a handy API tool for creating ready-to-paste API requests. A few other example endpoints that may be useful for you would be:

  • Top 10 backlinks:
https://apiv2.ahrefs.com?from=backlinks&target=YOUR_DOMAIN&mode=domain&output=json&token=YOUR_TOKEN
  • Top 10 referring domains ordered by Domain Rating:
https://apiv2.ahrefs.com?from=refdomains&target=YOUR_DOMAIN&mode=domain&order_by=domain_rating:desc&output=json&token=YOUR_TOKEN
  • New backlinks on 2020-07-20
https://apiv2.ahrefs.com?from=backlinks_new_lost&target=YOUR_DOMAIN&where=type:"new",date:"2020-07-20"&mode=domain&output=json&token=YOUR_TOKEN

2 thoughts on “Import Ahrefs Data to Google Sheets”

Leave a Comment