Audience Problem Keyword Research Workflow with OpenAI, Ahrefs and Google Sheets

This workflow automates the generation of SEO keywords and conversational AI questions based on a customer profile, enriches them with search volume and difficulty data from Ahrefs, and stores everything in Google Sheets for market research and content planning.

Purpose

No business context provided yet — add a context.md to enrich this documentation.

This workflow serves marketers, content creators, and SEO specialists who need to understand search intent for well-defined customer segments. Instead of spending hours manually researching keywords and questions that a target audience might use, this automation generates relevant search terms and conversational queries in minutes, complete with SEO metrics for prioritization.

How It Works

1. Define Customer Profile: Start by setting up a detailed customer persona in the Data node, including demographics, needs, and search behavior patterns
2. Generate AI-Powered Keywords: OpenAI analyzes the customer profile to generate 50 relevant SEO keywords with search intent classification
3. Generate Conversational Questions: A second AI model creates 50 natural language questions that the audience would ask ChatGPT or similar AI tools
4. Store Initial Data: Both keywords and questions are saved to separate Google Sheets tabs with their intent classifications
5. Enrich with SEO Data: Each keyword is processed through an SEO MCP server to fetch search volume and difficulty metrics from Ahrefs
6. Update with Metrics: The enriched keyword data (volume, difficulty) is written back to the Google Sheet for analysis

Workflow Diagram

graph TD
    A[Manual Trigger] --> B[Data Node - Customer Profile]
    B --> C[SEO Seed Keywords - OpenAI]
    B --> D[AEO Questions - OpenAI]

    C --> E[Parse Keyword JSON]
    D --> F[Parse Question JSON]

    E --> G[Loop Over AI Keywords]
    F --> H[Loop Over AI Questions]

    G --> I[Add keyword to Sheet]
    H --> J[Add AI question to Sheet]

    I --> K[Related Keyword Generator - MCP]
    K --> L[Parse MCP Keywords JSON]
    L --> M[Loop Over SEO Return Values]
    M --> N[If - Check Label Type]
    N --> O[Add keywords with metrics]
    N --> P[Add SEO research question]

    O --> Q[Loop continues]
    P --> Q
    J --> R[Loop continues]

Trigger

Manual Trigger: The workflow starts when you click "Execute workflow" in the n8n interface. This is a one-time execution model suitable for periodic market research tasks.

Nodes Used

Node Type	Purpose
Manual Trigger	Starts the workflow execution
Set (Data)	Stores customer profile and configuration variables
OpenAI (SEO Seed Keywords)	Generates 50 SEO keywords with intent classification
OpenAI (AEO Questions)	Creates 50 conversational AI questions with intent
Code (Parse Keyword JSON)	Extracts keywords array from AI response
Code (Parse Question JSON)	Extracts questions array from AI response
Split in Batches	Processes keywords and questions individually
Google Sheets (Add keyword)	Saves keywords to Keywords sheet tab
Google Sheets (Add AI question)	Saves questions to Questions sheet tab
MCP Client (Related Keyword Generator)	Fetches SEO metrics from Ahrefs via seo-mcp
Code (Parse MCP Keywords JSON)	Processes SEO data response
If	Routes data based on response type (keywords vs questions)
Google Sheets (Add keywords/questions)	Updates sheets with enriched data

External Services & Credentials Required

OpenAI

• Purpose: Generate keywords and questions based on customer profiles
• Credential Type: API Key
• Setup: Add your OpenAI API key in the credential configuration
• Models Used: O4-MINI for both keyword and question generation

Google Sheets

• Purpose: Store and organize keyword research data
• Credential Type: Service Account (recommended for self-hosted n8n)
• Permissions: Read & Write access to the target spreadsheet
• Sheet Structure: Requires "Keywords" and "Questions" tabs with specific column headers

SEO MCP Server (seo-mcp)

• Purpose: Fetch keyword metrics from Ahrefs free tools
• Credential Type: MCP Client configuration
• Dependencies: Requires CapSolver API for CAPTCHA solving

CapSolver

• Purpose: Solve CAPTCHAs when accessing Ahrefs data
• Credential Type: API Key
• Setup: Register at dashboard.capsolver.com and obtain API key

Environment Variables

For the SEO MCP server configuration:

{
  "command": "uvx",
  "args": ["--python", "3.10", "seo-mcp"],
  "env": {
    "CAPSOLVER_API_KEY": "CAP-xxxxxx"
  }
}

Data Flow

Input

• Customer Profile: Detailed persona description including demographics, needs, search behavior, and decision-making patterns
• Configuration: Ahrefs country code (default: "us") and search engine (default: "Google")

Processing

• AI-generated keywords and questions with intent classification (informational, commercial, transactional)
• SEO metrics enrichment (search volume, keyword difficulty)
• Data validation and routing based on response types

Output

• Keywords Sheet: Keyword, Intent, Volume, Difficulty columns
• Questions Sheet: Question, Intent columns
• Enriched data suitable for content planning and SEO strategy

Error Handling

The workflow includes basic error handling: - Parse MCP Keywords JSON node has "Continue on Error" enabled to handle malformed responses - If node validates response labels to ensure proper data routing - Split in Batches nodes handle empty arrays gracefully

Known Limitations

• Requires CapSolver credits for CAPTCHA solving when accessing Ahrefs data
• Limited to Ahrefs free tool data (not full API access)
• OpenAI rate limits may affect processing speed for large customer profiles
• Google Sheets API has rate limits that may require batch processing adjustments

Related Workflows

No related workflows mentioned in the provided context.

Setup Instructions

1. Copy the Google Sheet Template

   • Make a copy of this Google Sheet
   • Update all Google Sheets nodes with your sheet ID

2. Configure Google Sheets Access

   • Set up a Google Service Account for n8n
   • Grant read & write permissions to your copied sheet
   • Add the service account credentials to n8n

3. Set Up OpenAI Integration

   • Obtain an OpenAI API key
   • Add the API key credential to both OpenAI nodes
   • Verify the O4-MINI model is available in your account

4. Configure SEO MCP Server

   • Register for a CapSolver account and get your API key
   • Install the seo-mcp package: uvx --python 3.10 seo-mcp
   • Configure MCP credentials with the CapSolver API key

5. Customize Customer Profile

   • Edit the "Data" node to include your target customer profile
   • Adjust country and search engine settings if needed

6. Test the Workflow

   • Execute the workflow manually
   • Verify data appears in both Google Sheets tabs
   • Check that SEO metrics are populated correctly

7. Monitor and Iterate

   • Review generated keywords and questions for relevance
   • Refine customer profiles based on results
   • Adjust AI prompts if needed for better output quality