Category: Tools

Caching HTML in Cloudflare for Improved Page Load Speeds

Caching HTML in Cloudflare for Improved Page Load Speeds

Caching HTML can help you reduce the overall page load speed, as the CDN can hold your HTML rather than have to request it from the server each time.

There are pros and cons to doing this, but if you’re a static site that doesn’t change too often, caching your HTML could be the perfect solution to improving page load speeds a bit.

 

Does Cloudflare cache HTML?

Yes, however not by default. You need to enable it with the below steps to create a page rule.

 

How to cache HTML with Cloudflare

Caching HTML in Cloudflare is easy, so just follow these steps.

1. Open cloudflare, and click ‘page rules’ in the sidebar

2. Click ‘create page rule’ to the right of this page

3.  Insert your sites domain with a wild card on the end, just like the cloudflare example, and then select ‘Cache Level’ and ‘Cache Everything’ from the dropdowns.

And you’re done!

You should be able to run a test now, and compare to before HTML caching.

For me, here is my test.

Before HTML caching

After HTML caching

Yes, the site is already optimised, but a few key metrics were improved even further.

FCP dropped from 305ms to 112ms.

TTI dropped from 305ms to 112ms.

LCP dropped from 500ms to 444ms.

Not too bad at all!

 

Is it good or bad to cache HTML with Cloudflare?

It really depends. How static is your content?

If you have an extremely dynamic site, or even a news homepage style site, caching HTML might not be good for you.

Rather than a consumer constantly getting fresh content, they will get the HTML cache version until the cache is refreshed. This means they’ll only see the content available at the time of cache, and not anything that has been added or edited since.

Not just consumers, but Google too. We want Google to have the freshest content, so you might be holding yourself back by a day or two, along with annoying users, if you enable HTML caching.

However, if you’re site is extremely static, HTML caching can really help you knock off a few hundred milliseconds off some key core web vital numbers.

 

Use HTML caching wisely, and it can help you improve your page load speeds… provided you’re a good fit for it.

GTMetrix Review: Top SEO Insights You’ll Get

GTMetrix Review: Top SEO Insights You’ll Get

One tool I will use every single time that I audit a website is GTMetrix.

I’ve seen comments about it, and that other speed testers like webpagetest.org provide “better data” and “more insights”, but GTMetrix does everything I want and helps me solve my problems.

As with every tool, you take its automated insights with a grain of salt. You leverage them, to guide further insight gathering, or to back up a specific decision.

GTMetrix gives me the data to pass on to dev teams, and help get issues patched.

 

Running a Speed Test with GTMetrix

It’s pretty simple to run a speed test in GTMetric.

  1. Enter the URL you’d like to test.
  2. Change the location you’d like the speed test run from. Highly recommend you get this as close to your audience as possible.
  3. (OPTIONAL) Change the browser you’d like to test from. This is where you can also select a mobile browser if you’d like to run a mobile speed test.
  4. (OPTIONAL) Select the speed if you’d like to throttle the speed test. Throttling can help show more “true to life” bottle necks, like with a poor mobile connection, but will also help smaller issues show up easier as everything will get exacerbated.
  5. (PRO REQUIRED) Ensure you have the video test flicked on, if you’re a pro user. You’ll get a bit more useful insight.
  6. Click Analyze, and run your website speed test.

How to run a speed test with GTMetrix

 

How to interpret GTMetrix’s waterfall chart

The waterfall chart breaks down the exact points at when different resources are called, connected to, and downloaded.

Each resource is ordered based on when its loading starts.

All you have to ever really worry about here is when a specific resource is connected to, and when it finishes being downloaded. There are very few use cases you’ll run into as an SEO where anything in between is required.

GTMetrix waterfall chart example

Look through what items are being loaded and when, and then run through the standard checks from here to optimise.

Large files being loaded? Are specific requests taking too long? Too many files? External requests you didn’t know about?

Plenty of things to analyse here, but they’re very specific to each audit.

Each significant request stage is broken down by a coloured line. The following is what stage each of the coloured lines in the waterfall chart represent;

GTMetrix waterfall chart legend

You can find some more info on the waterfall chart from GTMetrix directly, here.

 

Page Load Video by GTMetrix

One of my favourite features, particular to help better identify CLS issues,

Unfortunately, it’s a pro-only feature.

In saying that, it’s worth it to help out with these audits.

When running your original test, you can tick on video audit.

