Enhancing SEO Analysis with GPT-4 and Google Search Console Data

Picture a world where SEO analysis and content optimisation transform from time-consuming chores into seamless, insightful endeavours. Say hello to the AI-driven future of digital marketing!

In this article, we’ll dive into how we’ve integrated GPT-4 with Google Search Console data and Google Sheets, enabling comprehensive SEO analysis and identifying content optimisation opportunities.

Embracing the AI Revolution in SEO Analysis

The main objective of incorporating GPT-4 into a Google Sheet with Google Search Console data is to harness the power of AI to analyse small chunks of data on a large scale.

While a trained human can quickly spot top-performing keywords in a short list of queries, doing so with a dataset containing 25,000 keywords is impossible. However, for GPT-4, both tasks are a breeze.

To link GPT-4 through the API, I employed ChatGPT to help me craft the appropriate script for Google Sheets Apps Script. Despite encountering a couple of bugs (all solved thanks to ChatGPT), anyone can replicate this type of script with the aid of ChatGPT.

const OPENAI_API_KEY = "[Insert-API-Key-Here]";
const OPENAI_API_URL = "https://api.openai.com/v1/chat/completions";

function gscGPT(range) {
  if (!range || !Array.isArray(range) || range.length === 0 || !Array.isArray(range[0])) {
    return "Error: Invalid range provided.";
  }
  
  const textIn = "comment the data provided " + range[0].join(', ') + ".";
  const requestBody = {
    "model": "gpt-4",
    "messages": [
      {"role": "system", "content": "You are an SEO expert analysing Google Search Console data in a Google Sheet, you wish to comment on the potential to get more traffic for each page you are analysing. For each page you will get the following information, Query, Page, Clicks, Impressions, CTR, Average position, all for the last 30 days. Our goal is to identify pages with great traffic potential, thanks to content optimsiation. Keep your comment consise, a few words are fine."},
      {"role": "user", "content": textIn}
    ],
    "temperature": 0.2,
    "max_tokens": 2048
  };

  const options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + OPENAI_API_KEY
    },
    "payload": JSON.stringify(requestBody),
    "muteHttpExceptions": false
  };

  try {
    const response = UrlFetchApp.fetch(OPENAI_API_URL, options);
    const responseJson = JSON.parse(response.getContentText());

    if (responseJson.choices && responseJson.choices.length > 0) {
      Logger.log(responseJson.choices[0].message.content);
      return responseJson.choices[0].message.content;
    } else {
      return "Error: No response from OpenAI API.";
    }
  } 
  
  catch (error) {
    Logger.log("Error:", error.message);
    return "Error: " + error.message;
  }
}

In just ten minutes, I managed to configure the script to comment on traffic potential for each query by merely optimising the content.

Take a look at how it appears in my Google Sheet:

I appreciate the AI’s recommendations. In some cases, it suggests increasing the CTR, as seen in the first row. In other instances, when the average position is lower, like in the second row, it recommends enhancing the meta tags and content.

Watch the video to see the AI commenting on the data:

Since we’re working with a machine rather than a human, I particularly like the recommendation in the video that says, “High CTR, improve impressions for more traffic.”

 It wouldn’t make sense for a human to increase impressions for a specific keyword. We don’t control who searches for a particular keyword or how many people should.

However, from this, we may want to explore similar search queries to drive even more traffic.

Much more could be done to gain better insights, but this initial step is already quite revealing.

Streamlining Data Analysis with GPT-4

GPT-4, whether via chat or the API, has a specific token API limit. Currently, GPT-4 API token limit is set at 8,000 tokens (although I recently discovered a 32k token model is now available, which is exciting).

A 10-character prompt equates to about three tokens. This means that your prompt and prompt response must use less than 8,000 tokens or 25,000 characters combined.

At the moment, we can’t get GPT-4 to analyse an entire CSV file with 25,000 rows and multiple columns. It would simply return an error.

