From keyword research to planning articles/silos, do you keep it all in spreadsheets, do you use PM software, mind mapping, just curious?

[4potato]

A while back I went ham on keyword research. Maybe way too much, I have tens of thousands to filter through. I am planning a very big site in a big vertical and the goal will be 500 articles, then 1000, I am inspired my threads in the Laboratory here about users reaching such goals.

I think to compete in my vertical, 1000 posts has to be the goal. So I'm not intimidated by the amount of keywords in my spreadsheet (I ran hundreds of individual seed through answerthepublic and ahrefs) and I'm wondering where to go next.

Part of me sees duplicate keywords, and extremely similiar keywords which can be merged into an article. Mind mapping / spider graphing seems powerful here, especially when some of these keywords are sub-topics of sub-topics, it could let me wrap my brain around it. But I also like the sortability of spreadsheets, for example I could tag keywords in a spreadsheet with difficulty scores that I come up with, even if it's as simple as "low comp", "high comp" etc.

I want to frontload all my work and come up with a logical way of taking the first step to 1000 posts so I don't even need to think about it, just pull the next topic out of the queue and start writing (or eventually hiring, but you get the idea).

So yeah, workflows, that stage between exporting keyword spreadsheets and writing the posts, where you assign keywords to categories, silos, etc. Did I just bite off way too much up front and now I'm overthinking? Keep it all in spreadsheets with categories, subcategories and sub-sub-categories for each post?

How do you deal with big spreadsheets where you have keywords that are unique enough, to not get filtered out as duplicates by spreadsheet software, but still similar enough that you KNOW they're the same damn thing. Some keywords have 6-12 of these variations, where the wording is so similar.

 

[secretagentdad]

Depends on the words.

You can search them individually and look at the variations between the search engine results to see where different search engines draw their boundaries.

That might help you figure out what groups to merge together into topics.

For silo planning I typically like to organize my spreadsheets with the big topic words on the far left.
In my experience, its pretty rare for a vertical to have more than 10 big ones, if you're making more categories than that you're probably overthinking things. (does not apply to products / brand names)

For me, It really comes down to what is the major terminology used to describe things in the category and than adding all of the common adjectives and verbs before and after your topic items. Typically they're exactly the same for everything in a given niche. Best, reviews, used, new, cheapest and top are all words that play well with just about every topic word and tend to have search volume. Humans all seem follow a pretty predictable syntax when looking for things. Even if ahrefs and the keyword planner don't show anything there is usually volume if you're smart about what you combine with your topic words. Stuff that's not flagged in database tools like semrush and ahrefs tends to have lower than average competition if you can find items worth the time.

If you take the time to read through and actually think about the key phrases its usually pretty obvious which ones should bridge categories and which ones work best as support for a larger item that warrants its own article.

