Author: Sam Partland

Programmatic SEO: How Enterprise & Larger-scale Websites Dynamically Scale Their SEO

Programmatic SEO: How Enterprise & Larger-scale Websites Dynamically Scale Their SEO

During my time as an SEO, I have slowly but surely honed in on my specialty – Programmatic/Dynamic SEO.

It’s something I enjoy more so than regular SEO, because the smallest of change can have such a vast impact due to the sheer volume of pages being modified.

However, I’ve seen a large gap in many SEOs understanding of what dynamic SEO is, how it works, and most importantly – how to think about it.

You have to go through a different thought process when compared to normal SEO, but I’ll get into that shortly.

So let’s run through what is it, how to think about it, dynamic strategies, and a handful of examples for some inspiration.

 

What is dynamic SEO?

Dynamic SEO at its core is bulk optimising a website using variables/templated elements across many pages once. One change affects thousands of pages. These variables are stored in a database somewhere, that could be extended out to create the new pages.

You could be building out the system, or just optimising an existing system.

With ‘normal’ SEO, you make one change and it will affect a single page.

Dynamic SEO refers to making a single change and having it affect hundreds, thousands, or even hundreds of thousands of pages.

Imagine a car hire website, with hundreds of locations available to rent their cars from. You could have a page title of;

Car hire <location> – Deals on rental cars in <location>

Then every location page will dynamically insert their location and you’ll get;

Car hire Melbourne – Deals on rental cars in Melbourne

Car hire Sydney – Deals on rental cars in Sydney

Car hire Brisbane – Deals on rental cars in Brisbane

But then maybe you want the combinations of vehicle type in there too, you’d look at a title like;

<vehicleType> hire <location> – Deals on rental <vehicleTypePlural> in <location>

Which would give you;

Car hire Melbourne – Deals on rental cars in Melbourne

Truck hire Melbourne – Deals on rental trucks in Melbourne

Van hire Melbourne – Deals on rental vans in Melbourne

4WD hire Melbourne – Deals on rental 4WDs in Melbourne

With this being replicated across all locations & vehicle types in the database.

Another good example is a real estate website.

Imagine all the different search result pages available when looking for a property.

There are two key elements in there that should almost always be optimised for – Channel, Property Type & Location.

So if we use a page title template of;

<propertyType> for <channel> in <location> – <channelSecondary> <propertyType> from <price>

That will throw out titles like;

Apartments For Sale in Richmond – Buy Apartments from $499,000

Houses For Sale in Richmond – Buy Houses from $499,000

Apartments For Rent in South Yarra – Rental Apartments from $599,000

Units For Rent in Richmond – Rental Units from $325 p/w

Those titles use the combination of channel, property type, and location, along with a secondary version of channel to optimise for that variation. I’ve also thrown in a dynamically updated price which would reflect the lowest price from that search result and would be leveraged in an attempt to help the CTR a little.

Each of these pages would obviously have optimised content, whether it be just a search result page or whether it be some sort of dynamically optimised location page.

That is Dynamic SEO in the most basic form.

 

The types of websites that can leverage dynamic SEO

There’s what I feel is a common misconception when it comes to programmatic SEO, and that’s that it involves completely JS websites dynamically updating all the time.

Mostly poorly optimised, client-side JS crap essentially. And hey, that’s what I used to think because I never really understood it.

There’s so much more to it than that though!

Yeah, JavaScript websites tend to do this a lot more as they are built to allow this type of optimisation. They don’t have a core set of “pages” for the most part and just leverage a database.

So do many other websites though.

So, who could use dynamic SEO?

Anyone with a database of some sort of content. This could even be a simple spreadsheet to start.

Something that could be used to basically create a page.

My favourite example is for portals, marketplaces and classifieds websites.

You get to leverage your consumer’s & customers’ content to rank!

You don’t need to create the content. Just need to organise the DB content most effectively.

Large portions of the content on these types of websites get copy-pasted across many different websites so it just comes down to who uses it most effectively.

These sites have listing data. This data is super valuable, but not just for the listing page itself.

This data becomes valuable for the information associated with that listing.

Categories, locations, features… the list goes on!

Let’s say for a real estate portal.

A listing can rank for its address, but a term like “real estate Sydney” is silly to rank for with a single listing.

Consumers want multiple options shown when they search for that.

So, you create search result pages (SRPs).

Search result pages that are dynamically optimised because they aren’t actually “created” one by one.

Using all the listing data, you aggregate your listings based on factors that make sense to rank for.

Location – “real estate Sydney”

Property type – “Apartments”

Channel – “Real estate for sale”

Location + Property Type – “Apartments Sydney”

Location + Property Type + Channel – “Apartments for Sale Sydney”

The list just goes on.

For these larger websites, these pages aren’t created one by one. They’re set up similar to how I mentioned above, where you kind of just map the templated elements.

The listing data contains all these properties on each listing so that the URL can then be used to make the appropriate request.

A URL of /buy/sydney/ would obviously represent all properties for sale in Sydney, so that is what the database would return.

A URL of /buy/sydney/apartments/ would be all the apartments for sale in Sydney, and thus that is what would be requested from the database.

There is no /buy/sydney/apartments/ page itself, the system just knows the specific values.

These variables essentially just filter the database query that is made, to return related content.

The dynamic SEO thought process

The biggest thing to think about with dynamic SEO is that you won’t always get everything 100% right.

You will have to make sacrifices.

Sacrifices that may hinder a portion of pages, but will benefit the majority.

One optimisation that will benefit some, won’t benefit others.

You need to just think about, and optimise for, the majority.

As time goes on, you can slowly refine your strategy as you go, to lower the sacrifices that are made.

Depending on the level of site/client you’re working with will come down to what % that majority will be in the initial stages.

So you may launch something that helps 80% of the pages, great! But that poor 20 % sits off alone. Next time, you could come back and specifically tweak that 20% so that 80% of them, could be helped. Each tweak is less valuable individually, but can still help you grow overall once all the quick wins are ticked off.

What do I mean by this?

Well, let’s say you have a list of 1,000s of locations. Those location names have different lengths.

“Sydney” is a nice 6 characters long, but then the suburb of “Sydney Olympic Park” is 19 characters.

If you’re trying to come up with a single page title that allows for the lengths of both, you could be missing out on keyword/CTR opportunities for the shorter location but also be getting the longer one truncated.

Apartments For Sale in Sydney – Buy Apartments from $499,000

Apartments For Sale in Sydney Olympic Park – Buy Apartments from $499,000

Another one could be where “Apartments for sale in Sydney” is the top keyword for Sydney, but then for Melbourne, the keyword used could be “Melbourne apartments for Sale”.

You’ll have to pick one to target as your primary format.

Down the track, you might be able to tweak the system to allow for both formats. You’d also need to go through and assign the formats to each, so it’s nowhere near as simple as just picking a single format to work with.

 

Think about the majority

You should do an analysis of all the top keywords, work out the average format, and primarily optimise for that.

This means that you will need to make sacrifices, but just remember – you want to try to optimise for the 80%, with 20% of the effort. Keep it simple, to start.

At this level of site, page-by-page matters less than for a smaller site, especially when you’re getting started.

Once you’ve really started to take hold across the board, and there are no more ‘bulk’ changes you can do, that’s where you can focus on your more manual, page-by-page optimisations.

After a while, you could come in and do fallbacks for these titles.

So you may have one format if the location is less than x characters, and another format if the location is longer than that.

Yeah, you could do it from the start, but you may increase workloads to get all the possibilities created when they just aren’t needed right away.

You’ll also get additional buy-in from stakeholders if you can find the quick wins within your optimisations.

 

Optimising a Dynamic Build

 

The Variables

What variables do you have access to

The data points that you actually have access to, and can leverage.

For a real estate portal, this could be things like; Location, Property Type, Price, Bedrooms, Bathrooms etc

You should start by listing them all out somewhere so that you can work out if there are any gaps that you might want to optimise for.

 

What variables do you actually want to rank for

Based on keyword research you’ve done for the niche, you should be able to line up some of the variables you have access to, with the keyword data.

Using the real estate portal example, Location & property type are both great and a large portion of keywords will contain one, or both, of these variables.

 

What variables should you actually try and rank for – not all are equal

Not all variables are equal. You will get more ‘bang for buck’ with some, over others. For many variables, you will just end up creating significant amounts of crawlable/indexable URLs that will waste Googlebot’s time and offer minimal to nil value.

It’s vital to choose your variables carefully.

With real estate, you’ll have pricing, bedrooms, bathrooms, sqft and many more that just may not be what you should create pages for due to the significant volumes of variable options.

Using pricing as the example here, there are soooooo many different price filters that could be set. If a site offers 50k increments for the filter, and starts at 200k, they could go into the low millions meaning there could be 50+ variations of the single filter. This becomes large issue when you mix this with a location set of say 10,000 locations, and end up creating 500,000 price-related indexable URLs when only a handful of the keywords like ‘real estate for sale under 500k’ may be worth actually optimising for.

Similar thing with bedrooms. Yeah, 3 bedroom houses in <largeCity> may be worth it, but ‘3 bedrooms houses in <middleOfNowhereTown>’ when you have 10,000+ towns like that, along with 5-6 bedroom filter options, you’re creating 60,000+ URLs just for that single additional filter.

You can add rules to these, and create more ‘manual’ pages to target specific versions of the variables, but as a whole, anything that creates significantly more URLs that what would actually be used to rank should be avoided.

 

Multi-variant – multiple options

When creating pages, you could create single variable pages that could target pages like ‘houses for sale’ or ‘apartments for sale’, but many of the keywords may include two variables, like ‘apartments for sale in Sydney’.

You will need a way to allow for the creation of these pages. Not only that too, but a way to create them cleanly so that you can ideally set one as the ‘parent’ in the hierarchy to appropriately pass value around the site.

 

Why would too many low-value URLs become an issue?

There are a few reasons why too many URLs isn’t always a good thing, and the first comes down to crawling. Whilst Google will try and prioritise certain URLs as best possible, you will end up just wasting your crawl budget if a significant percentage of your crawlable URLs offer little to no value.

The other reason is that the content being used on these pages begins to show up on 100s to thousands of similar pages leading to Google essentially seeing duplicate pages. If it keeps seeing the same content page after page it’s may not treat it the same way, and will start devaluing these specific URLs. Then you’re left with URLs that are still available to crawl, but with minimal chance of ranking.

There are edge cases to this, particularly when you have an extremely strong domain that can hold up on its own, however, for the majority of the sites they will not be able to effectively use this content at all.

 

Programmatic Rules

Creating rules for the pages will significantly help a dynamic system in ranking. Rules can help stipulate what gets pages ‘created’ by limiting what content you’re linking to.

Setting up these rules will greatly benefit crawling performance as you basically say whether a page ‘exists’ or not. When it ‘exists’, you include it in the sitemap, along with internal links. This basically hides the lower value pages away in a dark hole, since you don’t want them found by the crawlers.

 

0-result SRPs Rule

One of the main rules I work with my clients on setting up is a 0 results rules. Google often flags 0 result pages as soft-404s, as it can somewhat understand that the page may as well be a 404 as the results are ‘not found’. When a search page returns 0 results, it should be excluded from sitemaps and links, ensuring that we don’t waste value & crawl budget on URLs that are returning ‘no results found’ type text. Google will often even flag these as soft 404 errors.

 

