Full Keyword Research Workflow with OpenAI, Ahrefs, and Google Sheets

The most critical part of search engine optimization is to truly understand the Pain Points that your audience has! Failure to recognize these will turn all of your keyword work into just collecting useless junk to talk about. Today Search Engines are not just a place to go find information; they also serve as a means for people seeking solutions to their Problem. Search Engines receive around 8.5 Billion Searches Daily; the Majority of Searches Are By People Trying To Find a Solution To A Specific Pain!

This is why when you concentrate on the Problems specific to your Audience; The potential for Conversion increases 30%. Take for Instance: A person types in the search phrase of: "How To Increase Landing Page Conversions." A conversion that was only 1.2% at the time of creating the advertisement! This is An Audience Problem, or Pain Point, that Needs To Be Addressed and Showed That You Understand it!

When we began using Open AI to assist us in analyzing these Problems, there was a dramatic increase in the Accuracy of our Query Analysis. With the Addition of OpenAI Processing, the number of Queries our team was able to Conclusively Identify in Just One Quarter Increased By 41% over the previous three months! The Scope Of Our Content Development Changed Significantly from What Would Have Been Considered General SEO Terms to more Specific Terms, Example: "How To Avoid Losing Organic Traffic After Google Algorithm Update!" The Difference Is Clear. To successfully achieve your content strategy, it is essential to understand the problems experienced by your audience.

The combination of OpenAI, Ahrefs, and Google Sheets allows you to leverage the individual strengths of these three tools while gaining maximum perspective on current trends surrounding your content.

OpenAI creates ideas and hypotheses for addressing specific issues and collecting data through Ahrefs helps you verify demand for those topics; Google Sheets is an easy way to organize, track progress and analyze everything without needing to code.

Utilizing this combination of tools, we effectively addressed 74 content gaps for a fintech client in the last quarter: while traditionally this would have taken multiple months of effort; we were able to do this in just 11 days!

Generally speaking, this method allows you to cut keyword research time by 5x when compared to the traditional "manual" way of performing keyword research. It's simple, effective and fast.

"OpenAI + Ahrefs + Google Sheets = a powerful combination for achieving speed, depth, and ease of use simultaneously!"

It's important to segment your audience into three groups based on their experience level, the issue they currently face and the urgency in resolving that issue. For example, beginning investors looking to safely purchase their first Bitcoin and individuals with many years of experience, concerned with issues surrounding arbitrage between DEX and CEX, will submit separate types of queries, which necessitate sending separate pages and developing different strategies.

There are a number of ways to determine the problems your target market is having, such as client surveys, reviewing support tickets and monitoring conversations in platforms like Telegram and Reddit. For example, in one week, ASCN was able to identify more than 340 unique ways of describing the same problem from themed channels to create 89 keywords.

Gathering Your Tools: OpenAI, Ahrefs & Google Sheets

OpenAI:

Obtain your Open AI API Key by going to platform.openai.com. We recommend using GPT-4o because it achieves the best combination of high-quality results, fast, for keyword research. You should securely store the token. Be sure to set limits so that you do not incur a large bill due to unexpectedly high number of requests.

Ahrefs:

To access the API for Ahrefs, log into your account and navigate to the Settings page. Find API Access, and create a new API key which will allow access to Keywords Explorer and Site Explorer tools. If you are managing a team, create a separate API key for each member for easier management and verification. The easiest way to verify the API Key is by using a seed keyword to verify that the keyword volume and difficulty reports will be generated by the Ahrefs system.

Google Sheets:

To begin gathering information for the keyword research process, you will need to enable the Google Sheets API. You will do this through the Google Developer Console, create a service account and download the JSON file containing your access credential. If you don't want to use coding to connect Google Sheets with OpenAI or Ahrefs, there are services that do the integration automatically, such as Zapier or Make. With an integration through ASCN.AI you can create a chain in less than twenty minutes. An event will trigger an OpenAI request, this will return a list of items from the Ahrefs API, and lastly, you'll get a table populated with that data. You can also get a template based workflow from the ASCN library.

Stage 1: Keyword Idea Generation Using OpenAI

GPT is an excellent tool for identifying problems and issues that your target audience may face. While this won't replace an experienced analyst, it will save you an extraordinary amount of time when generating ideas and hypotheses. In fact, if used correctly, you could save up to ten times the amount of time, using GPT to generate your ideas. You can provide a description of your audience and receive back a great list of possible problem areas and keywords to track down related to each problem area.

