Creating Keyword Opportunity Lists with Excel Custom Sort
This YouMoz entry was submitted by one of our community members. The author’s views are entirely their own (excluding an unlikely case of hypnosis) and may not reflect the views of Moz.
Using Find & Select and Custom Sort in excel for keyword research helps a simple list of keywords turn into an organized cluster, and everyone likes organized clusters right?
I use this method to show clients new blog post ideas, landing pages they could create and a smarter way to organize their site based on keyword research.
Ready? Let’s do research for a recipe blog. The holidays are around the corner and it’s time to start preparing, but what should we write about? Can we create any posts that will help for after the holidays?
1. Head over to your favorite keyword tool and start typing in your keywords: “holiday recipes,” + “Christmas recipes” + “recipes”
2. Export all of the keywords to excel (2007), combine them into one long list and sorted them from highest monthly clicks to the lowest
3. Insert a new column and enter in the formula “=IF(A2=A3,TRUE)” next to the first keyword
4. Pull the formula all the way down so that you see a full list of “TRUE” and “FALSE”
5. Delete all of the keywords that have “TRUE” next to it, so that you have a list free of duplicates
6. Scan through the list and delete all of the obscene keywords and branded keywords, like “Rachel Ray holiday cooking”
7. As you go through the list, make note of common terms that you come across: Christmas, Cooking, Baking, Country Terms (Mexican, German, Italian, Ethiopia), Vegetarian/Vegan, Punch/Drinks, Dessert, Meats (Chicken, Turkey, Salmon, Beef), Crockpot/Slow Cooker, Kid Friendly
8. Create a new tab, because you don’t want to delete the raw data in case you want to reference it later
9. Click on Find & Select (under the home tab, in the editing category) > Find, and enter one of the terms that you saw come up in the keyword list, like “cookie”
10. Instead of inserting a term next to Replace With, click on Format, pick your favorite color and click Replace All
11. Click on Sort & Filter (next to Find & Select) > Custom Sort > Expand the Selection > Sort. Sort by the column that holds your keywords (in the picture below you can see that I have already labeled my column as “Keyword”)
12. Under Sort On, change the drop down to “Cell Color,” then pick the color that you chose under Order
13. Click OK
14. Highlight all of the “cookie” keywords, copy and paste in the new tab that you created (this becomes your first list of organized keywords)
15. Go back to the first tab and un-fill all of the color for “cookie”
16. Re-sort by monthly searches (highest to lowest) - Make sure to do this, because some terms that you sort are going to contain multiple keywords, like “baking Christmas cookies”
17. Go back and complete the list of terms that were found
Once done, all of the keywords are categorized and a lot more manageable. It’s easy to see different keywords that are getting searched, trends that are coming up and possibly categories you didn’t think of, like “recipes for Christmas leftovers.”
Here's the completed list of opportunity keywords for our holiday terms.
Comments
Please keep your comments TAGFEE by following the community etiquette
Comments are closed. Got a burning question? Head to our Q&A section to start a new conversation.