Variable-match rules

Another rule you could create would be one that only ‘creates’ pages when the variable matches one from a list. You could have say 20 property types, but maybe 5 of them are super close matches, or crossovers, of the other property types. By using a match rule like this, you could get the developers to exclude some property types from the pretty URL and instead just used them as a query parameter.

 

Variable to Pretty-URL Rules

Following on from choosing which variables you want to optimise for, you could set up a rule that only creates optimised pretty URLs for specific variables. ie you could create one of the property type & location variables, but not create it for bedrooms, bathrooms & filters, and instead just use a query parameter for those filters. That would ensure the user experience isn’t impacted, along with ensuring that you can cleanly cut off the lower-value filters with a canonical tag.

 

Graceful Fallbacks

Graceful fallbacks are used to best help you bulk optimise, but not completely stuff over the remaining 20% that may not benefit from a change you want to make.

They’re essentially just a catchall rule that is only used when another rule won’t work. It’s for the edge cases.

An easy example is a result count sentence. On an SRP you could have the text ‘We have 10 properties that match your search.”

That’s all well and good until you don’t have any. You should avoid just saying “We have 0 properties that match your search.”

It’s bland and offers both 0 value to users (… and Google).

But, a fallback could be added that was something along the lines of “We currently have no properties matching your search, but here are some highly similar properties” with either some listings included below, or a link out to related listings.

The fallback just allows you to catch that edge case, and make it a little cleaner by either optimising the content a bit, or just lining out to somewhere more appropriate for the user.

I follow a “use what you’ve got” SEO methodology.

Even if 10% of your content has a specific element you can optimise for, why limit that 10% by not using it? – other than the added time it can take of course 🙂

Just get a rule going to use it, and then use a fallback to catch & handle the other 90%. Takes time once you get to this level, but it’s well worth it to ensure continued growth.

The best thing about it is that even if say 90% need that fallback initially, as the site grows maybe it’s only 80% in a month, then 70, 60, etc. Once your rules are set up, they’ll live there forever.

 

Internal linking for Dynamic SEO

When optimising a dynamic site, you should really place an emphasis on a good internal linking structure. Between helping to set up a site’s hierarchy, and passing link value around the site in a more prioritised way, internal links can truly work wonders.

I wrote a bit more about my specific internal linking strategies here but the crux of it is; link through the hierarchy. Yes, links can help you prioritise pages and pass value around the site. But, at the core of any good dynamic site is the internal linking through the hiarchy. Start at the parent pages, and work your way down through the children.

Cross-link between page types when it makes sense.

Only once you’ve done the core linking strategy like that, should you look at a more individualised linking strategy that will help the key pages that may be a little deeper within the site.

Ideally, you do both at once. But ensuring efficient indexing of your setup should be a higher priority than prioritising a handful of pages in the beginning.

Finally, with linking, avoid linking to 0 value pages. This sort of setup can be built to automatically link to new pages as they get enough content to be considered a ‘page’. New content posted on the business, means new pages being created. These new pages being ‘created’ means these systems can automatically grow as the business grows.

 

Dynamic content generation

Ideally, you have a manually crafted piece of content for each page. When you have 10,000+ pages with a dynamic setup, that isn’t always possible.

This is where dynamic systems can shine, and a lot of websites aren’t leveraging their dynamic site to their full potential.

Using whatever variables you have that relate to a page, you could generate a dynamic piece of content to use.

One of the best examples of this is the many travel comparison websites.

ie on https://www.booking.com/city/au/sydney.en-gb.html you’ll find expandable FAQs down the bottom.

One of them is

“How much does it cost to stay in a hotel in Sydney?”

which is essentially just

“How much does it cost to stay in a hotel in <location>?”

They give an answer of:

On average, 3-star hotels in Sydney cost AUD 273 per night, and 4-star hotels in Sydney are AUD 388 per night. If you’re looking for something really special, a 5-star hotel in Sydney can on average be found for AUD 568 per night (based on Booking.com prices).

That piece of text, could be broken down into a template like;

On average, 3-star <propertyType> in <location> cost <currency> <3starAverageCost> per night, and 4-star <propertyType> in <location> are <currency> <4starAverageCost> per night. If you’re looking for something really special, a <propertyType> in <location> can on average be found for <currency> <5starAverageCost> per night (based on Booking.com prices).

There would then be rules added, that if there were no 5 star hotels in that location, then just not include that sentence. So maybe there would only be 4 star hotels in a city, then only that sentence would be added.

Just don’t forget though, you could always override this dynamictext section with a manually written section when it makes sense. So maybe your top 1% pages get ‘upgraded’. Another strategy here is to use the dynamic text to kick things off, and then as pages start to gain some traction you upgrade them with manual content.

You essentially let your users decide on the content to upgrade, and to, if all goes well, continually grow a specific page.

Just don’t forget though, you could always override this dynamictext section with a manually written section when it makes sense. So maybe your top 1% pages get ‘upgraded’. Another strategy here is to use the dynamic text to kick things off, and then as pages start to gain some traction you upgrade them with manual content.

You essentially let your users decide on the content to upgrade, and to, if all goes well, continually grow a specific page.

 

Expanding dynamic content generation with spintax

Taking the dynamic content generation to the next level, you could also add in variations so that each version of this is a little bit more separate from the last. Essentially just a great new use for spintax.

So instead of just the single version of;

On average, 3-star <propertyType> in <location> cost <currency> <3starAverageCost> per night

You could work in the variation of;

<location> 3-star <propertyType> have an average cost per night of <currency> <3starAverageCost>.

Spintax obviously doesn’t work the wonders that it did back in 2010, but the layer of the dynamic variables within text changes things up a little bit. When you’ve got 10,000+ pages using a rather similar sentence too, mixing things up a little bit certainly can’t hurt!

However, there’s a pretty big caveat here. If you do something like this you definitely need to make sure that the text isn’t being regenerated each time the page is built. It should generate the template once-off, and then only dynamically update the variables on page build. Chunks of robotic-ish text that completely refresh each time Googlebot visits might be a nice little red flag for them.

 

Adding a ‘manual’ page element to dynamic builds

A programmatic SEO build doesn’t need to be 100% dynamically generated pages. You can add a more manual layer into the mix.

I lean towards calling these more ‘custom’ pages, and are essentially there to catch the leftovers that a dynamic build can’t efficiently catch.

Using the pricing filter as an example, not every price needs a clean URL.

However, top cities could benefit from having an under 300k, under 500k, under 1mil type page. So maybe 5 pages, across maybe 10 cities.

Yeah, you could work some rules into it to curtail the page creation to just what you want.

However, sometimes it might be easier to literally list out the 50 pages, and then have some sort of page filter rule to limit the content to what you want.

In this case, it could be;

Houses under 500k in Melbourne

With filters of;

propertyType = house

price < 500000

location = Melbourne (or alternatively, ‘location CONTAINS melbourne’, depending on your setup & variables)

This sort of setup would also allow the creation of pages like ‘Beachside houses in Melbourne’ if you have some sort of property feature of ‘beachside’. You might not want that created for everything, so this could be the way to maintain some flexibility.

 

Optimising search verse creating separate landing pages

One of the options that have come up with a couple of clients for me now, is whether we try and optimise their existing search, or create a separate landing page style setup.

Both have their own merits, with the landing page setup being great due to an easier ability to limit content should you have some sort of gating/membership type setup.

Optimising an existing search can also need a few tweaks to things like dropdown filters. A good old fashion text link has always worked the best for crawling, so additional links need to be added to make things work properly. Whereas a separate build could be a bit less ‘design-heavy’, and more focussed on initial users to get them into the system.

It really depends on tech capability too, as a simpler landing page system may be the way to go to effectively build out an MVP. You can then get it to market, and see how it performs before a larger investment can be made.

 

Common Dynamic SEO Mistakes

The joys of a dynamic system are that one tweak can affect all pages. It’s not hard to optimise, in bulk, so you don’t really need to hold them back too much before launching with a skeleton.

However, there are some mistakes to keep in mind with these systems.

 

  • Over-creating indexable pretty-URLs

This is by far the worst thing that can happen with a programmatic site build. Allowing variables to create pretty URLs that really shouldn’t be, leading to 10s or even 100s of thousands of pages being crawlable, and found, by Google that have minimal to nil value.

This can be avoided primarily via controlling what variables get pretty URLs, and what variables stick to parameters. This is due to query parameters being a little easier to control crawling of.

 

  • Over-indexation of query parameter URLs

Whilst these won’t tend to be linked to, query parameter URLs can still cause over-indexation issues. One of the main issues I have seen cause this, is that once one URL contains a query parameter filter, the rest of the URLs on that page may also contain the parameter to maintain similar results from a UX point of view.

At a minimum, non-pretty variables should be stripped from the URL with a canonical tag. That should limit the amount that are getting indexed, but minimising / completely avoiding links through to query parameter results will stop this issue at the root.

 

  • Letting crawlers run wild

Even if you’ve significantly culled the number of variables creating indexable URLs, linking through to them all can still create major over-indexing issues. Say you’ve got a variable of location, with 10,000 locations in there. Maybe only 1,000 of those locations are actually valuable enough, or even have content.

So whilst a ‘pretty’ URL may be created for all 10,000, it technically won’t exist to Google unless it’s linked to.

Ensuring you have proper rules set up on all linking widgets will help limit what is crawled, which will then obviously limit what gets indexed. This goes the same for any XML sitemaps you have. Exclude anything you’re deeming as ‘lower value’.

If you’ve run into this issue already, and are looking for a fix, 301 redirecting or 404/410ing the URLs have been the only way I have seen it efficiently patched. No canonical hack, or other trick, seems to work.

 

  • Forgetting to establish a hierarchy

“But a flat architecture works best”. For a 100 page content site, maybe! For a 1,000+ page website, you need to establish a hierarchy.

It best passes value around the site, appropriately prioritises URLs, sets the clear parent/child relationship between URLs that share content, and helps crawlability.

This could be via URL structures and/or breadcrumbs, along with a hierarchical interlinking system with footer/sidebar links.

The hierarchy is helpful for crawling, but extremely helpful in establishing whats what on the website, and what page is essentially a ‘filtered’ version of another page.

If you’ve got property type URLs like ‘apartments for sale in Sydney’ you really need this linked to ‘sydney real estate’ in the hierarchy. The filtered apartments page will already be using content found on the generic real estate page, so clearly showing that this page sits below the generic one will help Google understand the relationship between them.

Yeah, it probably can without setting a clear hierarchy. But why make things more difficult for Google? Make their life easy, and maybe they’ll make yours easy.

 

  • Not thoroughly understanding the variables at play

When you’re working with dynamic elements, they heavily rely on the actual data within the system, If this isn’t heavily vetted, or understood, you could be created content or filters that could be led stray by the data.

A simple one here is words that are plurals v singular. If you have property types that some are plurals, and some are singular, your content sentence that uses these variables won’t make sense.

Find an apartment for sale in Sydney

 

Find an apartments for sale in Sydney

 

That one little letter throws it all off, so you really need to know all datapoints of a variable to be able to work with them.

