Just Another WordPress Programmatic SEO Build – Part 2

Need help with your enterprise or programmatic SEO strategy? Let’s chat.

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

Posts you might also be interested in

Like this article?

Share on Facebook
Share on Twitter
Share on Linkedin
Share on Pinterest

2 Replies to “Just Another WordPress Programmatic SEO Build – Part 2”

  1. What is in the column Data!Q:Q,?I can’t find on pics above anything.
    =ARRAYFORMULA(TEXTJOIN(“, “, TRUE,UNIQUE(IF( Data!Q:Q=”YES”&A8 ,”ANYTHING”&Data!E:E&”ANYTHING”,””))))

Leave a Reply

Your email address will not be published.