Or, when viewing an audit you can click ‘enable video and re-test’ and GTMetrix will re-run the test, including the video test this time.

Video of pagespeed test

Here’s an example video output, from the test I ran above of SammySEO.com

You can play/pause the video, or run it at 1x, 1/2x, or 1/4x speed.

 

Testing Core Web Vitals with GTMetrix

When you run a GTMetrix test, you get a basic overview of your core vitals.

Core web vitals testing on GTMetrix

You can extract a little more information from the waterfall chart, and a few other places in GTMetrix, but this overview can help you delve into each specific CWV separately.

 

GTMetrix Alternatives

So, if this isn’t the tool for you, what other options are there?

The top few that come to mind are;

 

Is GTMetrix pro worth it?

Yes, I believe GTMetrix pro is worth it. Well, for me anyway.

If you have an alternate speed tester you’re using, then it probably wouldn’t be.

Other tools have similar features, I have just used GTMetrix for years now, so have gotten used to it and understand what to look at a bit more than the other tools.

Cumulative Layout Shift (CLS) – Causes, Testing & Fixes

Cumulative Layout Shift (CLS) – Causes, Testing & Fixes

CLS (Cumulative Layout Shift) is one of the fun ones inside Core Web Vitals (CWV), and can sometimes be a bit annoying to find exact causes.

Or even exact locations it happens on, as Google can be a little light on the examples sometimes.

What is Cumulative Layout Shift (CLS)?

Cumulative Layout Shift is a score assigned to a page based upon how much the page changes/moves around between the initial render and the final load.

It’s about assets loading in, and then whilst the page continues loading, others load in and push the original content around.

Long story short, it’s when a website will annoying shift just as you’re about to click a button, and then you end up clicking the wrong thing because everything moved.

 

Does CLS affect SEO?

Yes, CLS very much affects SEO.

It is one of the main elements that make up the Core Web Vitals, which Google is now taking into account as a ranking factor.

Yeah, its one of hundreds of different ranking factors, but when you’re talking about an apples-to-apples comparison with a competitor, I would very much rather know that I have ticked as many boxes as possible to help me rank a site.

 

Identifying that you have CLS issues

Your first point of call to check for CLS issues, or where you might have spotted them initially, would be in Google Search Console.

On the “Core Web Vitals” tab in GSC, you’ll see some pretty charts that show how you’re doing across your URLs, for both desktop and mobile.

If you then click through to one of the reports, you’ll get a list of the issues that make up the Yellow or Red lines.

If one of them looks like the one below, you’ve got CLS issues;

Clicking on this, Google might give you a couple of URLs examples. Chances are though, it’ll just be a single one, even for hundreds or thousands of URLs.

Google might be saying they’re “Similar” pages, but sometimes they will group completely separate page types in here so don’t fall for their trap.

Now that you’ve identified you’ve got an issue, you need to actually find the root causes of this.

 

Isolating specific CLS issues by testing CLS

There are a couple of ways of isolating the CLS issues so that you can make a specific request with developers for a patch.

If you just go to them saying “fix CLS” they’ll either go in circles or call you crazy because “everything works fine”.

 

Testing CLS with GTMetrix

The first method I use is with GTMetrix. A super quick test, and it’s normally something I am running anyway, so can give a good initial overview.

Run your test, and then you’ll get a web vitals to score like the below;

CLS will flag on the right. For this one, green is fine, but it’s enough to use as this example.

This score will probably be different to what Google is flagging, but it’s not about the actual score. It’s about what’s causing that score, so that you can isolate and patch.

If you go to the ‘structure’ tab, you can then expand the ‘avoid large layout shifts’ section, and GTMetrix will break down the main causes for your CLS score.

GTMetrics flags the main offender here, which contributes 99% of the CLS issue.

Funnily enough, this test was run on a webpage talking about CLS here as I was looking for an example site. Definitely a case of, “do what I say and not what I do”. The post is still worth a read though.

In saying that, we can break down this CLS further by just loading the page.

Click that page above, and see if anything loads and then shifts around.

If your internet is fast, you might not notice it.

I used the GTMetrix video reports, so that I can show devs step-by-step what is happening in the load, and help them troubleshoot.

They are loading in the entire content, which pauses for 0.2 of a second, and then loads the image. This image load pushes all the content down.

Google is seeing this massive shift, and would be assigning it a rather high CLS score because of it.

Super easy to fix though!

 

