Track keyword rankings, analyze competition, and optimize your content strategy
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Keyword planning goes wrong when volume is treated as the primary sort. This template scores opportunity from volume, difficulty and — the part usually missing — how close the term is to a purchase.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Keyword | Volume | KD | Intent | Position | Score |
| 2 | excel consultant | 2,400 | 34 | Transactional | — | 88 |
| 3 | excel templates free | 49,000 | 71 | Informational | 18 | 41 |
| 4 | hire excel developer | 880 | 22 | Transactional | — | 84 |
| 5 | vba vs apps script | 1,300 | 18 | Commercial | 6 | 72 |
Why each one is written the way it is
=ROUND(LN([@Volume])*10*(1-[@KD]/100)*IntentWeight,0)
Opportunity score. The log damps volume so a 50,000-search informational term cannot dominate a 2,400-search buying term, and difficulty and intent both scale it.
=VLOOKUP([@Intent],Intent[[Type]:[Weight]],2,FALSE)
Intent weight from a table — transactional 1.0, commercial 0.7, informational 0.3. This single factor is what stops a content plan being all top-of-funnel.
=IF([@Position]="",0,IF([@Position]<=3,1,IF([@Position]<=10,0.3,0.05)))*[@Volume]
Estimated traffic from current position, so you can rank opportunities you already partly own.
=[@Volume]*CTRat(TargetPos)-[@Volume]*CTRat([@Position])
Incremental traffic from improving a position. Moving from 8 to 3 on a mid-volume term usually beats a new page on a high-volume one.
A worked example with real numbers
A site planned six months of content by search volume and wrote extensively about free templates. Traffic tripled; enquiries did not move. Rescoring with intent weighting put "excel consultant" and "hire excel developer" — a fiftieth of the volume — at the top. Two pages against those terms produced more qualified enquiries in a quarter than the previous six months of content.
Tab by tab
| Tab | Contents |
|---|---|
| Keywords | Term, volume, difficulty, intent and current position. |
| Intent | Intent types and their weights — the tuning dial. |
| Clusters | Keywords grouped into the page that should target them. |
| Tracking | Position history by keyword and month. |
| README | How the score is composed and how to reweight it. |
What is included, and what to look at next
Specific to this workbook, not generic
Because traffic is not the goal. A transactional term at 800 searches a month will produce more enquiries than an informational one at 40,000, and sorting by volume alone hides that completely.
Only where intent is transactional and you have topical authority. A commercial term at difficulty 30 beats an informational one at difficulty 70 in almost every case.
One primary term and a cluster of close variants. Pages targeting several unrelated terms rank for none of them well.
"This keyword planner has helped us identify valuable opportunities and track our SEO progress effectively. Our organic traffic has grown significantly."