Safe to say this build is progressing pretty fast thanks to sheets & WP.

A custom build like this would normally take quite a bit longer! And that would be on data configuration alone.

Being able to format stuff and quickly make mods in G-sheets makes things sooooo much easier.

If you haven’t read the other parts, you can find part 1 of the WordPress Programmatic SEO build and then work your way up to this one.

Let’s continue.

Linking widget tables

A few of the pages are starting to get some serious link counts, particularly the category pages.

Yeap, a few links.

Definitely a few more than what a normal comma list would contain.

But, we need these links here. We can’t just strip them from the page.

A standard programmatic implementation, particularly in the early phases, is an amazing-looking table.

One of those linking widgets that could have 10 or 100 links in. They work though, so don’t talk negatively about them!

I figure we can look at the link count, and if it’s above say, 10, we swap it out for a pretty table instead.

I grabbed some default HTML table code from here and slapped it into one of the category pages to test it.

Boom. Table.

I’m not counting this copypasta as code. If it was, then HTML links are code too yeah?

Updated the data to some sample data and some links to see what it’d look like, and threw in a centring style, and it isn’t too bad at all.

Since there will be quite a few links though, there’s a bit too much padding.

Fixed.

Now we need to work out how to actually get this going in the Google sheet.

We have a comma separate list of links that we currently use in the page, but how can we turn this into a table.

We need some way to split that list up into groups of 3 for a 3-column table.

I got stuck here for a bit, but then did some Googling and came across this magical formula.

We can just replace every 3rd comma with the row code (<tr>), and then every other comma with the table column code (<td>).

After more tweaks than I care to admit, I pieced together the below;

We start by including the table opening code, then the comma separated that breaks up with every 3rd comma being swapped for the </tr><tr> portion of the table code.

Then we substitute the remaining commas with the <td style> code.

Then throw the table closing code on the end, and we’ve got ourselves a nice link widget.

Well, nice is debatable.

For those that might want it, the formula for a table from a comma-separated link list is;

=substitute(substitute( “<table style=””text-align: center””>
<tbody>
<tr><td style=””padding: 0px””>”&REGEXREPLACE( REGEXREPLACE(L2,”(\,.*?){3}”,”$0*”) ,”\,\*”,”</td>
</tr>
<tr>
<td style=””padding: 0px”” >”),”,”,”</td>
<td style=””padding: 0px”” >”),”<animal>”,A2)&”</td>
</tr>
</tbody>
</table>

*ignore my styling, and formatting, and well….. just anything I do that has code involved.

Throwing it into the site to test we can see it works. Works = nice.

Obviously quite a bit bigger than the comma list, so not 100% sure whether this could be better or not.

However, it gives us another option.

Adding a little extra code could give it a little expander option but that’s beyond what I’d want to do now so I’ll skip that.

Think I will just completely skip including this and just shelve the table code. Will keep it in the spreadsheet and maybe return it later.

If I was going to use it though, I’d be going to the content formula that includes the comma-separated list.

Then using a formula, I’d run a count formula that when the comma count is below say 10, the comma-separated link list could be used. If it was higher than 10 it would pull in the table links cell.

Scraping videos

To build out the content even more, we could embed some Youtube videos.

Ideally, you have your own set of videos to mix up the content.

I don’t.

I’m just going to scrape a heap of youtube videos and embed whatever video currently ranks for the combo keyword.

This used to actually work a treat to help a page rank, and I have seen some benefits recently on a few of my sites, however, this is purely to build out the example content and show you how to leverage it.

To do this, we’re going to come up with a heap of keywords to put into a rank tracker (serprobot). We’ll then export the top10 SERPs, remove everything not in the first place, and then remove any non-youtube links.

Anything left over will be included as a video in the pages.

I’ve loaded up a project in serprobot looking for ‘<combo> youtube video’ keywords.

After a few minutes, it’ll complete the rank check, and I’ll be able to export my SERPs report.

I’ll just insert two rows in the keyword column here so the keyword & URLs line up, and then substitute out “keyword” along with sort the list.

Sorting it by URL, we can then delete anything non-youtube, and then delete 2nd position onwards so we end up with a keyword | youtube list like this;

Strip out the ‘ youtube video’ part from the keyword, and we’re back to the combo name, then throw it back into Google sheets and we’ve got a list of 1,000 youtube URLs with their respective combo.

I’ve added a <youtube> into the content template where I’d like to throw the video in.

We now need to craft the youtube embed code, and get that into a column.

The code I’ll be using is this;

=”<p style=””text-align: center;””><iframe frameborder=””0″” scrolling=””no”” marginheight=””0″” marginwidth=””0″”width=””712″” height=””400″” type=””text/html”” src=”””&substitute( VLOOKUP(A2, Videos!A:B,2,0), “watch?v=”,”embed/” )&”?autoplay=0 &fs=0&iv_load_policy=3 &showinfo=0&rel=0 &cc_load_policy=0 &start=0 &end=0″”><div> </iframe></p>”

Which yes, will look crazy, but, it’s a standard Youtube embed code. This then has the vlookup inside it that goes off and grabs the combo’s youtube video, and then replaces the ‘watch?v=’ with /embed/ so that the embed works.

This then gets substituted out in the intro content section.

Re-uploading the data and we get…

Fancy videos!

Well, kind of fancy. Whatever Google ranked for the keyword, that’s what gets included.

Some are better than others.

Homepage update

The homepage is still looking a little sad with just a short list of posts.

We need to get this updated with some content.

Possibly links out to the animals with a pretty picture, and a heap of text-centric content to pad it out a bit.

I’d love to set up a bannerbear template for this, and be able to generate a text-over-image thumbnail for each category.

That’s pretty and ideal though, so let’s just go with the functional solution for now.

Also probably don’t need to try and think programmatically about the homepage…. can try something a bit manual.

Bit of text, and some chonky links added in.

Much better.

Well, somewhat better at least anyway.

I LIKE IT ALRIGHT.

Additional content sections

Another big part of a programmatic SEO builds’ content is what is essentially content re-use.

This content re-use primarily occurs on search result/aggregation type pages, where you list out and link to the listings/specific content.

Where these links are included, portions of the content are also included, and that pads out the content on these aggregation pages.

You’ll also find this type of content on sites like G2 & Capterra. Each software has its own page, but then they take a chunk of that text and list it out on the ‘best software for x’ type pages.

Think of this sort of content more like a big block of ignored text. It won’t penalise a page, but it might not add direct value when used alone. It needs to be combined with other elements, and is just used to support those other elements.

