Keyword Rankings

C

catbert13

Newbie
Joined
Oct 19, 2022
Messages
8
Reaction score
3
Hi so, I wont say I'm new to this, but much more inexperienced that probably most of you. I'm working on a keyword research tool to help me rank my site organically, and I'm wondering if I'm putting in a lot of effort where there might be a spreadsheet, tool, or any other easier/less time consuming method might already exist.

Getting keyword suggestions is the easy part, finding out the difficulty and volume is the need for all of this. I'm shooting for completely free/low cost (i do have access to some Ai, but not API access), but what I've done so far:

In google sheets:
Import my site's GSC keyword ranking
Import Ahref's Organic Keywords rankings for my site
Import daily, 10 competitors top 10 keyword rankings from SEMRush
Import daily, 3 related keywords search and rankings from Ubersuggest
Import daily, 5 related keywords search and rankings from KWFinder

Take all the keywords, sort them into a unique column subtracting out any keywords I already rank for(those are handled separately), and then vlookup the rankings into the list (where the data exists) and average out the KD and search volume.

So then I'm left with a long list of Keywords I don't rank for, KD, and Search Volume. Day 1 yielded about 300 keywords. I then add an additional column to score those keywords based on KD and Search volume. The highest and most relatable keywords gets content made for it.

Now I'm still adding sites to draw data from, so any suggestions would be appreciated! But I was just curious if there's...a better free method than doing...all of this, this daily. All in all it's probably like 0.5-1hr of work
 
Advertise on BHW
catbert13 said:
Hi so, I wont say I'm new to this, but much more inexperienced that probably most of you. I'm working on a keyword research tool to help me rank my site organically, and I'm wondering if I'm putting in a lot of effort where there might be a spreadsheet, tool, or any other easier/less time consuming method might already exist.

Getting keyword suggestions is the easy part, finding out the difficulty and volume is the need for all of this. I'm shooting for completely free/low cost (i do have access to some Ai, but not API access), but what I've done so far:

In google sheets:
Import my site's GSC keyword ranking
Import Ahref's Organic Keywords rankings for my site
Import daily, 10 competitors top 10 keyword rankings from SEMRush
Import daily, 3 related keywords search and rankings from Ubersuggest
Import daily, 5 related keywords search and rankings from KWFinder

Take all the keywords, sort them into a unique column subtracting out any keywords I already rank for(those are handled separately), and then vlookup the rankings into the list (where the data exists) and average out the KD and search volume.

So then I'm left with a long list of Keywords I don't rank for, KD, and Search Volume. Day 1 yielded about 300 keywords. I then add an additional column to score those keywords based on KD and Search volume. The highest and most relatable keywords gets content made for it.

Now I'm still adding sites to draw data from, so any suggestions would be appreciated! But I was just curious if there's...a better free method than doing...all of this, this daily. All in all it's probably like 0.5-1hr of work
Click to expand...
bro you’re doing everything manually but why not just use an api like serpapi or google search console api ?? you can automate fetching keywords instead of copy-pasting all day long also check seo powersuite rank tracker or seodity some tools do this for free on trial mode
 
GlobalProxy4g said:
bro you’re doing everything manually but why not just use an api like serpapi or google search console api ?? you can automate fetching keywords instead of copy-pasting all day long also check seo powersuite rank tracker or seodity some tools do this for free on trial mode
Click to expand...
I totally agree, it's a lot of manual work! That's why i figured I'd asked before i make this a standard routine! So big thanks for the help!

I'm playing with Rank Tracker now, but there are some parts of the software that make me a little cautious of the security of the whole thing. But seems like the type of workhorse I've been looking for, although pulling in keyword data is taking forever, not sure if that's normal. Also, there's not a lot of YT videos on the usage of it other than the developer's, so again...a bit suspicious. Is this an older app that just isnt used much by mainstream?? I'm having a hard time finding anyone outside of the developer using it on there that doesn't seem like a paid promotion.

Serpapi I'm just starting to dig into, that appears to unlock a lot of things I probably don't need, so it's a matter of finding the needle in that haystack.

I have some UI software built through lookerstudio and recently some n8n integrations to pull my google search console keyword info, but none of that was assigning a KD to any of the keywords and those were one I already rank for anyways, then again, it could be my ignorance there as to it's true capabilities?
 
There are guides for this shared on BHW. You can refer them, they are simpler than the above approach you mentioned.
 
Sounds like you’ve already got a pretty solid system going, even if it’s a bit manual. Maybe checking out what others in similar situations are doing could give you some fresh ideas—sometimes a little tweak in workflow can save a ton of time!
 
If you're on a small budget, LowFruits and Keyword Chef are great for finding low-competitive keywords without much effort.
 
Ubersuggest
LowFruits
KWFinder
Long Tail Pro
Keyword Surfer
SERPstat
AnswerThePublic
Keyword Chef
SpyFu
Wordtracker
would be best suit for you.
 
catbert13 said:
Hi so, I wont say I'm new to this, but much more inexperienced that probably most of you. I'm working on a keyword research tool to help me rank my site organically, and I'm wondering if I'm putting in a lot of effort where there might be a spreadsheet, tool, or any other easier/less time consuming method might already exist.

Getting keyword suggestions is the easy part, finding out the difficulty and volume is the need for all of this. I'm shooting for completely free/low cost (i do have access to some Ai, but not API access), but what I've done so far:

In google sheets:
Import my site's GSC keyword ranking
Import Ahref's Organic Keywords rankings for my site
Import daily, 10 competitors top 10 keyword rankings from SEMRush
Import daily, 3 related keywords search and rankings from Ubersuggest
Import daily, 5 related keywords search and rankings from KWFinder

Take all the keywords, sort them into a unique column subtracting out any keywords I already rank for(those are handled separately), and then vlookup the rankings into the list (where the data exists) and average out the KD and search volume.

So then I'm left with a long list of Keywords I don't rank for, KD, and Search Volume. Day 1 yielded about 300 keywords. I then add an additional column to score those keywords based on KD and Search volume. The highest and most relatable keywords gets content made for it.

Now I'm still adding sites to draw data from, so any suggestions would be appreciated! But I was just curious if there's...a better free method than doing...all of this, this daily. All in all it's probably like 0.5-1hr of work
Click to expand...

Have you tried using Google’s API? It has a free plan and could help optimize the process. Might save you a lot of time.
 
You're doing great, but you can save time by automating tasks.
Use Google Apps Script to pull Google Search Console (GSC) data directly into Google Sheets.
Try IMPORTXML() to scrape free keyword data from Ubersuggest or SEMRush.
Use Google Keyword Planner (free) to check search volume in bulk.
Instead of daily updates, do it weekly to save time. Use QUERY() and ARRAYFORMULA() in Sheets to auto-sort and score keywords.
If you're open to it, Python can automate data collection and filtering.
This way, you spend less time on manual work and more time on ranking your site.
 
Advertise on BHW
You must log in or register to reply here.
Sign up now!

Main Menu

Home Main Forum List Black Hat SEO White Hat SEO BHW Newbie Guide Blogging Black Hat Tools Social Media Downloads

Marketplace

Account Selling Content / Copywriting EBooks / Methods Hosting Misc Proxies For Sale SEO - Link building SEO - Packages Social Media Social Media - Panels Web Design More

Making Money

Affiliate Programs Hire a Freelancer IM Journeys Making Money Pay Per Click Site Flipping Want to Buy

BlackHat World

Dispute Resolution Forum Suggestions & Feedback Introductions Lounge
Back
Top