Google Sheets Formulas for SEO Keyword Research and Content Analysis (Copyable, Tested)

Not every keyword project needs Python. A lot of the labelling I do on a keyword export can happen right in Google Sheets with a few well-built formulas, and that’s where most marketers already work. This free set collects my most-used formulas for keyword research and content analysis, refreshed for 2026 and checked by an automated test script.

What’s included

• Search intent labels: informational, commercial, transactional, navigational and local (US states, UK cities, or a cities list kept in another tab), as single labels or every matching label.
• SERP features as intent signals, and which content types Google is featuring, from the SERP features column in a Semrush or Ahrefs export (AI Overview included).
• Branded splits: own brand, competitor brand and non-branded.
• URL formulas: site section from the URL (or from a rules tab with XLOOKUP), language folder, and the brand from a URL, which handles subdomains and suffixes such as co.uk.
• Rank buckets that handle decimal Search Console positions.
• Rules tabs for intent and site sections, plus seven sample keywords to try everything on.

Who it’s for

Spreadsheet-first SEOs and content marketers who want consistent labels on a keyword list without opening Colab.

How to use it

1. Open FORMULAS.md on GitHub. Each formula lists the cells it expects: C2 for the keyword, J2 for SERP features, A2 for the URL or position.
2. Import rules_intent.csv and rules_sections.csv into new tabs named Rules and Sections if you want the table-driven versions.
3. Paste a formula into the first row of an empty column, or use the BYROW or MAP versions to fill the whole column from one cell.
4. Swap in your own brand and competitor names, and add words that matter in your niche.
5. Save the ones you reuse as Named functions (Data > Named functions) so your team can type something like =INTENT(C2).

What changed from the 2024 versions

The old intent lists were case-sensitive and matched parts of words (“show” matched “how”, “laptop” matched “top”). The new ones use (?i)\b...\b regex for whole words in any case. Rank buckets no longer let a position of 3.4 fall through the gaps, and empty labels no longer leave stray commas.

Limits

• Rule-based labels are a quick first pass. Words like “cost” or “deal” sit in more than one intent list on purpose.
• The language formula reads any two-letter folder as a language, so exclude folders such as /us/ or /ai/.
• Free, and no add-ons needed.

For labels that go beyond rules, read Search-Intent Classification with Machine Learning, and when you’re ready to try ML, start with the Introduction to Machine Learning for SEO course.