FREE SEO Template Keyword Golden Ratio Calculator

How to Find Keywords for a Website

Step 1: Give Away a Free Thing that is Useful

Hi! I'm Mike, owner of Bind on Equip. I've been working hard on building my business and learning everything I can about everything I can. One of the things I've learned is, a great way to bring traffic is to provide useful things for free. So I wanted to share this Google Sheets template I made:

FREE KGR From SEMrush CSV

It's very specific use is for something I'm extremely surprised isn't already part of SEMrush -- A way of calculating and sorting by the Keyword Golden Ratio. From everything I've learned, people like me who are just starting out do best to focus on what are called "Long Tail Keywords", the term referring to the way search terms are distributed when graphed out. Here's a crappy visual aid I made:

The gist is, 70% of all searches are for things with several words instead of just a few. Everyone is trying to rank on Google for "snowboards", and wouldn't it be lovely if everyone could, but math is a thing and so here we are trying to put our wares in front of people who are looking specifically for "ethically sourced 100% hemp farm to table snowboards with a pink pot leaf graphic and a chibi anime girl wearing a sundress flashing the peace sign". Okay, I exaggerate but you get the idea -- long-tail = more words in the search term

I've discovered that knowing how to find the RIGHT keywords for Search Engine Optimization (SEO) becomes a more complex challenge with every moment I spend trying to learn it. At least, that's how it's been for me. I will try to share what I've learned so far -- and please -- if I've got something wrong, or it turns out I'm just full of it, tell me in the comments below. You'll be able to teach me something and I'll have your email address to try to sell you a tee shirt from my store later.

Keyword Golden Ratio Calculator

Step 2: Teach People How to Use the Free Thing You're Giving Them

Without turning this into a tutorial for SEMrush's "Keyword Magic Tool", I'll just briefly run-down the settings I use to help me find the words with a potential to be in the Keyword Golden Ratio (KGR). First, let me tell you what KGR is.

The Keyword Golden Ratio (KGR) is a method for identifying keywords that have low competition online. By using data analysis, KGR can help you find phrases that are underserved and easier to rank for. If you create content around a KGR keyword, your article may achieve a high ranking within a short time, often within a few days or even hours. Pretty cool. So it's even more baffling why this feature isn't built into SEMrush. But it isn't, so on we go.

I leave it to you to learn how to research keyword search terms using SEMrush's "Keyword Magic Tool". If I though less of you, I'd give you a quick run-down, but I know you're capable of figuring it out by no other virtue than the fact you've arrived at my doorstep specifically for help with KGR. Lesser mortals would never make it this far.

That said, I'd like to list for you the 4 settings in that tool I use to help me find good candidates for KGR terms. To round up as many words which have any potential at all of fitting into that KGR sweet-spot, I use these filters:

1. KD% (Keyword Difficulty Percentage) - (custom setting) 0-35%
   "Keyword Difficulty" is a technique that involves assessing the level of difficulty in achieving a high rank on Google's search results for a particular keyword. It takes into account several factors, such as the quality of the content, the domain authority, and the page authority, among others, to determine the level of competition for the keyword. I set this value first to immediately eliminate anything above 35%. Anything above that is basically a waste of time when you're first starting out.
   
   
2. Volume - (Custom Setting) 1-250
   "Volume" refers to the number of times a particular keyword or search term is searched for in a search engine over a given period, usually a month. This metric is used to assess the popularity or demand for a particular keyword and to determine its potential value in SEO. The higher the search volume for a keyword, the more traffic it can potentially bring to a website if it is optimized correctly. Don't ask me why, but apparently KGRs for terms with a volume above 250 don't do well. It has something to do with the KGR sweet-spot being below 0.25 and multiples of ten and math and stuff. You can look into it if you want but I'm just not curious enough at this stage of my business to worry about it. Others who've been doing this way longer, and spent way more money testing it say the number is 250, so the number is 250.
   
   