For example, you might use the following prompt to generate a list of possible keywords:

Prompt: You are a SEO analyst. Your target market is a group of traders who have been trading cryptocurrency for at least one or two years, and want to automate the process of doing routine tasks. Please write me a list of thirty "pain" points each with at least 2 or 3 keyword phrases associated with each problem.

Results:

Pain Point: I spend three hours a day checking the funding rate on each token I own

1. Automated funding rate monitor
2. How to track funding rate automatically
3. Funding rate notification when something changes.

You can come up with anywhere from 60 to 90 ideas, of which about 30 to 40 may be of interest. The key is to constantly modify your original idea and create a better idea. You could enhance your query to include the following: 10 additional problems for beginners or more technical wording.

Creating Semantic Clusters

After all of your lists of ideas have been generated, the next step is to take all of the queries you've collected and categorize them into semantic clusters based on similar meanings. One way to collect relevant content and information is to group, or cluster, similar keywords/queries that are connected in the same theme/intent. An example of this would be to cluster "how to buy bitcoin," "where to buy bitcoin safely," and "my first BTC purchase" under a cluster named "Safe Cryptocurrency Purchase."

Using GPT for initial clustering is effective. A common request we provide when clustering is to take an 80-query list, group them based on intent, generate a group name and also highlight the main keyword of the group.

In one of our ASCN Projects, we separated 340 queries into 23 clusters that were then converted to separate landing pages. Organic traffic grew 120% after 4 months of implementing this method, while bounce rate decreased by 25%. This method works!

Once your clusters have been created, the next step is to analyze your competition and estimated search volume using Ahrefs.

Ahrefs Keywords Explorer is the best tool for evaluating seed keywords, as it provides multiple metrics for each keyword, including Search Volume, Keyword Difficulty (KD), Cost Per Click (CPC), and SERP.

If KD exceeds 50, then the competition is fierce. Long-tail keywords should be sought out, or the effort needed to achieve a top result will be substantial. KD of less than 20 is ideal for new projects with a domain authority rating of less than 30; therefore, the opportunity to rank among the top of search results within 1-2 months will be easier.

For example, using the keyword "Cryptocurrency Arbitrage" we found that it had a search volume of 1200, KD of 45 and CPC $3.80; however, we also found several long-tail variants with lower numbers such as "Cryptocurrency Arbitrage Between Exchanges", which had a search volume of 480, KD of 22 and "Stablecoin Arbitrage", which had a search volume of 210, KD of 18. In summary, they are low competition with a more defined search intent. You can also use the Site Explorer tool to take advantage of high-traffic pages (over 500 visits). From there, you can often find ideas for new queries to target.

Visualizing Traffic and Difficulty Potential

With Keyword Difficulty (KD), you can tell how many unique link building domains you will need to rank in the top 10. If a keyword has a KD of 30, that means you would need to have links from approximately 30 different websites. However, this is just a rough estimation or benchmark; you should not take it literally.

Traffic Potential (TP) is not just based on how many people search for the keyword, but also includes the long tail or variants of the keyword. You should view this on SERP overview.

CPC (Cost Per Click) is a measure of commerciality/marketability—which is why keywords with a CPC of over $5 are generally seen as having a high potential for lead generation or sales.

Priority is calculated by taking TP x CPC and dividing by KD + 1; the higher the value, the greater the SEO value of that keyword. This simple procedure allows you to maximise your investment into SEO.

Step 3—Organising and Analysing Your Data in Google Sheets

Importing OpenAI and Ahrefs Data

Once you have collected your data, you will need to arrange and organise it in a structured manner. Create a master table with columns for: Keyword, Cluster, Volume, KD, CPC, TP, Intent, Status for about 90% of the work that you will need to do.

To automate the importing of your data, create a workflow in ASCN.AI where you create a schedule to trigger an API request to Ahrefs that updates the Google Sheet.

By utilizing the methodology outlined above, you can easily determine if you have any gaps in your content that may be impacting your ability to rank in the SERPs. This methodology provides a way to evaluate the top 20 search engine results page (SERP) queries from Ahrefs, which per Ahrefs, rank the following forms of content in the top 10 positions within the SERPs.

If you see that the SERPs for these top 20 queries are filled with forums, Reddit posts, and older articles (2019-2020), then you certainly have identified a content gap for those queries. Content gap analysis can help identify content gaps to fill, and after refining and re-optimizing that content, traffic to your site can increase by as much as 150%.

