Do you have an idea as to what your keywords should entail for you AdWords account, but want a quick way to come up with your list? A simple Excel formula can help with that.
The first step is getting a grasp on what keywords will garner quality traffic. There are multiple keyword tools available to help with this type of research. Below are just a few of the tools available to get you started:
- Google Adwords: Keyword Planner – Probably the most used keyword tool is the Keyword Planner straight from the AdWords interface. Just go to: Tools > Keyword Planner in order to access this tool. The great part about this tool is that it can already see what is currently in your account and help you find new keywords that are not currently present. It also allows you to find keywords based on certain locations, and allows you to utilize a feature that puts keyword suggestions into ad groups for you (though each account may be organized in its own way, and so this is not always helpful).
- Wordtracker.com – Word Tracker is probably the easiest of the tools to understand and use. The process is simple. You enter the type of business (ie: accounting), and the tool spits back the top searched keywords related to that term in order to get you started. The images below show you how simple the process really is.
What keyword would best describe what you are trying to sell?
What are the top searched queries similar to the keyword you inserted?
Pretty simple process, huh?
- Uber Suggest – The Uber Suggest tool is as simple as the Wordtracker tool, but might give you more ideas for keywords. With Uber Suggest the functionality is very similar to Wordtracker. Just type in a keyword you would want to focus your build around, and it spits back top keywords connected with that word. It will take you through the entire alphabet as seen below:
Once you get all of these ideas from these great tools, how do you come up with your own list of keywords that will fit in with your account? This is what we are here to go over in Excel.
Back in October I wrote about an Excel build out sheet that utilized the formula: ‘Cell A & ” ” & Cell B’ in order to come up with keywords along with the campaign naming convention and the ad group naming convention. This sheet is also available as a whitepaper download. This sheet works wonders for account structure as you implement keyword build outs. However, it does have limitations. If you want to add modifiers, adjustments have to be made in order to do so.
If your goal is to just come up with a list of keywords you want to implement into your account, one simple formula will allow you to do so in a quick manner. This formula is as follows:
=IF(ROW(B1)>COUNTA(B:B)*COUNTA(D:D),””,LOWER(INDEX(B:B,IF(MOD(ROW(B1),COUNTA(D:D))=0,ROW(B1)/COUNTA(D:D),INT(ROW(B1)/COUNTA(D:D))+1)))&” “&LOWER(INDEX(D:D,IF(MOD(ROW(B1),COUNTA(D:D))=0,COUNTA(D:D),MOD(ROW(D1),COUNTA(D:D))))))
Utilizing this formula you can simply create two lists: one list of categories (ie: accounting, accountant, finance, finances) and one list of modifiers (ie: services, agency, association, help). Then insert the formula above, and you have your list of keywords. (NOTE: The exact formula above works if you start the categories list in Cell B1 and move down, and the modifiers list in Cell D1 and move down as seen below:
Cell F1 will have the formula listed, and then drag that formula down the F Column. As you can see, your keyword list will then live in the F Column.
This tool can also be used in reverse with the simple formula below:
=IF(ROW(B2)>COUNTA(B:B)*COUNTA(D:D),””,LOWER(INDEX(D:D,IF(MOD(ROW(B2),COUNTA(D:D))=0,COUNTA(D:D),MOD(ROW(D2),COUNTA(D:D))))&” “&LOWER(INDEX(B:B,IF(MOD(ROW(B2),COUNTA(D:D))=0,ROW(B2)/COUNTA(D:D),INT(ROW(B2)/COUNTA(D:D))+1)))))
When would you use this tool in reverse? Below are examples of keywords you could add to this list for an accounting firm:
Hey, this is a great list, but all of these keywords would be in different ad groups in my account. Anyway we can get the ad group beside the keyword for implementation purposes? Sure there is!
Utilizing an IF statement such as the one below can help with this process:
=IF(ISNUMBER(SEARCH(“accoun”,F1)),”Accounting”,IF(ISNUMBER(SEARCH(“finan”,F1)),”Finance”,IF(ISNUMBER(SEARCH(“book”,F1)),”Bookkeeping”,IF(ISNUMBER(SEARCH(“tax”,F1)),”Taxes”))))
As seen below, this puts an ad group name with each keyword created. In this case the ad group names are based off of the categories:
It is quite amazing how much easier one excel formula (or a couple Excel formulas) can make your life in the paid search world. I have found that learning new tricks in Excel has helped me with account efficiency.
