The English language is the arrangement of a set of governing letters that when combined into well-formed words and sentences yield meaning. In the world of technology or computer programming, the same concept is applied, only keywords and phrases are more commonly referred to as strings. Google does a great job at identifying meaning amongst these strings and associates them with what they deem to be an appropriate ranking URL in search results. For years now, I’ve been experimenting with some of the best ways to efficiently conduct keyword research and analysis during the early discovery phases of client contracts, all the while keeping syntax in mind. And although Google does a fairly good job at understanding the less than experienced search engine user, it’s still valuable to have a process in place for identifying key phrases or strings of words that are more competitive than others—these are the ones we want to target from an SEO perspective.
How Does it All Typically Begin?
So what’s a standard process for identifying competitive keywords specific to a brand’s target market? Well, the first place you should look is within your analytics platform. Provided you have access to your client’s analytics account (which you should if you don’t), there’s a generous amount of critical information about organic search traffic, including elements of user behavior such as which keywords visitors are searching and where they’re entering your site upon doing so. Combined with insights from Google’s proprietary “Keyword Planner,” (formerly known as Adwords Keyword Tool) you should be on track to success. But compiling every keyword in the universe that’s remotely related to your practice, product, or service and slapping it down in a spreadsheet isn’t the best way to track and measure ranking success. You first need to realize that not all keywords suggested in Keyword Planner’s keyword/ad group idea tool will imply new and qualified visitors. And in platforms like Google Analytics, you need to realize that not only is a segment of traffic unaccounted for due to ‘not provided,’ but also that most of the traffic you’ll see is from users who are already brand-aware—visitors searching for your brand, product name or service.
Even with raw data from Google about keyword volume or insight from analytics, there’s still somewhat of an inefficiency in discovery and analysis. For years, I relied heavily on both sources and spent a lot of time manufacturing spreadsheets with extensive amounts of data, ranging from local/global monthly keyword volume to Cost Per Click information. Time and again, my tireless efforts to provide a clear path to success paid off. However, I knew there was a better way of doing things, and I knew Excel had the capabilities that I needed hidden somewhere deep within.
And Then I Met The Formula of My Dreams
With a little help from reputation management expert, Joseph Torrillo, it wasn’t until a couple years ago that I discovered the concatenate formula (=concatenate()) in Microsoft Excel; and what a gift it was. The concatenate formula helps to combine words or phrases, or in the case of the technologically savvy, strings. So why was my encounter of this formula so helpful?
Well, for two major reasons really: efficiency and organization. For instance, a car company typically provides customers with the option to lease or finance. In essence, they’re trying to reach two target markets: buyers and renters. Provided there’s a master list of every car in their inventory, research is limited to identifying major keyword modifiers. These modifiers include phrases like “for sale,” or “lease,” and they make up a significant part of long-tail search where conversion rates are high. You can think of long tail modifiers as accessories—there are an endless number of them, and they help businesses profit greatly due to demand and ridiculous markup! Segmenting out keywords based on these two very different markets becomes easy once modifiers have been identified.
Concatenate in Practice
Simply create a column for each separate modifier using the concatenate formula =concatenate(). In quotations, you can indicate which words or phrases you’d wish to combine. The end result would look something like this: concatenate(A2,” for sale“). A2 would indicate the type of car, and “for sale” is the common modifier. Notice the extra space in the second string, just before the phrase “for sale”—this is here so that when the formula combines both strings, they actually form a phrase, and not an inaccurate and non-sensical mash up. You can simply click-n-drag your new concatenate column down to combine column A with its modifier. Take a look at the screenshot below to see what I mean.
Here, I actually have header cells that represent each modifier. Accordingly, I’ve designated which cells I wanted to combine. In this case, cells represent the appropriate keywords. Again, remember to add an extra space before or after your keyword (in your keyword cell) so that your desired keyphrases make sense upon combining keywords. Also, remember to anchor your modifier cells with dollar signs—This keeps your new cells relative and keeps your click-n-drag from going haywire.
Once you’ve compiled all your keywords into a master sheet using the concatenate formula, simply plug in the data in Keyword Planner and voila; you have everything from competition ratings to raw numbers. You can then sort accordingly.
But Google, I Was Doing it First!
This formula allows a better framework for incorporating and sorting excel data based on keyword volume. And the most intriguing part of all of this is that Google’s Keyword Planner now allows users to concatenate, or as they call it “multiply” keywords to ease in the process of planning your next campaign. Featured below is a screenshot showing how Keyword Planner’s “multiply” feature works, paralleling the Excel functionality demonstrated above.
If you need to reverse the order of things, I suggest doing an additional multiply list that will combine strings where modifiers come first (i.e. used Dodge Dart). Like concatenate, this functionality increases the rate at which keyword research can be conducted and alleviates a lot of the time and stress that comes with sorting through vast amounts of irrelevant and noisy keyword data.
One of the most time-consuming and arduous parts of SEO is research and discovery– keyword analysis being a highly substantial component of both. And it should be for the following reason: Keywords drive traffic, and the most competitive and qualified keywords or phrases will always provide the most long-term ROI. Forgetting this basic principle can get you into a lot of trouble and potentially cost you time and money. Using the concatenate formula or Keyword Planner’s “Multiply keyword lists” feature can help you avoid losing both, but only if you take the time to properly analyze some of the more popularly used and competitive modifiers that help make up the long tail. So next time you’re conducting a thorough keyword analysis to help you track and measure success, let the basics of formal language theory help you combine words into action.