Templates + workflows

Keyword research without a repeatable workflow is chaos. Here's the workflow I use on every new project, plus the spreadsheet template it produces.

The workflow (step by step)

Phase 1. Setup (Day 1)

1. Define the business: product, audience, key locations, brand terms
2. Audit current rankings (GSC + rank-tracking tool)
3. Identify 3-5 SEO competitors (not business competitors)

Phase 2. Seed + Expand (Days 2-3)

1. Generate 10-15 seeds from product, customer language, JTBD, category
2. Expand each in Ahrefs/SEMrush: "Matching terms," "Related terms," "Questions"
3. Pull competitor top keywords (top 200 per competitor)
4. Scrape People Also Ask + Related Searches
5. Merge all into one spreadsheet. Dedupe.

Phase 3. Filter (Day 4)

1. Manual pass: mark each keyword as relevant / irrelevant / maybe
2. Drop irrelevant (often 50-70% of raw pool)
3. Filter by volume threshold (e.g., >20/mo for small site, >100/mo for larger)

Phase 4. Enrich (Days 5-6)

1. Pull volume, KD, CPC for each remaining keyword
2. Classify intent (I/C/N/T)
3. Note current ranking (from GSC or tool)
4. Assign suggested format (guide/listicle/product)

Phase 5. Cluster (Day 7)

1. Run SERP-similarity clustering (tool or manual)
2. Name each cluster by its primary keyword
3. Assign each cluster to either an existing page or a new one

Phase 6. Prioritize (Day 8)

1. Score each cluster: volume × conversion potential × (1/difficulty)
2. Adjust for strategic value (clusters that open up follow-on content)
3. Slot into 90-day content plan (top 10-15 clusters)

Spreadsheet template, one row per keyword

• keyword, the query text
• cluster, cluster name
• primary_or_supporting. P / S
• volume, monthly
• kd, tool difficulty score
• intent. I / C / N / T
• current_rank, if applicable
• target_url, existing or new
• format, guide / listicle / product / comparison
• priority. P0 / P1 / P2 / P3
• notes

Cluster-level summary tab

• Cluster name
• Sum of volume across all keywords in cluster
• Average KD
• Intent
• Format
• Target URL
• Priority score
• Assignment (writer + deadline)

Refresh cadence

Run a lighter version of this workflow every quarter. SERPs move. New keywords emerge. Old clusters may need splitting.