Just Another WordPress Programmatic SEO Build

Join me on a 30-day journey to a 1,000+ page programmatic SEO build using a combination of WordPress and Google Sheets (with plenty of formulas!).

I’ve been writing a heap about programmatic SEO lately…. if you haven’t noticed.

It’s mostly been about enterprise-level sites though. Portals, classifieds & marketplaces type stuff.

How do we apply this to more niche-type sites?

How can the ‘little guy’ leverage programmatic, without big budgets and customised dev builds?

Well, let’s find out.

Let’s build out a no-code (unless Google Sheets formulas count?) WordPress programmatic build.

Caveat: This may or may not actually rank for anything significant. I’d be expecting to pick up some long tail, but probably not too much traction due to significantly thin content. It’s a lot of work to do this more thoroughly, and since it’s just a proof of concept I’m really not spending that extra time, effort & money… especially when one of you will copy it straight away haha. This build is more to talk through some concepts, and share some techniques I use. If you wanted, you could then go off and build your own programmatic system in WordPress using this setup. Even copy it! Let’s see what you can do.

The idea

This tweet got shared with me the other day;

Here is an example of a keyword cluster that will help you gain topical authority:
1- Can rabbits eat grapes
2-can rabbits eat cucumber
3- can rabbits eat broccoli
4- can rabbits eat onions

All are about rabbits eating but different topics so you avoid Keyword cannibalism

— Aisha Preece (@AishaPreece) May 18, 2022

It’s not a niche I’d normally build something in, but, this is the perfect scenario for a niche programmatic build.

Probably also one of the largest non-location-centric keyword sets I have seen.

I also came across a post by Charles & Mushfiq from the website flip here discussing programmatic SEO and showing a quick WordPress example, leveraging an automatic integration with Zapier.

This got me thinking.

Not so much on the zapier side of things – that gets expensive!

But more so on the simplicity of the templating system, they’ve used along with the idea of a bulk WP import.

I want to build this system with a few rules in place, rather than just pure dynamic variable inserts.

A way to be able to pick different content templates based on different values, like yes/no for instance.

Probably going to end up turning into a behemoth of a mess in Google Sheets, but everything will hopefully have a purpose.

Let’s see what we can do.

The data

Before we do anything, we need to build out the data set.

Initial keyword research

I’m gonna start by just jumping into a keyword tool and grabbing a heap of keywords using the seed of ‘can eat’ as the tool will fill in the other sides.

I exclude a tonne of keywords, with a handful of phrases to get rid of some unrelated junk.

Exporting all these keywords, I throw them in Google sheets and now need to extract the animal and the food.

Variable extraction

The majority of the keywords follow the same template, so we can strip the ‘can’ and anything after ‘eat’.

To do this I am using the strip before/after text google sheets formulas from here.

I’ll first strip off everything after and including the “eat” by using this formula;

=LEFT(A2,FIND(“eat”,A2)-1)

After this, I can then substitute “can” out of the keyword, by modifying the formula to;

=substitute(LEFT(A2,FIND(“eat”,A2)-1),”can “,)

To then get the food, we will strip off everything including the eat to use what’s left, with the following formula;

=RIGHT(A2,LEN(A2)-FIND(“eat”,A2)-3)

Throwing these in a pivot table we get the top used animals and foods in the top 1,000 keywords from the keyword tool;

You’ll see some junk in there like “when babies” and “catholics” so I’ll just quickly copy the list and spend 2 minutes isolating the main animals.

After cleaning, I’m left with a list of 20 animals, and 72 foods;

If we created a page for each combo, we’d have 1,440 pages!

Pretty good start.

But that’s 1,440 combinations I need to work out whether the animal would eat the food.

Quite a bit of work to get the site started, especially when I don’t know what combos are worth it.

Prioritising data

Rather than just working through all 1,440 combinations to build out the data set, I’m going to try and prioritise the top combos.

Since we’re working off the same keyword template for all the keywords here, we can bulk generate the combos.

Following the template of ‘can <animal> eat <food>’ let’s throw the animals and foods in mergewords with “eat” in the middle.

We throw these in google sheets, and prepend “can ” by just doing the formula of;

=”can “&A2

Now I’ve got the keywords for all the combos sorted, I’m going to go grab some search volumes.

Throwing the keywords in the volume tool I use, we get;

A prioritised list of combos based on the search volume for the combination keyword.

We can now sort the combos based on this, and know which ones to focus on first.

Creating the base data set

Time to build out the actual dataset for the page.

