Working in SEO, I always find myself poring over data and looking for ways to expedite the analysis process. Analyzing data can often be tedious, mind-numbing, and boring work, so anything that can be done to speed up finding that needle in the haystack is almost always a good idea. A few months ago, I began using a formula in Excel to categorize data and I’m constantly finding new ways to use it.
It took a little bit of time and practice to remember the formula, to understand how it works and how to troubleshoot it if it breaks, but the time and energy put into learning it have been dwarfed by the rewards I’ve seen from employing it successfully. If you take the time to learn this formula, Ipromise that it will be worth it — you’ll easily be able to cut down thousands (or more) of rows in Excel into bite-sized chunks for easy insight-pulling and data presentation.
Without further ado, I present to you:
=if(isnumber(search(“string 1”, [beginning cell])),”Category 1”, if(isnumber(search(“string 2”, [beginning cell])),”Category 2”, “Other”)
I apologize if I’ve confused you already. I’ll dive into the formula deeper, explaining its meaning and providing 3 different use cases for how it can help you speed up your work.
Use Case #1: Keyword research
When I’m doing keyword research for a client and I’m staring down a list (likely thousands of rows long) of potential keywords to analyze and their search volumes, I try to lump similar ones together to see patterns of similarity. At Distilled (we’re hiring, btw!), I might use a tool like Brightedge or SEMrush to see the queries a website has visibility for. Additionally, I could just put a topic into Google Keyword Planner and receive an output of similar terms per Google. Export your results in a CSV file and you’ll have your starting point for data analysis. You might even wonder how the formula I mentioned before could even be useful because Google Keyword Planner provides an “Ad Group” column, so one should easily be able to know how to divide up the provided keywords.
Problem is, the output is often divided up between “Seed Keywords” and “Keyword Ideas”, neither of which is helpful for segmenting keyword cohorts. The screenshot above captures the queries and search volumes around related terms for “workout supplements” (note the “Seed Keyword” in cell A2 compared to all others.)
But what if I want to break down this entire list (681 queries, obviously all not shown in the screenshot) to find out how many queries include the word “supplement?” Or perhaps I want to know how many contain “muscle”; I can do that too.
Read full article here: https://moz.com/blog/one-formula-seo-data-analysis-made-easy-excel