Keyword Research Workflow — GSC + Ahrefs + ChatGPT

Overview

This is a reusable three-step workflow for analyzing the current search performance of an established client account and generating actionable SEO and PPC recommendations. It combines two complementary data sources — what users are searching to find us (Google Search Console) and what we formally rank for (Ahrefs) — and feeds both into ChatGPT for synthesis and prioritization.

Developed during a weekly strategy call with Gilbert Barrongo reviewing ^[1], but applicable to any account with sufficient search history.

When to Use This Workflow

The client has been in business long enough to have meaningful GSC and Ahrefs data
You suspect there are high-intent keywords ranking on page one but too low to generate clicks (positions 6–15)
You need to brief an account manager, SEO specialist, or content writer with specific, prioritized tasks
You are starting a new keyword research cycle for an existing account

The Three-Step Workflow

Step 1 — Export GSC Queries

Tool: Google Search Console
What it captures: The actual search terms users typed that resulted in an impression or click on the client's site.

Log in to GSC under the team account (credentials in LastPass)
Select the client property
Navigate to Performance → Search Results
Set the date range (90 days is a useful default)
Scroll down to the Queries table
Click Export → Excel (.xlsx format — ChatGPT handles this more reliably than CSV)

Note: GSC "queries" are equivalent to "search terms" in other platforms. They represent real user intent signals, not keyword targets.

Step 2 — Export Ahrefs Organic Keywords

Tool: Ahrefs
What it captures: The keywords the site is formally ranked for, along with position, volume, and difficulty data.

Log in to Ahrefs under the team account (credentials in LastPass)
Navigate to the client's project (sort alphabetically if needed)
Click Organic Keywords (shown as a count in the overview card)
Filter to positions 1–20 to focus on actionable rankings
Select all rows
Click Export → UTF-8 format

Distinction: Ahrefs keywords are what the site ranks for according to Ahrefs' crawler. GSC queries are what users actually searched. They overlap but are not identical — using both gives a fuller picture.

Step 3 — Synthesize in ChatGPT

Tool: ChatGPT (GPT-4 or later)

Open a new ChatGPT session
Upload the GSC Excel file with the prompt:

"Evaluate these queries for [client domain]."
Upload the Ahrefs export file with the prompt:

"These are the organic keywords we rank for now."
Once both files are loaded, prompt:

"Consider the query report and the keyword report, and give me a combined summary and recommendations."
Follow up with targeted prompts as needed:
- "Give me additional core commercial keywords we should be targeting."
- "Give me more detail on recommendation #2 so I can assign it to a developer."
- "What blog content should we create to support these keywords?"

Interpreting the Output

ChatGPT will typically segment findings into categories. Key things to look for:

Signal	What It Means	Action
High impressions, low CTR, position 8–11	Ranking on page one but too low to get clicks	Prioritize moving these keywords into top 5
Strong brand CTR (>40%)	Brand recognition is healthy	Maintain; not the focus
Off-topic informational rankings	May or may not be intentional (e.g., recipe content for a B2B juicer brand)	Evaluate strategically before acting
Keywords with volume that we don't rank for at all	Gap opportunities	Add to content and PPC roadmap

Key insight from Citrus America: The account was ranking for high-intent commercial terms like "commercial citrus juicer" at positions 8–11. This produced high impressions but near-zero clicks. The entire SEO effort was reoriented around pushing those specific keywords into the top 5. See ^[2] for details.

Routing Recommendations to the Right People

Once ChatGPT produces its recommendations, route tasks as follows:

Recommendation Type	Assignee
Build or expand core commercial landing pages	SEO specialist (e.g., Yash)
Rework title tags and meta descriptions	SEO specialist (e.g., Yash)
Internal linking overhaul	SEO specialist (e.g., Yash)
High-intent supporting blog content	Content writer (e.g., Gavin)
New PPC ad groups / SKAGs for commercial keywords	PPC manager (e.g., Gilbert)

The account manager (e.g., Melissa) should receive the full ChatGPT output and coordinate task assignment. Sharing the Fathom recording of the research session is also recommended.

Competitor Research Add-On (SpyFu)

For a fuller picture, supplement the GSC + Ahrefs workflow with SpyFu competitor analysis:

SEO Combat view: Compare your domain against 2–3 competitors using a Venn diagram. Focus on keywords competitors have that you don't — these are gap opportunities.
PPC Overview: Review competitor ad copy and keyword targeting history. Useful for identifying proven messaging and keyword angles before building new ad groups.

Run SpyFu comparisons in pairs (two competitors at a time) and rotate through the competitor list to surface the broadest set of ideas.

^[3]
^[2]
^[4]
^[5]