3. Include Keywords - (based on what you're trying to rank for) - "Any"
   Exactly what it sounds like. It's a list of words that describe what you're trying to rank for. In my case, I have many niche fandom-type genres which I have printed on my products. When I do a search in the Keyword Magic Tool, I'm almost always searching for the niche since the products themselves would be way out of my league to try to rank for. So I put the products in the list of keywords to include, so SEMrush will use those products to show me results related to my products but specific to my niche.
   
   An example might be that the niche term I'm looking to find long-tails for is "Steampunk". Well, I sell tee shirts, hoodies, canvas prints, etc. So the list of included keywords I might try to augment my niche term "Steampunk" with, might look like "Tee", "Tees", "Tee Shirts", "T Shirts", "T-Shirts", "Hoodie", "Hoodies", "Hooded Sweatshirt", "Wall Art", "Canvas Wrap", "Home Decor", etc. I may also add keywords which aren't part of a fandom niche, but rather a theme, like "patriotism" to further modify the results SEMrush gives me. So, searching for "Steampunk", with the above included keywords, I may get a result like within the rest of my search criteria like, "mens patriotic hoodies made in the usa". The best part is, I can re-use these to get results for ever niche I search for. Make sure you change it to include "Any" keywords in your list or else you will get zero results.
   
   
4. Exclude Keywords - (things you don't want to see results for) - "Any"
   This is basically the opposite of the last one. Some things you'll want to filter out would be anything to do with other companies' brand names, or any celebrities, for example. Just use your head on this one. One more caveat -- make sure you put THESE keywords in [these brackets]. Unlike the previous filter, you want it to remove EXACTLY and ONLY the keywords you wish to omit. That's what the brackets are for.

Once you've found your keyword candidates within a search, send them to a SEMrush Keyword list. Do this with all your searches. This is the list you will export your .csv file from.

One piece of advice before I move on -- Make a text document where you can save, and add-to, your lists of Include Keywords and Exclude Keywords. SEMrush doesn't save your settings and you will want to be able to just copy and paste the entire lists back into the filters each time you use the Magic Tool.

Searching for word using this advice is the best way I've found to gather as many KGR candidates as I can in as short a time as possible. Next, I'll teach you how to use my KGR Spreadsheet Template to help you figure out which ones of those Search Terms is your fabled "low-hanging fruit" which is KGR search terms.

Low Hanging Fruit SEO

Step 3: Profit

Taking a look at my template spreadsheet, the first three columns are B, E, and F. The categories are Keyword, Volume, and KD% respectively.

• Q: But Mike, what happened to columns A, C, and D?
• A: Oh, they're there, they're just hidden.
• Q: Why would you hide them and not just delete them?
• A: If I deleted them, the data from the .csv file would populate the wrong fields.
• Q: Okay, but why hide it?
• A: It's not important and I didn't want to look at it.
• Q: But Mike, why are you such a stud muffin?
• A: It's mostly just genetics. Sorry

These are the columns that will be populated once you import your .csv file you made with SEMrush. This will ONLY WORK with SEMrush. If you're using something like Moz.com, the data will appear in a different order and won't be compatible with this spreadsheet.

If that's the case, and you want to use this with some other search tool, no problem. This all hinges around the the next topic -- "allintitle". If you need to do this with data collected from somewhere besides SEMrush, the equation I wrote will work for anything, you'll just have to change the column labels within the equation to match the way your columns are formatted. If you're willing to figure out how to do that, here's the equation: 

={"KGR";ArrayFormula(IF(LEN(O2:O)=0,,O2:O/(IF(LEN(E2:E)=0,,E2:E))))}

But we're gonna move forward assuming you're using SEMrush. As I mentioned before, your next concern is going to be something called "allintitle". Google has a search modifier called "allintitle" which only returns results for searches which have the words you specify in their title.

allintitle:keyword golden ratio 

It's simple to use. In googles search field, as shown in the above example and image, you type "allintitle:" followed immediately by your keyword search term. It's recommended you put no spaces between the colon and your search term, but I haven't noticed a space making any difference. DO NOT put your search term in quotes. That will make the search way more specific than you want.

Just above the search results, you will be notified of the total number of search results which have those words in their title. In the above example it reads like, "About 4 results (0.40 seconds)". So, what you do now, is you take THAT number, in this case "4", and put it in the "allintitle" field on my spreadsheet for the keyword term "Geeks and Gamers Merch". The template will automatically give you the ratio of the "allintitle" search results divided by the "monthly volume". IF that number which now appears in the KGR field is "0.25" or less, congrats, you now have a keyword which is within the "Keyword Golden Ratio". Do this with all of the keywords you've researched and delete the ones that aren't in the ratio. That's your list of keywords to target. Congrats. Mission accomplished.

• Q: But Mike, what about those other 3 fields you haven't mentioned yet?
• A: Oh, those. Genre, category, and subcategory are fields I added to help me re-organize my results to quickly group keywords based on things they have in common. It is basically my endgame for helping me use these keywords in SEO copy. I use these columns more than any other to write category and product descriptions, and any other copy for the sake fo Google bots blessing me with bountiful clicks. You're welcome to use those columns or re-make them for your own specific purposes.

I think that's it. If there's anything I've missed, or anything you didn't understand fully, or just anything you would like me to expand on, let me know in the comments below. Thank you guys for checking this out and I hope I've been able to contribute something useful to the world with this template.

-Mike