Testing CLS with the CLS Checker Chrome extension

Firstly, just download the CLS Checker Chrome extension from here.

Using the same site as the GTMetrix test, you just need to load the page, then click the extension and click ‘check the page’.

It flags 2, with the first one possibly being related to the bigger one anyway;

 

If you click on toggle, it will make everything white, red and green.

White hasn’t moved, red is the original location, and green is the new location.

Sometimes a few different CLS issues will get grouped together here, so just be careful that a score the tool flags isn’t an aggregate view of about 3-4 different issues.

So this chrome extension is flagging that something has happened in that big red zone, which has pushed all the content down.

Safe to assume what has caused this based on the screenshot, but combine this with the GTMetrics video and you can really drill into what’s going on with CLS.

 

Testing CLS tester with Webvitals.Dev

This one is a tool I discovered recently, and whilst it’s useful to add to the mix it’s not really anything that GTMetrics or the chrome extension don’t cover.

It does bits from both of them, in the single tool though, so might be useful if you’re after a more consolidated view that you can send directly to the development team.

They do include a cool little GIF of what’s moving around on mobile though! Worth checking out, as it might be what you’re after.

 

Testing CLS with WebPageTest

If your preference is webpagetest, then you can also check CLS issues in there.

Once you’ve run the test, click on the “view” drop-down and navigate to ‘web vitals’.

 

You can then scroll down and you will see the CLS issues.

Here you can also view a filmstrip, but more importantly, also view the video that’s now included.

The same as how gtmetrix helps with the video, webpagetest can now help you with your CLS issues with their video report too.

 

How to fix cumulative layout shift

You normally need a dev team to fix CLS issues, so if that’s not the answer you’re expecting – this is awkward…

In saying that, CLS issues are pretty easy to get fixed, once you’ve identified them.

All the developers need to do is make sure that elements don’t move. They get them to not move, but ensure they load in their final position.

This means they need to set fixed heights for elements that have delayed loading, like images.

If an image is going to be 300px high, then make sure there’s a blank space of 300px (plus padding) to fix that image when it loads.

This ensures nothing will move when the image finally loads in.

Steps to fixing CLS issues

  1. Break down every individual item that you think is attributing to the CLS score based on the CLS testing above
  2. Show the issues to developers, along with with some pretty pictures, so they better understand
  3. Politely ask them to fix it
  4. Deliver cake in the hope it speeds up delivery

 

It really is that simple.

Your CLS issues should now be gone!

Injecting HTML Code “Server-Side” with Cloudflare

Injecting HTML Code “Server-Side” with Cloudflare

I’ve got an older site with a good amount of link value I want to add some links to for some newer sites.

Problem is, the site is on 4 year old tech, and hasn’t been updated in 3 years.

Dev is long gone. If I try run a build, there’s a 90% I’ll break it completely.

I’ve since rebuilt the tech, but can’t move the old site over because…. bloat.

Running Cloudflare, I quickly looked into options and might have found a solution!

 

Injecting HTML with the Add HTML Cloudflare App

This was the initial solution I found, and I was super excited. Took 2 minutes, and I had a link on the old site!

However, the darn thing was client-side.

If that suits you though, this is how you can inject HTML with the ‘Add HTML‘ Cloudflare app.

 

1. Load up a Cloudflare website, and click on ‘Apps’ in the sidebar menu

2. Search for ‘add html’ and click the app

 

3. Click ‘preview on your site’ to load a preview

 

4. Select ‘pick a location’ and a little selection editor will load. If you want to inject into the head, you can just enter head.

 

5. Click on your website preview where you’d like to inject the code. I’m selecting the first post in the list.

 

6. Select from the dropdown where specifically you’d like the code inject. I want to inject the code before this first post, so I am going to select the before option.

7. (OPTIONAL) Select the location you’d like to use this, if not the homepage. Manually add the URL, then reload the list. You might need to do this a few times until it shows, but eventually you can then find the URL and press the little tick on the left.

 

8. Enter the HTML code you’d like injected, and it will display in the preview on the right.

9. Click install and boom, you’ve got some HTML magically injected.

 

As mentioned before though, it’s unfortunately client-side code, and won’t load with JS disabled or show anything in the HTML source of the page 🙁

 

Injecting HTML code with Cloudflare Workers

This took me a bit longer than it should have to work out, because my coding skills are lacking a bit.

However, from the limited examples of direct use of this, I managed to piece together what I needed.