However, getting GPT-4 to analyse one row at a time is possible. That’s when integrating GPT into a spreadsheet becomes extremely useful. We can easily drag and drop the formula that prompts GPT-4 to analyse the first row (as demonstrated in the video).

As an SEO expert, I’ve spent countless hours identifying low-hanging fruits from extensive keyword databases for numerous clients. I’ve also experimented with various costly tools that automate this type of task.

Now, I have my own AI assistant handling most of the work for me. I’ll still need to check and train it to ensure it continues doing the right thing, but it will save me significant time and money in the end. 

Cost Considerations and Limitations

Speaking of money, how much does it cost to use the GPT-4 API to analyse thousands of rows of data in a Google Sheet?

Currently, OpenAI charges $0.04 per 1,000 tokens for using the 8k model of GPT-4. I will need to use approximately 160 tokens per row. Therefore, I will need 4,000,000 tokens for a 25,000-row sheet, which would cost about $120.

While this may not seem like a lot for a task that a human couldn’t perform, especially in just a few minutes, it is too costly when considering that many of the 25,000 rows contain irrelevant keywords.

I prefer to limit the list of queries to check to around 1,000. In this case, the analysis would cost me about $5.

It’s crucial to ensure that the formula used to call the function, which then calls the GPT-4 API, is removed from your sheet once executed. Otherwise, it will continue to refresh regularly.

Practical Applications and Future Improvements

Checking the traffic potential behind each query is one of the many things that could be achieved with GPT-4 integrated into a Google Sheet containing Google Search Console data. According to GPT-4 itself, it would also be possible to:

• Evaluate average position trends: GPT-4 could analyse changes in average position over time and provide insights on whether your website is moving up or down in search rankings, helping you prioritize which pages to focus on.

• Suggest content improvements: By examining the queries associated with each page, GPT-4 could recommend topics or subtopics to include in the content to address user intent more effectively, potentially increasing clicks and engagement.

• Analyse CTR patterns: GPT-4 could identify patterns in click-through rates, such as consistently high or low CTRs for specific queries or pages, providing insights into possible issues or opportunities to improve user experience and increase clicks.

• Find underperforming pages: GPT-4 could pinpoint pages with low clicks and impressions, suggesting that these pages may need more significant content updates or improvements in technical SEO aspects to increase their visibility in search results.

• Recognize top-performing pages: By analysing pages with high clicks, impressions, and CTR, GPT-4 could highlight your top-performing content, allowing you to understand better the factors contributing to their success and apply those lessons to other pages on your website.

• Seasonal trends and opportunities: GPT-4 could identify seasonal fluctuations in impressions and clicks, allowing you to capitalize on opportunities during peak times and adjust your content strategy accordingly.

• Long-tail keyword opportunities: GPT-4 could identify long-tail keywords driving traffic to your website by analysing query data. These queries often have lower competition, allowing you to optimise your content for these specific terms and improve your search visibility.

• Analyze search intent: GPT-4 could assess the search intent behind the queries driving traffic to your pages, helping you tailor your content to match users’ needs more effectively.

• Identify content gaps: GPT-4 could find queries where your website is not ranking well, indicating potential content gaps you can address by creating new content or updating existing pages.

• Discover content cannibalization: GPT-4 could identify instances where multiple pages on your website compete for the same keywords, potentially diluting the authority of each page. In these cases, you should consolidate or differentiate the content to avoid cannibalization.

• Optimise for featured snippets: GPT-4 could analyse queries and average positions to identify opportunities to optimise your content for featured snippets, which can drive additional traffic and increase your website’s visibility in search results.

At Vine Digital, we are developing new scripts daily to conduct the above types of checks. It is challenging to keep up with the number of ideas we generate every time we consider the incredible things we could do to enhance and expedite our services with AI, but we’re making progress.

Conclusion:

The integration of GPT-4 with Google Search Console data heralds a new era in SEO analysis powered by artificial intelligence.

By embracing AI technologies, we can save a significant amount of time, streamline our analysis, and uncover highly valuable insights to boost our marketing results.