Whenever you are doing keyword research or analysing content, you will likely work with a large dataset of keywords or URLs, and accompanying data points. In this context, categorising and cataloging this data would be a great way to better understand your dataset, quicker.
In this guide (which will be frequently updated with new, relevant formulas), I’ll show you different Google Sheets formulas to help you improve your keyword universe organisation and understanding or content performance analysis datasets. Here’s what I’ll cover:
Google Sheets Formulas to help with SEO Keyword Research – Search Intent Classification
In my article on Custom Dimension Formulas in Google Looker Studio , I’ve shared a pretty robust version of a search intent classification formula. In the following few subsections, I will show you how that might look like as Google Sheets formulas. This is the basic formula template we are using:
=ifna(ifs(REGEXMATCH(C2, "{enter keywords}"), "Informational", REGEXMATCH(C2, "{enter keywords}"), "Transactional",REGEXMATCH(C2, "{enter keywords}"), "Commercial"), "Unclassified")If you want to add more intent buckets, or have only one field, where all checks are applied, and a single label is returned, you simply add a new layer of regex to the formula, like this:
=ifna(ifs(REGEXMATCH(C2, "{enter keywords}"), "Informational", REGEXMATCH(C2, "{enter keywords}"), "Transactional",REGEXMATCH(C2, "{enter keywords}"), "Commercial"),REGEXMATCH(C2, "{enter keywords}"), "Localized"),REGEXMATCH(C2, "{enter keywords}"), "Navigational") "Unclassified")I encourage you to also familiarise yourself with my linked resources, if you want to learn more about how to do search intent classification better, or international search intent classification or in different industries. I also recommend following the content that Veruska Anconitano publishes on search intent, and specifically on implicit search intent.
How to label keywords for informational search intent in Google Sheets
This category is for queries where the user is looking for information. Keywords like “how,” “why,” “does,” “which,” “when,” “who,” “guide,” “tutorial,” “learn,” “examples,” “resource,” “ideas,” and “tips” indicate that the user is seeking knowledge or understanding.
This revised list focuses on keywords that are widely applicable for informational search intent, helping to ensure that content is categorized accurately when these terms are present. It maintains a broad coverage of terms related to learning, problem-solving, decision-making, and understanding concepts, which are core aspects of informational intent.
=IF(REGEXMATCH(C2, "how|why|does|which|when|who|guide|tutorial|learn|examples|resource|ideas|tips|difference between|strategies|ways|example|directory|news|basics|concepts|troubleshoot|resolve|risks|beginners|upgrade|improve|optimize|case study|best practices|templates|pros and cons|analysis|articles|audit|benefits|blogs|build|calculate|campaign|choose|common questions|consider|cost|create|definition|definitive guide|earn|easy steps|essential|events|explainer video|factors|find|fix|grow|hacks|how to|ideal|importance of|increase|infographic|information|meaning|measure|metrics|mistakes|myths|opportunities|popular|practices|questions|remove|set up|shortcuts|stats|strategy|study|successful|tactics|techniques|terms|things to know|time|trends|tricks|types|updates|well explained|tutorials|framework|checklist|process|plan|test|glossary|sample|survey|exercises|report|certification|webinar|calendar|quiz|directories|to do list|design basics"), "Informational Intent", "Other")How to label keywords for commercial search intent in Google Sheets
This category is for queries indicating the user is in the consideration phase, looking for options, reviews, or comparisons. Keywords like “best,” “top,” “vs,” “review,” “comparison,” and including “cheap” again, indicate commercial intent. Although “cheap” appears in both Transactional and Commercial categories, its context in a search query could lean towards either, depending on other words it’s combined with.
For commercial intent, keywords are chosen to identify when users are researching products or services before making a purchase decision. This intent captures users comparing features, prices, or brands, seeking reviews, or looking for the best options available. The expanded list includes terms like “ratings,” “buyer’s guide,” and “pros and cons,” aiming to encompass all stages of the pre-purchase research process. By covering a wide range of research-related activities, the list helps in accurately categorizing queries where the user’s primary goal is gathering information to make an informed purchase decision.
=IF(REGEXMATCH(C2, "best|top|vs|review|compare|comparison|ratings|recommendation|buyer's guide|alternative*|feature*|benefit*|cost-effective|affordable|premium|quality|deal|offers|savings|discount|value|choice|selection|rankings|choice|user ratings|testimonials|feedback|pros|cons|evaluation|assessment|analysis|benchmark|brand|models|versions|upgrade|update|launch|new|latest|innovative|technology|solutions|providers|vendors|suppliers|applications|software|platform|systems|tools|equipment|machinery|accessories|devices|gadgets|electronics|appliances|products|materials|suppli*|kit|package|bundle"), "Commercial Intent", "Other")How to label keywords for transactional search intent in Google Sheets
This category includes queries where the user’s intent is to perform a transaction, which could be purchasing a product or signing up for a service. Keywords such as “buy,” “price,” “cheap,” “expensive,” “recommendation,” “recommended,” “near me,” “firm,” “coupon,” “order,” “purchase,” and “pricing” suggest this intent.
The selection of transactional intent keywords focuses on pinpointing moments when a user is ready to make a purchase or is inquiring about the purchase process. Keywords such as “buy,” “quote,” “for sale,” and “checkout” directly indicate the user’s readiness to engage in a transaction. This intent is crucial for identifying users at the final stage of the buying funnel, capturing a wide array of transaction-related terms to ensure comprehensive coverage of potential purchase-related searches. The expanded list aims to cater to various transaction scenarios, from online shopping to inquiring about services.
=IF(REGEXMATCH(C2, "buy|purchase|order|price|quote|for sale|sale|deal|offers|discount|coupon|promo code|voucher|bargain|cost|pricing|cheap|affordable|expensive|premium|near me|online|e-commerce|shopping cart|checkout|shop|store|retailer|dealer|supplier|marketplace|auction|bid|buy now|pay|payment|finance|leasing|rent|rental|subscribe|subscription|free trial|book|reserve|enquire|inquiry|quote|estimate|cost calculator|shipping|delivery|available|in stock|out of stock|pre-order|backorder|special offer|limited time offer|flash sale|clearance|wholesale|bulk buy|group buy|membership|reward|loyalty|gift card|cashback|trade-in|upgrade|renew|checkout|secure checkout"), "Transactional Intent", "Other")How to label keywords for Localised intent in Google Sheets
Label keywords for US state to identify localised intent
For searches that demonstrate a desire to find information or services specific to a location in the United States, you can create a formula to detect “Localised Intent – US” by incorporating the full names of all 50 states. This allows you to categorize queries that include state names, indicating the user is looking for something within that specific state.
=IF(REGEXMATCH(C2, "Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New Hampshire|New Jersey|New Mexico|New York|North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode Island|South Carolina|South Dakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West Virginia|Wisconsin|Wyoming"), "Localised Intent - US", "Other")Label keywords for UK city to identify localised intent
To detect “Localised Intent – UK,” focus on incorporating major city names. This formula helps identify searches that are specific to locations within the United Kingdom, suggesting that the user’s intent is tied to finding local information, products, or services.
=IF(REGEXMATCH(C2, "London|Edinburgh|Manchester|Birmingham|Glasgow|Liverpool|Bristol|Newcastle|Leeds|Belfast|Cardiff|Sheffield|Nottingham|Leicester|Southampton|Brighton|Plymouth|Stoke-on-Trent|Wolverhampton|Derby"), "Localised Intent - UK", "Other")Improve this list by adding all UK cities. You can get this from here: List of cities (HTML) – GOV.UK , or any other csv/downloadable list of all cities. Concatenate cities with a “|” to help build the formula.
Navigational intent refers to searches made with the aim of finding a specific website or page online. Users with navigational intent typically already know where they want to go and use search engines to get there directly. To detect navigational intent, include common terms associated with website navigation, as well as specific domain names, if applicable to your context.
=IF(REGEXMATCH(C2, "login|homepage|website|www\.|\.com|\.org|\.net|\.edu|\.gov|\.uk|\.co\.uk|facebook|youtube|gmail|instagram|twitter|wikipedia"), "Navigational Intent", "Other")This can also be improved by adding your brand names and variations, or adding your competitor brand names.
How to SERP features as search intent signals in Google Sheets
Starting from the SERP features that are returned by Semrush (you can find the equivalent documentation for Ahrefs, or the keyword research tool you are using), you can create a formula to analyse the SERP features column from your export, and classify the search results based on the features that Google is providing.
To categorize the SERP feature names based on user intent and create a formula that returns all possible labels for a keyword, we can divide the features into several intent categories. For instance:
- Informational Intent – SERP features like Featured snippet, People also ask, Instant answer
- Navigational Intent – SERP features like Site links, KnowledgeGraph
- Transactional Intent – SERP features like Local pack, Shopping ads, Local teaser pack, Shopping results
- Commercial intent – SERP features like Top ads, Bottom ads
- Engagement/Entertainment intent – SERP features like Reviews, Video, Top stories, Images, Twitter, Featured video
=JOIN(", ", IF(REGEXMATCH(J2, "Featured snippet|People also ask|Instant answer|Knowledge panel|Carousel|Thumbnail|Video preview"), "Informational Intent", ""),
IF(REGEXMATCH(J2, "Sitelinks|Knowledge Graph"), "Navigational Intent", ""),
IF(REGEXMATCH(J2, "Local pack|Shopping ads|Local teaser pack|Shopping results"), "Transactional Intent", ""),
IF(REGEXMATCH(J2, "Top ads|Bottom ads|AdWords top|AdWords bottom"), "Commercial Intent", ""),
IF(REGEXMATCH(J2, "Reviews|Video|Top stories|Images|Twitter|Featured video|Image pack"), "Engagement/Entertainment Intent", ""))This alone would not be exhaustive or very helpful for assigning intent, but combined with the previous classification (based on keywords), can be very helpful.
Note that in the formula above, cell J2 refers to the SERP features field, from your keywords export from your preferred tool.
Google Sheets Formulas to help with SEO Keyword Research – Opportunity mapping and scope
In this section, I’ll share a few other formulas I use in Google Sheets to help organize your keyword universe a bit better with keyword labels.
How to split keywords based on brand type (branded versus non-branded)
Here’s a formula for a custom brand label field in Google Sheets. It evaluates the content of cell to determine whether it matches any of a list of specified patterns—these could be the name of a brand, variations and misspellings of that brand name, or names and variations of competitor brands.
=if(REGEXMATCH(C2, "{brand name}|{brand name variations and misspellings}|{competitor brand names and variations}"), "Branded", "Non-branded")How to identify prominent content types Google is featuring based on the SERP features
You can also use the SERP features to get a rough overview of the type of features that Google is showing in the SERP. This can help guide the content you need to create to have a better ranking chance for the user queries you aim to appear for. Here’s a possible split of SERP features, indicating different content types:
- Videos: Grouping “Video” and “Featured video” to highlight video content.
- Images: Including “Images”, “Image pack”, and “Thumbnail” to capture image-related features.
- Ads: Consolidating all ad-related features such as “Top ads”, “Bottom ads”, “AdWords top”, “AdWords bottom”, and “Shopping ads”.
- Informational Features: Features like “Featured snippet”, “People also ask”, “Instant answer”, “Knowledge panel”, “Carousel”, and “Video preview” that provide direct information.
- Navigational Features: “Site links” and “Knowledge Graph” are aimed at helping users navigate to specific sites or get concise information quickly.
- Local Features: Focused on local search results like “Local pack”, “Local teaser pack”, and “Shopping results”.
- Social/News Features: Categories such as “Reviews”, “Top stories”, and “Twitter” represent content that is either user-generated or relates to current news and social media.
And here’s how the formula for doing that in Google Sheets might look like:
=JOIN(", ",
IF(REGEXMATCH(J2, "Video|Featured video"), "Videos", ""),
IF(REGEXMATCH(J2, "Images|Image pack|Thumbnail"), "Images", ""),
IF(REGEXMATCH(J2, "Top ads|Bottom ads|AdWords top|AdWords bottom|Shopping ads"), "Ads", ""),
IF(REGEXMATCH(J2, "Featured snippet|People also ask|Instant answer|Knowledge panel|Carousel|Video preview"), "Informational Features", ""),
IF(REGEXMATCH(J2, "Site links|Knowledge Graph"), "Navigational Features", ""),
IF(REGEXMATCH(J2, "Local pack|Local teaser pack|Shopping results"), "Local Features", ""),
IF(REGEXMATCH(J2, "Reviews|Top stories|Twitter"), "Social/News Features", ""))
Google Sheets Formulas to help with SEO Content Analysis
How to classify pages per site section from a URL In Google Sheets
When managing a large website, especially one run by different teams each overseeing separate segments, it’s crucial to dissect the data by page sections. This division becomes particularly handy for pinpointing technical issues, like those affecting Core Web Vitals performance, which might stem from site-wide images or specific section templates.
In Google Sheets, creating a formula to categorize page URLs by their sections can greatly aid in this analysis. Here’s how you can craft a formula to classify URLs into different categories, such as blog posts, white papers, or branded pages. This method can be especially useful in identifying and addressing website performance issues.
=IF(REGEXMATCH(A2, "blog"), "Blog Posts",
IF(REGEXMATCH(A2, "white-papers"), "White papers",
IF(REGEXMATCH(A2, "news"), "News",
IF(REGEXMATCH(A2, "case-studies"), "Case studies",
IF(REGEXMATCH(A2, "insights"), "Insights",
IF(REGEXMATCH(A2, "careers|about-us|contact|job-opportunities|clients|reviews|our-work|our-team|join-team"), "Branded Pages",
IF(REGEXMATCH(A2, "hospitality-travel-tourism|expertise|economic-development|energy|higher-education|technology|financial-services"), "Services Pages",
IF(REGEXMATCH(A2, "tag"), "Tag Pages",
IF(REGEXMATCH(A2, "/page"), "Paginated",
"Unclassified")))))))))This formula uses Google Sheets’ REGEXMATCH and ARRAYFORMULA functions to categorize each URL based on its content, providing a segmented view of your website’s structure. This segmentation can be pivotal for performance analysis or SEO optimization, giving you a detailed breakdown of your site’s various sections alongside other metrics you choose to monitor, such as clicks, impressions, or sessions.
How to label URLs based on subdirectory language In Google Sheets
When handling an international website with pages in various languages, it’s crucial to analyze performance data separately for each language version. To facilitate this in Google Sheets, you can create a formula that categorizes URLs by language based on their path.
The aim is to provide user-friendly labels for each language version of the URLs. This enables users to filter the data accordingly and view performance metrics—such as clicks, impressions, or sessions—for each language separately, aiding in targeted analysis and decision-making.
Below is how you can adapt the provided formula for use in Google Sheets, assuming the URL or page address is in cell A2 (of course, you can add as many languages as you wish to the formula, based on your knowledge of the website):
=IF(REGEXMATCH(A2, "/es/"), "Spanish",
IF(REGEXMATCH(A2, "/fr/"), "French",
IF(REGEXMATCH(A2, "/de/"), "German",
IF(REGEXMATCH(A2, "/it/"), "Italian",
"English"))))This formula checks the content of cell A2 for specific substrings that indicate the language of the page (e.g., “/es/” for Spanish). It assigns a label based on the first match found. If no language-specific substring is detected, it defaults to labelling the URL as “English.”
How to classify keyword rankings per opportunity bucket in Google Sheets
This method allows for a comprehensive view of ranking performance across different tiers of keyword positions, from “money” keywords that rank in the top positions to those that might require significant improvements. Here’s a consolidated Google Sheets formula that categorizes keyword positions into distinct buckets, based on opportunity and action needed:
=IF(AND(A2>=1, A2<=3), "Money Keywords - Protect (1-3 positions)",
IF(AND(A2>=4, A2<=10), "High-Potential Keywords - Move to top (4-10 positions)",
IF(AND(A2>=11, A2<=20), "Striking Distance Keywords - Prioritise improvements (10-20 positions)",
IF(AND(A2>=21, A2<=50), "Need Improvements (20-50 positions)",
IF(AND(A2>=51, A2<=100), "Low Ranked - Heavy content edits needed (50-100 positions)",
"Not ranked")))))Key Points:
- Replace
A2with the cell that contains the query’s rank - This formula applies a tiered approach to categorize rankings, providing labels that reflect each keyword’s performance level.
- It’s designed for easy integration into Google Sheets, enabling direct application to datasets derived from various ranking tools or the Google Search Console.
This approach simplifies the classification of keyword rankings into actionable categories. Adapt the formula to the specific ranges you deem relevant for your analysis or reporting needs.
By categorizing keywords into these buckets, you can quickly assess the distribution of your site’s rankings, identify areas of strength, and pinpoint opportunities for optimization.
While primarily for classification, the output of this formula can also be used to enhance data visualization in Google Sheets, facilitating a clearer understanding of ranking distributions across your website’s keywords.
How to extract the brand name from a URL in Google Sheets
When conducting large-scale competitor analysis or evaluating search engine results page (SERP) rankings, a crucial step is simplifying your data for better insights. A common method involves distilling URLs to their respective brand names to assess the Share of Voice among competitors effectively.
To accomplish this in Google Sheets, you can utilize a formula to extract the brand name from a given URL. This process involves stripping the protocol (http or https) and www prefix, then isolating the brand name from the URL.
Assuming the URL is located in cell A2, here’s how you can adapt the formula for Google Sheets:
=TRIM(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2, "https?://", ""), "^www\.", ""), "([^\.]+)"))This formula works as follows:
REGEXREPLACE(A2, "https?://", ""): Removes thehttp://orhttps://part of the URL.REGEXREPLACE(..., "^www\.", ""): Removes thewww.part, if present.REGEXEXTRACT(..., "([^\.]+)"): Extracts the brand name, which is typically the first segment of the URL following thewww.This is done by capturing characters until the first dot.
The result is a trimmed version of the URL, effectively leaving you with the brand name, which you can then use for further analysis, such as visualizing the Share of Voice per competitor in your dataset, or even bucketing the number of pages ranked per competitor for your keyword set.
Want to contribute a formula to this list?
If you want to contribute a formula to this list, simply message me on Twitter or LinkedIn, and I’ll add it in, as a contribution with the appropriate accreditation.
