Classify keyword intent in Google Sheets
=HUNCHPICK(text, options) reads a search keyword and returns its intent: informational, commercial, transactional, or navigational. A second =HUNCH(text, question) formula flags keywords that already name a brand, so you can split branded from non-branded before a content brief gets written.
Intent decides what to write next
A keyword list without an intent column reads like one long queue: write a page for each one, in whatever order the spreadsheet happens to be sorted. With intent attached, the queue splits itself. Transactional and commercial keywords are worth a page this month. Navigational ones are not worth a page at all, since the searcher already picked a brand.
Intent
=HUNCHPICK(A2:A1000, "informational: wants to learn or understand a topic|commercial: comparing options before buying|transactional: ready to buy, sign up, or download now|navigational: looking for a specific brand, product, or website|other")In Excel: =HUNCH.PICK(A2:A1000, "informational: wants to learn or understand a topic|commercial: comparing options before buying|transactional: ready to buy, sign up, or download now|navigational: looking for a specific brand, product, or website|other")
Four intents, plus other for the keywords that are a fragment or a typo with no clear intent behind them at all. Full syntax is on the HUNCHPICK reference.
Branded or not
=HUNCH(A2:A1000, "Does this keyword name a specific brand, product, or company?")In Excel: =HUNCH.ASK(A2:A1000, "Does this keyword name a specific brand, product, or company?")
A keyword can be transactional and branded at once, "hubspot pricing", or transactional and not, "crm software free trial". The two columns answer different questions, which is why this is a yes/no column and not a fifth option above. Details are on the HUNCH reference.
Real outputs
Nine keywords from a mixed B2B software list, the kind a content calendar has to sort through every month.
| Row | A | B | C |
|---|---|---|---|
| 1 | Keyword | Intent | Branded? |
| 2 | best crm for small business | commercial | 0.05 |
| 3 | hubspot pricing | commercial | 0.97 |
| 4 | how does lead scoring work | informational | 0.04 |
| 5 | buy sheets ai addon | transactional | 0.33 |
| 6 | salesforce vs hubspot | commercial | 0.88 |
| 7 | crm software free trial | transactional | 0.07 |
| 8 | what is a decision maker in sales | informational | 0.03 |
| 9 | notion login | navigational | 0.94 |
| 10 | excel vs google sheets for budgeting | commercial | 0.86 |
Two keywords name a price and still land on commercial rather than transactional, "hubspot pricing" here and "how much does salesforce cost" in the harder set below: checking whether a tool fits a budget reads as comparison, not a ready-to-buy click, in both cases. The branded flag also earns its keep on "buy sheets ai addon", 0.33, well short of a confident yes or no, because sheets alone is a weaker brand signal than a full product name, unlike "download google sheets app" below at 0.93.
Where intent gets fuzzy
Six keywords picked because they carry more than one intent signal at once.
| Row | A | B | C |
|---|---|---|---|
| 1 | Keyword | Intent | Branded? |
| 2 | hubspot free vs paid | commercial | 0.97 |
| 3 | how much does salesforce cost | commercial | 0.98 |
| 4 | crm | informational | 0.17 |
| 5 | download google sheets app | transactional | 0.93 |
| 6 | asana alternatives | informational | 0.11 |
| 7 | is hubspot worth it | commercial | 0.97 |
"crm" on its own reads informational at a low branded score, exactly the thin signal a single ambiguous word should give. The one worth double-checking against your own judgment is "asana alternatives": it comes back informational rather than commercial, which will surprise anyone who treats an alternatives query as comparison shopping by default. If your keyword list leans on that pattern, add "alternatives to a named product" as an example under the commercial option rather than trusting the default reading for that whole class of keyword.
Group the list and pick what to write first
=QUERY(A1:C1000, "select B, count(B) where B is not null group by B order by count(B) desc label count(B) 'keywords'")=FILTER(A2:C1000, B2:B1000="transactional", C2:C1000=0)The second formula pulls non-branded transactional keywords, the ones where nobody has already decided which product to use. That is usually the shortest list and the one worth writing first.
Once the count by intent looks reasonable, cross it with the search volume column your keyword tool already exports: =SUMIFS(D2:D1000, B2:B1000, "commercial", C2:C1000, 0) adds up the volume of every non-branded commercial keyword, a rough size of the opportunity for a comparison page.
Skip navigational and branded keywords for content entirely. Someone searching a competitor's name already made a decision; a page will not change it.
Limits
- It does not check search volume, keyword difficulty, or who currently ranks. Keep those columns from Ahrefs, Semrush, or Search Console and add intent alongside them, not instead of them.
- A single ambiguous word like "crm" carries little intent signal on its own, and the read reflects that rather than forcing a confident pick.
- English keywords score most reliably. Test a sample in another language before trusting a full column.
- It reads one keyword at a time, with no memory of the list around it. Two near-duplicate keywords that mean the same thing to a person can still land on different intents if the wording pulls in different directions.
Copy the template to try both formulas on your own keyword export. The general classification guide covers the HUNCHPICK pattern on any list, and this comparison explains why a probability beats a chat model's generated text for a column like this one.
Questions
How is this different from the intent label in Ahrefs or Semrush?
Can it process a 10,000-row keyword export?
Can I split out question keywords like "how to" separately from informational ones?
Does it work on keyword lists in languages other than English?
Can I add my own fifth intent instead of using other as a catch-all?
Run it on your own column
Copy the template (or use the Excel add-in), paste your key, point the formula at your data. Every formula here has its Excel spelling underneath. 100 rows free to start, then $29 for 5,000 rows. Credits never expire and there is no subscription.
More guides
- Categorize support tickets in a spreadsheet Ticket triage
- Code open-ended survey responses in Google Sheets Survey coding
- Tag customer reviews by topic in Google Sheets Review tagging
- AI formulas for Google Sheets: what returns a number, what returns text AI formulas compared
- Classify cold email replies in Google Sheets Cold email replies