Get high-end insights from Ahrefs' organic keywords
Here’s a brand new Google Looker Studio template to help you get the most valuable insights from Ahrefs’ organic keywords. This template is also designed to backup your decisions with data when it comes to prioritizing your content roadmap.
Below are the steps to extract the data and then build the report on Data Studio.
Get data from Ahrefs
Export the list of keywords from Site Explorer 2.0
First of all, head to Ahrefs, then Site Explorer and enter the website for whom you want to gather insights. I randomly chose a website on Awwwards; pretty useful when it comes to browse trends in terms of design and user experience.
Then hit, Organic keywords and click on Export. No need to modify anything here; we will export all the data as they appear.
One your export is ready, import your CVS directly in Google Sheets (File > Import) and named your sheet organic-keywords.
Export detailed information for specific keywords
Now, you will filter your results to get the full list of keywords for whom the current position is equal or higher than 11. Copy the list and paste it in Ahrefs’ keyword explorer. Again, hit Export.
Import this new dataset as a new sheet and name it: insights. You’re now good to go 🙂
In case you don’t want to bother with these steps, here’s the spreadsheet used with Merci Larry (you can make a copy if needed).
Creating your Looker Studio template
Again, if you don’t want to bother building your own, here’s the link to the Looker Studio template; it should work fine if you copy paste your own data in the spreadsheet.
Categorize SERP features
First of all, I wanted to categorize all the keywords from the list depending on their SERP features. Of course, you can update it accordingly to your needs and willings. You can do that by adding a new field in the data source organic keywords:
CASE
WHEN REGEXP_CONTAINS(SERP features,'Knowledge panel') THEN 'Brand, Person or Thing'
WHEN REGEXP_CONTAINS(SERP features,'Adwords|Shopping') THEN 'Transactional query with high probability of ROI'
WHEN REGEXP_CONTAINS(SERP features,'Tweet|Top stories') THEN 'Breaking news'
WHEN REGEXP_CONTAINS(SERP features,'Image pack|Video') THEN 'Creative content opportunities'
WHEN REGEXP_CONTAINS(SERP features,'People also ask|Sitelinks') THEN 'In-depth content opportunities'
WHEN SERP features IS NULL THEN 'Classic SERP'
ELSE 'Unclassified: update needed'
END
Filter keyword difficulty by buckets
I used Ahrefs’ explanation about their Keyword difficulty metric to create a dedicated bucket by level of difficulty. Creating this field can also interest you if you are looking to create bins for Search Console data in Data Studio; this question has been asked by JR Oakes on Twitter (here’s the thread).
CASE
WHEN FLOOR(KD) >=0 AND FLOOR(KD) <11 THEN "1. Easy"
WHEN FLOOR(KD) >=11 AND FLOOR(KD) <31 THEN "2. Medium"
WHEN FLOOR(KD) >=31 AND FLOOR(KD) <71 THEN "3. Hard"
WHEN FLOOR(KD) >=71 AND FLOOR(KD) <101 THEN "4. Super Hard"
END
Count number of words in queries
Based on the Keyword column, we can also create a new field to count the number of words in keywords.
CASE
WHEN REGEXP_MATCH(Keyword,'^[A-Za-z]+') THEN '1 word'
WHEN REGEXP_MATCH(Keyword,'^[A-Za-z]+\\s[A-Za-z]+') THEN '2 words'
WHEN REGEXP_MATCH(Keyword,'^[A-Za-z]+\\s[A-Za-z]+\\s[A-Za-z]+') THEN '3 words'
WHEN REGEXP_MATCH(Keyword,'[0-9+]') THEN 'Numbers'
ELSE '4 words and more'
END
Using the Data Studio template to gather insights
Now that you have learned the main steps to create this template, below’s a little helpful guide to help you use this template at its best.
Let’s begin with a list of questions:
- What is the total search volume of the keywords I rank for?
- Are all the keywords valuable? Are the organic results get clicked?
- How Google understands the query? What kind of answers the SERP offer?
- What kind of content I should focus on to get more traffic?
- What is the difficulty level of my top keywords?
- Should I rely on short or long-tail keywords?
If these questions ring a bell, then this template could be helpful 🙂