A Cloudflare worker basically is just a little script, that runs on Cloudflare between your server and your end-user. Cloudflare grabs the page from the server, executes your script, then sends it off to the user. Super powerful, when you know how it works.

Hopefully, this simple breakdown of actually using a worker to inject HTML can help you out too!

 

1. Go to workers in the Cloudflare dashboard, and click ‘create a service’

 

2. Insert a useful name, click ‘HTTP Handler’ and click ‘create service’

 

 

3. Click on ‘quick edit’ in the top right corner

 

4. Navigate to the page you’d like to edit, and then paste the following code;

 

class ElementHandler {
element(element) {
element.append(`*HTML TO INJECT*`, {html: true});
console.log(“injected”);
}
}
async function handleRequest(req) {
const res = await fetch(req)
return new HTMLRewriter().on(“*CSS SELECTOR*”, new ElementHandler()).transform(res)
return res;
}

addEventListener(“fetch”, (event) => {
event.respondWith(
handleRequest(event.request).catch(
(err) => new Response(err.stack, { status: 500 })
)
);
});

* You might need to edit the quotes due to WordPress formatting. Sorry!

 

5. Edit the *HTML TO INJECT* variable with what you’d like to inject. This could be plain text, or any full HTML.

 

6. Find your selector, and at modify the *CSS SELECTOR* variable. To get your selector, right click anywhere on your page, and click ‘inspect element’. Then right-click the element, copy, then click ‘selector’. You just paste this into the *CSS SELECTOR* box and you’re good to go.

 

7. The preview should now update to inject your HTML into the spot specified. There could be some issues with this, but provided you’ve follow the steps it should put it exactly where you specified. This is a bit newer to me, so comment with any issues and I can try help out!

8. Click ‘save and deploy’ to get the worker saved

 

 

9. Go back to the cloudflare website you’d like this added to, and navigate to workers again, then click on ‘add route’.

 

10. In the modal that pops up, edit the location you’d like the worker run at, along with select the worker, and then just select production, and click save. I only want this on the homepage, so I’ll modify the screenshot to remove the wildcards (*).

 

11. The route should now be loaded in the account, with the worker selected.

 

 

12. Go test the site! It should be live on the pages you selected, and it should load server-side!

 

You can confirm it’s server-side by viewing the page source, and then just searching for the code you added.

Perfect. Just what I wanted!

Just make sure you’re putting the worker in the right spot, if its not a unique piece of code you’re adding.

If a div is available across the site, and you set your path in the route to be the whole site, then that piece of code will show up everywhere.

Great if that’s what you want, not so great if its not what you want!

 

How does this magical HTML insertion with Cloudflare workers work?

This all uses the Cloudflare HTMLRewriter class, with a heap of documentation here.

There’s essentially an audible woosh as this all flys over my head.

 

The options for the HTMLRewriter class

Whilst I don’t understand most of this, yet, I can tell you the following important options.

Inside the code you have;

element.append(`*HTML TO INJECT*`, {html: true});

Instead of ‘append’, you have a heap of different other options here that you can use.

removeAttribute(name) – Removes the attribute.

before(content, contentOptions) – Inserts content before the element.

after(content, contentOptions) – Inserts content right after the element.

prepend(content, contentOptions) – Inserts content right after the start tag of the element.

append(content, contentOptions) – Inserts content right before the end tag of the element.

replace(content, contentOptions) – Removes the element and inserts content in place of it.

setInnerContent(content, contentOptions) – Replaces content of the element.

remove() – Removes the element with all its content.

So just tweak this to how you want to use it.

Cheers Cloudflare!

 

Adding Links with Cloudflare

Using the same code, we drop a link wherever you want it!

For this code;

element.append(`*HTML TO INJECT*`, {html: true});

You would want to modify it to be something like;

element.append(`<a href=”https://www.sammyseo.com/”>Best SEO Blog Ever</a>`, {html: true});

And then just find the best location selector you can that will suit. ‘before’, ‘after’, ‘prepend’ or ‘append’ would probably be your best options to get the link inserted.

 

Great way to insert HTML into somewhere you can’t get access to!

Would love to hear if you use it, and if so, how!

Cloudflare Crawler Hints & IndexNow

Cloudflare Crawler Hints & IndexNow

Using Crawler Hints in Google, you can add an additional way to tell search engines about your new URLs and content changes.

Yeah, Google doesn’t support it for now. But you never know! That could change.

 