Same goes for formatting, where if some cells have a dollar sign $ and others don’t, you need to try and standarise it.

Another example is when a datapoint could contain multiple options. So for the property types, a data point could be like ‘apartments, land’ rather than the property types individually.

So you’d need to account for this by splitting them, otherwise you may end up with additional unexpected pages and/or weird wording within the content.

 

 

Programmatic SEO Examples

 

Zapier

 

Without a doubt one of my favourite programmatic examples as it’s a non-standard setup.

Firstly, have a read of this post by Ryan on zapier’s SEO strategy.

The post is great, but the following point is a bit off:

But Zapier recognized the SEO value of each page having unique, relevant content.

Yeah, they recognized it. Just not from the start. Their growth exploded well before they took on the strategy he talks about to expand their unique content.

In it’s early days, Zapier really just did bare-bones pages. They used what they had, which was written purely for users.

Their original system looked like this;

Source: Wayback Machine 2012

Each specific zap has a single sentence, with the entire page’s content just being made up of these zap-specific sentences. Nothing else added.

They’ve then come in and done their first big upgrade, which included both the design, along with page-specific content & content about overall zapier.

Source: Wayback Machine 2017

This upgrade was when they really started leveraging templated dynamic text, using the variable of the software’s name.

They then upgraded a couple years later, with additional zap content being loaded in.

Source: Wayback Machine 2019

 

A similar transformation was seen on their more specific zap pages, where you can see the content evolve a bit more.

Early version:

Source: Wayback Machine 2014

 

How it’s going:

Source: Live 2022

Unfortunately can’t find a super original version of the zap pages, as it seems wayback only holds about 2014ish onwards, but even from the 2014 version we can see how basic their original zap pages were.

 

Whilst Zapier has improved their offering as time has passed, its crucial to remember where it began for them. They’re a great example of starting with whatever you can, and improving it along the way.

 

Porch.com

You’ve probably never heard of these guys and yet they’re one of my favourite examples of a true rules-based programmatic SEO builds.

Well, at least they used to be. Just looked at them whilst writing this up, and they’re taking a little dive. So, why not just break out a few of the things they’re doing.

Doubt it’s actually the cause, but hey, you never know!

The last great example of the porch.com I knew was from November 2020 here.

Why do I say that? Well, they had one key element:

This hideous but sexy thing provides deep links to their key category pages.

They’ve still got the header menu, but that was always there too. They just don’t seem to have the same effect that I’ve seen from these raw link widgets.

Digging into their specific category/location pages, we can see a mild but also clear difference between older and newer versions of their page.

Comparing this version to the live one here.

They’ve decreased quantities of content within each of the widgets.

Instead of 25 electricians being shown, it’s now 10.

Instead of 20 reviews, it’s 9. (however some other pages look like they have more…)

Instead of 15 ‘more electricians’ it’s 8. (again, possibly more so not sure what they’re doing here)

They’ve also updated their linking widget from the below;

To become;

They’ve essentially updated this from being highly relevant links for either the category or location, to being somewhat related by child-location types. But since these child-locations go to other categories, like plumbers, that it throws any related value out the window and just becomes a random link in Google’s eyes.

Same category, or location. These types of links shouldn’t be mixed with child locations & different categories.

Whilst there are still some extra sidebar links, these sorts of highly relevant to not-as-relevant change could impact things.

 

Could this be what is impacting them? Possibly, but again, these are pretty mild changes. Combined with other changes they’ve made they could all be adding up, and I’d certainly be recommending a few patches to them to try and revert a couple of items to see if they can re-acquire their former glory.

 

Gumtree.com

Gumtree is a staple in Australia for buying stuff, but I prefer the .com setup over the .com.au version.

Gumtree has gone the route of optimising their standard search, and has a mix of a great setup, with a slightly too loose keyword targeting strategy.

But hey, it’s working for them so who’s to judge.

Their setup starts with their homepage linking widget. With popular searches first, and popular locations second.

Clicking through to one of their cars section pages here, we can see what they’re doing with a car model page.

There are two main widgets here that really stand out for their build.

The first is their dynamic content widget;

They’re using all the magical dynamic words in there to try and pad the content, and not make it sound programmatically generated.

A similar widget is generated for pretty much every other car page too, when there’s enough content. Some widgets have less sections, so they’ve got some rules to limit what’s being used.

If we compare this content side-by-side with completely separate page we can see;

You’ll see that the questions and answers pretty much exactly match. They do seem to either rotate through questions and answer templates, with both examples have the same question, yet different answers. They then have separate questions for #2 and yet have the same answer.

Looking at once in particular, “What is the average mileage on <model> cars?”

We can see two templates they’re using;

On Gumtree, <model> cars have an average of <averageMiles> miles on the clock. To give you a guide on pricing, you can expect to pay around <averagePrice> for an average <model> on our site.

 

The average mileage on <model> cars is <averageMiles> miles. On Gumtree, you can expect a <model> to currently sell for around <averagePrice> on average, and the most popular years for these models are <popularYear1> and <popularYear2>.

Very similar responses, with the second one even getting a little extra added with the popular years. However, it does seem that it’s a two-sentence format, with the first being the actual miles, and the second being pricing. The popular years could just be included in that particular popular years text template. It could just be that they’re tacking an extra sentence into each section to try and pad the content a little.

They even manage to sneak some internal links into these sections, but there definitely seems a rule to it. They either aren’t able to correctly match, or just don’t, like to every variation that is mentioned within the widget. ie for colour, they link to grey, black and white, but don’t like through to yellow or green. Both seem to have enough results, so who knows.

 

The other widget that stands out is there footer linking widget;

All of the ‘top searches’ are highly related to the current page. Whether direct children, or similar models, they’ve done a great job of keeping these links to somewhat tight knife groups.

They seem to be pulling in top searches that fit into the matched hierarchy for the page, being the current page, parent pages, and then other children of the parent pages.

 

Then the ‘top locations’ tab of this widget links through to the other ‘country’ versions of this current page.

Not every page has these links, so they’ve got something setup that determines whether a page has this shown or not. It also just seems to be a handful of top locations, mixing cities/countries, rather than a proper hierarchical linking setup.

If you also go to the wales version of this page here you can see that the ‘top searches’ widget is now hidden behind an expander, and becomes very broad:

So it seems if they either can’t match it correctly, or just don’t have enough ‘sub’ results they default to a more country-wide link widget and then hide it under an expander.

 

One shortcut they’ve taken though, is using query parameters for all sidebar text links. Even though they’ve got an accessible ‘pretty’ URL in there system, a query parameter version still gets linked to from the sidebar.

So even though

https://www.gumtree.com/cars/uk/mercedes-benz/a-class/black

exists, the ‘black’ selector from the sidebar will link through to;

https://www.gumtree.com/cars/uk/mercedes-benz/a-class?vehicle_colour=black

This would definitely be impacting crawling, and potentially causing them issues, but they’re Gumtree. This stuff can pass pretty easily for them.

If you’re looking to jump into a contested market, you should definitely try and avoid a situation like this. It certainly won’t help  you.

I tend to recommend that clients should link to the pretty URL version of a page all the time, to avoid these issues.

At minimum, when only a single parameter exists and it has a pretty URL, it should be redirected to that pretty URL, but even that still doubles crawling for what could be seen as key pages.

It becomes a little messy, and highly modified on a case-by-case basis, when a multi-select filter is at play where multiple options can be chosen.

They’re less likely to cause as many issues, as they tend to not be linked to as much as single option filters.

 

Wrapping it up

Programmatic SEO builds can come in all shapes and sizes, but they’re all coming back to one thing at their core – leveraging data.

You need to change how you think about SEO, to be able to work with these types of sites.

You can’t just deliver 10,000 page titles in a spreadsheet for a dev team to implement. But hey, I’m sure we’ve all tried!

Once you get over the initial step in thinking, you will realise how the large sites develop and grow their strategy.

Internal Linking Strategies for Programmatic / Dynamic SEO

Internal Linking Strategies for Programmatic / Dynamic SEO

Internal links are important. Unless you’re just starting out in SEO, this should be known as a fundamental part of optimisations done to ANY website. Whether a little local business site, a niche content site or a larger scale, dynamic/programmatic site like portals, directories or marketplaces, it’s important for all types of websites.

It’s one of the main elements I’ll look at with clients, and they’re often overlooked at scale. It’s surprising how easy it is to get some quick wins by just adding a link widget though.

For programmatic sites, you should have 3 types of links built into your template so that they’re ‘automated’;

✅ 𝐏𝐚𝐫𝐞𝐧𝐭 𝐥𝐢𝐧𝐤𝐢𝐧𝐠

The ‘up-the-hierarchy’ links, and are easily accomplished via breadcrumbs. In addition to a well laid out site structure, these breadcrumbs help set the hierarchy to your site via helping to ‘parent’ content, which helps ensure your main content gets the value from its ‘child’ pages.

So a location page could link up to its parent category.

✅ 𝐂𝐡𝐢𝐥𝐝 𝐥𝐢𝐧𝐤𝐢𝐧𝐠

Essential to passing authority down the site structure, these links are also extremely important to help Google crawl through key pages. These should link to pages that sit below the current page.

I recommend these are accomplished with footer/sidebar style link widgets, with filters applied to them to ensure only important / priority pages are included. This helps maximise the usage of your crawl budget, essentially just avoiding wasting Google’s time.

A parent category could link through down to its key location pages. In some cases, it could link to a region-type page, with the region-type page linking through to individual locations.

✅ 𝐂𝐫𝐨𝐬𝐬-𝐥𝐢𝐧𝐤𝐢𝐧𝐠

Also be known as related page linking, these links link across to similar pages. These links help connect similar pages together that may sit within a separate part of the site.

The links could be to pages within the same category but nearby locations, or the same location but in a different related category.

If you’re missing one, or all, of the above links then you should look at kicking them off asap. These can be extremely easy to integrate, and may actually show measurable impacts alone due to their importance.

Just keep in mind though, there are always risks with something like this done at scale. Automated widgets like these could expose URLs with issues that may not have been crawled before, or high quantities of low-value pages (ie 0 results search pages). Just make sure you’re adding some rules/intelligence/filters to the widgets, along with significant crawl-testing, and you’ll be good to go.

Product Comparison Keywords: A foot-in-the-door SEO strategy

Product Comparison Keywords: A foot-in-the-door SEO strategy

Comparison keywords are an awesome foot-in-the-door SEO targeting strategy if you’re kicking off any sort of product-based strategy.

Many decent volume, low-comp keywords just sitting there for the picking and can be targetted individually, or in bulk.

Many people won’t even consider many of them!

My latest little project has even just cracked 270 clicks a day on the comparison pages alone… within 2 months.

Not a bad little start!

Comparison keywords search performance

As with all keywords though, there are some that are easier than others.

How do we find the keywords?

How can we best target them to make the most of the opportunity?

Who’s doing it well?

Before we get stuck in and take a look at a few options, I just want to explain a little about these comparison terms.

 

What are comparison keywords?

As the name suggests, they’re simply keywords that a consumer will search when they’re looking to compare products.

Two of the main comparison keyword formats you’ll see are;

Product 1 vs Product 2

