ChatGPT Keyword Analysis and Google Sheets Automation

Keyword research and content planning are the fundamental yet most time-consuming tasks in SEO studies. There's one question that could save us every minute we invest: "How do we automate this?" In this article, we will lay the foundation of keyword research using ChatGPT and attempt to derive keyword ideas about the topic we want to create content for, from ChatGPT. We also have a surprise for you at the end of the article!

We are talking about a situation where we all spend a lot of time, where similar steps are followed and outputs are similar in every keyword analysis. "When the steps are defined and the outputs are similar, how do we optimize this?" The answer to this question is to break it down into steps and obtain outputs from Language Model (LLM) models using appropriate prompts. LLM models often generate output based on the pattern of words that come one after the other in sequential order in the training data. The important part in this information is the "coming one after the other in sequential order" aspect. This area actually provides us with information about what users write and ask about. In short, it works logically the same way as the "related searches" section on the SERP. While someone takes into account the word order in their users' searches, LLM considers the sequence of words or the more frequently occurring consecutive words in the dataset.

As a result, LLM, i.e., ChatGPT, being trained on data available on the internet, can predict very well what people will search for and how. What these will be are hidden in the questions we ask.

If everyone has opened ChatGPT, let's get started!

ChatGPT Should Act Like an SEO Expert!

Now, we will make ChatGPT behave like an SEO expert. We have to force ChatGPT to act like an SEO expert so that it can answer all our further questions within a certain framework.

Our magic phrase:

"I want you to act as an SEO expert specialized in website optimization, search engine optimization, and keyword research. This is how you should answer the questions I will ask from now on."

Can ChatGPT Generate Keywords Like an SEO Expert?

The question "How to generate content with ChatGPT?" has become a favorite search query for many of us. The only way to find the answer to this question is to try it out. Let's test the prompt I use to generate relevant subheadings for ChatGPT:

My command to generate a subheading for the content I provided to ChatGPT:

"Create a keyword research report containing a Turkish table layout that will show keyword suggestions related to the root keyword 'Pregnancy Symptoms.' The data in the table aims to provide information about the most popular keywords and keyword phrases related to the root keyword. The keywords should be sorted from highest to lowest based on past search volume.Search volumes should be indicated as low, medium high."

Now, Let's Create Subheadings and Determine Keyword Density!

In the first step, we glanced over keyword ideas superficially. These provided us with some insights and directions about the topic. Now, we should decide which headings to cover in our content. Once we extract the headings, determining the keyword density will be much easier.

The prompt we will use to generate subheadings is as follows:

I want you to assume that you write Turkish content that competes with the top websites in this field and consistently ranks high on Google. Utilize the keywords you identified in your previous answer to create subheadings, and provide a comprehensive list of frequently asked questions (PAA) related to these keywords on Google.

Determining Keywords Based on Subheadings

We now have an idea of which subheadings to use in our content, along with an understanding of the questions people will want answers to. Our next step is to prepare a draft of a keyword list that matches the subheadings and also includes frequently asked questions. We can quickly solve this problem with a short prompt.

Our prompt for identifying keywords with ChatGPT:

Prepare the keywords to be used in the headings you've determined, taking into account the People Also Ask (PAA) section. Remember that synonyms of keywords will also be important for us. Prepare the keywords with the highest search volume to match the headings.

Our output is just as we wanted:

Time to Combine All the Data!

Up to now, the data we've gathered includes keyword ideas, synonyms, search volume estimates, and the most frequently asked questions related to our main root keyword. Additionally, we have insights into subheadings we can use when publishing content.

Seeing all of these in a table and providing a general overview will complete our keyword analysis. While ChatGPT's provided data is generally based on predictions, it also offers quite insightful ideas.

Now, let's run our prompt to create our table:

Your task is to prepare a keyword analysis report. Task details: Transform the report you prepared with the keywords to be used in the determined headings, taking into account the People Also Ask (PAA) section, into a detailed table. The columns of the table should be: keyword, search volume estimate (high, medium, low), synonymous keywords, People Also Ask (PAA) questions.

Each column should contain up to three People Also Ask (PAA) questions. Select keywords primarily from those with the highest possible search volume.

And our output will be as follows:

The output we received provided us with quite good ideas, and if you're a ChatGPT Plus user, we can say that this table will expand even further. In this article, I completed my keyword analysis using ChatGPT, which is accessible for everyone for free. A small tip; you can achieve a more comprehensive table by using the "Regenerate response" button or by providing the same prompt again.

A Little Surprise for Those Who Have Reached the End!

Up to this point, we have completed our keyword analysis using ChatGPT by writing our prompts step by step. If you want to quickly generate your keyword analysis report via Google Sheets without spending time on these, I have prepared keyword analysis sheets automation for you with ChatGPT. To use the automation, simply click the "Click to Use the Automation!" button. This way, you can copy the Sheets automation to your own workspace and start using it right away.

Click here to perform keyword analysis with ChatGPT using the Sheets automation I've prepared!