What is Crawler Hints in Cloudflare?

Crawler hints is an inbuilt function in Cloudflare, launched in 2021, that offers a new ability to ping search engines about your new URLs.

Crawler Hints provide high quality data to search engine crawlers on when content has been changed on sites using Cloudflare, allowing them to precisely time their crawling, avoid wasteful crawls, and generally reduce resource consumption of customer origins, crawler infrastructure, and Cloudflare infrastructure in the process

Rather than just constantly pinging about URLs, and wasting server resources, Cloudflare can monitor for actual changes and only tell them when the content is worth re-indexing after a change.

Shortly after launching, Cloudflare enabled IndexNow inside Crawler Hints,

 

What is IndexNow?

IndexNow is a new way to pinging search engines about your content. Much like the old ‘WordPress’ ping system, or even similar to submitting a sitemap, IndexNow pings supported search engines your URLs to help them keep updated.

In its simplest form, IndexNow is a simple ping so that search engines know that a URL and its content has been added, updated, or deleted, allowing search engines to quickly reflect this change in their search results.

It’s nothing super-advanced, but any bit helps when it comes pinging search engines your new URLs.

 

How to use IndexNow on Cloudflare

  1. Go to Caching > Configuration in the sidebar;

cloudflare configuration menu

2. Enable tickbox under Crawler Hints section

enabling crawler hints

It’s that easy.

 

Is IndexNow Worth Using?

Well, it takes 2 minutes to turn on with Cloudflare and its free. So yes, IndexNow is worth using.

Does IndexNow actually make a difference?

That’s what we’re yet to find out. But even if its only Bing and some other search engines using it, its free so its worth just throwing on. You never know.

Does Google Support IndexNow?

No, Google does not currently support the IndexNow protocols. However, Google has shown interest so they may support it in the future.

 

So, for 2 minutes of work you can hopefully improve your sites indexation in Tier 2 search engines.

Top Ranking Domains Analysis for a Keyword Set

Top Ranking Domains Analysis for a Keyword Set

Ever wanted to see the top-ranking domains / URLs for a particular keyword set?

This analysis will let you do exactly that, and give you more refined performance data, allowing you to dig into the true top performers.

There are a few tools out there that let you see the top competitors and their estimated traffic.

However, many of these tools fail to give good insight into category-specific performance. This holds you back from being able to extra true competitive performance insights, as you’re only seeing the wider picture.

You’re missing out on the drilled-in numbers

Watching the video above, you’ll learn how to build out your own analysis, using a combination of Excel & SerpRobot.

 

Steps to extract the top-ranking domains

  1. Export SERPs data from SerpRobot
  2. Format the data appropriately in Excel
  3. Extract the domains from the ranking URLs
  4. Run estimated traffic formula based on ranking & search volume
  5. Categorise the keywords into required categories
  6. Pivot the data based on the domains, with slicers/filters for categories

 

And there you will have it. Your own analysis, with estimated traffic figures at the category level, so that you can get the direct competitor insights you’re after.

Generate Product Descriptions from Specs with AI

Generate Product Descriptions from Specs with AI

AI content is taking SEO by storm. Some good, some, well, not so good.

Whilst it might spit out some junk when not guided, if you give some of the AI content generators a little bit to work with then their output can be alright sometimes.

Particularly when it comes to product descriptions.

Everyone knows that you shouldn’t just use the stock-standard description by the products creator. So how about leveraging AI a little bit to generate something unique?

We can take the specs of the product, and do exactly that.

Now, the large caveat with this is you need products with a few different specs that the generator can do. This works excellent with tech products. and a few other categories.

 

Generating a list of specs in Google sheets

Before we can use an AI Content generate to generate a product description, we need to get a list of specs that can be inputted.

Most people will have a list of specs for each product, in a CSV or database. We will essentially take them, and then create a list of all the available features.

I’ve created a Google sheets here that you can just duplicate to get started.

We’ll be using the following formula;

=”Name: “&A2& CHAR(10) &if(istext(C2),”Brand: “&C2& CHAR(10),)

It goes column to column, and adds the product spec to a list if it exists. Essentially just concatenates them.

Each product spec will be added with a line break, if it exists. This means that some product specs might not exist, and will be excluded from this single cell.

The end result is all the product specs in a single cell.

This will make it easier to use going forward, ensuring you can create a product description from the specs list.

 

Generate an AI product description from a product specs list

