The other night, I stumbled upon a suite of keywords in Ahrefs (as we do from time to time). Knowing I had that itch to start a new website, I figured why not go the programmatic route and get it knocked out in a few hours rather than a few months.
Here’s how it went.
It started with a tweet
For some reason, I seem to get more done between 10 PM and 2 AM hours than at any other time. Whether that’s writing and publishing or, in this case, trolling for new keywords.
And boy, did I find them.
Like finding the cache of translated keywords a few weeks ago, these keywords had all the makings for a decent niche site. By niche site, I mean getting the pieces published, and that’s it. No ongoing publishing for months.
The competition was somewhere between one and three results that answered the searcher’s intent. The rest of the SERPs were whatever Google could serve up, meaning there was room for another website.
On the search volume front, looking at all the keywords related to the parent term, we had ~19k keywords and nearly one million monthly search volume. Good to go! In our first push, we’re just looking to get indexed. Our second push has about 800 keywords with 115k monthly search volume. Our third push is another 26k pages with an unknown search volume, but intuition says it’s there.
On the monetization front, display ads are the way to go. I already have an Ezoic account, so I’m going down that route. I don’t expect RPMs to be in the upper echelon, but if I could get at least a $10 RPM I would be happy. The third push would bring that down a bit, I imagine.
The major downside is this niche’s sheer amount of seasonality, even though volume isn’t abysmal outside of those spikes twice a year.
Just to recap
- Low competition
- SERPs incomplete
- Decent volume: 817 keywords under 10 KD at 115k total volume
- Longish tail, 3, 4, or 5 keyword length
- Expansion can go up to 30k pages, starting with 850, then around 800 more, then big pump
- Display ad monetization
- ⭐Other programmatic sites in the SERPs⭐
Steps taken
First off, we did our typical website stiff. Buying a domain, installing WordPress, theme, and the handful of plugins I use for most sites made. You know how to do this.
Next, we had to gather data. Spreadsheets galore, multiple spreadsheets, combining spreadsheets, extracting spreadsheets. I’m looking for a dark-mode spreadsheet plugin, so my eyes aren’t blasted by brightness late into the night. This was easily the most time-consuming part of the process, not because it was difficult but because I was trying to do things I’m not yet capable of. Like extracting infobox information from Wikipedia’s JSON file. Please laugh.
Once I realized that simpler was better, it was time to draft up the template I would use for generation. This part wasn’t so bad; I knew I would only have to do it once. I included [brackets] for where I wanted to plug into cells in Google sheets. This was done in WordPress classic editor, so I could copy the text-side editor as it included the H tags, media embed, etc.
Then just copy that text into sheets, work that concatenate function to plug in your data where appropriate, and double click that bottom right square.
BOOM!
Software used
I was a no-code extraordinaire throughout this process. Yeah, a few tools exist that are essentially concatenate functions with a bit more user-friendliness, but that’s no fun.
As I said, Google Sheets is where all the magic happened. Data collection, formatting, and whole posts sitting there waiting to be published.
But I had to get those posts from that column to WordPress somehow.
That’s where Pabbly came in…for the time being. More on that later.
Pabbly is an alternative to Zapier. I purchased a lifetime offer for something like 3k automation triggers per month a few months ago. I was using it for social sharing from a few sites, so I was barely putting a dent in that limit.
This experiment quickly maxed out that limit 😂 well, nearly, I still needed it to run my social automation for the rest of the month, so I had to cut it short.
So I still have about 350 posts that are waiting to get published, which I’m exploring other options for. I found what could be a promising plugin connecting Google Sheets to WordPress that may be what I’m looking for.
What was learned
Since this was my first foray into real programmatic SEO, I treated this like a learning experience. If the site goes well, great! If not, I now know how to get it created, at least.
That was the money maker for this: getting the process down. Gather data, format, publish, repeat.
And for the next one, I can go further. Maybe throw some spintax into the mix for variation among posts/pages. Include more information for more detailed posts.
Additionally, this scratches the new site itch! Now, when I explore keywords and stumble upon gold, I can look at it from a 24-hour lens rather than a 24-month lens.
- How could I make this the best possible thing in the least amount of time?
- How can I publish a year’s worth of content in a few minutes?
Plus, it’s not a massive investment whether it succeeds or fails! That’s both on the time front and money front.
What’s next
At this point, I’m just hoping indexing goes well. According to Google Search Console, video pages are only indexing being shown. That’s a good sign because if that’s showing as indexed a day after submitting the sitemap, the rest of the pages could follow suit.
Once indexing starts looking promising (or pages start appearing in the SERPs), I’ll go through and add Ezoic to the site.
If I can’t find a proper solution to get the rest of the pages published, I’ll have to wait until November to get the rest live.
If I can find a solution to publish, I can also go in and create the 2nd and 3rd batches of posts and have the project be a big check box for completion. Then revisit how the site performs after a year or so…