This build is a bit different, and not at such a scale. Our re-use chunks might be a bit over-used, when looking at the total percentage of pages with a piece of text.

Oh well though, it’s just a concept build anyway.

Let’s see what we can apply to the build!

Animal-based content paragraph

We’re going to write up a custom dynamic element for each animal, and try to feed in a food variable where we can, but more so focus purely on the animal itself.

Something like the general nutrition habits of the animal in question. Maybe what they normally eat, and what their bodies require.

I’ve leveraged OpenAI to come up with generic garbage about each animal;

I’ve then going to get this included in the combo page’s outputs by adding an <animalGeneric> into the conclusion content template, and then substituting it out.

Essentially just a pure little dupe content section, but mixed in with the rest of the content it shouldn’t be tooooo bad, we’ll see.

Might update it to add a dynamic element to it later, or split it into 2 sections and include them in separate parts of the page content.

Food-based content paragraph

We’re going to do a similar thing to what we did with the animals, and add a section purely about the food.

Probably about their nutrition value, but will really just use whatever OpenAI spits out for us.

Created a list of all the foods, ran them through OpenAI with a little prompt, and received a heap of text;

I should edit it, but nah. Let’s just roll with that. Might get an editor to just tweak it later.

Leveraging these additional sections

I’ll throw both of these new sections in the footer section of the combo pages.

They’ll get included in the content template as a variable, and will then be substituted in the content builder.

It will not include a templated content section, the generic section about the animal, the 4th image, and then a generic section about the food.

Two columns are then added into the content builder sheet.

These do a vlookup to the generic content templates sheet, with one matching the animal and one matching the food, and will return the text element if it exists.

They’re now added to the conclusion content section, with the substitutions in place.

Stripped pagination

Finally stripped out the pagination on the category pages, by editing the raw code of the theme.

This will probably get overridden when the theme updates, so I’ll need to get a child theme going, but I can sort that out later.

I tried to also strip out the post inclusion on archive pages, but the content still shows so I am not sure what’s up there.

Reduced the item count to 1 for archive pages though, which is the lowest it’ll go.

Just a single item is included now, with no pagination below it. Better than before so let’s go with that.

The intent behind this is to de-bloggify the site a bit, and to also ‘silo’ it a bit more by containing the links as much as possible.

We’ve got a decent internal linking setup within the content itself, so that is our core interlinking.

If we need more links, that’s where we will try and get them in.

Might even try fully silo it later too by cleaning up the templated header/sidebar links a bit.

Wrapping it up

We’ve now added some new content to each page, investigated an alternate table linking option, along with creating some shiny new pages.

On top of all this, I discovered a silly mistake in one of my linking codes.

I wasn’t substituting the space in multi-word animals & fruits, in the link codes and imagery names.

This led to 404 pages being crawlable, and a lack of imagery on heaps of pages due to an incorrect lookup.

Rookie mistake.

In the words of a wise man, DOH!

Taking a look at current performance though, we can see a few things.

Crawling is spiking, but a few days delayed so that spike is a week old now.

Google’s started indexing it all, slowly but surely.

16 in mobile usability report. I’ll go into why I look at this in a separate post at some point.

Impressions are starting to kick off now, because….

A heap of keywords are hitting the board. 35 pages with impressions in gsc. Decent little start.

Oh, and I added another 150 pages.

Stay tuned for Part 6, and see what we can do next.

Current Page Count: 320 pages

Time for part 4. If you haven’t already, make sure to check out this WordPress programmatic build part 1, part 2, and part 3.

Today, we’re spending the entire part on a single topic.

Imagery.

Like a portion of the last part regarding the AI-generated text, this probably won’t actually apply to you.

You’ll probably have your own imagery to use, or at least, acquire proper images to use.

However, this one is fun. Promise.

Stick with it, and join me as we tinker on the edge of the dark side.

Text-to-Image Generation

Not to be confused with AI Image Generation, which I’ll get into shortly, text to image generation is exactly that.

Generating an image from text.

Yes, I know. Its how AI image generation works.

But this is literally taking the text and putting it in an image.

You build out a template, throw it some text and any images you want included, and an API will spit out a shiny new image.

Normally used for social media banners, programmatic SEO builds have started to leverage this to generate unique imagery related to their content in a less-than-greyhat way.

However, it’s not something I have actually leveraged.

Been on my to-do list for quite a while!

Time to get it going.

Picking a text to image generator

Yes, you can either code this yourself, modify one of the available code snippets out there, or even just buy one! There’s probably a plugin available.

But I’d rather find a good SAAS that does this well for the project.

After doing a bit of research, I’ve decided upon Bannerbear, and their image generation API.

Figured it also suited the whole animal theme.

They’ve got a nice, pre-formatted demo on the homepage that gives you some options.

These options are then spitting out;

A pretty neat way of generating a custom featured image for a post.

They’ve actually got a cool tutorial here on connecting WordPress with Bannerbear to get something like this going for OpenGraph imagery.

The query string method has been updated, to an apparently ‘simpler’ method, but let’s take a look at the whole process.

An alternate, and a bit cheaper, could be using placid. Seems they have some good integration methods too.

Creating a template

Once you’re signed up, you can start by creating a new template. The template will be the shell of what you’re generating, and allow you to hot-swap the different items out.

They offer quite a few templates, so I picked one that suited me and modified it as required.

I’ve included 4 images on the banner, along with the logo and a couple of text elements.

The 4 images will be swapped out, along with the title on the graphic.

Half jumping forward a bit with what the 4 images will be, but you’ll find out in the rest of the post.

As for the title, that will be the combo name.

Generating an image via URL

Following the tutorial here, to change the title and generate an image we get a URL of;

https://ondemand. bannerbear.com/simpleurl/ <base>/image/title/text/ Can+Monkeys+eat+ Bananas?/

After about 5 seconds it spits out;

Lost my question mark though… I’ll work out how to get that back a bit later.

Now, the best part about this?

You can bulk generate the images with Excel!

Well, you can bulk generate them a tonne of different ways but I already have this bulk image downloader & renamer that I use.

This works perfectly. I wasn’t expecting that at all.

Make sure you follow the tutorial in the post above on how to set it up, as you just need to edit a single bit in the code to tell it where to save images.

After that, just fill in the image name (no extension), the generation URL, along with what’s being replaced.

So for my example above, it’s just going to replace the question text.

Unfortunately, you’re rate-limited at 10 requests per 10 seconds, which yes, you could hit.

