fbpx

CASE STUDY 11

Google Sheets - SEO Edition

Mastering Keyword Categorization with Google Sheets

Mastering Keyword Categorization with Google Sheets

Understanding the search intent behind a user's keywords is crucial for effective SEO. Categorizing keywords allows you to tailor your content to the right audience at the right stage of their journey. While many SEO tools offer basic keyword categorization, Google Sheets allows for a far more customized and powerful approach. In this guide, we'll dive into advanced formulas to streamline your keyword categorization process.

Why Keyword Categorization Matters for SEO

  • Content Alignment: Knowing whether a keyword is informational, transactional, or navigational helps you create content that matches the searcher's needs.
  • Gap Analysis: Identify areas where you lack content for specific stages of the buyer's journey.
  • Prioritization: Focus your efforts on keywords that align with your business goals and are most likely to convert.
 

The Formulas You'll Need

 

A Simple Example to Get Started

Let's start with a small set of keywords:
  • buy vegan protein powder
  • protein powder reviews
  • how to make protein shakes
  • whey protein vs casein

Using REGEXMATCH and IFS, we can assign categories based on predefined logic.


In our case, the CATEGORY column is automatically calculated based on the following logic:
  • Transactional: if the keyword contains any of the words: buy, purchase, shop
  • Comparison: if the keyword contains any of the words: review, comparison, vs
  • Informational: if the keyword contains any of the words: how to, what is, guide
  • Other: all the other keywords do not match any of the previous criteria

This makes our formula look like this:
 

Let's break the formula into pieces

IFS Function: This is like a series of "IF" questions the formula asks. =IFS(...) means "If the first condition is true, do this. Otherwise, check the next condition, and so on." REGEXMATCH Function: REGEXMATCH(A2, "buy|purchase|shop"): This is where the magic happens!
  • REGEXMATCH(...) searches for patterns within text.
  • A2 means it looks at whatever is in cell A2 (your keyword).
  • "buy|purchase|shop" is the pattern it checks for. The "|" acts like an "or" - if ANY of those words are found, it's true.
  • "Transactional", "Comparison", "Informational": These are the categories your keyword gets sorted into:
    • Transactional: Words like "buy" signal someone wants to purchase something.
    • Comparison: Words like "review" mean they are comparing products
    • Informational: "How to" suggests they're looking for information, not to buy.
    • TRUE, "Other": This acts like a safety net. If a keyword doesn't fit the earlier patterns, it gets labeled "Other" for you to review.

How it works?

If the keyword is "protein powder review", the formula does this:
  • Checks for "buy", "purchase", or "shop": Doesn't find those words.
  • Checks for "review", "comparison", or "vs": It finds "review"!
  • So the keyword gets categorized as "Comparison".
 

Best Practices

  • Refine over time: Your categories may evolve as you understand your audience better.
  • Don't rely solely on tools: Use categorization to inform your keyword research and content strategy, not replace it.
  • Additional uses: Categorize log file data, customer questions, etc. for broader insights
Looking for a more complex version that will consider a dynamic list of classifications? Contact me for your personalized file.

Registered members have FULL ACCESS to the content. REGISTERING IS FREE!