Using AI for content gap analysis

Integrate AI with a spreadsheet to identify gaps in your content strategy.

Tags Streamline Icon:
Wrench Streamline Icon:
People Man Graduate Streamline Icon:

In today's fast-paced digital world, ensuring your content strategy meets the needs of your target audience is crucial for engagement and conversion. With the help of AI, we can optimise our content strategy by identifying what our audience is seeking but not finding on our platforms.

This tutorial will guide you through the process of using AI for content gap analysis.

In a nutshell, we’ll connect Google Sheets with OpenAI to analyse content, then use ChatGPT to help us identify gaps and suggest ways to fill them. Here are the steps we’ll take in more detail:

  1. Define your target audience, and their main needs and problems
  2. List all your content in Google Sheets
  3. Integrate Google Sheets with OpenAI
  4. Request OpenAI to summarise the key themes/topics of each piece of content
  5. Use ChatGPT to identify gaps by cross-referencing the summary with your audience needs and problems
  6. Use ChatGPT to generate content ideas that fill the identified gaps
Products Give Gift 1 Streamline Icon:

Try before you bite?
This one's on us.

Sign up for a free account to view our free courses

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Step 1: Define your audience and their needs

The foundation of any effective content strategy lies in understanding who your content is for. By dissecting your audience's interests and needs, we're laying down the groundwork to ensure every piece of content serves a purpose. This not only increases engagement but also builds trust and authority in your niche.

As a first step, you should define your target audience and list their main interests and problems.

If you don’t already have this info to hand, you can use surveys, social media analytics, and customer feedback to compile a comprehensive list of topics, questions, and problems your audience faces.

After pulling this data together, look for recurring themes in the feedback that could indicate areas of interest or concern for your audience. Make a note of these themes as we’ll use them later.

Step 2: List out your content

Now, let’s assess your current content landscape. In a new Google Sheet, list of all your pieces of content - ideally the title, the URL, and a short, relevant text snippet, e.g. the intro of the content piece, or the meta description.

We’re using the blog posts of a fictional company, Engage (employee engagement software), to demonstrate:

Step 3: Set up Google Apps Script

You can use the power of AI to summarise the key themes across all of your content pieces. To do this, we’re going to use Google Apps Script to connect to OpenAI.

Let’s walk through how that’s done.

From your Sheet, click ‘Extensions’ and ‘Apps Script’.

Delete any code that’s showing in the editor, and copy-paste the following code:

function GPT(Input) {
 const BASE_URL = "<>";

   const headers = {
     "Content-Type": "application/json",
     "Authorization": `Bearer ${GPT_API}`

 const options = {
     method: "GET",
     muteHttpExceptions: true,
       payload: JSON.stringify({
       "model": "gpt-4",
       "messages": [{
         "role": "system",
         "content": ""
         "role": "user",
         "content": Input
       "temperature": 0.5
 const response = JSON.parse(UrlFetchApp.fetch(BASE_URL, options));
 // console.log(response)
 return response.choices[0].message.content
💡 Tip: “temperature” sets how creative the AI will be in its responses; play around with a number from 0-1 to find what you prefer. “model” determines what OpenAI language model you use - we’d always recommend using the latest one. Under “messages” you can set, within the currently empty “content” field, an instruction to tailor how GPT-4 will respond to you, e.g. “content”: “you are an expert content marketer” or similar.

You’ll need to replace [YOUR OPENAI KEY] with an API key generated in OpenAI. Continue on to see how that’s done.

Step 4: Generate your OpenAI key

Log in to, then click ‘API’ to be taken to the developer platform.

Click ‘API keys’ on the left menu, and click ‘Create new secret key’.

Give it a name and click ‘Create secret key’. Once generated, copy it to your clipboard. Also, keep it secure; do not share it publicly as it gives access to your OpenAI account capabilities.

Step 5: Testing your Google Apps Script project

Come back to Google Apps Script and replace [YOUR OPENAI KEY] with your newly generated key.

Click the ‘Save project’ icon above the editor (you may need to grant authorisation if this is the first time), and come back to your Sheet.

Now, in the column where you want the summary to appear (column D, in our case), copy-paste the following formula into a cell:

=GPT("Please summarise this blog post title, URL and meta description snippet, to give me an overview of the main theme explored in the blog post from an SEO point of view. JUST WRITE WHAT THE THEME IS AND NOTHING ELSE, 1 TO 3 WORDS MAX: " & C1 & " " & C2 & " " & C3)
💡 Tip: the & C1 & " " & C2 & " " & C3 part of the formula concatenates (joins together) the information in columns 1, 2 and 3. You can customise this if you have data in different columns. Also, the part of the formula in CAPS ensures the AI really pays attention to that part - we found that without it, the responses were too wordy.

The script should run and a response should generate.

Note: Making API calls like this doesn’t come for free. You’ll need to purchase credits in OpenAI - go to to set up.

Now you can simply drag the little blue dot in the bottom-right corner of cell D2 down for all of the posts you want to summarise, to repeat the formula across all of those cells.

Play around with the prompt you use within the formula if the responses you get are too granular or not granular enough. When you’re done, you should have a list of all the different themes your content currently addresses.

Now to see how that matches up against your audience’s needs…

Step 6: Identify content gaps

By identifying content gaps, we’re effectively finding unmet needs or unanswered questions within your audience. You can use ChatGPT to help you identify where any such gaps might exist.

This is where those audience interests and pain points you identified at the top of this tutorial will come in handy.

Open up ChatGPT and upload your Google Sheet (exported as a csv) along with this prompt:

Hey ChatGPT, I'm working on a content gap analysis for my business, [Company Name], which specialises in [product]. Our audience primarily consists of [profile overview] looking to [audience goal]. Here's a detailed list of our audience interests and pain points. Based on this, could you help identify which topics are not adequately covered in our content? I'm attaching a summary of our content alongside for comparison.

Audience Interests:

[List interests]

Pain Points:

[List pain points]

Here’s the response we got when we ran it through ChatGPT for our fictional company, Engage:

Step 7: Generate content ideas that fill the gaps

Now, for each gap, ask ChatGPT to generate several content titles or outlines.

Based on these gaps you’ve identified, please generate a list of content titles or outlines that specifically address these themes. Additionally, could you suggest how to group these ideas into categories for more straightforward prioritisation, possibly by theme or audience segment? Ideally, the content should offer practical insights, focusing on solutions and strategies that can be implemented directly.

Following on from here, you could also use ChatGPT to help you:

  • Decide which content ideas to execute based on impact and feasibility
  • Choose the top ideas for immediate development
  • Generate initial drafts
  • Edit the drafts to ensure they align with your tone, style, and factual accuracy
  • Review each piece, making adjustments for clarity, engagement, and SEO
  • Give advice on how to further refine your content strategy based on content performance

By following these steps, you can leverage AI to effectively optimise your content strategy, ensuring it aligns with your audience’s unique interests and fills any existing content gaps. Continuous iteration and responsiveness to audience feedback are key to maintaining a relevant and engaging content strategy. Good luck!

Get full access

✔️ All 100+ courses & tutorials in our catalog
✔️ New content added weekly
✔️ Private community access
✔️ No subscription, $150 paid once
✔️ Expense it using this template. Or get a team account.
✔️ 30-day refund policy. No questions asked
Join 3,107 learners from companies like Microsoft, Coca Cola, NBA, Adobe & Google

More tutorials like this

View all

If you scrolled this far, you must be a little interested...

Start learning ->

Join 3,107 professionals already learning