We have to modify the Excel script by adding some lines. When you’re in the editor modifying the file name (see in guide linked earlier) you need to look for;

Next i
End Sub

Insert 2 lines above that, and add before it;

‘~~> Pause for 2 seconds between requests
Application.Wait (Now + TimeValue(“0:00:02”))

The code will look like this;

It will add a 2-second pause before each request.

However, I just realised that Bannerbear requires the darn $150/month plan to do this simple URL generation. You get 10,000 images rather than the 1,000 I was going to play with, but I’m not upgrading for that.

New method time.

Generating an image via Airtable

They offer an Airtable generation set-up too (tutorial here) where you just throw all the fields in an Airtable and then it will build everything for you… proper no code!

You just map the table headings to your field names, insert your data, and then add your Airtable API key to Bannerbear.

Then when setting up the import, you insert your Base ID and table name, and then Bannerbear gets full access.

Here’s my setup before import:

Bannerbear will then take it all, run through them all, and then spit out the image URLs in the final column!

Pretty cool.

I’ll use the Excel image downloader on these URLs now so that I can properly rename the images, but generating them this way was faster and easier anyway!

I also got to keep my question mark.

I’ll sort all of that after I sort the images that will actually go into these, and the pages.

Now the fun bit.

AI Image Generation

Dall-e 2

Have you heard of Dall-e? And in particular, dall-e 2?

Dall-e takes a text prompt, and turns it into an image.

Their homepage demo gave me an option of a prompt of;

Teddy bears working on new Ai research on the moon in the 1980s

Which outputs an image of;

Pretty cool.

Can you see where I’m going with this?

Unfortunately, Dalle 2 is in super limited beta access.

Even if you get access, you can ‘only’ do 50 images a day.

Midjourney

Another ‘top tier’ option is Midjourney.

They’ve recently entered beta, and work via discord where you make your request and it’ll build the image in front of you. You can join the beta here.

When you run a generation, you get an initial set of 4 images.

You can then pick one, and get 4 more, or refine a specific one.

I’m going to try and get 3 new versions of the second image.

Lets now upscale the first one, and see what it spits out.

After it spends a minute generating the image, we get this thing of pure beauty.

It’s definitely more ‘artsy’ than what Dalle-2 is, but it could certainly work in the context of a ‘dogs eating grapes’ page.

Dall-e Mini

There’s an alternative called dall-e mini, which is basically just ‘simpler’ version of Dalle-2.

A very very simple version.

Let’s throw in the same prompt as what dall-e 2 gave me and check the result;

I mean, it’s so close haha.

However, it’s free and easily accessible.

So it is a massive step up from anything else we can have access in bulk to at the moment.

Let’s take a look at an example that directly relates to our topic.

Just for comparison, here’s the exact same output from a dall-e 2 beta account…

You can heavily tweak the prompts though.

There is some great info starting to popup, about throwing different elements on the end.

dog eating a banana cartoon

dog eating a banana sketch

dog eating a banana, studio lighting

I ran through a heap of options, trying to work out what would work best before finding something I liked.

Yeah, it’s not amazing, but it’ll do.

Bulk generating imagery

Now, in case the plan didn’t click, I’m going to attempt to bulk generate images for all the combo pages.

Dall-e Mini will throw out 9 images per request so we’re gonna hook into that.

There are mentions of a dall-e mega trained model, which would be a bit more ‘refined’ than the images we’re seeing above.

However, that takes a bit more work to get going. So for the initial build, we’re going to aim for just grabbing this output and throwing imagery in an S3 bucket.

I’ve got one of the developers I’ve used previously to build something for me that’ll do this. Might look at sharing it later, won’t for now though.

I know I said all in Google Sheets… but… you obviously would never, ever, ever, need to do this sort of thing.

You’d have completely original imagery and would never use AI to generate them.

So it doesn’t count. This is just me filling in the gaps to better show you how to leverage your own content.

Safe to say this is my new favourite thing.

This is essentially a folder full of folders. Each folder is for a specific animal & food combo.

Each of these combo folders, then contains the 9 images that were generated by my prompt.

HOW COOL.

I mean, look at these little fellas just enjoying life chilling with some strawberries.

Each request takes 2-3 minutes, so let’s go with 3 minutes. If we have 1,000 combos to generate imagery for, we’re looking at 50 hours to generate 9 images for each combo.

That’s not too shabby for generating 9,000 somewhat-usable images.

Probably won’t need all 9 for each post, and I certainly don’t want to go through 1,000 folders of imagery to manually select the best.

We need to prioritise the imagery somehow.

Google Cloud Vision AI

Google used to have something where you could put an image up, and it would tell you what it thinks it was.

Well, I eventually managed to find it again.

Google Cloud Vision AI

You can throw the images in there, and it’ll output what it thinks it is;

Both the dog and the banana are clearly recognised.

Unfortunately, however, if we click over to the ‘safe search’ tab, we can see that Google is determining it as ‘very likely’ of being ‘spoof’.

Now I can’t find much quick info as to what this exactly means, but seems to just be their fake image detection.

Google knows it’s faked.

Massive sad face.

But then we look at this outputted image…

This one is marked as ‘likely’ now.

If we generate some others, we can then get it too;

So now it’s only flagged as ‘possible’.

Maybe if we generate enough, it can get even lower.

After testing a heap more, I couldn’t seem to get dogs & bananas any lower.

Something about bananas doesn’t seem to work properly with AI haha.

However, this potato & cat image managed to get this;

So it’s clear you can fool the vision AI detection API with a potato butt cat.

Obviously, there’s a chance Google is using something completely separate in their algo.

There’s also a chance they’re not.

If people swear by the NLP API to optimise text, why can’t we leverage it to choose our imagery?

Image prioritisation with Vision AI

How can we use all this to pick the best x images?

Knowing that we can get at least a portion of images passed the auto-detection, we could filter our imagery and give the images a score based on whether the animal & food are detected.

Another score could be based on the level of spoof the image has come back with.

Mocked up a quick Google sheet template, and will have to also throw this over to the dev to handle.

I’ve then thrown together a quick quality scoring system.

If the animal is detected, the image gets a +2.

If the food is detected, the image gets a +2

Then a score depending on the spoof level.

The scores are just all added up, and I get a sortable score.

Once sorted, there is a ‘sortID’ column which is the following formula;

=IF(D2=D1,F1+1,1)

All it does is count up from 1, when the combo is the same.

Then there’s a formula in useImage which just tells me if I should use the image or not.

