Keyword Research skill for Codex

Done well: the output is a keyword map, not a keyword list. Each row is a cluster with one intent, one primary query, supporting queries, a target URL (existing or new), and a priority justified by business value and realistic ranking odds.

Data sources and what they are good for

Source	Good for	Caveats
Search Console (Performance export or API)	Real queries you already appear for, impressions, position	Only your own visibility; anonymized long-tail queries are hidden
Google Keyword Planner	Volume ranges, ad competition	Buckets and merges close variants; needs an Ads account
Ahrefs / Semrush / similar exports	Volume estimates, difficulty, SERP URLs, competitor keywords	Modeled numbers; compare relative, not absolute
Live SERPs	Intent, format, who ranks, SERP features	Personalised and location-dependent; needs network
Customer language (support tickets, sales calls, reviews, community threads)	Problems and phrasing tools miss	Must be turned into queries by hand

Codex usually has no keyword tool access. Ask for exports (CSV) and work offline from them; say so when volume numbers are missing instead of inventing them. Fetching SERPs needs network access and may require approval.

Workflow

- [ ] 1. Define the business frame: products, audiences, conversion pages, exclusions
- [ ] 2. Seed list from products, problems, competitors, existing GSC queries
- [ ] 3. Expand and import tool exports
- [ ] 4. Clean and normalize
- [ ] 5. Label intent and funnel stage
- [ ] 6. Cluster by SERP overlap
- [ ] 7. Map clusters to URLs; flag cannibalization
- [ ] 8. Prioritize and deliver the map

1. Business frame

Write down before touching data: what the site sells, to whom, which pages convert, which topics are out of scope (jobs, free alternatives, adjacent industries), and the markets/languages. Keywords without a path to the business get dropped in step 8.

2–3. Seeds and expansion

• Seeds: product category names, problems the product solves (“invoice late payment reminder”), job titles of buyers, integrations, competitor names + “alternative”.
• Expansion patterns that surface real demand: question modifiers (how, why, can, vs, best, for [audience], template, example, pricing, error message strings), and Search Console queries where the site ranks 8–30.
• Merge all exports into one table: keyword, volume, difficulty, cpc, source, url.

4. Clean

• Lowercase, trim, collapse whitespace; dedupe exact matches.
• Merge close variants (plurals, word order) but keep the variant with the most volume as the label.
• Remove: out-of-scope topics, competitor navigational queries you cannot serve, adult or irrelevant ambiguous terms, and queries with zero volume and no business value.

import csv, re
rows = {}
for r in csv.DictReader(open("keywords_raw.csv", encoding="utf-8")):
    kw = re.sub(r"\s+", " ", r["keyword"].lower()).strip()
    vol = int(float(r.get("volume") or 0))
    if kw and (kw not in rows or vol > rows[kw]["volume"]):
        rows[kw] = {"keyword": kw, "volume": vol, "kd": r.get("difficulty", "")}

5. Intent and stage

Label from the SERP when available, from modifiers when not:

Signals	Intent	Typical page
how, what, why, guide, example, error text	Informational	Guide, docs, glossary
best, top, vs, alternative, review, comparison	Commercial	Comparison, alternatives page
pricing, buy, demo, free trial, coupon, “[product] for [use]”	Transactional	Pricing, product, landing
brand names, login	Navigational	Homepage, login
near me, city names	Local	Location page

Modifiers mislead often (“crm” alone is commercial; “invoice template” is transactional for a template site). When in doubt, the SERP wins.

6. Cluster by SERP overlap

Two keywords belong on one page when Google ranks largely the same URLs for both. With a SERP export (keyword, rank, url for the top 10), cluster by shared URLs:

import csv
from collections import defaultdict

serps = defaultdict(set)
for r in csv.DictReader(open("serps.csv", encoding="utf-8")):
    if int(r["rank"]) <= 10:
        serps[r["keyword"]].add(r["url"].split("#")[0].rstrip("/"))

THRESHOLD = 3  # shared top-10 URLs needed to group; 3-4 is a sensible default
volume = {k: v["volume"] for k, v in rows.items()}  # rows from the step 4 cleaning code
by_volume = sorted(serps, key=lambda k: -volume.get(k, 0))
clusters, assigned = [], set()
for head in by_volume:
    if head in assigned:
        continue
    members = [k for k in by_volume if k not in assigned and len(serps[head] & serps[k]) >= THRESHOLD]
    assigned.update(members)
    clusters.append({"primary": head, "members": members})

This is a greedy, head-first method: the highest-volume unassigned keyword becomes a cluster head and absorbs keywords sharing enough URLs with it. Raise the threshold to split more; lower it to merge more. Without SERP data, cluster by intent + shared core terms and mark the clusters “unverified”.

7. Map to URLs and find cannibalization

• For each cluster, pick the existing URL that already ranks best for its members (from Search Console) or mark “new page”.
• Cannibalization check: in a GSC query+page export, find queries where two or more of your URLs each get meaningful impressions and positions alternate. Resolve by merging content and 301-redirecting, differentiating intent, or re-pointing internal links; not by noindex on the weaker page by default.

8. Prioritize

Score each cluster 1–3 on three axes and sort by the product:

• Business value: 3 = intent directly precedes a conversion for a core product; 1 = awareness only.
• Winnability: 3 = you already rank 4–20 or the SERP has weak/forum results; 1 = SERP is all major brands with deep content and strong links.
• Demand: relative cluster volume (sum of members) within this dataset.

Prefer improving existing pages ranking 4–20 before creating new pages; that is the fastest return.

Gotchas

• Keyword difficulty scores from different tools are not comparable, and none accounts for your own topical authority. Use them to rank within one dataset only.
• Volume for new trends lags; zero-volume keywords with clear buyer intent (specific integrations, error messages) often convert better than head terms.
• Do not create one page per keyword variant; that is the doorway/thin-content pattern Google’s spam policies target.
• AI Overviews and other SERP features reduce clicks for simple definitional queries; note SERP features in the map and weigh click potential, not just volume.
• Search volume is monthly average; seasonal topics need the monthly trend.

Output

Deliver a CSV and a short summary:

cluster_id,primary_keyword,intent,members,cluster_volume,target_url,status,value,winnability,priority,notes
c01,invoice reminder email,informational,"invoice reminder email; payment reminder email template; overdue invoice email",4400,/blog/invoice-reminder-email,update,3,3,high,ranks 9; add templates section
c02,freelance invoicing software,commercial,"freelance invoice app; best invoicing software for freelancers",2900,/freelancers,update,3,2,high,title mismatch
c03,net 30 meaning,informational,"what does net 30 mean; net 30 terms",8100,new: /glossary/net-30,new,1,3,medium,AI Overview present

Summary: number of clusters, top 10 by priority with reasoning, cannibalization pairs found, data gaps (e.g. “volumes from Semrush US only; no SERP data for 40 clusters”). Hand each new page to content-briefs.