Product 2 or Product 3

And then now and then, you’ll find yourself an elusive three-way comparison that could be worth targeting;

Product 1 vs Product 2 vs Product 3

But, what can we deem as a “product” when it comes to these comparisons?

Anything.

These aren’t limited to products of the standard physical nature found in a store.

Which leads me into who can target these comparison keywords.

 

Who can leverage product comparison keywords?

Many will initially think Laptops, Tablets, Phones etc and all your other gizmos, gadgets and anything you walk into a store to buy.

But they go so much further than that.

Affiliate marketers, media companies, business owners.

There are plenty of options to jump into the comparison ring and knock out some rankings.

So let’s take a look at a few of the options.

 

Ecommerce / Traditional Product-based Websites

The traditional ecom / product-based websites are the default go-to for the usage of comparison terms.

Whether selling laptops or washing machines, phones or vacuums, cars or blinds, consumers are probably looking at comparing that product to something else before they buy.

Someone looking at grabbing an XPS laptop could be searching;

Product comparison keywords example 1

Albeit a smaller volume, you’ve also got the category/subcategory/type comparison keywords that fit in here, with keywords like;

Product comparison keywords example 2

These have a much-limited scope, so normally are reserved for these higher volume product-based comparison terms.

However, still worth looking into their value for you.

 

SAAS / Software Businesses

Many software companies get stuck with where to start their SEO strategy.

Most will tackle a content strategy, filling up their blogs with general content that fits their niche.

Which is great! But… they’re missing out on potentially closing an audience that’s a bit further along their funnel.

Users that are just doing some final comparisons before they pull the trigger on their chosen software.

Wouldn’t you want to ‘sway’ the conversation with some favourable data-points showing you as the #1 choice?

Let’s check out what people might compare one of the top mailing software with…

Product comparison keywords example 3

But guess what? Mailchimp is nowhere to be found for these keywords…

Except for one.

Mailchimp comparison example

Klaviyo is also running the comparison back to them.

This is the only comparison they appear to be running.

Mailchimp is leaving out so much opportunity where they could be controlling some of the comparison conversations for the other comparisons too.

 

Service Providers

Looking at a new electricity provider? Or a new phone company?

What about a new internet plan?

Well, even if you’re not, there are thousands of people that are, every single month.

Product comparison keywords example 4

What about a new web hosting plan?

Product comparison keywords example 5

 

Location

Looking to move house?

What about planning your next holiday?

Once you lock down a few options, you probably wanna weigh them up against each other to see where could be best for you.

Product comparison keywords example 6

Websites in travel could really be getting in on this to grab some potentially easy conversions.

Even the official tourism groups of the locations should be jumping on this, to try to swing it in their favour.

One big callout here though, be careful of sports teams, or other things that use the location name in these comparisons. Your volumes will be thrown out the window.

A quick Google search can confirm Google’s intent from the keyword, and avoid you wasting your time.

 

Sports

This is one of the categories where things get interesting.

A majority of the volume for sports-related comparison terms will come down to users trying to find out information about an upcoming/completed match between the teams.

However, there is one other big share of this market that is worth taking a stab at.

Consumers search these keywords to work out who could win, should they ever play against each other.

Some random American hockey team still sits at some pretty high volume;

Product comparison keywords example 7

One even includes ‘tickets’ which can show the intent here.

Correct targeting by a sports team could help them grab some of the SERP real estate for these keywords.

Correct targeting by a sports website, could even steal some keywords from the larger players.

 

General Businesses

You really start scraping the barrel here, due to the localised nature of general small businesses.

But, the keywords are still there. Hiding away, waiting for you to find them!

Product comparison keywords example 8

Never know, your business/businesses in your niche, may have some comparisons waiting for you to leverage.

 

Finding the keywords

How do we actually go out and find the keywords though?

There are two tools I use for this, but both involve a little pre-generation of your seed keywords.

It all starts with a list of the products you want to compare.

If you’re only looking to try find comparisons for a single product, or a single name, it’s easy!

Just do;

$name$ vs

So;

Product name vs

You can then try to look at any variations of the name.

For a basic product, I’d start with

$brand$ $productName$ vs

$productName$ vs

That get’s whacked into the tools, and hopefully, you’ll find yourself some nice comparison keywords to play with.

However, for the people that are looking to find comparisons they could do, from a larger list, you need to;

  • List out all your product names in Google sheets
  • Add any variations to the list
  • Append “vs”, “verse”, “or”, or any other extension you’d like to search for
  • Copy all into a single list

This can all be done pretty quickly in Google sheets;

Generating product comparison keywords with Google Sheets

From here, we just need to paste the ‘C’ column somewhere, to remove the formula and avoid mishaps.

 

Finding comparison keywords with ahrefs

Being one of the obvious larger keyword research tools around at the moment, ahrefs offers something that many tools can’t.

More keywords!

Which is exactly what we need for this.

ahrefs has a ‘phrase match’ keyword research option, which will return any keywords in their database that contain the inputted seed words.

Just access their keyword explorer, paste in 10 seed keywords (their max for phrase option) and then click “phrase match”.

ahrefs phrase match example

This will hopefully come back with plenty of the top comparisons for you to look at!

Using my XPS example, I can see that I should potentially be looking at creating comparisons for hundreds of different combinations!

If you’re working with some higher volume products, you’ll find plenty of comparison duplicates, with alternate names.

Read on for something I’ve shared below, to help analyse your list.

 

Finding comparison keywords with Google Suggest/Autocomplete

The other method of finding comparison keywords is to use Google’s autocomplete feature.

Google autocomplete suggest

This will give you hot comparisons to use. Hyper dynamic keywords, that will popup well before any keyword research tool’s database will grab them.

Yeah, my example doesn’t do this justice though, as the XPS is a popular laptop!

But how about a less popular model.

If we take a look at one of Asus’ newer laptops, one I am actually hoping to buy as soon as Australia has some more stock, we can see a different story.

ahrefs phrase match

Google suggesting comparison terms

Look at all that magical autocomplete data!

ahrefs had nothing.

I wouldn’t expect it too either. It sometimes takes months and months before keyword tools will collect see these keywords.

Simple typing the product names into the Google search bar is a quick and dirty way of grabbing these terms.

However, I prefer to use a tool as I can grab some search volume in the process, should it be available.

Keysearch is a tool I’ve used for a couple of years, primarily because they come out with some awesome discounts now and then, and have decent enough data for it.

Type in the keyword, one at a time for autocomplete usage, and then click ‘Google suggest’.

keysearch

Filter the keywords to contain your comparison phrase, in this case, it is ‘vs’.

It will then spit out the same autocomplete keywords as before, and a few more, for you to get stuck into.

Repeat the process for each of your seed keywords, and it will give you the comparison terms along with any volume or CPC data the tool has.

If you’re really not fussed about the extra data, jump into Keyword Shitter and throw in all your seed keywords at once.

keyword shitter comparison keywords

Add all your comparison phrases in as positive filters and then you can go ahead and keyword shit yourself silly!

 

Zero search volume does not mean zero search volume

Keyword search volume is derived as an average of the previous 12 months of searches for that keyword, should tools actually have that data.

When products are released, it could take months before a tool even shows the first month of volume for it.

Any comparison you’re doing for a new product is pretty much guaranteed to have no volume.

You will also find many comparisons that will show zero volume, even though they are older.

Due to the lower volume, lower comp, nature of these keywords it could still be worth creating the comparison.

Considering how easy some of these pages are to create, even if it just helped make 3-5 conversions over a 6-12 month period, what’s that worth to you?

For some, it’ll be worth plenty.

Need proof of their value? Fine.

Here are some stats of a single, shiny comparison page;

74 total keywords showing up in GSC for this page, with the top one being almost 50 clicks, over the 6 week period.

How do these keywords look in ahrefs though?

Like absolute garbage – as expected haha.

Ahrefs didn’t even have 2/3rds of the keywords in their database!

Two keywords show a whopping 30 combined volume, yet they’ve driven a combined ~200 clicks for the month.

Rarely, will someone target a set of “zero volume” keywords because of this.

Comparison keywords are where I’d recommend that be a bit different. They’re worth the “zero” volume.

That’s because that zero search volume does not mean zero search volume.

 

Matching & extracting the product names from the comparisons

If you’re working in bulk, the generated keywords can sometimes be a bit of a pain as you might struggle to match up the product names.

There could be many names for a single product, and individually matching these would be painful if they’re across lots of comparisons.

Well, it was a pain for me anyway – so maybe this will help you out too!

Duplicate My Google Sheet Here

It’s a bit of a behemoth… but I wouldn’t have bothered doing it if it wasn’t going to turn out that way 😛

1. Paste in all your keywords & volumes

2. Scroll across to the right and paste in your product seed words, along with their associated product names & IDs

The ‘seed’ is a word/phrase within the keywords, that would align it to the specific product. So for me, ‘xps 15’ relates back to the Dell XPS 15.

3. Drag down the formulas, into any empty rows you have

The formulas will now run and match any products within your comparison keywords, to the seed list you provided.

This is a two-step process, where you’ll find that ‘model 1’ and ‘model 2’ will be broken up by either “vs”, “verse”, “or”, or “compared to” as the comparison text in the middle.

If the comparison keyword doesn’t contain one of them, the formula will break. You should be able to adjust the formula to suit your needs, and if not, just comment and I will try help you out 🙂

Once broken down, the formulas will categorise the two models, using the seed keyword list you provided. It will just look for the seed, inside the keyword, and then match it up with the given product.

If the keywords aren’t being matched to the products, you will need to build out your seed list to include the missing seed words. This means you’ll just need to add the alternate names for a product, until the formula can correct match them together.

Model 1 assumes that you know the exact model name before the ‘vs’ because that’s what you would have been researching.

However, model 2 tries to match two different ways.

There’s the same ‘exact’ match of the seed as Model 1, but then there is also a partial match.

If I delete the seeds for ‘xps 15’, and ‘xps 17’, you’ll see that the partial match still matches these to keywords to their correct model.

This is because the partial match formula is based on a wildcard match, and will match ‘xps 17’ inside the seed of ‘dell xps 17’.

The final column will then look for an exact matched product, and if one isn’t there it will look for a partial match.

Sometimes the partial match can go haywire though, so that’s why it’ll prioritise an exact match where possible. It’s also best if you manually check the partial matches, as you never know what it’ll match up to.

Duplicate My Google Sheet Here

Prioritising the comparisons

If you’re only working with a few comparisons, just stick to however you normally prioritise SEO stuff. Usually just by a mix of search volume & competition.

But, if you’re working in bulk you’d probably be better off running your keywords through this sheet.

Provided you’ve done the above instructions on setting up the sheet, you’ll just want to load up all your product names, with their combined search volume on the far right. Whether this is volume from one keyword, or many, it just needs to be one total volume per product.

The sheet will pull in this product volume data, and display it next to the match model names.

And this is where things get interesting.

The formula in the sheet, will look at the volumes for the two products being compared, and then optimise the ordering of the comparison to ensure the highest volume product is first in the order.

Might not be helpful to everyone, but it was something I wanted to use, so you get it too 🙂

In the above, even though the XPS 13 came first in the keyword, the dataset says that XPS 15 had a higher volume, thus putting it first in the calculated comparison.