=IF(F2<5,“Image”&F2,)

If the sortId is less than 5, then put Image<sortId>. So, Image1.

When it’s time to use these images I know there will be exactly 4 images flagged, and their names will always be Image 1-4, and not the source image number, to avoid confusion.

The dev will just do a mass scan with the cloud API and then throw me back a CSV.

Another thing that I’ll have to rely on a dev for, but another super quick thing to do. You could do it yourself too if you want, but I’d rather just hand it off for a few $.

While that happens though, the image scraper has been working in the background and we’ve got images for 500 pages ready to rock and roll.

Embedding imagery in the posts

Now that we’ve got our images, we need to be able to get them into our post data, and be able to embed them.

I’ve added the image folder to the Image column, which contains ‘Image1’ up to 4.

This way, we can now vlookup from the main datasheet, 4 separate times. Once for each image.

Created 4 different columns on the datasheet.

You’ll see some blank spaces, the images errored out.

So basically, if there’s no image there will be no image link.

Exactly what we need it to do.

Now we need to take these, and build out the HTML code.

I could build the HTML code in the actual content for the page, but that’ll be a bit dirty and won’t help to troubleshoot.

Rather, I’ll add yet another 4 columns. One for each HTML of the image.

This way, I can just slap the cell reference into the content, and do any tweaks separately.

We can’t do this all as one, because the images won’t be a big slab of imagery. Well, that’s not the aim anyway.

We will want to insert the imagery separately, so one at the top, one at the bottom, and then two in the guts.

Let’s start with a formula to check if the link exists, and then will just use the link if it does, and keep the cell blank if it does’nt.

=IF(len(T2)>0,T2,)

We can then modify it with a standard HTML link code, to dynamically insert the image URL from the image1 cell, along with create an alt text that combined the animal and the food.

=IF(len(T2)>0,”<img src=”””&T2&””” alt=”””&proper(D2)&” with “&proper(E2)&”””>”,)

Be sure to use 3 quotations so that they stay in the outputted text. Since sheets is looking at the quotations as the beginning and end of text, it will error out unless you do the 2 for the inclusion of one, and then a third to finalise the text portion and start the formula portion.

Duplicated it across the other 3 columns, and mixed up the alt text a little bit in each of them too.

Now we gotta stick them in the content.

I’m going to leverage the content templates we built out for this one, and just insert <image1> wherever I want to put the first image.

It can go after the first paragraph, but before the question answer.

Image 2 can then go after the first answer.

It didn’t end up quite as clean as I was hoping, but I have inserted the 4 variables into the text templates.

Have then done substitutions on the content sheet to swap out the variable for the HTML from the data sheet.

Probably should have done that HTML code on the content builder to save the vlookup, but oh well, it’s done now.

Uploading the new data with images

I’m not exactly sure whether this will work with the HTML code out the box, so rather than toying around I’m just gonna give it a crack.

Going through the import there’s a step in WP Import/Export that allows for the importing of imagery;

Not exactly sure how this works though, so I am just going to continue the import of the HTML with the S3 references, and maybe we can update so that it actually imports new imagery a bit later.

That’d be cool though.

The upload took a little longer than before…

Almost 4 minutes compared to the normal 30 seconds. Bit more to process now I guess.

But, IT WORKS!

Beautiful.

Except due to the image quality they’re pretty small, and left aligned.

Let’s update the HTML code to centre them, and make them a bit bigger.

I’ll make them 400×400, about double size. The new formula is;

=IF(len(T2)>0,” <img class=””aligncenter”” src=”””&T2&””” alt=”””&proper(D2)&” with “&proper(E2)&””” width= “”400″” height=””400″” />”,)

Remember those double quotes to ensure they’re included in the output.

Now to reupload it all and check it.

Looks much better now.

Oh, and bonus!

It actually imported the imagery from the HTML, didn’t have to do anything else. Thought I was going to need to specify the URLs separately or something.

Bloody ripper!

Including the text-to-image featured image

Now that we’ve sorted out images for the pages, we need to sort the featured graphic we built out in banner bear.

I loaded up the 4 images per page into the Airtable we made, then jumped into BannerBear to run the generation

500 images were generated in about 5 minutes.

Well, there goes half my quota!

Completely worth it though.

How about those crazy cat eyes though!

Demons.

Now we gotta get them thrown into our spreadsheet, so I’ll just add yet another tab that can keep track of all of them.

I was thinking that maybe we could just import from the bannerbear URL.

Unfortunately, though, all the images will have a jibber jabber name of random characters. More like an ID than an image name.

I could be lazy and just use that, but let’s do this right and give them a proper page name.

I formatted the titles to turn it into a file name (without extension) so we can use the excel image downloader.

We can throw these names into the downloader with the bannerbear URL.

And then just run it… and wait. It’ll be a while.

The images all come out at like 600KB though, so I need to try and compress and/or resize them.

I’ve just come across this image optimisation tool.

Free, and you can just upload a tonne of images and then it’ll optimise and you download.

I resized all the images to 800px wide, rather than 1200, which halved their file size.

The tool downloaded them as pngs though, so I then converted them to JPG and it dropped them by 70%, and they’re all pretty much under 100KB now.

That’ll work perfectly.

Not amazing quality, but definitely plenty for what we need at the moment.

Threw them all in S3, and now we just need to reference them in our data.

Knowing the folder where the images is, along with the name, we piece a URL together by adding the folder along with a .jpg extension on the end.

Now just need to vlookup this from the main data sheet and we’re set.

Time to actually use these images somehow.

Figured I’d try put it as the featured image to test on one of the posts.

Works perfectly right out of the box, so we will just go with that.

The WP Import/Export plugin doesn’t have a separate field for the featured image, but there’s a box you can tick that sets the first image as the featured image.

Sounds dangerous if there isn’t a banner bear image available, but seems like it’ll sort it for us provided we enter the featured image URL in the image URLs box.

Continuing with the import we just select it all and wait for the site to download from S3.

Worked like a charm.

Image quality is pretty shotty though so I might try to step the compression back a bit if we can.

Only 62KB now,  so it’s pretty zoomy though!

Meet Caroline

Caroline loves animals!

Leveraging fake name generator for the name, and this person does not exist for the face, we made an author!

That’ll suit nicely for now. Definitely some solid credibility right there.

More so to look real to users, than Google, for this one though.

That’s a wrap for now

I’ll call it there for the end of part 4. We’ve done a lot!

No new data was added as Google is slow to kick the crawling off, so the 160 pages will be enough.