I’ve thrown the keywords in a new sheet, along with their animal & food values;

Now for the part that will actually take some time, building out the actual values for each combo.

I’ve also included a few key elements that I think are valuable to have on the pages.

So we now need to fill out, at minimum, the ‘canEat’ variable value to ensure we can answer the key questions of “can <animal> eat <food>”.

This is going to be super tedious, but obviously outsourceable.

I’ve filled out the first few combos so that there is some sample data to build the rest of the site off.

Just googling each keyword and filling out the data based on info in the SERPs. The featured snippet and PAA are pretty useful here!

Don’t know the exact formats we want the data in yet, so best not to fill out much more, or outsource, until we know the formats the data will be in.

Basic SEO elements

I’ll work through the main SEO elements now, and bulk generate them for each combo.

Slug – Need to substitute the spaces for hyphens. No other funky characters so that’s it.

=substitute(LOWER(A2),” “,”-“)

h1 – Need to capitalise the words and throw a question mark on the end.

=PROPER(A2)&”?”

pageTitle – Just the H1 with the site name tacked on the end for now.

=M2&” | Something Something”

metaDescription – The h1 at the front, with some text along with the animal & food variables mixed in again.

=M2&” Find out whether “&D2&” should be eating “&E2&” right here. We’ve got the answer.”

After that, we get the following;

Building the core page content

Now that the basic pages are mapped out, and we have some sample data for it all, we need to look through the core content.

We can’t just straight insert the variables to answer the core page question, unless each variable included both the “Yes” and the part of “can” or “can not”.

I’d rather not have to have another data point, so instead, we will use content templates and dynamically generate the page content.

So if the value of <canEat> is “YES”, then use the “can eat” and if the value of <canEat> is “NO”, then use the text “can not eat”.

It’ll just start off like this:

But then we’ve got some other variables to include, so let’s build out a quick template for each of them.

(second canEat rule is meant to say “NO”, not “YES”)

It’s a bit boring, but it’ll do for a start and get the system build-out.

I can get a writer to err… fix my handy work later.

Time to put it all together.

I’ll try to keep this whole system as simple as possible, by building the template out for each sentence, and then just sticking each outputted sentence together.

I’ve created a new sheet to map this all out, with each variable being a new column. Probably don’t need to do all of this, and could reference the main table for the rules, but I figure this will be easier to troubleshoot.

All values are just vlookups so that there is one data source.

Using an IF formula, I’ve just worked through the 2 possible variations of this data, being YES or NO, and then outputted the associated content template.

=if(C2=’Content Templates’!$B$2, ‘Content Templates’!$C$2, IF(C2=’Content Templates’!$B$3,’ Content Templates’!$C$3,))

It correctly outputs the YES or NO content template.

Moving onto the howOftenContent, I ran into an issue with that formula.

I’ll want to add other options later and don’t want to tweak the formula each time.

A vlookup would work perfectly, but it only works on a single value and couldn’t match both the variable name and the variable content.

Adding a new column to the content template, allows us to combine the variable name (column header) & the variable value, and then match it to the combined value on the content templates.

=iferror(VLOOKUP($E$1&E2,‘ Content Templates’!C:D,2,0),)

The other ones are a little easier, for now, as we’re just using a single content template that inserts the variable if it exists.

=IF(istext(G2),’Content Templates’!$D$8,)

So now we’ve got all these templates, we need to replace the variables in them.

To do this, we use a nested substitute formula that swaps out the variable templates, for the actual variable value.

=substitute(SUBSTITUTE(F2, “<animal>”,$C2),”<food>”,$D2)

That formula can be pasted into all the contentFinal cells, and it will substitute out the <animal> and <food> giving you the base template.

We now get something like this for some of the other templates;

There are two issues with this one.

The first is that we need to add another substitution layer to swap out <quantity>.

=substitute(substitute( SUBSTITUTE(L2,”<animal>”,$C2), “<food>”,$D2),”<quantity>”,K2)

That would mean each variable needs to be manually replaced though, which can get annoying and doesn’t help to scale.

Let’s use the column heading instead, since that will match the variable.

=substitute(substitute(SUBSTITUTE( L2,”<animal>”,$C2),” <food>”,$D2),”<“&K$1 &”>”,K2)

It will output the exact same, except give us a copy & paste formula so that we can copy the cell, and paste it into the other contentFinal cells.

The other issue with this quantity content is that the ‘dogs’ is lower case. It’s at the front of the sentence though, so lets make it capitalised.