You can just kill off the search volume for the products if you don’t want to use this feature.

Individual comparisons will then be prioritises by their search volume with the ‘comparison volume’ column aggregating the search volume of any comparison keywords with their associated matched comparisons.

All the duplicate comparisons will have their search volume aggregated from the unique comparison keywords that were matched to them.

Search volume for any time XPS 15 was compared to XPS 17 gets aggregated into the “Dell XPS 15 vs Dell XPS 17” comparison.

You’ll then be able to sort all your comparisons by volume, to work out what to target first.

Duplicate My Google Sheet Here

How to properly target comparison keywords

I’ll keep this bit brief, as you’ll see a few different ways to target them below. Also cant give you alllll my secrets 😉

There are two mains ways to target them though.

Dynamically and manually.

Dynamically will leverage a dataset and let you target the keywords in bulk, giving you the option to build out and optimise the content further as they grow.

Manually means you’d be writing out the content for the pages individually as you go.

Both have their own merits, but if you’re looking at targeting more than a handful of comparisons at a time, some sort of dynamic solution would be best.

No matter the strategy you’re taking, there are a couple of things that should really be included in these comparison pages.

A nice and simple table/chart showing a direct comparison of any features/specifications of the products, and then a summary of the strengths and weaknesses of each product off the back of these.

At its core, that’s what you’d need at a minimum. The rest is SEO.

 

Good comparison examples

Who does this well?

There are soooo many examples of comparison pages – particularly in tech.

The best examples, are looking at the keywords you’re trying to target though. Nothing better than leveraging what Google already loves, for what you’re targeting.

You don’t need to be as good as the overall best, you just need to be better than your direct competitors.

Apply some of what you can learn from the overall best to your market though, and it should help you out!

 

Versus.com

These guys are definitely one of the “best in class” comparison websites… you’ve probably never heard of them though!

Tech, Sports, Food, even dating apps!

423k out of their total 493k keywords are rankings for URLs with “-vs-” in them.

That’s their comparison pages.

They’re absolutely killing it.

versus comparison example

They are definitely one of the “prettier” comparison websites floating around and give some great breakdowns of the individual features of each device.

 

CameraDecision

Can you even guess what they compare? Yeah, cameras, and they’re killing it too.

Not as high as they used to sit, but they’ve managed to keep themselves pretty stable over the last couple of years.

CameraDecision example

They’re just keeping it simple though.

Overall summary, key specs of both, and then the side-by-side features comparison.

 

RTINGS.com

These guys do a lot more than comparisons, but they’ve got some great comparison pages here!

rtings example

Simple summaries, with some awesome side-by-side comparisons of specs and features.

Not much text, but they’re covering that on their reviews side. These are just their pure reviews.

They’re ranking well with them too!

 

HostAdvice

Webhosting is a tough niche to crack. There are some prime commissions available for those that do though!

Host advice example

Bit of a slap from the May update it seems though!

These guys have cracked one of the largest web hosting comparisons they can – Godaddy.

HostAdvice comparison example

They’ve covered so much here, but as you’ll see, it’s just a giant comparison table!

A lot of work goes into the data powering this page, but at its core, it’s just that – data.

 

Why wouldn’t you target comparison keywords?

There are a few reasons I know of, of why people might not target these keywords. All completely understandable, and up to you if you see them as blockers.

But hey, you never know until you give things a go sometimes!

 

You don’t like free traffic from brand jacking

Has anyone… ever… turned down highly targeted free traffic before?

Probably, but I wouldn’t be. I’d be milking that traffic as much as possible!

Comparison keywords are great for when people are looking for those comparisons, but, there’s something that I haven’t mentioned yet.

These pages will also rank for non-comparison terms.

Imagine being a brand new email platform, creating a comparison of you vs MailChimp, but then ranking for keywords like “MailChimp features”, “What is MailChimp used for” or even “MailChimp cost”.

This is known as brand jacking, and it’s a great way to get your brand in front of a highly targeted user-base!

You probably won’t be able to jump out the gate with such a known brand, but there are definitely some competitor terms you could get in there with, stealing yourself some nice SERP space.

Brand jacking is also a perfect segway into the next reason…

 

Legal stuff

Probably one of the biggest is the fear of being sued. If you’re outright comparing yourself to your competitors, and it’s favourable for you, they’re obviously not going to like it.

They’re especially not going to like it if you’re ranking at the top of Google for keywords including their brand name, or worse, their brand name by itself.

I have pretty much no clue about this side of things, so anything I say about this stuff is further from advice that it could possibly ever be, but what I know is you must avoid anything slanderous.

Stick to what you know. Talk yourself up, but don’t slander or negatively talk about the competitors.

Let your numbers & features do the talking.

 

Biases

Users will often prefer third-party, unbiased reviews.

If you’re not third-party and you’re just comparing yourself to competitors, consumers may not take to that favourably.

However, it’s still worth jumping in there with your say. Something is better than nothing!

 

Lower volume markets

The volume is so low, it might not be worth the effort for some.

Yeah, this is completely reasonable.

But, you never know until you try!

At least dig in, see what you can find, and give it a shot. Do a test run with a few, see how they perform, and go from there.

 

Unknown brand

Following on from lower volume markets, if you’re a brand new SAAS and no one knows your name, you won’t benefit from these comparison terms like others will.

Why? No ones searching your brand, so no ones searching for comparisons.

As you grow, they will though.

And if someone comes across you, they might want to see how you’re the better option anyway, so I’d say it’s still worth getting in early here.

You might even just rank for the competitors brand names without anyone searching the comparison anyway 😉

 

What’s stopping you?

Don’t see the value?

Can’t find any comparisons you’d want to target?

I’d love to hear if there are any reasons holding you back.

Hit up the comments with any comparison feedback, especially if you’re successfully targeting them already!

Bulk Generating Page Titles in Google Sheets (and Excel!)

Bulk Generating Page Titles in Google Sheets (and Excel!)

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.

Ideally.

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;

=INDEX($BA$12:$BE$12,1,TRUNC(( countA($BA$12:$BE$12)*RAND())+1))&” | “&INDEX($BA$13:$BE$13,1, TRUNC((countA($BA$13:$BE$13)* RAND())+1))

Where;

$BA$12:$BE$12 – Cell reference to the Keyword parts

$BA$13:$BE$13 – Cell reference to CTR page

This formula will randomly select from the keyword range, add a pipe in the middle, then randomly select from the CTR range.

So by having 3 of each template, there are a potential 9 options for the title structure.

Now you just need to substitute in your variables.

Adding the substitutions to the same formula as before, we get;

