Categorising a large keyword list in bulk takes hours to do, unless you do it a special way. Learn how to categorise thousands of keywords in seconds through the use of a magical Excel formula.
You can categorise anything you want with this formula, it doesn’t only work on keywords. Another popular use of mine is to categorise landing pages, which can significantly help with showing how ranking performance has improved certain landing page’s traffic.
This is an older video, of doing categorisation in Excel. The overall principles are exactly the same, however this will not work in Google Sheets.
But, I have a solution for that. A different formula that works in Google Sheets.
Categorising & Classifying Keywords in Excel
To categorise and classify your keywords in Excel, just like in the video, you can download my Excel template below.
An overview of how graphs in Excel work, and the basic behind customising the look.
This is a great starting point if you’re just getting into SEO, and want to play around with a data set in Excel.
Yes, Google Data Studio and Google Sheets are replacing a lot of this now, but this is still good foundation work.
There are so many things I do with my graphs. Some of these things are smoothing the lines, moving the legend to the bottom instead of the right, and then ofcourse customising the style.
One of the best things to do for a client is match the design of graphs to their brand. This makes it look so much more professional, because you have gone above and beyond. It is so easy to do this, but adds so much value for the client.
The pre-work to this is the pivot table training video here.
=IFERROR([*CELL FOR SEARCH VOLUME*]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(*CTR MODEL TABLE RANK COLUMN*,*CURRENT RANK*),*CTR MODEL TABLE CTR AT RANK COLUMN*),”N/A”)),0)
Removing the bulk of it, we essentially have the search volume, along with a lookup for the CTR at that rank. The lookup could be simplified with a vlookup if you want to, however, I choose to just use my categorisation formula for this.
This can be simplified to just a vlookup for the rank table, especially if you’re using whole rankings rather than GSC average rankings that have a decimal place.
Estimating SEO Traffic in Google Sheets
You can duplicate a Google sheet below which is 95% the same as the Excel above to get you started.
Discover the easiest ways to generate keywords in bulk using Excel. These formulas are the basis of so much work, and keyword generation really will just give you a top-level understanding of the formula’s use.
Concatenate – Join as many cells together as needed
=CONCATENATE(*First CELL to join*,*Second CELL to Join*)
Concatenation is a method that many people are using, and its just adding one cell to another. This can be useful to start things off, but there ampersand (&) does this too, a lot cleaner with a lot more flexibility.
So use this if you’re used to it, but I’d recommend switching to using the below ampersand (&) method.
& – The better way of joining items together
=*CELL to Join*&*CELL to Join*&”Some text to join on the end”
My preferred method, you literally just put an ‘&’ between each cell you want to work with.
So =A2&” “&B2 will output A2, a space, and B2. Nothing really complex about it, giving you full freedom.
Substitute – Swap text in a cell, for other text
=SUBSTITUTE(*CELL for text swap / the template*,*Old text for replacing*,*New text to replace with*)
Giving a little more customizability through template elements, the substitution formula lets you bulk substitute out specific dynamic elements into a keyword template.
So you could have a list of categories, products, locations, or really anything else, and just swap them out in a keyword template.
You can create keywords following a specific rule, and this tends to generate less keywords overall, with a higher percent of them having volume as you know the formats to follow.
Merging keywords with MergeWords
Mergewords offers a simple version of the formulas above, allowing you to just paste in the different parts of the keywords and output the merged keywords.
Ever wanted to see the top-ranking domains / URLs for a particular keyword set?
This analysis will let you do exactly that, and give you more refined performance data, allowing you to dig into the true top performers.
There are a few tools out there that let you see the top competitors and their estimated traffic.
However, many of these tools fail to give good insight into category-specific performance. This holds you back from being able to extra true competitive performance insights, as you’re only seeing the wider picture.
You’re missing out on the drilled-in numbers
Watching the video above, you’ll learn how to build out your own analysis, using a combination of Excel & SerpRobot.
Steps to extract the top-ranking domains
Export SERPs data from SerpRobot
Format the data appropriately in Excel
Extract the domains from the ranking URLs
Run estimated traffic formula based on ranking & search volume
Categorise the keywords into required categories
Pivot the data based on the domains, with slicers/filters for categories
And there you will have it. Your own analysis, with estimated traffic figures at the category level, so that you can get the direct competitor insights you’re after.
Merging Multiple CSV Files Together with Windows Command Prompt (CMD)
Yeap, chances are that if you’re working with a large enough client that you’re contemplating bulk-generating page titles for, you’d be working with a dynamic system. Ideally, you can just give the developers your rules and they’ll build them in and you don’t need to manually generate anything other than some examples.
So, in the less-than-ideal world where you need to manually generate tens, hundreds, or thousands of page titles, here’s a potential solution to get you started in the right direction.
You’ll learn how to;
Randomly pick between title ‘keyword’ templates
Randomly select from CTR-centric templates
Select a shorter fall back if the variables make your title too long
Trim the variation off if the title tag is too long
You’ll essentially be able to bulk spin together unique page titles for each page.
The majority of people won’t ever use this, but, the principles behind it could be applied to many different bulk techniques.
Building out your variables
Initially, you’ll need to get all your data together by listing out the variables of each appropriate page. These are the different dynamic bits that get used in the new page title.
You might have this data already, but if everything you need is in the URL, then you could go ahead and extract it by using the formulas I mentioned in my migration guide to extract what you need.
If you’ve got a nice dataset already, then you can just get started with crafting your titles.
For my demo, I’ll be using a mix of the channel (buy or rent), location, and the property types (apartments, houses, etc).
You’ll see each variable I plan on using in a template is in a separate column, which will allow us to pull it into the page title as needed.
Above each of the variable types, I have a fallback option in case the variables are blank.
So if ‘location’ is blank, use ‘Australia’, and If ‘type’ is blank, we will default to ‘Properties’ for the titles.
Building your page title templates
Next, we’ll need to write out the different variations of page titles. These are what will take your variables into account, and then spit out the page title.
You could just want to use the exact same template for all your titles, which is just a nice simple substitution formula.
But that’s too easy. So, instead, we’re going to randomly select from multiple title elements.
This setup will randomly select from multiple choices of template, and then rotate through a few times if the selected template is going to be too long once the specified variants are pulled in.
Probably a bit confusing right now, but as an example, some locations could be 5 letters, like ‘Perth’.
You could then also run into locations that are rather long like ‘Eggs and Bacon Bay’ (legit place in Tasmania!). So having a single title structure to account for both, or even just the median length, could leave you at a loss by not being able to utilise the full character limit and maximise CTR.
There are plenty of ways SEOs choose to write their page titles, however, I normally stick with a tried and tested format of;
KEYWORD | SOMETHING FOR CTR
Following this format, I’ve split the title generator into two parts.
Keyword part of the page title
We have to write out the variations that will be used at the front of page titles.
For many bulk sites, you would essentially just look at using the exact same template for this depending on the keyword structure.
However, for example sake, I’ve gone with the 3 different variations.
You’ll see I’ve included the 3 potential variables that will be used within a template.
You can include as many, or as little variations here as you’d like, and with my template sheet you can do up to 5.
CTR-centric page title extension
The next part will be the extensions that get added after the keyword, with a primary aim of being for CTR, and maybe an additional keyword drop.
Again, I’ve added 3 variations our templates can use.
However, adding those two together for many locations & property types will create titles that will be way too long for us to use as they’d get truncated.
To combat this, we can add some fallback extension variations.
These are significantly shorter than our initial extensions, and they’ll get used if the Keyword + Extension variations getting generated are too long.
Generating your page titles
The magical formula to randomly select from your page titles is;
It might look a bit confusing, but the substitution formulas are wrapped around the initial formula, and then within the subsitution formulas I have an IF statement that lets me check if the cell is blank essentially.
So if a variable has a length of 1 or more, swap out that variable.
If the given variable has a length of 0, use the default values for that variable that I set.
For me, that will all spit out;
As you can see though, some of my shiny new page titles are too long!
To fix this, I am going to try to regenerate them again, and again, and again until I’m confident the variations can’t produce one that will fall under the length requirement.
Looping through the page title generation
Whilst it’s not exactly ‘looping’ due to the constraints of a formula, it’s pretty close.
Every time time something on the sheet changes, the page titles get regenerated. It can be turned off, but it’s still just easier to paste the initially generated titles in a new column.
We then throw another column on the end of where we just pasted the page titles, and run a new formula over it.
This is exactly the same as the earlier formula, except for an IF statement at the start. This IF just checks the length of the page title that was just pasted to the left.
If the pasted page title matches a requirement (set to be 59 characters or less), then copy that page title over.
If the page title is longer than 59 characters, generate it again!
So for this example, I have pasted my titles from the first generation on the left, and then run the new formula on the right, and you’ll see two more page titles matching the criteria that haven’t previously fallen within the length requirements.
Then we repeat that again;
And again, and again, and again, until there’s a pretty slim chance of hitting a match that will fall under the length requirement.
My example runs through this 7 times, and then it changes on the 8th time.
Using a fallback extension when the titles are too long
Once we’re happy that no more short titles will be generated, we move onto using the shorter fallback extensions.
I’ve included 2 extra variable columns in case you want to use them, allowing you up to 5 different variables. Just paste your data in and follow the same format as the example data.
3. Fill out the variable details
Update your variable names, along with the defaults that should be used if the variable cell is blank.
You can even set the title length limits for the formulas if you want (it won’t change the conditional formatting on the cells though).
4. Fill out the page title template variations
You can include up to 5 of each version, but just make sure you have at least one in each row.
If you don’t want a different extension fallback, just include the same templates as the extension aboe it, and the formula will just strip them off if it’s too long.
5. Click generate!
I built a macro into the sheet that will work through all the columns, and copy all your title data as it goes.
This will save you the need to manually doing anything. Just fill out the template and then this button will do the hard work!
It’s going to ask you for access to the document, but this is just to run the macro itself. That macro is all self-contained, nothing leaves the document.
If you don’t want to give the macro permission to modify the document, just unhide the hidden rows, and then follow the process above of copying your page titles into each ‘pasted’ column. Then just paste the final 10th generation into the first page title column and you’ll be done.
A few different reasons why you’d be stuck trying to generate your titles.
My first scenario for this was to deliver 200 page titles to a client, across all their service pages, and safe to say that I really wish I had something like this back then!
Big shout out to the guys here though, as I got the original formula from them! Have tweaked it a bit, but the core thanks sits with them.
Let me know if everything works okay for you, and whether you have any issues or feedback!
I would love to hear if you’ve found it useful.
Stripping and Rebuilding a URL Structure for Migration Testing
Some clients have recently required a migration mapped out for 10’s to 100’s of thousands of URLs.
To do this, we needed to capture all the different URLs, build out the migration rules, and then test the redirects.
Mapping out and building the rules is one thing, but then how do we bulk generate what the URLs should look post-migration so that we can actually test the redirects?
How can we test the implemented redirect rules, to ensure they match what we originally mapped out?
No one wants to manually create and map 1,000+ URLs to compare the redirect path too, and I personally prefer to not just test the redirects then give them ‘after’ the once over.
I’d like something to compare the implementation to.
Using Google sheets (or Excel) you can strip down the existing URL, extract the parts the redirection rules will use, and then rebuild the existing URL into their new formats.
This will allow you to do a bulk migration test against the implemented redirect rules, and confirm everything redirects where it should.
There are a couple of ways of achieving this, and I will just go through the different methods I use below.
A quick note though, this has nothing to do with building the actual redirection rules themselves. Whilst it might help you build better examples of what you need to redirect, this post is purely about building the expected URLs so you can confirm the redirects match.
Stripping a URL down
There are a few main types of URLs we can account for here.
The standard hierarchical folder-based structure, the flat URL structure with everything to load the page in the base URL, and the ugly query parameter based structure.
They can all be stripped down relatively easily, but firstly we can strip out the URL path from the domain to simplify issue patching later.
Removing the domain from a URL Path
A handful of different ways to strip a domain from a URL are available, but this formula is possibly the easiest;
As an SEO, you really can’t escape Excel. Almost every month a new SEO tool pops up, that can do so much, but will never seem to replace those little things you can do in Excel.
You find yourself using a tool for 75% of the job, but will then always jump back into Excel to finish it off.
Well, that’s me anyway. If it’s not you, then you probably just don’t know many of the things that Excel can do!
I run through VLOOKUPs, IF statements, LEN, TRIM, PROPER / UPPER / LOWER, COUNTIFs & SUMs and show you some basic usage examples. I’ve also added a few extras to the bottom that aren’t in the video!
Basic Excel Formulas for SEOs
Whether you’re starting out, or an experienced SEO, these are the basic building blocks to so many advanced Excel uses for SEO. You need to know these.
VLOOKUP – Find data for a value, within a range
The simplest summary of what a VLOOKUP will do is to match two sets of data where you have something to match them with.
So is long as one column in each data set has some sort of unique identifier, then you can VLOOKUP between the two datasets and bring the data together.
If you don’t have a unique identifier, will you have one if you match two columns together?
You might have a date that isn’t unique, but if you add it to another column (like colour) you will get a unique value that you can use to match the data sets.
=VLOOKUP(*CELL of what you want to search for*,*RANGE of where you want to look for it*,*COLUMN NUMBER the data is in you want to extract*,*TRUE (1) or FALSE (0) but 99% will be FALSE*)
LEN – Count character length of a cell
Nice and simple formula. The LEN formula is great for counting the lengths of page titles and meta descriptions!
Plenty of other uses, but that’s what the majority of people will use it for.
=LEN(*CELL to count*)
TRIM – Remove spaces before & after a cell
Sometimes when trying to match data up, the cell might visually look the same as another cell, but you can’t seem to match it up!
The amount of times I have run into this is crazy, and the majority of times it happens there is a pesky space before or after the text.
The TRIM formula will clean these up for you and make sure there isn’t something hidden there wasting your time!
=TRIM(*CELL to trim*)
SUM – Add all numbers in a range together
Just adds numbers. It’s that simple. Just select the range you’d like to add together and away it goes.
=SUM(*RANGE to add together*)
COUNT – Display a count of cells with numbers in them
Out of your selected range, how many of the cells have a number in them?
=COUNT(*RANGE to count for numbers*)
COUNTA – Display a count of cells with numbers and letters in them (so not empty)
Out of your selected range, how many of the cells have a number of letters in them? This is what you need to use if you are trying to count cells with characters rather than numbers.
=COUNTA(*RANGE to count for numbers*)
COUNTIF – Count all the cells when a specific criterion is met
Only count a cell that matches your criteria. This is great for being able to select cell as your criteria.
So if you have a column of ‘Colour’ and a cell that has the text of ‘Blue’, you could select that ‘Blue’ cell and then the range that has all the colours, and the formula will count how many times ‘Blue’ appears in the range.
=COUNTIF(*RANGE to count for numbers*,*CRITERIA for when to count*)
PROPER – Capitalise the first letter of every word in a cell
Great for cleaning up a list of titles, or any words, where you’d like to capitalise the first letter of every word.
=PROPER(*CELL to capitalise first letters*)
UPPER – Capitalise every letter of every word in a cell
This will format it AND MAKE IT LOOK LIKE YOU’RE SHOUTING. Just use the upper formula when you need to make sure everyone knows you’re SERIOUS.
=UPPER(*CELL to capitalise everything*)
LOWER – Make every letter of every word in a cell lowercase
The lower formula will make everything lower case. Great to run through your keywords with to ensure there isn’t any weird capitalisation going on.
=LOWER(*CELL to lowercase*)
LEFT & RIGHT – Extract x characters from the left or right of a cell
This is a simple way of extracting a certain amount of characters from the left or the right of some text.
Great if you want to cull the end of a title, remove a domain, or trim some trailing slashes from a URL.
=LEFT(*cell reference for text to look in*, *number of characters*)
=RIGHT(*cell reference for text to look in*, *number of characters*)
However, if you are looking trim everything before or after a certain character, instead of a number of characters, check below for a more advanced formula!
IF – Only do something when a certain condition is met
The IF formula lets you create rules for your cells. Maybe you need to filter out titles that are too long.
You could do an IF formula in a new column that says =IF(length>70,”LONG”,”OKAY”). This will give you back a cell that says LONG if the title is too long, or OKAY if everything is okay.
Probably not the best use case, but gives you the idea!
=IF(*The condition to run*,*What to do if it’s true,*What to do if it’s false*)
Does Cell Contains Text – Run condition if the cell contains specific text
A quick formula to use if you want to do something off the back of a cell containing a specific piece of text.
This formula will return TRUE or FALSE, which is what I normally use with this one, but you can expand it do use IF. So IF bla = TRUE, do this.
Great way to quickly segregate data by whether something is in a URL, or keyword.
=ISNUMBER(SEARCH(“text to look for”,*Cell to look in*))
& – Yeap, the ampersand bad boy is rather useful
If you’re trying to merge stuff together in Excel, you need the ampersand. This thing will let you attach anything, to anything else.
It will let you run multiple formulas, then just stick them all together to form what is essentially a sentence.
=”Stick this “&”,with this “&”, and merge all “&2+2&” sentences together”
Which will output:
Stick this, with this, and merge all 4 elements together
SUBSTITUTE – Swap out any text, with any other text
This formula is invaluable if you are doing any sort of text editing or manipulation.
You select the cell you’d like to manipulate, enter the text that currently exists, then enter the text you wish to change it to.
=SUBSTITUTE(*text to look in*,”old text here”,”new text here”)
Trim URL to Domain / Subdomain
This is something that you will find incredibly useful if you’ve never done it before. Some extra tools do it, but you can achieve almost perfect trimming with just a formula – SOOOO much easier!
Quick and Dirty Trim Domain Prefixes Method
If you are just looking to trim the HTTP part, here is a hacky formula that will just substitute out the HTTP part. It’ll leave subdomains / domains, and all their folders intact however.
With A2 being the cell reference to where you have a URL to trim.
Trim URL to Root Domain
If you are wanting to get rid of the subdomain too, so you are just left with the root domain, you can use this formula. It will remove folders, subdomains, prefixes, and anything else so that you are just left with the root domain stripped out.