For example, using the query "how to automate on-chain data analysis," we found that we had 4 forum results, 3 generic articles, 2 course-type offerings, and 1 video in the Top 10 results. Therefore, we built an in-depth guide for this topic with API integrations, etc. Within 3 months, our guide was ranked within the Top 5 results on Google for the query.

Building the Content Plan with Keywords

You can build a content plan on targeted terms based on their importance/priority within your website, and group similar keywords into clusters.

Once you have defined what is important for you to rank for, you can create a complete priority matrix, which allows you to quickly and easily create content around those key terms.

The prioritization of keywords may look like this:

• High Priority: KD < 20, TP > 1,000, intent = commercial.
• Medium Priority: KD between 20 and 40, TP > 500, intent = mixed.
• Low Priority: KD > 40, TP < 500, intent = informational.

The above model is simple because it allows you to start with your quick wins and develop materials of medium importance, in order to continue to build the momentum for long-term growth. In just over a 3-month period, we successfully published 47 pieces of content that resulted in our site's monthly traffic from 1,200 to 8,400 visitors.

Some Additional Practical Ideas and Templates

OpenAI Prompt Examples

• Keyword Idea Generation Example: "As a person with 10 years of SEO experience and knowledge of what it takes to create SEO-friendly content, you can identify your target audience by: [description of your target audience]. Create 50 problems and provide 3 variations for each problem.
• Clustering: "Take a list of [N] queries, cluster them by intent, name the cluster, and identify the primary query and the intent category."
• Competitive Analysis: "Examine the top 5 articles that contain [keyword], and provide themes, content gaps, and unique formats."
• Article Structure: "Using [keyword], create an article structure with H1, H2, H3, as well as SERP Analysis."

Google Sheets Templates for Convenience

Columns: Keyword, Cluster, Volume, KD, CPC, TP, Intent, Priority, Status, Notes

Formulas:

• Priority = (TP * CPC) / (KD + 1)
• Intent Classification = Use the formula =IF(SEARCH("Intent",G3),"Intent not Found","Intent Found")
• Conditional Formatting: Green - Priorities > 100, Yellow - Priorities 50-100, Red - Priorities < 50

Note: You can utilize additional sheets within Google Sheets to facilitate organization such as Cluster, Content Plan, Competitors, and Archive. Create and customize the template to your own specifications. Connect your Ahrefs account for an automated system for 1-2 months of future content development.

FAQ — Frequently Asked Questions regarding the Workflow

How long does it take to complete the entire research process?

It takes between approximately 3-5 days from the time you set up the necessary tools until the time you have a completed content plan. In regards to ASCN.AI, the weekly refresh update process requires between 4-6 hours of time per week.

Do I need to use Ahrefs if there are any free alternatives?

You may use free alternatives; however, the data provided will not be as accurate or in-depth as that provided by Ahrefs. Google Keyword Planner may be used for starting out, but the accurate data margin could be up to 30-40 percent different from the real data.

When do I need to refresh the data?

Ahrefs refreshes their data approximately every 2-4 weeks; however, I suggest that you refresh your data once every week so that you are not missing out on any new developments that may have impacted the data.

What should I do if GPT provides me with irrelevant ideas?

To improve your queries, you will need to provide GPT with more specific information about the topic. This includes better descriptions, examples of good and bad queries. If GPT continues to generate poorly performing suggestions after 2-3 attempts, change the seed keyword/s you are using.

How do I choose between several similar important queries?

Use the priority formula or look for KD < 15 and TP > 800.

Do I have to set up all three programs together?

Not necessarily. Start with OpenAI, add Ahrefs for validity, and then add Google Sheets when you have 50-100 queries.

Conclusion and Next Steps

The OpenAI + Ahrefs + Google Sheets combo is not simply a toolset; it is a complete system for understanding your audience, validating their needs, and turning it into a clear plan for content creation.

Next Steps:

1. Set Up OpenAI and Ahrefs, and Create a Basic Table in Google Sheets.
2. Generate 100-150 Ideas using GPT and Validate them using Ahrefs.
3. Create Semantic Clusters, Rank Queries, and Build a Content Plan for a Month.
4. Implement the First 3-5 Pieces of Content and Setup Automatic Data Refreshes.

Now go find some content gaps to optimize for those articles that previously received traffic; optimizing old content can grow traffic by as much as 60 percent in 70 percent of instances without generating new content.

With the ASCN.AI automated platform taking care of 15-20 hours of repetitive work, you will have more time to focus on your content strategy. We Create Automated Tools Once, and They Continue to Work for Many Months After That.