Using this outputted list of specs, we can throw it into an AI content generate and have that generate a product description for us.

You can use the ‘generator’ sheet to keep things a bit cleaner. It’ll mean you’re not working on the same sheet as the specs list.

There are a few different pieces of AI software we can use for this, but my preference is WriteSonic.

Once you’re signed up you’ll see a heap of options on the dashboard, but just click the ‘Product Descriptions’ option.

This will load up the description generation, where you enter the product name, and paste the automated specs in.

Once it’s all entered, click Generate.

You’l be presented with 5 different descriptions to choose form

The descriptions will vary in quality, along with also vary in what specs they pull in, so pick the highest quality one and paste it back into the sheet on the ‘generator’ tab.

You’ll also find them pull in specs that you didn’t give it, as the AI will do its thing and grab them from other sources as it writes the description.

The more you give it though, the more of your own specs it’ll use.

As an example, I never gave it the mAh battery capacity, only wh capacity, so it has pulled that in, along with some other specs, itself.

You’ll want to double check all of these, as it can sometimes pull in incorrect specs.

 

Generate for each product

Just work through your product list, and copy paste each automated list into WriteSonic and generate a product description for it.

You can even outsource this bit to a VA, as all they will need is a little bit of product knowledge (ideally, but not required) and a decent English level.

You can merge a couple descriptions together too, if you’d like to extend a little bit.

 

Expand the product descriptions

You can even expand these descriptions, using the sentence expander tool from WriteSonic.

Just paste in your original description, and let writesonic attempt to write some extensions.

 

Using your descriptions

You can use these descriptions for whatever you want, whether it’s website copy, email, or social shares.

They might not be as good as a quality writer, but they’re performing better than just a product specs lists for me. Hopefully they work well for you!

Merging Multiple CSV Files Together with Windows Command Prompt (CMD)

Merging Multiple CSV Files Together with Windows Command Prompt (CMD)

Doing keyword research, and have a million CSV files that you need to extract data from?

Well, this is where a handy, and rather tiny, SEO hacks that has saved me plenty of time over the years comes in.

The ability to merge as many CSVs as you want into a single monster file, allowing you to then just dedupe it and use it how you need.

Built into Windows, the command prompt is not something the average user will open up.

I’ll show you how to open it, and most importantly, how to merge all your csv files together.

 

Opening the windows command prompt

You open the command prompt by;

  1. Clicking start/windows in the bottom left corner
  2. Typing ‘CMD’ (just type it, don’t need to press anything)
  3. Clicking on ‘Command Prompt’

You’ll be presented with a blank command prompt, that will default to your user directory and look similar to the below;

 

Change the directory to the location of your CSV files

The next step is to change the directory in CMD, to where you have your CSV files stored.

To do this, you type CD, and then the folder location.

So for me, I will be typing in;

CD C:\Users\sam\Box Sync\Clients\Sample CSV files

And then hitting enter, and it will look like the below;

CMD has successfully changed the location to where you have your CSV files.

If you don’t know the location of this, you can just browse through your folders and then click in the top of the screen.

Browsing to my folder, I will see;

You then click in the bar at the top where I have highlighted, and you will be able to copy the folder location, like this;

 

Merging your CSV files

Now that you’ve updated the location, you can merge your CSV files.

To merge the files, you need to type copy *.csv filename.csv

the wildcard *.csv will select all the csv files in the folder, and the filename can be anything you want.

So for me, I will type the following;

copy *.csv all-keywords.csv

The command prompt will then run through and merge them all, and output a new csv file based on the name you gave it.

Depending on how many files, and how big they are, will depend on how long this takes to run.

For my small batch, it took 2 seconds. Even with a large one it shouldn’t take more than 10-15 seconds to run though.

 

Deduplicating your new CSV

After you’ve merged your files, you will just need to open the csv, and dedupe them. You could do this in Excel, or open it in Google docs.

  1. Select the column of keywords
  2. Click on ‘remove duplicates’ under the data tab
  3. Ensure ‘expand the selection’ is selected, and then click ‘remove duplicates’
  4. Click ‘unselect all’ and then tick on the keyword column only so that it only dedupes the keywords

Excel will then duplicate all the keywords, and you can then throw them into google sheets or whatever you use to manage your keywords, or look at the data.

 

Successfully merged your CSV files

And that is how you merge multiple CSV files together with the Windows command prompt.

Couldn’t be easier!

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 the link below, 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.