--- title: Keyword Research Workflow — GSC + Ahrefs + ChatGPT type: article created: '2026-04-05' updated: '2026-04-05' source_docs: - raw/2025-12-03-weekly-call-w-gilbert-105982888.md tags: - seo - ppc - keyword-research - google-search-console - ahrefs - chatgpt - workflow - citrus-america layer: 2 client_source: null industry_context: null transferable: true --- # 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 [[wiki/clients/citrus-america/_index|Citrus America]], 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. 1. Log in to GSC under the team account (credentials in LastPass) 2. Select the client property 3. Navigate to **Performance → Search Results** 4. Set the date range (90 days is a useful default) 5. Scroll down to the **Queries** table 6. 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. 1. Log in to Ahrefs under the team account (credentials in LastPass) 2. Navigate to the client's project (sort alphabetically if needed) 3. Click **Organic Keywords** (shown as a count in the overview card) 4. Filter to positions **1–20** to focus on actionable rankings 5. Select all rows 6. 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) 1. Open a new ChatGPT session 2. Upload the GSC Excel file with the prompt: > *"Evaluate these queries for [client domain]."* 3. Upload the Ahrefs export file with the prompt: > *"These are the organic keywords we rank for now."* 4. Once both files are loaded, prompt: > *"Consider the query report and the keyword report, and give me a combined summary and recommendations."* 5. 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 [[wiki/clients/citrus-america/seo-strategy|Citrus America SEO Strategy]] 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. --- ## Related - [[wiki/clients/citrus-america/_index|Citrus America Client Overview]] - [[wiki/clients/citrus-america/seo-strategy|Citrus America SEO Strategy]] - [[wiki/knowledge/ppc/single-keyword-ad-groups|Single Keyword Ad Groups (SKAGs)]] - [[wiki/knowledge/seo/position-vs-ctr|SEO: Why Position Matters More Than Impressions]]