API Connector Documentation
Import Ahrefs Data to Google Sheets
In this guide, we'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!
Note: You need an account with Ahrefs to use this integration, and Ahrefs is not a free tool. However, through this integration, you can access their v2 API without paying any additional API fee.
Ahrefs also has a new v3 API, but unfortunately that one is only available for their Enterprise customers.
Contents
- Before You Begin
- Part 1: Connect to the Ahrefs API
- Part 2: Pull Data from Ahrefs to Sheets
- Part 3: Create a Custom API Request
- Part 4: API Documentation
- Appendix: Connect to the Ahrefs v3 API (Enterprise only)
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Connect to the Ahrefs API
The easiest way to get started with the Ahrefs API is through API Connector’s built-in integration.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select Ahrefs from the drop-down list of applications
- Under Authorization, click Connect to Ahrefs
- You will see a modal asking you to approve the connection. Click Allow.
- You'll then be returned to your Google Sheet, and can verify that your Ahrefs connection is now active.
Part 2: Pull Data from Ahrefs to Sheets
Now that we're connected, let's pull some data into Sheets.
- There's only one endpoint for Ahrefs so skip straight to Request parameters. Choose the table from which you'd like to retrieve data. For this example, we'll choose
backlinks.
- Fill out the other required parameters (mode and target). For this example we'll set mode as
domainand target asahrefs.com, but you can use your own values.
- Set a destination sheet, name your request, and click Run to see response data in your sheet.
- Now select any other "from" table of interest and adjust parameters to get different data sets. You can pull in any of the data you see while logged into Ahrefs.
Part 3: Create a Custom API Request
Alternatively, you can run your own custom API requests instead of using API Connector’s pre-built integration, using any of the URLs shown in the API documentation. To create a custom request, open API Connector, add the complete URL into the request URL field and select Ahrefs from the OAuth menu. Here's an example request setup:
- Application:
Custom - Method:
GET - Request URL:
https://apiv2.ahrefs.com?target=mixedanalytics.com&limit=1000&output=json&from=backlinks&mode=domain&order_by=ahrefs_rank:desc - OAuth:
Ahrefs
Part 4: API Documentation
Official v2 API documentation: https://ahrefs.com/api/documentation
Official v3 API documentation: https://docs.ahrefs.com/docs/api
Appendix: Connect to the v3 API (Enterprise only)
Ahref's newest v3 API has a lot of really nice features. However, unlike their v2 API, it's only* available for users on Ahref's Enterprise plan. If that's you, here's how to connect and run a request.
ahrefs.com. More info >- Log into Ahrefs and click Account Settings > API keys > Generate API key
- You'll see a key, copy it and keep it handy as we'll insert it into a request header (details shown in the next step).
- Create an API request using any endpoint of interest from Ahref's v3 documentation. Open API Connector, and enter that request like this:
- Application:
Custom - Method:
GET - Request URL:
https://api.ahrefs.com/v3/site-explorer/url-rating-history?date_from=2022-01-1&target=ahrefs.com - OAuth:
none - Headers:
Authorization:Bearer 1234567(insert your own API key where it says 1234567)
- Application:
- Save and Run the request to see response data from Ahref's v3 API in your Sheet.
Can we retrieve backlink for each cell instead of a one-off API request
Hey Amine, the ImportAPI function lets you run API requests from cells, is that what you're looking for? If not please clarify and I'll be happy to answer further.
This is quite handy. Thanks for sharing
You're welcome, glad it's useful!
Hi there
Does this works as integration app? Ahrefs offers API and integration as 2 separate things for some reason. Even the premium plan doesnt offer direct API access however you can connect your Ahrefs account with some existing App to get the required data. Will this work with an account or do you need API?
Yep, it lets you access API data for free, as long as you have an Ahrefs account. You don't need to have their API plan.
Hi Ana,
when im using the sidebar this works perfect! But im trying to use importAPI with this and im getting this error:
"Request failed: Access not granted or expired. Please click Manage Connections and check OAuth2 connection status."
You probably need to switch to a sheet where you're the sheet owner. Please check the error messages section of this article, it explains the issue in more detail.
You mention that I can grab Keywords in your intro. What 'From' do I need to set in order to grab my saved keywords data for a specific domain?
It doesn't look like they provide saved keyword lists via the API, you can see an old discussion about it on their roadmap. You can get some keywords data using the positions_metrics parameter.
Hello,
Is this possible to target several urls in the same query ?
For exemple to concatenat a bunch of url and test all of them with only 1 request ?
Larry
Ahrefs doesn't let you query multiple URLs in the same request, but you can use API Connector's multi-query functionality to list out your URLs and automatically cycle through them. You'll need to convert to a custom request first.
Hey! It looks like the "Connect to ahrefs" button is not working for me. It shows just unclickable. Am I missing something?
Hey Sarah, the Ahrefs connection is only available for users on the Business plan, sorry for the confusion. You get a free 2 week trial when you first install API Connector, or feel free to message support with your email address if you'd like me to extend that for you.
Hey Ana,
Are we able to use cell references in the 'target' section of the Application connection?
Hey Kerry, currently you'll need to use a custom request for that, as only custom requests let you reference cells. (We'll enable cell references in the preset application menu in the future, but it's not available yet).
Update: you can now reference cells through the integration.
Does it support api v3 requests?
I'm getting "Completed with errors" result in cell
Our integrated connector is for V2, but you could create your own custom V3 request. However, unlike v2, v3 requires that you are an Ahrefs Enterprise customer.
Hey Ana,
Would this support pulling data from multiple competitors into one sheet? Ideally want to build a giant competitor database of Referring Domains. Would this be achievable?
Sure, you could pull referring domains for multiple competitors into a single sheet. It would probably be easiest to list all the domains in one sheet and then cycle through them with a custom request like
https://apiv2.ahrefs.com/?output=json&mode=subdomains&from=refdomains&order_by=domain_rating:desc&target=+++Domains!A1:A10+++I think my main concern would be that if you want to build a giant database, Sheets may not be the best place, since it's not really suited for very large amounts of data. But that really depends on what you have in mind.
Hey Anna, Many thanks for your response. - I'd love to speak to someone on your side on this a bit deeper!
Is there a way to pull in data by segment using this? I have segments saved in Ahrefs site audit, but I can't apply those when using the looker studio connector. I was hoping this might be a workaround...
As far as I know segments are available only through the interface, not through the API. You can choose whether to match on the exact URL, the domain, the subdomain, or the prefix, which overlaps with some of the segment functionality, but you can't directly access the segments you've created.
What will happen to this integration when the V2 API is discontinued?
Ahref currently writes: "Existing Integration apps will continue to work as before until further notice, but new submissions are no longer accepted."
If/when they discontinue existing integrations, then this integration will stop working, but we haven't received any notice about that yet.
Hi Ana, Is there an option to pull stats by date? I'm interested in the number of referring domains over the past 30 days. Thanks
When you fetch data from the refdomains table, it returns the fields
refdomains.first_seenandrefdomains.last_visited. Further, you can sort this by enteringfirst_seen:descinto theorder_byparameter, to get a list of all the most recent referring domains. So, while it doesn't specifically filter for the last 30 days, I think this is close to what you're looking for.