This article was written by Rob Tindula, Director of SEO at NP Digital.
Early in my SEO career, I put together a keyword research spreadsheet for a client. It had the URL paired with metrics like the primary keyword, search volume, current rank, and more. After showing my client the keyword research spreadsheet, they asked if I could take my research and add other data points like cost per click and any available conversion data.
It was a reasonable request and of course, I said yes.
But I remember thinking in the back of my mind that it would take me some time to complete the task. I would have to sort the data and then painstakingly copy and paste in the additional data points to match the current format of my spreadsheet.
It wasn’t until after I had spent far too much time manually pasting in data that I learned about an Excel formula called a VLOOKUP that could do what had taken me hours, in mere seconds. From that point on, my quest for SEO efficiency had begun.
This brings us to the point of this blog post.
Being good at SEO is one thing, but the ability to do good SEO quickly, efficiently, and at scale is a different story.
Whether you are working on large websites with thousands of pages, manipulating large data sets, or managing a large book of clients with little time, the following tips will allow you to research, analyze, and provide optimizations FASTER. And the faster you can do your SEO, the sooner your clients can implement to see results.
1. Getting more efficient at SEO with Advanced Data Manipulation
While it may seem daunting at first, getting proficient with Excel is the backbone of getting more efficient at SEO.
Note: Although Excel is referenced throughout the article, these tips work in places like Google Sheets as well.
Pairing Metrics From Different SEO Tools
If you couldn’t already tell from the personal experience I shared, I believe this formula is a MUST for any SEO. VLOOKUP stands for “vertical lookup” and is used to search for a specific value in a column. It then retrieves a corresponding value from a different column within the same row. Once you create the formula for one row, you can click and drag it down to populate all the corresponding rows in seconds.
In the example below, I’m using a VLOOKUP to pair data from two different tabs onto one sheet. One sheet has columns for keyword, volume, and KD. The second sheet has columns for keyword, rank, and ranking URL. Using the keyword column as the common data point between the two tabs, my VLOOKUP is marrying the data sets into one.
Whether you are pairing metrics from different SEO tools into the same sheet or even doing QA, this is a formula I use almost every day for optimal SEO efficiency.
Note: In 2020, Microsoft introduced the XLOOKUP which performs the same essential function as a VLOOKUP but also allows the formula to look both left and right of the lookup array for more flexibility with your data sets.
I still primarily use VLOOKUPs, but regardless of which formula you choose, this will be an essential part of your SEO efficiency tool belt.
Easily Pair Keywords Data For Cluster Template
Concatenate is a simple formula that allows you to combine the contents of two or more cells into one. In the example below, I used a quick concatenate formula to easily pair keywords with their MSV (Monthly Search Volume) for pasting into our content cluster template.
From an SEO perspective, it has a wide range of uses. Some of my favorites include:
- Adding “| [BRAND NAME]” to the end of title tags at scale.
- Taking a list of relative URLs and pairing them with the domain name to make them friendly for a tool like screaming frog to crawl.
- Creating stacking concatenate formulas that allow for the templating of things like meta descriptions, title tags, or keyword research at scale.
As you can guess, the possibilities with this formula are only limited by your imagination and the task at hand.
Categorization And Organization Of Keyword Research
A pivot table is a powerful way to organize and summarize rows of data into a succinct table. I personally like to use them for categorization and organization of keyword research. Especially if there are thousands of keywords, a pivot table will allow you to drill down to specific pages or terms and make your analysis that much easier and your SEO more efficient.
It’s also a great way to present your research to clients rather than losing their focus by scrolling through and endless sea of rows in a sheet.
Just take a look at this data set before using a pivot table:
And it doesn’t stop at keyword research! From summarizing how sections of a website are performing by traffic or conversions to grouping new blog content ideas by category, pivot tables are your best friend for making large data sets digestible.
The Power of Data Visualization: Making Relevance Pop
Conditional formatting is another simple tool that has many use cases. The concept behind conditional formatting is very straightforward. Based on your input criteria, a cell will be highlighted in a color of your choice. The criteria can range from text that contains certain characters to cells that have numbers less than, greater than or equal to the number you input.
Some of my favorite use cases for SEO include:
- Flagging keywords or URLs based on certain criteria.
- Pairing conditional formatting with the sort feature to categorize a list keywords into relevant groups.
- Using color scales to more easily identify trends by highlighting low to high or high to low values in a spreadsheet.
Like concatenate, it is a simple feature that can enhance your SEO efficiency across a wide variety of tasks.
2. Speed up Analysis and Reporting With Regular Expressions
A key component of SEO is manipulating and analyzing data. From looking at how certain pages are performing to tracking keyword movements, the ability to segment data for comparison is a common thread that runs through many SEO tasks.
Here’s where regular expressions can be very useful.
A regular expression is a syntax of characters that are used to help identify patterns within a series of text. Regular expressions are very common in most coding languages, but in the context of SEO, they are extremely helpful for filtering keywords or URLs in places like Google Search Console, Google Analytics, or Looker Studio.
A regular expression could look something like this:
The example above is a broad matching expression. When dropped into a place like the Google Search Console query report, this would return all queries that contain variations of those keywords.
An exact match regular expression could look something like this:
Now the expression above will only return exactly those queries and exclude any queries that contain those keywords.
As you can see, getting comfortable with regular expressions can speed up analysis and reporting by allowing you to quickly drill down to a subset of pages or terms.
Now, you can learn regular expressions using something like RegexOne or take a shortcut and use any of the free regex builders that exist across the web. Internally at NPD, we built our own regex builder spreadsheet that takes a lot of the guesswork out of creating quick regular expressions for most common use cases.
Regardless of which route you go, comfort with regular expressions will enhance SEO efficiency while saving so much time in the long run!
3. Identifying Topic Threads for New Content
One of my oldest and most favorite tricks to saving time when doing keyword research for new content ideas is identifying and using topic threads. A topic thread could look something like this:
- How to [verb] [noun]
- The # best [nouns]
What does this look like in practice? Say for example you are working with a company that sells moving boxes. A valid topic for a business in that industry could be something like “How to pack dishes.” Taking this one topic idea, you could write multiple other articles around how to pack any household item like shoes, pictures, computers, clothing, appliances, and the list goes on.
With some smart filtering, you could come up with a list that looks like this:
Another example—you are working with a sports retail website that sells a variety of products from all different brands. A good topic could be “The 10 best basketball shoes for 2023.” Again, taking that topic thread, you could write an article that rates and reviews products from almost every category they sell, such as baseball helmets, tennis rackets, mouthguards, and more. You can even refresh these articles each year moving forward to keep up with the newest products on the market.
Now of course, still do SERP analysis to ensure that these are all truly valid topics and monitor the performance of the first few articles to make sure that they are performing as intended. But as you can see, from just one topic thread, you could have hundreds of relevant articles and all it took was finding a good topic thread and swapping out one keyword.
4. Utilizing AI Tools
When OpenAI released ChatGPT in late 2022, it created a brand-new avenue to increase efficiency across many industries. It didn’t take long for SEOs to realize that many of the tasks we do on a regular basis could be significantly sped up through the prompting of AI.
Whether you are using tools like ChatGPT, Bing’s Discover tool, or others, here a just a handful of tasks that you can streamline with AI:
- Writing metadata
- Generating content ideas
- Improving the readability of complex subjects
- Categorizing keywords
- Identifying cannibalization
- Completing SEO optimizations in different languages
One of my favorite SEO processes to enhance with AI is writing article outlines. As most SEOs know, providing content recommendations is one of the most common tasks in an SEO campaign. Not only that, but we make multiple recommendations each month as part of an ongoing content strategy. Repeatable tasks are where using AI really shines.
So, using a tool like GPT for Sheets, I created a content cluster template that will generate everything from metadata and FAQs to a full article outline based on selected keywords. Here’s what some of the output looks like:
The main keyword used to generate this article is “lookalike audience on facebook.”
Here’s what the prompt for the outline is:
=GPT(“create an article outline using SEO best practices using the text available in”&C4&” “&”and”&” “&H4&” “&”and”&” “&K4&” “&”and”&” “&M4&” “&”and”&” “&N4&” “&”and”&” “&P4&” as context for what to include in the outline. Label headings as <H2> tags “&”and”&” “&P4&” as the H1.”)
Note: Anything like “&H4&” means that I am calling content from another cell (like secondary keywords, title tags, and more) to educate the outline. Formulas that are more detailed and stack like this really improve the output!
In the template I created, once your target keywords are entered into each row, simply click and drag the formulas down, and voilà! You now have full content recommendations that are ready for editing.
It’s important to note that the quality of your prompt highly influences the quality of output that you receive.
Pair these prompts with a tool like GPT for sheets and you are off to the races!
As always with AI tools, they still require a heavy human touch before delivering any work to a client. But if you can use AI to give you a better starting point and shave off even a small percentage of time for each task, that time really adds up over the course of a campaign!
How Can I Make SEO Faster?
You can improve your SEO efficiency by learning Excel formulas and regular expressions to speed up your work. Additionally, identifying topic threads and tapping AI can streamline routine SEO tasks and free up more time.
How Can I Use AI to Increase SEO Efficiency?
AI tools like ChatGPT and Bing’s Discover tool can streamline various simple SEO tasks including writing metadata, generating content ideas, producing outlines, improving readability, categorizing keywords, and more.
Enhance Your SEO Efficiency Today
I’m sure this list of tips may seem elementary to some and to others, process changing. At the very least, hopefully they made you think. There is almost always a better way to do something.
After running through this list of SEO efficiency tips, I think back to my younger self and wonder how much time I probably wasted by doing the SEO basics without some of these skills. I wish I knew then what I know now.
Even today, I’m sure there are many ways to improve efficiency that haven’t even crossed my mind.
So, in the spirit of improvement, let’s hear your tips!
What other tricks do you like to use to up your efficiency game? Please drop them in the comments below.
About the Author: Rob is the Director of SEO at NP Digital and has over 10 years of experience optimizing websites for organic search. He has worked on websites of all sizes, ranging from local businesses to large enterprises. With a solid foundation of technical SEO, content strategy, and analytics, Rob has produced results across multiple industries, including B2B, e-commerce, education, and SaaS clients. He also has a passion for implementing processes and training to help the team improve their SEO skills.
See How My Agency Can Drive More Traffic to Your Website
- SEO - unlock more SEO traffic. See real results.
- Content Marketing - our team creates epic content that will get shared, get links, and attract traffic.
- Paid Media - effective paid strategies with clear ROI.
Are You Using Google Ads? Try Our FREE Ads Grader!
Stop wasting money and unlock the hidden potential of your advertising.
- Discover the power of intentional advertising.
- Reach your ideal target audience.
- Maximize ad spend efficiency.