Using Excel For SEO – Local Keywords
Over the years I have realized that efficiency is key when it comes to creating keyword strategies for large projects. There are many great tools and ways to go about getting keyword ideas, personally I use a combination of SEMRush, Adwords, Analytics and of course Excel. Today I wanted to share a simple technique that I have been using for years and I realized that some people may not be aware of this simple trick. It involves using Excel to create keyword ideas in bulk, usually I do this when I have to come up with keywords that don’t have high search volume. In some cases, when dealing with city specific keywords that have little to know search volume, it can be hard to create keywords in bulk. For example, you could be an affiliate marketer starting a nationwide lead generation campaign and you want to rank for many thousands of city specific keyword terms. Google Adwords’ keyword planner doesn’t usually report search volume on keywords that are really long tail but we all know… that long tails are the highest converting keywords.
When I need to create keywords fast and I know that they are not going to have search volume, I sometimes have to rely on common sense because there aren’t enough keywords available with my tools. For example I could choose to just rank for every city in the country followed by “SEO company” so that I show up for “[any city] SEO company” but the small cities are never going to have search volume when I research them with the common tools. If I could manage to show up for every major city in the country, I am sure that I would be a happy man. So, how will this help? As I mentioned, sometimes you deal with really niche industries and there are barely any competitors to research or any keywords with search volume that your SEO tools can find. In this type of case, I like to use a few simple excel formulas to help automate my keyword generation process. It goes something like this;
1. First you need to identify your “root keywords” which we can later combine with variation extensions. I use Google trends to identify which non-city specific keywords are being searched the most to prepare to add them to my list of locations. To the left you can see a screen shot of Google trends being used to find keywords that an SEO company might want to try and rank for. So lets say we were dealing with SEO as an industry and I wanted to generate leads for my SEO company from every major city in the country. I could choose to use Google trends to see if “SEO company” is a better choice when compare to “SEO firm” according to the search volume. You can also use product categories, brand names in this step if you are working with an eCommerce website. For example “Nike Shoes”, “Addidas Shoes” or you can even use product names. As an ecommerce SEO provider, it can be very daunting to find product specific keywords, this technique can also be applied to speed up your keyword generation for product intrinsic campaigns.
Once you have you root keywords set, you need to start thinking like your customers. For example, I know through experience, and through analyzing Google Analytic’s data, that “SEO companies in Miami” has driven a lot of traffic to my site. I also know that keywords like “Miami SEO Companies”, “Miami SEO” and “Miami SEO Company” drove a lot of traffic to my site as well. Once I have an idea of how my customers find my site I can start to create long tail variations of the “root keywords” by merging these variation extensions with my root keywords. This can also be applied to things like product or service categories. For example you might choose product ID numbers as the root keyword and then extensions like “for sale” or “for sale online”. Alternatively, we can take the categories we mentioned earlier to get keywords like “Nike shoes for sale” or “Nike shoes for sale online”.
2.Next, you must select your root keyword variations, simply place them into columns next to each other and use simple functions that allow you to combine texts from the cells that you specify. What is a root keyword variation? If you were growing and SEO company and you chose “SEO company”, “SEO companies” and “PPC management” as your root keywords, your variations can be city names or state names (or a combination of the two). In this example you might use this technique to create many city specific keywords tailored around your root keywords like “Miami SEO company” or “Tampa SEO Company”. If you place the city specific variation in column A and the root keyword in column B, you can easily combine the text from A1 and B1 by calling the function =B2&” “&A2 which just tells excel to combine B2 with A2 and separate the two cells with a space. Makes sense right? The automation is really in the next step, once you apply the formula to one cell, you can drag the cell formula to all cells beneath it by double clicking the tiny square on the bottom right of the cell with the formula in it.
Again this is really helpful when you are starting a nation wide lead gen campaign but it can also be used in many ways. Recently I used this method for a Real Estate Agency that was looking for rank for a combination of cities followed by “homes for sale”. I had to create thousands of keywords and this technique saved me a lot of time. I was able to take the data from these fields and upload them into Adwords really quickly when creating their first PPC campaign. There are several other ways to use this technique and I encourage you to leave comments explaining how you think you might use this technique! Thanks for reading!
About The Author
Jason Hawkins / http://www.themiamiseocompany.com
Jason Hawkins is the CEO & Co-Founder of The Miami SEO Company. He has over ten years of experience in search engine optimization, conversion rate optimization and lead generation. His core responsibilities include identifying ways to increase value of services rendered, training staff on advanced SEO topics, and A/B testing internal processes to consistently improve client return on investment.