The hardest part is translating the lists of keywords into actionable articles. A lot of keywords require different types of content. The syntax that people use to search doesn't really match the syntax you use to describe things in written format a lot of the time.
When planning articles I like to focus qualitative factors related to language rather than keyword tool difficulty scores. I like to start with stuff that naturally makes articles or other bait I can promote easily, then, move on to the big money items. I only ever worry about about saturating long tails after I've got some traction in the niche. Been reading about a lot of success from people who write a text wall for everything here in the lab and juststart but I've never seen much roi on that approach. (Here's a good one thats also got a 1000 article goal if you havnt seen it already)

——————————————
Below isn’t really a direct response to your questions but it might be helpful. The TLDR is I'd start with the keyword clusters you can write bait for and then move on to the money and long tail saturation stuff after you've gotten something to stick.
—————————————

I get a lot of shit for this around here but I think you would be better off writing 10 amazing articles that people can actually justify linking to. Rather than trying to build a site out of the gate with a full spread of line item keywords.

The 1000 articles strategy is a bit of a win more plan that works best when you have some existing authority and are looking to ramp up the money making.

Go through your topic clusters and read stuff out loud. Pick the best couple items where you think you could make some content so good people will naturally link to it and start with that. Some keywords are much easier than others when it comes to promotability. Start by giving those your best shot and you will be setting yourself up to succeed when you get to the rest.

An easy low hanging fruit option that I really like is to try and identify 4 word plus reference keywords that are factoid type answer keywords likely to be searched by writers looking for some quick references. Getting a good easily citable article onto the first page is usually pretty easy for ultra long tails and can result in a nice trickle of links to get you moving up.

 

[bernard]

I actually use Dynalist to map out my silo structure and Google Sheets for content planning and tracking.

 

[Ryuzaki]

What I've found is if you want to be very thorough, you need to be very thorough during the process of finding keywords, as opposed to finding a ton of keywords and then trying to be thorough. Because you end up with a mess of indecipherable goodness you'll never be able to deal with.

Another thing to consider is when you're working at scale, you definitely need to keep the Pareto Principle in mind where 80% of the results come from 20% of the keywords.

You may have 20 variations of the same keywords all hidden within 10,000 keywords. You don't need to find them all. If you cover one of the variations thoroughly in the topic, Google will still rank you for the other ones. We're ranking for topics as much as we're ranking for keywords these days.

With that being said, you could filter by volume and cut out everything below 100 search volume (simply an example, tweak to your preference). You could also then seek out some keyword difficulty metric you trust and then filter out with that.

Now you'll be left with a much more manageable list to deal with. Any of those low volume / low competition keywords will get tackled as you cover the topics anyways.

But what I do is when I set out to do keyword research, I'll find one keyword that looks good and I'll go ahead and find the variations and synonyms right then. I'll gather it all up right then. Then I move to the next "main keyword" and then find the "secondary keywords". Repeat forever.

You don't end up getting 100,000 keywords all at once or "finish off your keyword research for good so you can move on" but you do end up with actionable data, and you can really only publish so many articles at a time.

I see keyword research as part of the conveyer belt rather than something you can front load and pack into a box and put it on the shelf with a bow and call it done.

 

[4potato]

Depends on the words.

You can search them individually and look at the variations between the search engine results to see where different search engines draw their boundaries.

That might help you figure out what groups to merge together into topics.

For silo planning I typically like to organize my spreadsheets with the big topic words on the far left.
In my experience, its pretty rare for a vertical to have more than 10 big ones, if you're making more categories than that you're probably overthinking things. (does not apply to products / brand names)

For me, It really comes down to what is the major terminology used to describe things in the category and than adding all of the common adjectives and verbs before and after your topic items. Typically they're exactly the same for everything in a given niche. Best, reviews, used, new, cheapest and top are all words that play well with just about every topic word and tend to have search volume. Humans all seem follow a pretty predictable syntax when looking for things. Even if ahrefs and the keyword planner don't show anything there is usually volume if you're smart about what you combine with your topic words. Stuff that's not flagged in database tools like semrush and ahrefs tends to have lower than average competition if you can find items worth the time.

If you take the time to read through and actually think about the key phrases its usually pretty obvious which ones should bridge categories and which ones work best as support for a larger item that warrants its own article.

The hardest part is translating the lists of keywords into actionable articles. A lot of keywords require different types of content. The syntax that people use to search doesn't really match the syntax you use to describe things in written format a lot of the time.
When planning articles I like to focus qualitative factors related to language rather than keyword tool difficulty scores. I like to start with stuff that naturally makes articles or other bait I can promote easily, then, move on to the big money items. I only ever worry about about saturating long tails after I've got some traction in the niche. Been reading about a lot of success from people who write a text wall for everything here in the lab and juststart but I've never seen much roi on that approach. (Here's a good one thats also got a 1000 article goal if you havnt seen it already)

——————————————
Below isn’t really a direct response to your questions but it might be helpful. The TLDR is I'd start with the keyword clusters you can write bait for and then move on to the money and long tail saturation stuff after you've gotten something to stick.
—————————————

I get a lot of shit for this around here but I think you would be better off writing 10 amazing articles that people can actually justify linking to. Rather than trying to build a site out of the gate with a full spread of line item keywords.

The 1000 articles strategy is a bit of a win more plan that works best when you have some existing authority and are looking to ramp up the money making.

Go through your topic clusters and read stuff out loud. Pick the best couple items where you think you could make some content so good people will naturally link to it and start with that. Some keywords are much easier than others when it comes to promotability. Start by giving those your best shot and you will be setting yourself up to succeed when you get to the rest.

An easy low hanging fruit option that I really like is to try and identify 4 word plus reference keywords that are factoid type answer keywords likely to be searched by writers looking for some quick references. Getting a good easily citable article onto the first page is usually pretty easy for ultra long tails and can result in a nice trickle of links to get you moving up.

I gotta remember to do more manual searches once I get my list more hashed out, and not only see who is ranking but "what". I haven't used surfer seo or anything like it but have heard more people talk about it lately. Since my vertical is heavily product based I think there are lots of small content silos, hence my goal to just write a ton. I shared with you my niche and I probably should have no problem sharing it publicly, because, ideas are cheap, but just to be safe I'll give an alias niche. We'll call it make-up. You know what it really is, and I'm probably being silly here, but the examples should be more or less the same.

Organizing this project is one of the bigger digital undertakings I've done and it's a good exercise, I really like the Getting Things Done methodology of clear your head, make a list of next actions, then just go. I want to put the time up front to know what are the next 50, 100, 300 things I need to write, then just grab the next one in line and run with it.

Know what's ironic? I'm sitting here after work trying to answer these replies, come up with examples from my own spreadsheet to make my point more clear, and I just leveled up in the spreadsheet world by finding out about FILTER VIEWS and CONDITIONS. That really helps, I didn't know I could take all this data and apply a basic filter like "text contains: lipstick" and only show that. Or only show "eye shadow". It's perfect. I can even create a bunch of these filters, name them and save them.

I think, sort of what you guys are saying (at least partially) is do batches, I don't know if I'm batching right, but I might start a first batch with say "eye shadow". Cover that completely and move on to blush or foundation.

Now my brain is screaming at me "which one are you gonna pick first???" and I don't know. I just need to start with one I guess, I could spend forever digging around in data. The fact I can filter my sheet down to specific terms does help me manage this huge amount of data, so I don't feel as compelled to mind map it all out. I can just high level mind map the few dozen topics to cover, pick one that feels best as a starting point (how do you eat an elephant?) and not second guess my choice 1000 times once I made it.

I agree with some of this data that turning it into actionable titles and article ideas is a bit of a challenge. And as I narrow down I'll do manual searches. I wish I ran more ahrefs searches when I had the sub and less answerthepublic (I bought a month of premium of this a while back) just to have search volumes, etc but I figure I'm going after longer tail stuff, so I'm not too concerned with it yet, hopefully this doesn't feel like a mistake months down the line.

I actually use Dynalist to map out my silo structure and Google Sheets for content planning and tracking.

Looking at Dynalist, I like it! Seems simple, but I often think in note form like this anyways, and I can just indent lists further to show structure, hide list items, I might take this!

What I've found is if you want to be very thorough, you need to be very thorough during the process of finding keywords, as opposed to finding a ton of keywords and then trying to be thorough. Because you end up with a mess of indecipherable goodness you'll never be able to deal with.

Another thing to consider is when you're working at scale, you definitely need to keep the Pareto Principle in mind where 80% of the results come from 20% of the keywords.

You may have 20 variations of the same keywords all hidden within 10,000 keywords. You don't need to find them all. If you cover one of the variations thoroughly in the topic, Google will still rank you for the other ones. We're ranking for topics as much as we're ranking for keywords these days.

With that being said, you could filter by volume and cut out everything below 100 search volume (simply an example, tweak to your preference). You could also then seek out some keyword difficulty metric you trust and then filter out with that.

Now you'll be left with a much more manageable list to deal with. Any of those low volume / low competition keywords will get tackled as you cover the topics anyways.

But what I do is when I set out to do keyword research, I'll find one keyword that looks good and I'll go ahead and find the variations and synonyms right then. I'll gather it all up right then. Then I move to the next "main keyword" and then find the "secondary keywords". Repeat forever.

You don't end up getting 100,000 keywords all at once or "finish off your keyword research for good so you can move on" but you do end up with actionable data, and you can really only publish so many articles at a time.

I see keyword research as part of the conveyer belt rather than something you can front load and pack into a box and put it on the shelf with a bow and call it done.

I'm probably firmly in the situation you described. I felt like I was scrambling under a rush to get as many keywords as I could, so I just threw every term in the niche at it, exported mass quantities of data and now I'm sorting. Like I said, filter views man. I'll experiment with that. And I'd like to say I'll do better next time, but I have keywords for at least one other huge interesting vertical (completely different) I'd like to write about one day too so I might not do keyword research for a LONG time now. I think I have so much content to write which is a good problem.

Does it sound like the right track to filter down to one "silo" of my sheet, and just blast that out first, in essence filtering out 90% of my sheet to just focus on the 10% related to that specific tool or product silo first?

I picked a very product based niche, with so many products it pumps the total amount of keywords way up.

If I made beginner mistakes and wrote content for things which tools tell me have 0 search volume, even though my brain says "no, people HAVE to search for that", is that a mistake too? What about 10-50, or 50-100? What's the cutoff? Because the danger of writing for 0-10 is no traffic, but the danger of trying to punch above my weight class with a big article dominated by big players is the same punishment, no traffic. Which side to err on as a beginner, too longtail, or too broad, if the goal is to eventually pump site traffic to 10-30k monthly sessions in first 8-10 months by just broadcasting the site with content?

 

[bernard]

Looking at Dynalist, I like it! Seems simple, but I often think in note form like this anyways, and I can just indent lists further to show structure, hide list items, I might take this!

You can also use folders. I use folders for categories and then the notes for each article.