We’ll probably do another batch of pages in about a week.

Those 150 pages now have 4 in-content images, along with a featured image set!

Well, most of them. Few image generation issues so there is a handful (~10) missing them.

I’ve also released this dynamic content generation Google Sheets template, which is a basic version of what I am using in this project.

What’s next? Find out in part 5 here!

Current Page Count: 160

Let’s continue the WordPress programmatic SEO build!

If you haven’t already, check out part 1 of the wordpress programmatic build here, and then part 2 here.

A little pre-note here is that the next couple of posts will take a little side-step. Purely because I don’t have my own content, and want to get something out ASAP.

You can do this infinitely better than I will be, if you’re putting in the time or money to build out your dataset.

We’ll be back on track afterwards.

Time to crack on!

Fixing the design

I couldn’t bear it.

Haven’t played with too much WordPress lately so wasn’t sure what design to go with.

Have just installed Astra free and tweaking it a little bit. Much cleaner. Might upgrade it to paid to get a bit more customisation too but it’s already infinitely better than before.

Managed to clean up some of the more “bloggy” stuff, and have also got breadcrumbs going!

Just need to remove that pesky home button but I can sort that one later.

Also removed comments, cleaned up a couple of things and added a pretty header menu.

And by pretty I mean non-default. It’ll do for now.

Will be getting rid of that ‘previous post’, along with the recent post links at some point too. They’ll kill some of the topical groupings by linking to who knows where.

Happy to also say that the change to Astra fixed the main category design problem!

Need to work out how to remove all the posts listed below though, as they’re already linked to from within this content.

Unfortunately, the theme killed the subheadings for the page, but that’s liveable for now. Can add some custom CSS to address this later… I hope.

Upgrading the content

On top of the continuous data expansion via my VA, I need to update these keyword templates.

Let’s be real, they’re shit.

I still might get a writer involved later, but let’s have a play with an AI tool.

Generating some generic template text with an AI tool

My go-to AI content generation tool is WriteSonic – for now.

I’m mostly using it to generate product descriptions with AI in bulk at the moment.

So let’s take a look at what we can do with it this content.

I’m going to use the ‘sentence expander’ option, and just work through the content to see what it can spit out.

Starting with the opener, let’s see what we can extract from a generic point of view.

The goal will be to pad out the dynamic text templates a little bit, and get some more content on the pages.

That first option will work perfectly with some tweaks, and I’ll replace animals with the <animal> variable so that it inserts our animal name.

Added a few extra bits of text to some of the templates.

Couldn’t find too much generic stuff, so need to work out a good solution for this, and want to avoid providing too many incorrect answers.

It’s all very specific to each animal & food combo, not exactly template-able without just being garbage.

Wonder if there’s a better way we can do this.

Working directly with an AI model

AI tools are great for helping you write a piece of content.

They spit out a suggestion, then you can edit it and tweak it, and away you go.

Perfect when you’re handling small quantities of content.

How can this be scaled though?

How can I generate content that actually relates directly to the current page at scale?

Well, you can go directly to the source.

The source that pretty much every AI tool leverages in some way or another.

OpenAI.

Their GPT-3 setup to be more specific.

You can ask it pretty much anything at all, and it could return a full response, or just a simple answer to a question.

Maybe we can just ask it, in bulk, whether an <animal> can eat <food>.

Once registered, you can jump straight into their playground.

I haven’t spent as much time working directly in Open AI & their API, so this project is changing that.

To start, we can just straight up ask it the question.

Yes, a robot in the computer wrote that.

Guess that means the project’s done now, doesn’t it?

Well, I could stop there, and if I was happy to spend the extra money for a VA to build out a quality data set and have a writer build out quality templates, this is definitely where I’d stop.

Alas, I’m not. So let’s take this further.

The initial step was obviously not enough content, so let’s try get that expanded out.

So that’s a great start, and it can generate content for each section we created.

Completely related content.

You can then kindly request two paragraphs of content, instead of a single paragraph by using the “write two paragraphs” prompt.

I am seeing it hit and miss, but seemed to get higher success throwing a ‘separate’ in there.

The ‘please’ is just to make sure the robots don’t come for me when they take over.

That’s 89 words though, from a simple question.

I’m no expert here, and have only just started learning the basic prompts, so feel free to throw anything in the comments that could help out here.

This is using the DaVinci model, which costs $0.06 per 1,000 tokens.

OpenAI says it’s ‘approximately’ 4 characters per token, including both the prompt and the output.

The above prompt was 77 characters and the response was 495 characters, giving us a total of 572 characters.

572/4 = 143 tokens

143 tokens = 143/1000 x 0.06 = $0.00858

That 89 words cost us less than 1 cent.

Let’s say we want to generate 8 sections of similar content for a page, it’ll be $0.069 per page.

To then do this for 1,000 pages, we’re looking at $68.64

1,000 pages of ‘content’, for under $70.

Would I recommend this done for a client build?

Never. I’d stick with the dynamic variable insertion techniques.

Would I do this for a test project where more emphasis is on the process & the overall build and I really just need a tonne of filler content to build out the pages and prove out some concepts whilst still having somewhat shot at being indexed and possibly ranked for longer-tail keywords?

You bet. Game on!

Bulk generating the AI text

Let me preface this by saying I have never done this before. Legit.

This is my first time doing any sort of bulk generation of text directly with an AI model, and not using a tool with an interface and a VA to enter the data.

This. Changes. Today.

Ideally, we get this working directly in Google Sheets.

Would mean we don’t need to worry about jumping in and out of sheets, or doing any bulk imports. As new data & combinations is added, we could just run a formula and boom, we’ve have some magic words.

Found a great little gpt script by Andrew Charlton here that’s for titles & meta descriptions, and has the core request.

Input a keyword, and it’ll spit out a title and description. It’ll also cost a little bit due to a rather large amount of sample data hardcoded in the prompt though, so don’t overdo it!

Rather than trying to modify it, a mate shared a mob called Riku.ai.

They’ve been working on a plug-and-play sheets addon, and I’ll be going with that.

I’ve started by building out a little Google sheet to test things, along with estimating the costs of the outputs;

Something I might look at cleaning up later, but this was more of just a way for me to learn how to best write prompts.

I was manually copying and pasting these prompts into the playground to test, and then just paste the output here.

Riku just released the Google Sheets add-on though, and if you’re a paying member (they’ve got a discounted option via app sumo for July) you can find the Google Sheet in their FB group.

