Here is a keyword research template you can paste straight into Google Sheets or Excel: 12 columns, two formulas, and a priority score that ranks every keyword by how well it fits the business, how likely you are to rank, and what a visitor is worth. Copy the header row below, fill it with keywords from free tools, sort by the Priority column, and write the top rows first.
Most free templates stop at keyword, volume and difficulty. That tells you what is popular, not what to write next. The three scoring columns are the part that turns a long list into a short plan, so they get most of the explanation below.
The template
Paste this into cell A1 of a new sheet. In Google Sheets, use File > Import or paste it and choose Data > Split text to columns with a comma as the separator.
Keyword,Group,US searches,KD,Intent,What ranks,Fit,Win,Value,Priority,Target URL,Status
how much does sod cost,sod,2900,1,informational,,3,,3,,/sod-installation-cost/,to writeThen add two formulas and drag them down the sheet:
- Win (H2):
=IF(D2="","",IF(D2>=30,1,IF(D2>=15,2,3))) - Priority (J2):
=IF(OR(G2="",H2="",I2=""),"",G2*(H2+I2))
What each column holds:
- Keyword: the exact phrase, in lowercase, one per row.
- Group: a short label for the search intent. Every keyword one page would answer gets the same label.
- US searches: average monthly searches. Use the same tool for the whole sheet so the numbers compare fairly.
- KD: keyword difficulty from your tool, on its 0 to 100 scale.
- Intent: informational, commercial, transactional or navigational.
- What ranks: a few words on the top results, such as “vendor pages”, “forum threads” or “local pack”. You fill this by searching the keyword in a private window.
- Fit: 1 to 3, your score. How closely the keyword matches something the business sells or knows well.
- Win: 1 to 3, calculated from KD.
- Value: 1 to 3, your score. How likely a searcher is to become a customer or lead.
- Priority: Fit times (Win plus Value). The maximum is 18.
- Target URL: the one page that should rank for this group, existing or planned.
- Status: to write, written, published, or skip.
The biggest keyword on that list comes last. “Landscaping ideas” has 27,100 US searches a month, but the people typing it are mostly planning their own yard, so it scores low on fit and value. “How much does sod cost” has a tenth of the searches and comes out on top, because the reader is close to hiring someone.
How the three scores work
Fit is the only score that multiplies, on purpose. A keyword you can’t serve isn’t worth writing for, however easy it looks. Use 3 for a service you sell, 2 for a topic your customers care about and you can write about with authority, and 1 for something loosely related.
Win comes from the KD column. The thresholds in the formula follow Semrush’s keyword difficulty bands, where 0 to 14 is “very easy”, 15 to 29 is “easy” and 30 to 49 is “possible”. Semrush says its score is a percentage from 0 to 100 based on factors such as the median number of referring domains of the ranking pages. Other tools calculate difficulty their own way, so a 20 in one tool isn’t a 20 in another. Keep one source per sheet and adjust the thresholds if your site is older and has links of its own.
Value is a judgment call. A cost question (“how much does sod cost”, “french drain cost”) scores 3, because the reader is pricing a job. A how-to question the business also does as a service scores 2. Pure inspiration or DIY searches score 1. The CPC column from your keyword tool is a useful hint: in the same data pull, “french drain cost” had a CPC of $6.44 and “landscaping ideas” $1.19, so advertisers pay more than five times as much for a click on the first.
Fill it from free data: the checklist
- Write 10 to 20 seed terms. Your services, the problems customers call about, and the questions you answer on the phone.
- Get ideas and volumes from Keyword Planner. Google’s Keyword Planner help page describes two modes: Discover new keywords for ideas and Get search volume and forecasts for a list you already have. You must enter billing information to finish account setup, but you don’t have to run an ad.
- Add your Search Console queries. In the Performance report, open the Queries tab and use the export button. This gives you the searches that already show your site.
- Add difficulty. Run the shortlist through one keyword tool and paste its KD scores. Free plans limit lookups, so skip rows you’ve already cut for fit.
- Search the top 20 rows yourself and fill in What ranks. If the top results are all national retailers or a local pack, note it, because that changes what kind of page can rank.
- Score Fit and Value, then let the formulas fill Win and Priority.
- Group and assign a URL. Sort by Group, give each group one Target URL, and mark everything else in the group as covered by that page.
- Sort by Priority, highest first, and write from the top.
Step 7 decides whether the sheet becomes a plan. “When to aerate lawn” and “when to overseed lawn” (1,900 US searches a month) are separate rows, but one article about fall lawn timing answers both. Give them one group and one URL, or you’ll end up with two thin pages that compete with each other. Once pages go live, an internal linking tool helps check that each group’s page gets links from the related ones.
Read the data columns carefully
Two columns in the free tools mean something other than what they seem to.
- Competition is about ads. Google’s Keyword Planner metrics page defines it as “the number of advertisers that showed on each keyword relative to all keywords across Google”. It says nothing about how hard the organic results are. Leave it out of the sheet or label it “ad competition”.
- Volumes are averages and rounded. The same page says average monthly searches cover “a keyword and its close variants”, are averaged over 12 months by default, and are rounded. A seasonal term hides its peak: “fall lawn care” averages 1,300 US searches a month, but in the same DataForSEO pull it had 4,400 in September 2026 and 210 in May 2026. Check the monthly trend before you schedule a seasonal article, and publish it a month or two before the peak.
- Search Console exports are a sample. Google’s About Search Console data page says tables show at most 1,000 rows, that rare queries are left out to protect privacy, and that values shown as “~” or “-” become zeros in the download. A zero in that tab means “not shown”, not “no searches”.
Adapt it to your situation
- A local business: swap the US searches column for searches in your city, and add a Map pack column (yes or no). Our guide to local SEO keyword research covers how to pull city-level numbers and which terms belong on the Business Profile instead of the site. Keywords like “landscaping company near me” go to the homepage or a service page, never a blog post.
- An agency with several clients: keep one sheet per client and add a Client column only to a master tab you build with
=QUERY()or a pivot table. Mixing clients in one scoring tab makes the Fit column meaningless. - Keywords from a competitor: add a Source column with “gap” for terms you found through competitive keyword research, so you can see later which method found the pages that worked.
- Tracking results: add Published date and Position columns once pages go live, and review them with the rest of your monthly SEO report.
FAQ
What should a keyword research template include?
At minimum: keyword, search volume, difficulty, intent, a target URL and a status. Adding a group column and a priority score is what makes it usable, because it stops you building two pages for one search and tells you what to write first.
Is Google Sheets or Excel better for keyword research?
Either works with the template above, and the formulas are the same in both. Google Sheets is easier to share with a client or writer, so most people start there.
How many keywords should be in the sheet?
Enough to fill a few months of writing, often 50 to 200 rows that collapse into 15 to 40 groups. Score the groups you could write this quarter, and leave the long tail unscored until you need it.
Score twenty rows before you write anything
The template only pays off once the scores are in. Fill 20 rows, score Fit and Value honestly, sort by Priority and look at the top five. If one of them is a page you already have, improve that page first; if not, that’s your next article.