=substitute(substitute(SUBSTITUTE( L2,”<animal>”, proper($C2)), “<food>”,$D2),”<“&K$1&”>”,K2)

Note the proper($C2) within it this time.

The new output is;

Now we need to start piecing it all together.

Created a new column for it, and now reference a cell to include it in the text.

CHAR(10) will insert a new line in Google sheets, and allow us to format it so that we can see the new lines in the Google sheet.

We then have to extend it to add a part so that it only includes the text, and line break, when a sentence exists to ensure there aren’t blank entries & extra line breaks.

Whilst you’d normally use an ISTEXT() for this in Google Sheets, because we’re referencing cells that include formulas Google sheets will always see text.

We have to instead use if(LEN()>1).

=if(len(H2)>1,H2,)&if( len(K2)>1,CHAR(10)& CHAR(10)&K2,)&if(len(N2)>1, CHAR(10)& CHAR(10)&N2,)& if(len(Q2)>1,CHAR(10) &CHAR(10)&Q2,)& if(len(T2)>1, CHAR(10)&CHAR(10)&T2,) &if(len(W2)>1,CHAR(10)& CHAR(10)&W2,)

It looks like a heap of junk, but it’s actually just going cell by cell, with the same formula that uses different cell references.

If this cell has content, include it and add two line breaks before it.
If this cell has content, include it and add two line breaks before it.
If this cell has content, include it and add two line breaks before it.
If this cell has content, include it and add two line breaks before it.

That’s it.

The output is now;

Pretty cool!

If you try and read it though, you’ll notice that any part that includes one of the variables might seem a bit… off.

This is because we didn’t know how the variables were being used.

Some variables contain a full stop, leading to double-ups.

Others contain a dash at the front, or a line break, leading to a weird format.

Let’s fix that.

Tweaking the variable values

After the mess that gets created by the initial content generation, we need to come in and patch the variables and better understand how we want to use them.

This will involve tweaking the variables, but also tweaking the templates a bit to make the new formats work.

To do this, I am bringing the content we put together into the main sheet with a vlookup.

=VLOOKUP(A2,’Content Builder’!A:C,3,0)

Now that it’s on the main datasheet, we can start to go through and edit the variable values to set a format.

I’m standardising the formats a bit by just making sure whatever text here will cleanly fit inside a sentence. Non-capitalised to begin, no full-stop at the end, that sort of stuff.

It will make the variable data fit more naturally inside the content.

After cleaning up the variables a bit, and extending the templates to add some little subheaders, we now get;

The build

We’ve got the post title, and the post content that we want to use, so now we “just” need to get this into WordPress.

Creating our CSV

The blog post I mentioned earlier leveraged Zapier to do this. That’s expensive…. and we’re hacking this.

We’re gonna use a CSV import plugin called ‘WP Import Export’ to get this cranking.

I’ve done a quick WP install on a nice fresh domain, so will get a plugin installed to import and work out what we need.

A quick demo export to test the formats gives us;

We can see the sample post includes some HTML code, yet my test post (with classic editor) doesn’t include any of that.

I’m going to roll with the format without HTML code and see what happens.

Replicated the export format, and created a new export tab to paste in the new data.

Gotta paste the values here, and not formulas/formats etc to keep it as a nice clean CSV export.

Download this sheet as CSV by going FIle > Download.

Importing the CSV into WordPress

and importing the CSV by following the steps in the plugin, we get to set all the variables and assign them to each field.

So you assign the title to the post title, content to the post content, category to the category field, etc.

After a few steps, you get a nice little confirmation, and the posts go live.

Magical! We’ve got our first small set of…. posts.

Yes. They need some love.

Time to format the post a little, and re-upload the test data.

Have now formatted the subtitles in the content to export with <h2> tags, which looks like

When imported, these now give us;

Much better.

Tweaking the design

It definitely needed some clean-up, so I threw up a quick design.

Obviously not too fussed here, and might clean it up later. Might not, we’ll see.

Looks a bit better haha, can upgrade it later.

What’s been built so far

So, we’ve got a functioning website, with 5 pages of ‘content’… if that’s what you can call it.

More than that though, we’ve got an entire data structure and process created that once additional data points are added for each combination, additional pages are created.

In under a minute, all the data required for a new page could be collected! Pretty neat.

Yes, I will reveal the site shortly. The Google Sheet will also be shared once the build is complete.

What’s next?

Find out in part 2 of the build here.

Current Page Count: 6 pages