Everything runs through your Riku account, and you set up prompts in there which is rather annoying as you need to work in 2 systems to make updates. To get this working how I wanted it I had to create ‘blank’ prompts in Riku, and then just fed in this prompt. So I am essentially just using it for the Google Sheet at the moment.

Loaded it all up, threw in a heap of prompts and this is what happens;

This is magical.

Following this format, I’ve built out a Google Sheet containing all the different prompts I might want to use;

Throwing some of these into the Riku sheet and running the AI over it, we get;

At top level, it looks pretty cool.

I get the exact question format I am after, along with a chonky paragraph.

Problem is, I started to see some issues when looking at the generation in bulk.

Like this one.

As far as my Googling tells me, no, they can, in fact, not.

I know I’ll get a heap of false info for this, but that is why the prompt was tweaked further to become;

So it all comes down to the prompts and some examples of certain expected outputs from the prompt.

I also realised that the prompts I was using around the paragraphs, just weren’t generating anything clean enough.

I figure it’s best to take it back to basics, and just use the dynamic variable system I have already built.

The one that the VA started filling in for me, where we just place some simple text and bulk it out with the generic text.

This is where the true intelligence of OpenAI shines.

The current text system to generate our combo pages accepts inputs of text like the below examples;

So let’s see what happens when I try to request something similar from OpenAI.

Nice summary, but, it doesn’t follow the format.

Well, we can feed the AI some examples of the formats we want it to follow by using these magical stop thingies ##.

Google tells me that’s pretty good.

Turns out the ‘comma-separated’ request might have been a bit confusing, particularly for some of the other prompts.

So instead, I modified it to be ‘a summary’ and the output slightly improved;

This time, it also followed the format in a couple of the examples where there is a full stop mid-sentence.

It followed it PERFECTLY.

Honestly, was not expecting this. Pretty neat.

I’ve fleshed a similar thing out for the other main sections, so that we can run this and get the content.

Unfortunately, I ran into an issue with the Riku Google Sheets.

It wouldn’t allow line breaks sent via the Google Sheets. I stripped them out, and thought I had gotten it to work, but unfortunately it only worked for a few. After that the Google Sheets kept acting like the stops didn’t exist and I had to drop it. It was too time consuming to patch the broken ones.

However, I was sent a direct Google Sheets script that I made work perfectly.

My prompt with examples, line breaks and stop sequences on the left, with the output on the right;

Bingoooooo.

I added a little tweak for some of them, that if the animal couldn’t eat the food then don’t run the request for the prompt.

In this case, it was for the benefit of food.

I don’t think it’s much of a benefit if the animal dies after eating food, and the robots can return some rather interesting stuff, so was best to just ‘ignore’ them for some sections.

It will need some more examples in the future though to account for different possibilities.

I ran this across 150 combo pages to kick things off, and it cost approximately $1.

In total.

$1 for an 80%+ ish, arbitrary, correct data set for all of those responses.

Waheeeeee.

I further tweaked the model to include the text template element that will sit before the text. This way, the robot has something to kick it off and they al nicely fit the template and will read cleanly.

One of the new templates is;

Which, as you can see, outputs exactly what I need. I nice and direct answer.

Yeah, a lot of them are ‘upset stomach’, but hey, that’s what happens with food.

It now works a treat and I’ve added content for them all, for 150 pages.

You’ll probably spot some stuff that might not be correct.

The robots get it wrong sometimes though.

There’s just so much trashy content out there these days that these little guys suck up way too much junk to feed it.

Yes, that food is okay
No, that food is poison
Oh, I feed it to her as a treat
Yeah you should be okay
Only a little bit though

There’s just no solid answer for it sometimes, so it confuses the AI when being asked yes or no.

Sometimes, the two different AI models will give two different answers. The same thing happens just using two different prompts at times.

My initial generations are mostly accurate, but I’d say it’s 80%. I’d like to get that closer to 95%.

For certain yes/no questions, I feel like if it’s a no, there’s a higher chance of the no being correct than a yes.

My plan later will be to run both models, and maybe an extra prompt, and then try and pick the average to give it more chances to be correct.

I’ll also extend out the samples, to try and be a little more accurate. It will cost more, but accuracy will be required, particularly in the yes/no question.

Something I’ll test out later, but I’ve got enough for now, ill probably just add a caveat or something as a warning just in case it actually ranks.

Adding an out an ‘about us’ page with AI

We need to now throw up a basic ‘about us’ page that gives it a little bit of a… err.. real feel.

Since we’ve just used a tonne of it already, why not throw a bit more AI in the mix.

Don’t need anything special for now.

Threw in a rather detailed prompt and the playground threw back the below.

It randomly spat out a domain name that is completely open for registration… at the time of posting. Go on. I dare you.

No clue what it did there, but pretty cool!

Added a logo

Just some text and an icon I whipped up in 2 seconds.

It needed it.

Re-upload the data

And just re-uploading all our data.

Updating the old pages, and adding in all our shiny new data.

150 posts, plus the 10 categories (imported separately), done.

Where we’re at now

After the initial build creation, and internal linking setups from the previous posts, we’ve now significantly scaled page creation along with expanded the available content for each page.

The pages are looking pretty text-heavy now though…

Maybe this guy can help us out.

So what does this banana face-hugger cross doggo have to do with the build?

Continue with part 4 here.

Current Page Count: 160

It’s time for part 2 of the programmatic SEO build in WordPress.

Now that the core data structure and processes are in place, and we know that we can successfully generate a WordPress page, it’s time to extend the build.

If you haven’t already, you can check out part 1 of the programmatic SEO WordPress build here.

Extending the data

In the background I’ve had a few data updates happening, to extend what we’re creating.

Nothing major yet, but enough to build out a good handful of pages.

This is an ongoing process, due to the time involved. Going to try and get it above 1,000 pages but let’s see where we get to.

Creating top-level animal pages

Now that we’re covering the “can <animal> eat <food>” keywords, we can move onto the top level “what can <animal> eat” keyword set.

Both for targeting these keywords, but also, to become a parent for all the related content that will sit below it.

These animal pages will essentially act as ‘category’ pages.

Including animal pages in the data

The first step here was to create a new sheet that lists out all the animals.

I’ve thrown in some extra columns, and will be trying to include an intro, content related to each food the animal can eat, foods the animal can’t eat, and then a conclusion.

Then I’ll piece it all together.

Creating the base-level content

Let’s start with an opener and a closer. They should be nice and easy.

A couple of templates added, and then joining them back together and we get;

