Free KGR Spreadsheet Template for Google Sheets

"I did some manual researches to find out the Keyword Golden Ratio (KGR) of some keywords but not sure if it is actually going to work or not?" — r/SEO

My first KGR wins came from a ugly Google Sheet. Three columns. One formula. Conditional formatting that turned cells green when the ratio dropped below 0.25.

No Chrome extension. No SaaS. Just me, Google, and a spreadsheet I copied from Doug Cunnington's Niche Site Project template.

I still keep that sheet — even after building EagleKGR. Spreadsheets force you to see every number. Automation hides the mistakes until you've already written the article.

This guide gives you the exact column layout, formulas, and workflow I use. Copy it into Google Sheets today. Graduate to batch tools when your keyword list outgrows your patience.

---

Why I Still Keep a KGR Sheet

Spreadsheets slow you down on purpose.

When I manually type allintitle counts, I notice patterns. "Wait, every keyword in this cluster has 200+ results." That's a niche problem, not a keyword problem.

"The KGR measures the imbalance between the two. And does it in a simple, easy and elegant way." — r/juststart

Supply vs demand. Two numbers. One ratio. A sheet makes that visible.

I recommend every beginner run 15–20 keywords manually before automating. After that, use the sheet as a backup and for client deliverables where people expect Excel attachments.

---

Column Layout That Works

Column	Header	What goes here
A	Keyword	Exact phrase you're targeting
B	Allintitle	Result count from allintitle:"keyword"
C	Volume	Local monthly search volume (≤250 for strict KGR)
D	KGR	Formula: B ÷ C
E	Rating	Excellent / Good / Poor
F	SERP Notes	Top 3 domains, intent, yes/no
G	Status	Idea / Draft / Published / Ranked
H	Date Checked	When you pulled allintitle

Optional: Cluster, URL, Position for larger projects.

---

Formulas and Conditional Formatting

KGR calculation (cell D2):

=B2/C2

Rating formula (cell E2):

=IF(D2="","",IF(D2<0.25,"Excellent",IF(D2<1,"Good","Poor")))

Conditional formatting on column D:

• Green: less than 0.25
• Yellow: 0.25 to 0.99
• Red: 1.0 and above

Doug's official template includes similar logic plus helper links per row.

---

Manual Workflow Step by Step

1. Export keywords from Google Keyword Planner (volume 50–250).
2. Paste into column A.
3. For each keyword, search allintitle:"your exact keyword" on Google.
4. Record count in column B.
5. Enter local volume in column C.
6. Sort column D ascending. Filter Excellent and Good.
7. Fill SERP Notes after checking top results.

"I then also use allintitle on google to check to make sure similar article titles and keywords don't have too much competition." — r/Blogging

At 2–3 minutes per keyword, 50 rows takes about two hours. For allintitle syntax, see What Is allintitle?.

---

Doug's Template vs Building Your Own

Doug's free template: proven layout, video walkthrough, community standard.

Build your own: only columns you need, integrates with your content calendar.

I started with Doug's, stripped three columns, added SERP Notes and Status. Either path works.

---

Sample Rows From My Coffee Niche Sheet

Keyword	Allintitle	Volume	KGR	Rating
how to descale keurig with vinegar	38	170	0.22	Excellent
best pour over kettle under 50	156	90	1.73	Poor
oxo brew grinder cleaning guide	12	70	0.17	Excellent

"Just because a keyword falls under the 0.25 ratio doesn't necessarily make it good." — r/Blogging

The sheet gives you the number. SERP Notes give you context.

---

When to Graduate to Automation

Switch when you hit 50+ keywords per session, keep fat-fingering counts, or run weekly trending batches.

Use EagleKGR batch mode or the Chrome extension. Export CSV back into the same column layout.

For KGR vs keyword difficulty on sheet winners, see KGR vs Keyword Difficulty. Full walkthrough: user guide.

---

Common Spreadsheet Mistakes

• Dividing volume by allintitle (backwards)
• Using global instead of local volume
• Skipping date column when counts drift
• No SERP column — how I ranked position 19 on wrong intent
• Never updating Status column

More in 7 KGR Mistakes.

---

---

Last updated: June 28, 2026 · Author: EagleKGR Team — niche site keyword research since 2019