=substitute(substitute(substitute(substitute(substitute( INDEX($BA$12:$BE$12,1, TRUNC((countA($BA$12:$BE$12)* RAND())+1))&” | “&INDEX($BA$13:$BE$13,1, TRUNC((countA($BA$13:$BE$13)*RAND())+1)), $D$8,if(len($D12)>0,$D12,$D$9)), $E$8,if(len($E12)>0,$E12,$E$9)) ,$C$8,if(len($C12)>0,$C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9))

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.

=if(M12<$C$7+1,L12, substitute(substitute(substitute( substitute(substitute(INDEX( $BA$12:$BE$12,1,TRUNC(( countA($BA$12:$BE$12)*RAND())+1))&” | “&INDEX($BA$13:$BE$13,1, TRUNC((countA($BA$13:$BE$13)* RAND())+1)),$D$8,if(len($D12)>0,$D12,$D$9)), $E$8,if(len($E12)>0,$E12,$E$9)), $C$8,if(len($C12)>0,$C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9)))

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.

=if(AK12<$C$7+1,AJ12, substitute(substitute(substitute(substitute( substitute(INDEX($BA$12:$BE$12,1, TRUNC((countA( $BA$12:$BE$12)*RAND())+1))&” | “&INDEX($BA$14:$BE$14,1, TRUNC((countA($BA$14:$BE$14)* RAND())+1)),$D$8,if( len($D12)>0,$D12,$D$9)), $E$8,if(len($E12)>0, $E12,$E$9)),$C$8,if(len($C12)>0, $C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9)))

This formula is the one we just used, that checks the length of the previously generated title, and then regenerates it if it’s too long.

However, there’s one small tweak.

It will look inside this range: $BA$14:$BE$14

That range has my fallback extensions, with the previous one looking at the primary extensions in $BA$13:$BE$13 – so it’s now looking one below.

Pulling in this shortened extension will give us back more page titles that fall within the range.

My example validates all of them, except one, in the first go, however, as before, I repeat the formula again just to confirm.

So we’re now left with one pesky page title that is too long before the location.

Albeit, not a real one, but this is still a scenario you could encounter.

So to fix this, the absolute final formula just strips the extension.

=if(AS12<$C$7+1,AR12, substitute(substitute(substitute(substitute( substitute(INDEX($BA$12:$BE$12,1, TRUNC((countA($BA$12:$BE$12)* RAND())+1)),$D$8,if(len($D12)>0, $D12,$D$9)), $E$8,if(len($E12)>0,$E12,$E$9)) ,$C$8,if(len($C12)>0,$C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9)))

That single page title, will now successfully validate once we don’t pull in the extension.

And then just pasting that final page title generation into a column near the start, we end up with all our unique page titles with all their variables swapped out.

Not exactly my best title copywriting in history… but you get the point!

You will just need to repeat the copy/pasting bit every time you edit your variables or templates, but you won’t need to touch those formulas!

So once setup, it’s a pretty easy process to bulk generate the titles going forward.

Or, just use my sheet to save you some time!

 

Using my page title generation template

I know this isn’t the easiest thing to follow along too, so I made a sheet that you can copy and use.

There are a few extra things in it to try and make the process a bit easier, but you need to be careful with it!

It’ll break pretty easy because of the macro that gets run to generate the titles. So please don’t insert/delete rows, or columns. Just input your existing data.

If you do want to modify things, just know that the ‘generate’ button will probably no longer work and that you’ll just need to manually copy the data around.

 

1. Copy the document

Click this link here, and copy the generator to your Google Drive.

 

2. Paste your data in

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.

 

Why?

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

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;

=RIGHT(A2,LEN(A2)FIND(“domain.com”,A2)LEN(“domain.com”))

This will strip everything up to and including ‘domain.com’ allowing us to account for all https, http, www, and non-www variations of a URL leaving us with just the URL path.

Stripping the domain from a URL

It will also cull subdomains, so just keep that in mind. If you need subdomains left, you can still use the same formula, but each subdomain will need it’s own.

I’d recommend extracting the subdomain in a separate column, and then referencing the subdomain’s cell, rather than plain text domain. That should save you a bit of time!

 

Extracting query parameter values from a URL

Let’s go over this one first, as it is slightly simpler than breaking down a ‘pretty’ URL.

There’s just one formula you need, and that’s regex extraction.

=iferror(regexextract(B2, “[?&]parent=([^&]*)”),“”)

That will extract the value for the ‘parent’ query parameter, and leave the cell blank if the parameter doesn’t exist, or is blank.

Extracting query parameter value from a URL

You then just need to repeat that formula, for each query parameter value you need to extract.

 

Stripping a hierarchical ‘pretty’ URL structure

Stripping down a folder-based, or hierarchical is the next structure that could be at play here – and generally my favourite to use.

The absolute simplest way is to use the “split text to columns” method.

Splitting url path text to columns

Highlight your text, click ‘tools’, and then click on ‘Split text to columns’.

You’ll then get asked for a separator. In this case, the separator is a ‘/’, so you’ll need to click ‘custom’ and enter the ‘/’.

After which, you’ll immediately get the following;

Select the URL separator

Each folder split across the different cells, giving you all the different folder levels all accounted for.

Works perfectly! If you don’t mind splitting any new URLs like this going forward any time you add new ones.

I personally prefer to try and work with formulas where possible, so that we can keep expanding the URL list, along with tweaking it as we go if modifications are required.

Provided you did the URL path column above, the following formula is what you’ll need;

=iferror(LEFT(B6,FIND(“/”,B6)1),“”)

Parent folder extraction from URL

That will just strip off everything after the first slash, leaving you with the parent folder.

Next, you’ll do a similar thing to get the child folder, but first, you’ll just subtract the parent folder from the URL path.

=iferror(LEFT(substitute(B6,C6&“/”,“”),FIND(“/”,substitute(B6,C6&“/”,“”))1),“”)

This will just remove the parent folder from the URL path, and leave you with the child folder, which will be the second folder in the path.

Extracting a child folder from a URL

And if you’re playing with 3 or more levels, here’s the formula you can modify;

=iferror(LEFT(substitute(B6,C6&“/”&D6&“/”,“”),FIND(“/”,substitute(B6,C6&“/”&D6&“/”,“”))1),“”)

You’ll just need to keep adding the new cells in there each time you need a new level

 

Breaking down a flat URL structure

These are extremely difficult to break down.

Unless there is consistency in how they’re formatted, this could be a very manual process.

However, I am just going to assume you’ve gotten lucky with something that resembles a consistent format for you to pull apart.

=LEFT(B15,FIND(“-for”,B15)1)

Stripping a flat URL structure

For the child folder, there are two formulas I have that basically just do the same thing.

They both look at a break point and just trim everything including and after it. For my example, my breakpoint is “-in”.

=substitute(REGEXREPLACE(B15,“(.*)-in.*”,“$1”),C15&“-“,“”)

Extract a part of the URL

=substitute(LEFT(B16,FIND(“-in”,B16)1),C16&“-“,“”)

Google sheets URL modification formula

Same same but different.

I just don’t remember how they’re different lol.

The last part here is to extract the location.

If your location is like mine, you don’t need anything fancy.

You just need to substitute all the previous folders from the structure, then remove the slash, and you’ve got your location.

=substitute(SUBSTITUTE(B15,C15&“-“&D15&“-in-“,“”),“/”,“”)

Extraction a location from a URL for SEO

You essentially rebuild the URL that came before it, based on what you had stripped out, and then you’re left with the ‘location’ that is on the end.

If your location isn’t on the end or doesn’t have something that can be clearly substituted out after it, you’ll need to modify the formula used for the child folder.

 

Rebuilding the New Post-Migration URL Structures

Once you’ve stripped down the old URL to its main elements, you can start building the new one!

If you’re merely converting query parameters to a clean structure, with no folder changes, you’re super lucky.

Many of us though, are not that lucky.

 

Converting query parameters to pretty SEO URLs in Google Sheets

Okay you lucky people, this is super easy.

=”https://www.domain.com/”&C2&”/”&D2&”/”

Converting query parameter to pretty SEO URL in Google Sheets

You just piece the URL together using the ampersand, ‘&’.

Include your domain with a slash, then the parent folder and a slash after it, and then the child folder and another slash.

Some people like to use concatenate, but you complicate things. Complications through concatenations.

Annnnnd you’re done! You’ve now turned your query parameters into a pretty URL structure, in bulk, so that you can test these out.

It’s rarely that easy though.

 

Rebuilding a URL structure where folders change names or get merged

Whilst it’s easier when things don’t change, it really isn’t that hard when they do now that you’ve stripped the URL down.

The first step is to paste all your parent, child, and any other folders into a separate column, and then remove all their duplicates.

Removing duplicates from a list of URL paths

This will leave you with a unique set of folder values.

You then just need to add their new value next to them.

If the value doesn’t change, just paste the same value in.

If the folders are getting merged, just paste the folder you’re merging into here.

If it is a completely new value, type out the replacement.

Old to new URL folder mappings

The mapping of the values might take time, as some websites could have hundreds and hundreds.

The benefit of doing it this way is that you aren’t dealing with all the possible variations & combinations.

Separating the child folders from the parent folders lets us independently map them to their new values.

Now we need to bring these new values into the mix, with a good old vlookup (if you don’t know how to do it, check out my SEO Excel formulas post).

=VLOOKUP(C2,M:N,2,0)

Vlookup the old to new parent folders

Repeat the process for the child folders, and you’ll get all the shiny new folder values.

You’ll just need a slight tweak to the child formula, due to the possibility of there being no child.

=iferror(VLOOKUP(D2,P:Q,2,0),“”)

If no child exists, the cell will remain blank.

Vlookup the new child URL folders

You should now have completely mapped the old to new folder values, so we can move on to constructing the URL.

Now you just piece it all together!

=“https://www.domain.com/”&E2&“/”&IF(len(F2)>1,F2&“/”,“”)

Rebuilding a new SEO URL for migration testing

This will add one folder to the other, and rebuild your new URL structure for you!

If there is no child available, it will ensure there is no extra slash added.

If you prefer the old flat structure, tweak the formula to be;

=“https://www.domain.com/”&E2&IF(len(F2)>1,“-“&F2&“/”,“/”)

Which will ensure there is no extra dash added, unless the child exists.

And if you need a stop word, or two, in the middle to clean her up a bit, just do the following;

=“https://www.domain.com/”&”theres-a-“&E2&“-before-the-“&F2&“/”

You’ll have to account for your own variations of the template, and whether a child may or may not exist.

 

Rebuilding a Structure that Previously Used ID’s

What if you’re just stuck with a heap of parameter IDs, and no ‘pretty’ URL to start with?

The benefit to the above vlookup style URL rebuilding is that you can extract the query parameter values, and then just map the old values to their new pretty folder name.

A quick rebuild, and you’ve gone from the old ugly URLs, to what could be thousands of crawlable new versions of the URL!

Start by getting the path out of the URL, and then extract all the parameter values, like I showed you earlier.

Converting query parameters to a pretty URL

Add your new parameter values to the parent/child vlookup lists;

Query parameters to clean folders

Then just vlookup the parameter value, to the new parent & new child values, and build your new URL!

New pretty URLs for query parameter ugly URLs

Everything was covered earlier, so just grab all the formulas from there.

That covers it though!

You’ve gone from the ugly parameter version to a shiny new & testable pretty URL.

 

Bulk testing your new URL structure

You can now run the old URLs through screaming frog (or your preferred bulk testing tool) and copy out the final URL in the redirect path.

Match it up to your expected ‘new URL’ and confirm whether it is the same or whether they’re different.

If they’re different, is it the redirect rule that doesn’t match? Is there an error in the expected URL? Are the redirects just not implemented?

This is guaranteed to save you time bulk testing your migration.

 

Testing a URL migration on staging before going live

In an ideal world, for a migration this size, you should be able to test out your redirection rules on a staging environment.

Swap out the website’s domain in the formula, for the URL of the staging environment, and do everything else the same.

You’ll get thousands of staging URLs ready to load screaming frog up with!

 

Download the sample

You can download all my sample data from here if you want to jump right in.

I’m always looking at new ways to simplify bulk analysis, so if this worked for you, or you do something completely different, let me know!

I’d love to hear about it all 🙂

 

Also, just a quick shout out to the guys at ExtendOffice.com though!

Plenty of awesome formulas, with this one definitely being one for the bookmarks: https://www.extendoffice.com/documents/excel/1783-excel-remove-text-before-character.html

Excel Formulas Every SEO Needs to Know

Excel Formulas Every SEO Needs to Know

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.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(substitute(SUBSTITUTE(A2 ,”https://www.”,””),”https://”,””),”http://www.”,””),”http://”,””),”www.”,””)

Trim URL to Subdomain

Spend a bit more time playing with a formula, and you can get something that will do a much better job though!

This formula will trim any URL back to its subdomain. It will remove all prefixes, or any suffixes (aka folders) from a URL so that you are just left with the subdomain & domain combo.

=SUBSTITUTE(REPLACE(REPLACE(A2, 1, IFERROR(FIND(“//”, A2)+1, 0), TEXT(,))&”/”, FIND(“/”, REPLACE(A2, 1, IFERROR(FIND(“//”, A2)+1, 0), TEXT(,))&”/”), LEN(A2), TEXT(,)), “www.”, TEXT(,))

And another formula is;

=REGEXREPLACE(REGEXREPLACE(A2,”(http(s)?://)?(www\.)?”,””),”/.*”,””)

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.

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(REPLACE(REPLACE(A1, 1, IFERROR(FIND(“//”, A1)+1, 0), TEXT(,))&”/”, FIND(“/”, REPLACE(A1, 1, IFERROR(FIND(“//”, A1)+1, 0), TEXT(,))&”/”), LEN(A1), TEXT(,)), CHAR(46), REPT(CHAR(32), LEN(A1))), LEN(A1)*2)), CHAR(32), CHAR(46))

Stripping Text Before/After Characters

This is something I have only picked up in the last couple of years, and these formulas allow you to strip out the text before or after a character or a set of characters.

I will leave this up to your imagination with how they work, but a great usage is stripped title tags of anything after a | (pipe).

Yes, you could find/replace this stuff out, or substitute it, but maybe there are 50 different items to swap out. That will take ages!

If they all have a pipe right before them, you can strip them all out with one formula.

Found these formulas here, thanks guys!

Strip Text Before a Character

This formula will remove everything before the pipe. Just edit the pipe out with any character, word, phrase, anything, and it will remove all the text before it.

=RIGHT(A1,LEN(A1)-FIND(“|”,A1))

Strip Text After a Character

Again, just replace the pipe with anything you want to have this formula strip all the text that is after it.

=LEFT(A1,FIND(“|”,A1)-1)

Finding Your Own Excel Formulas

The biggest thing to remember with Excel formulas is that chances are someone else has tried what you’re doing before.

Just break down what you’re doing, and Google it piece by piece. You might end up putting something together yourself, through what you find from others.

Building a Keyword Research Dashboard in Excel

Building a Keyword Research Dashboard in Excel

An extremely large table of all their keywords, isn’t going to give the insight some clients would want. Some would like more a bit more insight into the data they’ve paid thousands for.

Building a keyword research dashboard in Excel is a great way to allow a client to interact with their keyword data.

In this short video, I will show you how you can take a categorised keyword list and turn it into a client-friendly dashboard!

The video is a couple years old now, and I may eventually update it, however, for the most part, it still holds true today.

Now, this won’t be great for some clients. You could end up giving them way too much information, and have them asking way too many questions.

However, I personally don’t mind that. I love when clients have an interest in SEO as it gets them more involved in the project, and more excited when the results kick in.

How to build a keyword research dashboard in Excel

  1. Categorise up your keyword research data
  2. Create pivot tables for each category level
  3. Attach slicers to the categories so you can filter the data
  4. Design the tables & slicers to match your client’s branding

And there you have it, you will have an interactive Excel keyword research dashboard to send to your clients.

Creating an online keyword research dashboard

You can save this Excel dashboard to Onedrive and then share with your clients via a link instead of sending a file.

For dashboards under a certain size this means they will be able to view the dashboard online instead of having to download the file. You can then simply update the keyword research file, and they will be able to see the update when they refresh their browser.

This becomes a massive help when trying to send updated data to a client. No more v53 tacked onto the end of a file because you’ve updated it 53 times!

Download the Excel Keyword Research File

If you watched the video and can’t be bothered doing the work yourself, you can just have my file!

Or if you’re doing keyword research for car hire you can also just steal my keywords – chances of that have to be slim.

Download the Excel Keyword Research Template
  You will receive a download link via email. We hate spam and never share your details.

 

Don’t forget to add some proper styling to the file though! She ain’t exactly pretty at the moment.

If you have any issues with the Excel file, or just need a hand with anything from the video, just leave me a comment below and I can help you out.

10 Years of Keyword Research and What You Can Learn From It

10 Years of Keyword Research and What You Can Learn From It

I’ve been doing SEO a little while now on and off over the years (but mostly on) and finally getting back into it full time now.

Over time, I have learned quite a few little handy tricks doing keyword research that has helped me save countless hours… and many weeks!

I figured it was about time to share my little keyword journey and hopefully, you can leverage my wasted time and speed up your research.

Now, I am not going to spend time explaining some stuff, so if you haven’t done keyword research before you should definitely read the Backlinko guide, and then check out the ahrefs tools guide. Some of the post contains basic information, but much of it will only slightly touch on the basics so it’s best you read those guides beforehand.

 

Niche/topic planning

This is where I started back then, and it is where I start now.

The initial topic planning has barely changed for me over the years, as I am sure it hasn’t for many other people.

It does not matter how smart an automated tool is, it will never be as good as you just jotting down some ideas on a spreadsheet.

Back then, I used Excel. Now I use Google Sheets where possible. That’s pretty much the main difference for topic planning!

 

Google Sheets for niche & topic planning

The first part here is just jotting down all the ideas you can think of off the top of your head.

Each column should be separate subtopics, all sitting under a parent topic.

Using the video game niche as an example, this is what my planning would look like;

Google sheets niche research

This would be duplicated out based on every topic and sub-topic I can think of at the time.

The idea here isn’t to cover all the topics within the niche, it’s just to come up with what you can think of so you can build it out later.

So get your research started cleanly.

 

Finding additional topics by analysing competitors

I then try and find new topics along with variations of the existing topics that you think are worth finding sub keywords for.

Just start Googling all your topics and see if anything stands out, then either add it to an existing topic or create a new one.

You should be able to get a few variations just by looking at the SERPs, but jump into the competitor websites and check them too.

Pick up any similar keywords/namings to the topics you have, along with any topics you completely missed.

It doesn’t matter how well you think you know a niche, unknown sub-niches are just a few searches away!

 

What you’re looking for

The aim is to find the gems that an automated tool might not be able to pick up for you. With a secondary goal of starting to build out some top keywords.

Continuing with the gaming niche research as an example, we can take “ps4” and immediately see it’s also known as “playstation 4”. Whilst Google, and many tools, will see this as the same it is still worth jotting it down as the acronym and full spelling might not always be picked up as being the same.

Breaking out the ‘PS4 Consoles’ topic, I quickly come up with;

PS4 Consoles
Playstation 4 Consoles
Playstation 4 Slim
Playstation 4 Pro
Playstation 4 Bundle
Playstation 4 Deals
Preowned Playstation 4

It used to be a lot harder to get keywords like ‘playstation 4 pro’ out of ‘ps4 consoles’, but with the tools available these days it’s so much simpler.

The improvements Google has done on related searches, you can just search for ‘ps4 consoles’ and it’ll show ps4 pro right away;

Google related searches to keyword

But I will go into that in a bit.

Just make sure you get as many different variations of your topic as possible so that it is easier to leverage tools to expand on your seed keywords.

Expanding on topics and finding longer tail keywords

Once you have the initial topics mapped out, you need to start on expanding the keyword set for each of them. This will help you better understand the highest priority keywords so you know what to target on your website.

On top of this, you’ll be able to better understand the entire niche, rather than just a tiny keyword set, which will help with performance analysis and understand where you might be lacking.

 

Google Keyword Tool (now planner)

Finding keywords with Google Keyword Tool

In simpler times, the Google keyword tool was a real goto for my research.

For one, it gave great search volume data.

But its strength was the little “Match Types” selection on the left-hand side. You could actually get Google to come back with suggestions based on Broad / Phrase match, or just get the data for keywords you entered into the list with the exact match.

I would go through topic by topic, and add related keywords to a list, which could then be bulk exported keeping each keyword assigned to the list.

None of this range data, but especially none of the volumes for similar keywords being merged as they do now.

The newer Keyword planner is certainly not at the top of my list as a go-to anymore.

 

Google Autocomplete

Keyword research with Google autocomplete

Originally launched in 2004, Google autocomplete has been an invaluable resource for many SEOs for years.

Google literally gives you the longtail keywords that expand upon the keyword you’re searching for. Use them!

At first, just manually grab them. Do a few manual searches so you can see how it all works, then move into the automated tools to scrape it.

 

Google related searches

Just like Google autocomplete, the related searches offers so much value to SEOs.

Related searches used to be very similar to the autocomplete, and didn’t provide too much value.

Old google related keywords search

However, in 2009, Google added some extra intelligence to them and updated their design.

 

Updated google related searches

This was the first version of intelligence beyond the autocomplete and would be the start of many updates they have since made to it.

Took me a little while to get onto it, but they’ve proved invaluable since.

 

Keyword research tools to assist keyword generation

Over the years I have used quite a few tools to grab me the keywords.

Initially, I was rather cheap, so just stuck to the Google keyword tool. Took a bit longer, but at least I had somewhat quality keywords in there.

After a year or so I finally threw some money at a few tools to help with my research.

 

Scrapebox

Keyword scraper with Scrapebox

Scrapebox was the first research tool I grabbed back in 2009 with a nice little discount off BHW (Blackhatworld, back when it was cool… I think?).

Not initially for the keyword tool, but it soon proved rather useful at building out a nice list.

You can basically just plug a few seed keywords in there, select how many levels deep (awesome for super long keywords), and then click start!

The tool will use a variety of sources and grab you all the long tail keywords you ever need.

I used to just plug all my topics in one by one, along with any keywords I had already found for them, and then let scrapebox do its thing and generate my keywords.

However, there is one catch. You get A LOT of data. What you will need to do is seriously filter this stuff down.

Still a viable tool, and you can grab it here.

 

Market Samurai

Using market samurai for keyword discovery

 

I think Market Samurai was launched in 2008, but I grabbed it in 2010. This tool started to change how I looked at SEO.

It was the first tool I used that started to make me look at SEO a little bit deeper than just a few keywords and some automated link building (2010 SEO was fun!).

Market Samurai had me starting to spend more time on the keyword research, and I was looking at competition in a niche, rather than just trying to rank for anything.

The site still works, and I apparently still have access to the tool, not sure if it still works though. Better tools around these days.

 

Uber Suggest

 

Researching keywords with Ubersuggest

 

Uber suggest launched around late 2009 / early 2010, and quickly became rather useful for discovering new keywords.

Just like the other tools, you throw in a seed keyword and the tool will quickly spit out hundreds of longtail keywords for you to use.

Neil Patel recently-ish acquired Uber Suggest and has been making some updates that have only made the tool better, and at no cost.

I haven’t used the newer version too much, but can safely say I got plenty of value out of the old one!

Give the new tool a try here.

 

Longtail Pro

Longtail Pro was released back in 2011, and slotted itself in as a “new shiny” for me for a while.

I didn’t get massive amounts of value out of it just because I had a handful of other tools that were giving me what I needed at the time.

But who doesn’t like shiny new tech?

It has since been improved upon, grown in features, and can still help many people out.

The original creator sold the software a couple years ago but you can still pick up the software here.

 

SEMrush

SEMrush keyword research

I used SEMrush for years. It became a staple for me… until they started messing with their pricing and removing my API access.

I had a little Google Sheets setup that would hook in with the API and scrape me heaps of keywords & rankings. This let me do heaps of research fast and was within the credit counts that came with my basic subscription. They then removed the API access for this level and jacked up the prices.

Completely understand the business move, and I am sure it worked well for them, but just meant I needed an alternative.

Still worth checking out, as you may enjoy it more than alternatives. They’re still releasing new features you can jump on and you can still get some great value out of downloading the keywords for a domain / URL.

 

keywordtool.io

longtail keywords with keywordtool.io

Once SEMrush didn’t prove valuable for me any more, I turned to KeywordTool.io.

It quickly became my go-to research tool for a couple years due to its speed, simplicity, and filtering rules.

I used the pro version from around 2016 to early 2018, and just loved the fact I could have separate users & share access with outsourcers/mates.

Jump in here and let me know what you think if you haven’t used it before.

 

Ahrefs

It’s safe to say ahrefs is pretty epic. Epic in what you can get out of it, and epic in pricing too. Not recommended for early SEOs, but certainly should be on the list once you start getting work or making some money!

How do I get the value out of it for keyword research?

By stealing competitors keywords!

Keyword download with ahrefs

Just throw in a competitors domain, or the URL path directly related to your niche, and you get a massive list of keywords that you can start to filter through! Do it for the top 10-20 sites, and it’s pretty safe to say you’ll cover a significant chunk of the niche.

Worth a buy for just this, but only if you can actually afford it! Don’t raid your rent money for it. You can get by without it.

 

Keywords Everywhere

Related searches & volume with Keywords Everywhere

I think every SEO under the sun uses this free chrome extension now.

It isn’t a tool that I used very early on in it’s release, and have only just started using it properly, but whenever you Google something the tool will show you related searches on the right.

This significantly helps when expanding out your topics, and you can quickly export all the keywords and their volumes pretty easily.

Give it a crack here.

It does get a bit annoying through so I tend to turn it off regularly.

 

Keysearch

 

 

I have recently switched over to Keysearch for my research (grabbed a Black Friday special… so I am set for a year!).

Friendly interface, and fast keyword selection. I can just tick on the related keywords I like, and then save them to a little list.

A drawback here is each list needs to be exported separately. Takes a couple extra minutes to do that, then a couple minutes to re-merge, but I am happy with that for now.

This is ideal for generating an audited list of related keywords pretty fast, saving you on the cleanup later on.

Definitely worth it for newbies and more advanced users alike.

Give it a test run and jump into the tool here.

 

KeywordKeg

Using keysearch for keyword research

I only played with Keyword Keg for a little bit, since I grabbed Keysearch instead. Definitely one I will be keeping an eye on, as I could switch over at some time in the future.

For now, I am pretty happy using my current toolset.

Still worth having a play with Keyword Keg though, and you can check it out here.

It’s made by the guys that did Keywords Everywhere.

 

Getting the search volume

I have always used Google for my search volume.

The fact they’d allow an upload of 3,000 keywords via csv at a time and then offered a nice CSV export just had me sold from day one.

However, as many of you would know Google did this stupid thing of combining all the volumes for similar keywords.

keywoord search volumes for melbourne plumbers

So even though each of those 3 keywords would get searched a completely different amount of times, Google will now aggregate their volumes into a single one.

Which is an absolute pain if you use search volume for anything. You either need to use an alternate source or leverage one of the third party tools and their estimations if you want to separate out the volume for these keywords.

I personally prefer to try and remove all these similar keywords and just focus on the primary one, as that allows me to continue to use Google for the volumes.

Why? I use the search volume for a heap of different analysis & performance monitoring. You could still get away using this, but you will get inflated “estimated traffic” values, and some other metrics that might be more off than normal.

Google keyword planner upload

Unfortunately, Google killed off the 3,000 keyword limit upload via CSV in the latest planner, and also made it slightly easier uploading in that you can just paste a bulk keyword set in (used to be an 800 limit via pasting).

But there is always a catch!

The new keyword limit appears to currently be 2,500.

Still workable, just a pain.

Oh, and you need an active Adwords account to even get a somewhat accurate volume these days. So if you don’t have one of them, or can’t access one through a client, it’s probably best you look elsewhere for volumes.

 

Playing with large location-based keyword sets

Things really changed for me process wise when I jumped into agency life in 2013. Moving interstate and re-settling was nothing compared to changing from small business clients to some rather large businesses!

I went from helping a plumber to working with large international hotel chains.

I went from a home-based cupcake business to ranking a national car rental agency.

But most importantly, I went from a few hundred keywords, to keyword sets of 100,000+.

This forced me to rethink how I did SEO… and this is where the real time savers kick in!

 

Bulk generating keywords

When I was starting agency side, I could use Excel but I certainly didn’t have any sort of advanced skills.

I quickly saw how much time I could save with some of the processes, and began learning more about it.

The biggest thing I was using it for at first, was bulk generating keywords.

I had a handful of keywords, that I need to replicate across multiple locations.

 

How I started generating

So, before I played with Excel too much, I did a pretty manual thing to swap out locations in keywords.

I used to find/replace a location out of the keyword.

Find and replace location keywords

Hey, it worked and got the job done! Once I started playing with 10+ locations and larger keyword sets this got really annoying.

I needed a different way of doing this.

 

Along came concatenation (my version of it anyway)

Concatenation is basically just joining things together, but I started doing concatenation before I even knew what that meant.

Within Excel, you can use the ampersand (&) as a magical way to join things together.

You can also use a space between two quotations to insert a blank space into your formula.

By leveraging these two things, I was able to merge together different cells to generate my keywords.

Concatenating keywords in Excel

The formula for my above example is;

=$A3&” “&C$2

It allows me to tack on the location to the end of the keyword template, and create me the keywords I need.

The $ symbol locks in a cell reference. So by locking in the first column (the keyword ‘template’) and the 2nd row (the location), I am able to drag the formula down and across.

Excel actually has a formula to do this;

=CONCATENATE(text1, [text2], …)

To this day, I don’t know why you would use this over the ampersand though. The ampersand version is just so much more flexible, and easier to maintain as you build out your formulas.

Would love to hear of a usecase if you have one though!

 

Substitute yourself to local keywords

You might notice in my concatenation example I had “car hire” and “car rental” templates.

This is because I would like both ‘car hire sydney’ and ‘sydney car hire’, but to do this with concatenation you would need to modify the formula.

This is where some substitution magic comes in!

=SUBSTITUTE(text, old_text, new_text)

This formula quickly became how I generated location keywords and is still used in one form or another today!

It looks inside a piece of text, for specific text, then replaces that specific text with whatever you tell it to.

Instead of adding a template and location together, I used;

Car hire LOCATION

LOCATION car hire

And just swapped out the location for the location I wanted to use.

Substitution formula for keyword generation

The formula to swap the text LOCATION for the location is;

=SUBSTITUTE($A3,”LOCATION”,C$2)

And to take it one step further, you can even replace ‘car hire’ with SERVICE, and then add that to the substitution formula.

This could also be modified to suit any sort of keyword, so you could easily tack on modifiers if you wanted to. No reason you can’t substitute out 3,4, or 5+ words.

I made a video about it a couple years ago, so this might be a bit more useful 🙂

The issue when you start to do that is that you end up with too many different variations, and are just copying and pasting your formula all over the place.

Which is where I started learning about macros and how to repetitively do something I hated doing.

 

My excel keyword builder (magic macro)

Keyword builder in Excel

Created in 2014, this magical thing would cycle through keyword types, keywords, locations & even secondary locations, and bulk generated me a tonne of keyword variations.

The macro died off a little while ago, well something broke and I can’t be bothered fixing it, but saved me many hours.

Back when Google gave a good volume value, this worked best, as it let me easily identify the top search terms to target.

It sometimes took a little while to generate, and it was hard to get setup initially due to the macro.

So I needed a new plan.

 

IMforSMB Keyword Tool

I’d always wanted to learn a bit more about coding, and so in 2015, I decided to take my Excel tool and build out my own web tool.

Years of playing with WordPress gave me a decent enough understanding of HTML & PHP, so that was my obvious choice for this tool.

After about a week of hacking, I finally pulled something together.

IMforSMB Bulk Keyword Generator

This completely replaced the Excel generator for me.

Using the DIY Generator of the tool, I can now just paste in services & locations and click generate and the tool will spit out all my keywords.

IMforSMB search term generator

I threw in some templates services for people to use the builder if they’d rather, but my main focus here was just the simple generator so that I could let my Excel sheet die off.

You can check out the old bulk keyword generator here, however, this is going to get replaced in the future.

This tool started me thinking about a few things and got me working on my latest project.

 

SEOpp Keyword Generator

SEOpp keyword tool

After the IMforSMB tool started getting used, I figured it was time to build the tool out properly.

The first piece has just finally been launched with the SEOpp keyword generator now live.

The tool currently forces a business type selection, however, it will soon allow you to also enter custom services.

There is a big difference between this and the IMforSMB tool. SEOpp integrates with millions of locations, so you can generate a list of locations for your research!

You pick an initial location, a distance you service, and then it will spit out the locations for you to generate your keywords with.

Plenty more coming soon, so stay tuned!

 

Managing your keywords

It’s all well and good having a bajillion keywords laying around, but you need to be able to move them around manage them effectively.

I try and do as much of my work in a single Google Sheets or Excel File, depending on how large the data is and what I need to do.

 

Splitting CSV files of keywords

If you generated a list of 50,000 keywords, you’ll probably need to break it up if you want to run it through the Google keyword planner, or any other bulk upload tools.

Not many tools will accept an upload of 50k at once!

I started by manually doing this, 3,000 at a time for the planner. It is a tonne of copy/pasting and drove me mental.

Until I found the CSV splitter here.

Select a CSV, then enter how many rows for each file. You can tick on whether to have the header remain on each individual file or not as well.

This will spit out all the files in the same folder as where you have the large CSV, so if you’re splitting a heap of files just organise it accordingly beforehand.

 

Merging CSV files of keywords

If you’ve ever downloaded a heap of CSVs at once, you know how much of a pain it can be merging these all back together.

I too started by manually copy/pasting all the data one by one into a master set.

Mostly search volume exports, ranking data or competitor analysis, I had enough pretty quick and discovered a neat little CMD trick.

Mergine CSV files with CMD

How to merge CSV files together

  1. Open up the command prompt by typing CMD into windows search.
  2. Type ‘CD’ (change directory) and paste in the folder path for where all your CSV files are and hit enter
  3. Type ‘copy *.csv filename.csv’ and hit enter

If successful your merged file will be in the same directory where all your smaller files were stored.

You will be able to merge hundreds of files in seconds, so you can save quite a bit of time!

Not sure if this trick works on Mac, so feel free to give it a crack and let me know.

 

Bulk categorisation & classification of keywords

Categorising (or bucketing/classifying) keywords is a pretty big pain once you begin managing hundreds to thousands of keywords, let alone hundreds of thousands.

This is where Excel has really shined for me over the years.

I discovered a little magical formula that has allowed me to essentially create category ‘seed’ lists, and then bucket the keywords off the back of this.

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Category1[Find],$Keyword Cell Reference$),Category1[Category]),”Generic”)

Ignoring the super big number at the start (something to do with that being the largest row count Excel can do), the formula is actually really easy to use.

How to bulk classify keywords for SEO

  1. Create a table with two columns, ‘Find’, and ‘Category’, and name the table “Category1”
  2. Enter your ‘search’ words that you want to look for in the left column
  3. Enter your categories next to the search words, that will be given when a search word is found
  4. Enter the categorisation formula in a cell next to your keyword and update cell reference to point at it
  5. The formula should now categorise your keywords, based on what you entered

Bulk keyword categoriser & classifier

So essentially, it just looks for words (or parts of words) within your keyword, then categorises or classifies them appropriately off the back of that. If it doesn’t find something, it will just say ‘Generic’ instead, which you can update to something else in the formula. You can even use a cell reference, and customise it per category if you want.

No more match, lookup or vlookup formulas just looking for a single category at a time!

I don’t remember what it was originally used for, certainly wasn’t for SEO though! It was hidden away on some accounting forum, where SEOs will rarely go. Goes to show what you can find when you Google hard enough!

Another one I made a video for a couple years ago, which might explain the formula a little better.

I also created a keyword categorisation template you can download here.

It’s pretty useful for a few things, and I would love to hear what you do with it!

Among other things, I use it for dashboarding and keyword mapping to get them all assigned to the right URLs.

 

Leveraging categories for keyword to URL mapping

Once you’ve got your categories, you can throw them into a URL structure with a formula. There are quite a few variables here, and it sometimes comes down to personal choice as to how you’d like your URLs, but see below for a starting example.

Now, this isn’t how I would be grouping keywords & structure the URLs for this particular keyword set, but it still provides a good example.

Keyword to URL mapping in Excel example

=SUBSTITUTE(SUBSTITUTE(LOWER(“/”&[@[Category Type 1]]&”-“&[@[Category Type 2]]&”/”&[@[Category Type 3]]&”/”),” “,”-“),”generic/”,””)

The formula is taking the categories, and merging them all together, whilst following the standard practice of making everything lower case, and replacing spaces with dashes.

You’ll also see that I merged the first two category types together so they sat at the same level. Just for something different!

Then you just need to create separate pivot tables for each URL you’ve created, and you will have your URL > Keyword mapping.

Excel pivot tables with keyword mapping

You’ll then end up with a neatly mapped set of keywords, and some pretty tables that show the mapping.

You can event take it a step further and cross check this mapping against the actual rankings for the website, see what’s not ranking for the right page, then try to determine why.

But that’s for another time!

 

Building a keyword dashboard

By just leveraging pivot tables & slicers, you can create an interactive keyword dashboard. This could be for a client, or just to make it easier for you to see top keywords when you’re dealing with keyword lists of thousands.

For this one, probably best you just watch the video!

 

And that’s a wrap… for now.

I’d love to be able to break down the different bits here into mini-tutorials, so if there is anything, in particular, you want to find out more about, let me know in the comments below!