But we don’t want this output for every animal. Not every animal has content yet.

I could just add some content, and ensure each has a page, but then I couldn’t easily add new animals later.

We need the rule to make sure the animal has at least one food page created.

We can do this by counting the cells with either a YES or NO in the “canEat” column, when the animal matches up.

=COUNTIFS(Data!D:D, A2,Data!F:F,”*”)

Now the text is only generated for Cats in the example, because the data only currently contains content for cats & dogs.

Adding in the food content

I’m tossing up whether we actually say if an animal can eat the food or not, or just be like “find out if they can here…” and link through to the page.

For now, I am probably just going to do a nice ugly text link out and come back to it later.

First up, I’m just going to try and do a comma-separated list of all the foods an animal can and can not eat.

For this, I’ll be using an old formula I half found, and half put together, a few years ago.

This one took me a little bit to figure out how to convert it over though, as the original formula was only matching a single column. This time it needed to match two columns.

How do you match two columns? Well, you just merge the two columns into one column.

=ARRAYFORMULA(TEXTJOIN( “, “,TRUE,UNIQUE( IF(Data!Q:Q=”YES”& A2,Data!E:E,””))))

Long story short, this formula is looking into the data set for a new column I added, the is just YES<animal>. So YESdogs or YEScats.

When it finds a match, it grabs all the foods available for that set.

The source data looks like this;

And the output looks like this;

Definitely got me a little more excited than it should of haha.

As I mentioned, I want to build this out with a little section for each food eventually.

To avoid getting held up on that, and be able to continue to the build, I’ll just slap a sentence together for foods an animal can and can’t eat for now.

Will come back later and expand on it.

I’ve added a sentence for the can and can’t eats, and then substituted the animal and the food into it;

But you’ll see an extra variable in there, <foodEatCount>.

This one will just be a simple count of all the variables available.

There are a couple of ways to do this. Since it’s a comma-separated list, we can just count the commas and then +1 since there’s always one less comma than the item.

To count all the commas, and then add 1 to the count, we use the following formula;

=LEN(I8)-LEN( SUBSTITUTE(I8,”,”,””))+1

This is added inside a substitution formula, that swaps out the <foodEatCount> for the result of the comma+1 count formula.

The output is now the variable switched out with the count of items the animal can eat;

Replicating this for the cannot eat, for cats we get;

You may or may not pick it up, but I’m a bit pedantic so I am just going to quickly patch that with a little substitution and tweak.

Better.

Can tweak it later to allow for a secondary template if it’s just a single item.

Now, the comma-separated output looks like this;

Not quite “natural” sounding because you normally throw an “and” or similar at the end of a comma-separated list.

Probably okay for now, but I’m gonna patch it anyway.

It gets a bit messy, and it might look like an overly exaggerated formula, but we need to trim all the characters after the final comma.

=LEFT(I8,FIND(“@”, SUBSTITUTE(I8,”character”,”@” ,LEN(I8)-LEN(SUBSTITUTE( I8,”character”,””))))-1)

We then need to strip all characters before the final comma.

=RIGHT(I8,LEN(I8)-FIND(“@”, SUBSTITUTE(I8,”Character”,”@”,(LEN(I8)-LEN( SUBSTITUTE(I8,”Character”,””)))/ LEN(“Character”))))

And then finally, we slap it all together with an “and” in the middle.

I love the old oxford comma too, if you haven’t noticed. So I’m obviously gonna throw one of those in there too!

I’ll throw an extra column into my content creator, and clean it up there. It will help troubleshoot any issues later.

Once it’s all combined, we get;

=LEFT(I8,FIND(“@”, SUBSTITUTE(I8,“,”,“@”, LEN(I8)–LEN(SUBSTITUTE(I8,“,”,“”))))–1) &“, and “&RIGHT(I8, LEN(I8)–FIND(“@”, SUBSTITUTE(I8,“, “,“@”,(LEN(I8)–LEN( SUBSTITUTE(I8,“, “,“”)))/ LEN(“, “)))–1)

It now reads like proper English!

Just needs one final tweak to allow for when there is only 1 food available.

To do that, we count the commas again. If there are 0 commas, it’s a single entry so we can include the food.

=if(LEN(I8)-LEN( SUBSTITUTE(I8,”,”,””))=0 ,I8,iferror(LEFT(I8,FIND(“@”, SUBSTITUTE(I8,”,”,”@”,LEN(I8)-LEN( SUBSTITUTE(I8,”,”,””))))-1)&”, and “& RIGHT(I8,LEN(I8)-FIND(“@”, SUBSTITUTE(I8,”, “,”@”,(LEN(I8)-LEN( SUBSTITUTE(I8,”, “,””)))/LEN(“, “)))-1),))

And after all that the output is now;

Quite a bit of work to just swap out a comma!

After all that though, we now have page content for an animal-specific page.

Neat.

Will throw a quick H1 together, and then just tack these pages onto the bottom of the CSV output when I am ready to do another page generation.

Internal linking

As the data is extended, new pages will be getting created.

The problem is, that it’s all reliant on the WordPress setup at the moment.

Following the basic internal linking strategies here, we need to focus on three link types.

Parent linking, child linking, and cross-linking.

I’m going to add some extra data points to all the animals and food, to help us filter ‘related’ content.

Think about these as another category level.

The two I will be adding for now, are animalCategory & parent.

All the animals will have a category, but might not have a parent.

Parent linking

The parent linking for the build will attempt to achieve a couple of things;

• Combos linking to animal pages
• Animals linking to parent animals when available

Let’s kick off by getting the combos linking to the animal pages.

I’ll actually achieve this in two ways.

The first is by assigning the animal as a category for the post. This will then add a category tag, but I’ll try replace that with a breadcrumb, along with nestling the posts under their animal in the URL. ie /<animal>/can-<animal>-eat-<food>/

The second way the links will be achieved will be via a contextual link. These can pass a bit more linking value, and may get seen a bit differently than just a hierarchical-based link.

To do this, we need to add a piece of conclusion content to our combo pages to swap in a link in.

Added in a new content template for a conclusion, and have added some text with the output being;

We have a new variable to build out, which is the URL of the category.

There will be two though, as the category itself will have a slug rather than a built out URL, but then we need the URL for the contextual links.

Pretty simple to build out, as we know the URL will be domain.com/<animal>/.

Some nice contextual links were added in!

Child linking

Now we need to link the other way, and link to all the individual combo pages that were created for each animal.

We’ve already done the hard part in a previous step of including the foods that an animal can and can’t eat, so lets just convert them into links.

We can modify our comma separation formula to be able to add any text before & after the item that we want;

=ARRAYFORMULA(TEXTJOIN(“, “, TRUE,UNIQUE(IF( Data!Q:Q=”YES”&A8 ,”ANYTHING”&Data!E:E&”ANYTHING”,””))))

So let’s just throw the HTML for a link in there.

=ARRAYFORMULA( TEXTJOIN(“, “,TRUE, UNIQUE(IF( Data!Q:Q=”YES”& A7, “<a href=””https://domain.com/”& lower (“<animal>/can-<animal>-eat-“& Data!E:E &”/”)&”””>”& Data!E:E &”</a>”,””))))

Now it outputs the HTML linking code, including the dynamic elements of <animal>.

It just includes the return portion of the food lookup twice, as it’s all done inside the actual return code for a food, rather than having to use the entire lookup formula each time.

This will now get replicated for the canNotEat section.

The content for the animal pages will automatically update, so no tweaks need to be done there.

Cross-linking

For cross-linking, I don’t think I am going to link to other animals from each other for now, and just link from some foods to other foods.

I don’t want to link to every food, as for some animals that could be quite a few!

The goal would be to create a little link wheel type set up where one page links to a couple nearby.

I could give all the foods a tag, and only link within them, but I don’t think that’ll net much extra benefit.

Instead, I’m going to use an auto-incrementing ID for all the pages matching that animal.

=COUNTIF(D$1:D4,“*”&D4&“*”)

Basically, the range is locked at the first cell, and then will increment as it goes down the list, counting the number of values matching the current animal.

This way, it just counts as it goes, and the count is constrained within the current animal.

To find the closest values each way, we’re just gonna take this incremental ID and do a vlookup for 2 higher & two lower.

Since we’re working through this list from top-down, theoretically 2 up will always exist except for the highest numbers, and the same with 2 down, except for the lowest.

To make it a clean vlookup the animal has been added before the ID so we can use the two columns to match it.

On the content builder sheet, I’ve then grabbed this ID, and then stripped it back to the number, and then grabbed 2 IDs either side of it.

=D2&SUBSTITUTE(Z2,D2,)-2&D2& SUBSTITUTE(Z2,D2,)-1& D2& SUBSTITUTE(Z2,D2,)+1&D2& SUBSTITUTE(Z2,D2,)+2

I’ll now use this to be able to grab the combo names that are associated with these IDs.

Modifying the formula, to now include an index/match. It’s just a reverse vlookup since the increment ID is in the final column and not the first.

=iferror(INDEX(Data!A:R,MATCH (D2& SUBSTITUTE(Z2,D2,)-2, Data!R:R,0),1),)& iferror(INDEX(Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)-1, Data!R:R,0),1),)& iferror(INDEX(Data!A:R,MATCH (D2& SUBSTITUTE(Z2,D2,)+1, Data!R:R,0),1),)& iferror(INDEX(Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)+2, Data!R:R,0),1),)

If a value exists in the ranges of 2 before, and 2 after, it’ll grab it. If not, then it will error and not grab it.

And now tweaking it rather extremely to be able to build the URL by using that lookup in a vlookup to grab the slug, and then again to grab the <food> we get;

=iferror(“<a href=””https://domain.com/”&D2&”/”& vlookup( INDEX( Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)-2, Data!R:R,0),1), Data!A:L,12,0) &”/>”&vlookup(INDEX( Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)-2, Data!R:R,0),1),Data!A:L,5,0)&”</a>, “, )& iferror(“<a href=””https://domain.com/”&D2&”/”& vlookup(INDEX( Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)-1, Data!R:R,0),1), Data!A:L,12,0) &”/>”& vlookup( INDEX( Data!A:R, MATCH( D2& SUBSTITUTE(Z2,D2,)-1, Data!R:R,0),1), Data!A:L,5,0)&”</a>, “,)& iferror( “<a href=””https://domain.com/”& D2&”/”& vlookup (INDEX( Data!A:R, MATCH( D2& SUBSTITUTE( Z2,D2,)+1, Data!R:R,0),1), Data!A:L,12,0) &”/>”& vlookup(INDEX (Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)+1, Data!R:R,0),1), Data!A:L,5,0)&”</a>, “,)&iferror(“<a href=””https://domain.com/”& D2&”/”& vlookup(INDEX( Data!A:R, MATCH(D2& SUBSTITUTE(Z2,D2,)+2, Data!R:R,0),1), Data!A:L,12,0) &”/>”&vlookup( INDEX( Data!A:R, MATCH( D2& SUBSTITUTE(Z2,D2,)+2, Data!R:R,0),1), Data!A:L,5,0)&”</a>”,)

But breaking it down, it’s just 4 of the same thing. The individual bit might be a little easier to understand.

=iferror(“<a href=””https://domain.com/”&D2&”/”& vlookup(INDEX( Data!A:R, MATCH(D2&SUBSTITUTE(Z2,D2,)-2, Data!R:R, 0),1), Data!A:L,12,0) &”/>”& vlookup(INDEX( Data!A:R, MATCH( D2& SUBSTITUTE(Z2,D2,)-2, Data!R:R,0),1), Data!A:L,5,0)&”</a>, “,)

The output is worth the effort, but safe to say, I’ve had enough of looking at this, so let’s just throw it at the bottom of the generated content.

I’m calling that done…. on the linking anyway haha.

Updating the pages

Let’s get all the pages updated.

First up are the categories.

I’ve created a separate sheet for them, as it’s a different import process.

Whilst doing this I realised the category treatment is well, crap, so I’ll need to get category pages as their own post design. Will have a play with that a bit later and continue as is. Worst case, categories can just become posts and I’ll mod the combo URLs to “fake” sitting below the category.

Reimported all the post content, which includes a heap of new data so there are some new pages created.

A total of 50 live now, most with just the content relating to the yes/no answer, rather than full data though. Still a good prototype to continue working off!

Not looking too shabby so far.

What we’ve got so far

On top of the base-level setup completed in part 1, we’ve now added;

• Animal top-level pages
• Separate content for animal pages
• Parent linking
• Cross-linking
• Child linking

Oh, and I also just did the whole search console verification & XML sitemap submission thing to try kick off indexation.

Google’s a little slow on the old GSC updates these days, but here’s a chart of it crawling away;

Won’t be doing anything else ontop of that for initial indexation, so lets see what happens.

Part 3 is now available here.

Current Page Count: 57

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;