PDA

View Full Version : Adwords Keyword List Generator



orion_joel
10-27-2008, 03:41 AM
Hi All,

I have quiet a few ads to set up for product's in adwords, and while i don't mind the manual approach to ensure i get everything, it sure would make it easier if i could auto generate a list of keywords and copy and paste when that step comes up.

What i am thinking about is more so i come up with my 10-15 normal keywords, but then a program that will automatically tag on the location specific keywords for example. I come up with Keyword 1, Keyword 2, etc. Then the program would add to each keyword all the place names i put in like Brisbane, Cleveland, etc.

The reason a program would be handy is at the moment i often limit myself to about 3 or 4 location names tagged on the end of each keyword, however i would like to expand this to about 20 or 30 suburb names, to try and capture as many search queries as i can. Doing this manually would take me 10 times as long.

Thanks for any help.

vangogh
10-27-2008, 12:26 PM
Try this keyword list generator (http://tools.seobook.com/keyword-list/) from SEOBook.com. It takes a list of keywords and a list of modifiers and combines them into a list. There's a video at the top of the page showing you how to use it. I think it'll do what you want.

Check out some of the other tools at SEO Book too. Aaron has lots of good free stuff on the site.

Aaron Hats
10-27-2008, 01:56 PM
Why not just use the keyword tool built into Adwords?

vangogh
10-27-2008, 02:27 PM
Aaron this is more about combing two lists. One would be a list of phrases you might find through a something like the AdWords Keyword Toll and another list might be a set of modifiers.

For a simple example imagine your site is about real estate and you build a list of keywords like:

real estate
real estate listings
property listings
real estate investment
etc

You know you want to use the same phrases in many cities and towns. "real estate" is kind of a useless phrase, where "real estate san diego" is much more useful. So you want to combine the above phrases with modifiers like:

San Diego
Los Angeles
San Franciso
Sacremento
etc

Those are now two separate lists and you want to combine them into one so you have

real estate san diego
real estate los angeles
real estate san francisco
real estate sacremento
real estate listings san diego
real estate listings los angeles
etc

That's a lot of typing. The tool I linked to and what I think Joel is looking for is something that will take your two separate lists and combine them so you end you end up with one long list without having to type everything out.

billbenson
10-27-2008, 03:31 PM
I have a couple of tools I wrote when I was starting with adwords. They are pretty cryptic and need to be redone. What I did is generate primary and secondary keywords.

So, taking VG's example it would come up with real estate san diego as well as san diego real estate. I can send you a copy, but there are probably better on line solutions.

The thing is, you probably want to generate from a generator with an input of real estate and another field of san diego:

real estate sandiego
san diego real estate

"real estate sandiego"
"san diego real estate"

[real estate sandiego]
[san diego real estate]

Add in a third term as most searches are for three key words ie san diego real estate prices and you get a laundry list of terms.

I generally have the best results using brackets ei [san diego real estate] or [real estate san diego]

I put the naked keyword, key words in quotes, and keywords in brackets in different groups as well. Usually with the same optimized ad. Gives you the option to tweak the ad though if one performs better than the other.

You can put a laundry list of keywords in a group and just start deleting the ones that don't get impressions. Doesn't mean that the same keywords wouldn't work better with a different ad though.

Takes a lot of messing with. Delete the ones with no impressions after a month or two though. Ideally you like to ultimately have 20 key phrases per ad, but I usually end up with 50 or so.

Like I said, I wrote my own, but they put out a lot of garbage. Stats are probably the best place to get keywords. Remember to look at stats for negative keywords as well!!

vangogh
10-27-2008, 04:10 PM
Bill, I think the SEO Book tool might do what you're tool is doing. I'm not sure, but I believe it will.

billbenson
10-27-2008, 05:59 PM
I just gave it a quick try. Didn't read the instructions but...

It didn't give:

real estate sandiego
san diego real estate

It only gave san diego real estate.

I tried a different search term, but the analogy applies.

A typical searcher will try a search like this:

San Diego Real Estate
San Diego Real Estate Brokers
San Diego Real Estate Discount Brokers

Several searches, so the order of the words may be important and doesn't necessarily make sense. It could be just as easily Broker San diego real estate. You need to cover all bases, particularly in the exact match.

That's not important unless you use [] which is for an exact match. I called those brackets before because I don't know what the actual term is for them, but brackets are {} and are not used to my knowledge in adwords. Just a clarification.

vangogh
10-27-2008, 08:11 PM
I thought it did reorder the words, but maybe not. Aaron might have another tool for that. There's quite a few on the site. It wouldn't give sandiego though since that word wouldn't have been on the list. The tool isn't removing the spaces (or was that just a typo on your part)

Word order can be important depending on the query. I wouldn't think 'estate real' would be meaningful, but people might search for either

san diego real estate
real estate san diego

billbenson
10-28-2008, 01:01 AM
I didn't look at it very closely, VG, so I t may have that option. But in a quick test, it didn't show reorderd results.

The other thing that would make me nervous about the tool is data collection. If they are asking your for your website and search criteria, what are thy going to do with that info?

I'd rather write my own. I'd do it in php, but excel would certainly be capable of this.

orion_joel
10-28-2008, 02:52 AM
Bill, i kind of agree, not so much on the ordering as i had not even thought of that idea, but it may be something that i will have to have a look at, as people may be searching for the area name then the search term.

However more so what i was looking for was basically joining one list to another list, but where each term in the second list was joined to each term in the first list. As per Vangogh's first example.

I tried out the link, i didn't watch the video however i thought that the directions did lack a little on the page itself, and all i could get out was the two lists joined together where every terms was just listed. I had not even thought about using Excel before you mentioned it, however CONCATERNATE and just filling the cells may do what i am looking for quicker.

billbenson
10-28-2008, 12:10 PM
I'm not very advanced in excel and tend to just do stuff in php. Having said that, Excel is a very powerful program. Major corporations hire Excel programmers. I know a web designer who designs only in excel

vangogh
10-28-2008, 08:08 PM
Joel I remember seeing someone (Might have been on SEO Book, might have somewhere else) talking about combining lists like you have solely through Excel. I don't remember where I saw it or I'd link to it.

Bill I never used to use Excel much, but then I decided to set something up on it for personal use that required a lot of formulas and advanced features. I learned a lot and now find Excel very useful. Actually I'm using NeoOffice now which is basically Open Office on a Mac, but the spreadsheet program is essentially Excel.

billbenson
10-28-2008, 10:33 PM
VG, I use excel a lot, but not for database sort of functions. I use it a lot in writing queries for php. Also, for simple db functions like a 2000 line contacts / passwords / stuff I want to refer to in the future list. If you recommend a tool I like, I toss it in there. When I want it its cnt F I type in Vangough and just scroll through those. Easy and simple database for most people.

As I eluded to above, large corporations use Excel and have Excel programming departments. It can function as a crude database. I suspect one driving factor in using it as a database instead of access or something else is that most employees are familiar with Excel and probably have never even opened Access or some other database program. I bet for a large number of people here, Excel is very under utilized. It's an incredibly powerful tool and I am sure that it can do what Joel wants and things that others here would like to do. Go through some tutorials or some dummies type of books!!

I have chosen to do the complicated stuff in php / mysql for a couple of reasons. First, I know php and I would have to study for a week or two to do the same things in Excel (or a similar program like Open Office or your Mac program). Secondly, it forces me to use php which I use for web design and I always learn something. I have no desire to learn asp for the same reason.

I would really encourage people here to dig into Excel or similar programs. It can do amazing things.

As a footnote VG, I am going to set up my new pc as a dual boot win 2k linux machine. I'm going to put php / mysql on the linux side. I used to do that on the windows side but I was never able to get the server globals working properly. I bet that will be easier using Linux.

What that enables you to do is write sites, tools etc., that are local and test site design when your ISP is down. Additionally, you could do things like keep a local password database that is very unlikely to get hacked. It makes things very convenient. Like I said I quit using it locally because I never could get the server globals to work locally under windows. I use them a lot in my programs. I have a feeling it will be easier under Linux running locally or at least find someone on a forum who knows how. It's really nice to be able to test stuff locally.

vangogh
10-28-2008, 11:59 PM
I tend to use Excel less as a database and more for things like you described. I guess in some ways it serves as a small and easily accessible database. I never got into using Access, though I know the basics of how to use it.

I also have a server set up on my laptop. On my old laptop I installed Apache and then PHP and MySQL and configured things to work together. On the Mac that was all pretty much installed for me. I had to tweak things some, but the basics were already installed.

I'm working on a project now where I'm using sqlite (http://www.sqlite.org/). I read up on it for a few hours Sunday night and I started using it yesterday. It's pretty good. You use the same SQL to interact with it, though the connection between PHP and the database is different. It didn't take me too long to be working with it. Like I said it was a few hours.

The database is stored as a .db file so it's easier to move around. I should be able to copy the file I'm using for development on my laptop, upload the file to a server and the database will be live.

orion_joel
10-29-2008, 12:15 AM
Excel is absolutely a powerful tool, literally. I have seen some of the extremely complex creations that can be done with Excel. One that was done at work, connected to a SQL database for source data and then allowed that to be fully used however was needed. However this particular application did greatly strain the limits of excel. This workbook had about 10 sheets, each with the ability to enter 2500 lines of data for different purposes. The problem that this workbook posed was that literally each of those 2500 lines per sheet (about 25,000 lines total) used a formula to either extract the data or manipulate the data based on the need. It required a manual update selection and the more data that was getting created in the database the longer the update would take, by the end of a day it was getting near a 30minute refresh time.

This is one of excels greatest downfalls, it does have a limit on the number of formula's before it will refresh every formula each time. I think this limit is somewhere between 2,000 and 3,000 formulas, so it is limited. The benefit in excel over access though is that while access can store and perform many more functions, excel is pretty much a instant update platform, where you make a change and that is reflected immediately where ever refereced, where as i have found this to be a little more difficult to get live changes in access, while i am sure it is possible it is not entirely as straight forward as excel.

The formula as i mentioned that will do this in Excel is Concatenate. Which as with may other things in excel is very powerful. It allows you to join together and cut and join many things. The simplest is just joining two columns, however you can also take the first X number of letters from one column and the last X number from another and join them, i have know places that have automated the creation of large lists of user names and passwords in this way.

I think i will give this a go with excel, and if not a success then keep looking.

vangogh
10-29-2008, 02:57 AM
I haven't done anything that complex with Excel, though I have a few Excel files with quite a few formulas running through them and sheets updating based on the values of other sheets in the same file.

Mostly my use is the usual database as a flat file. More often than not it's just an easy way to transfer data either through email of donwloading off the web. When I really want to do something with the data it ends up in MySQL and now SQLite as well.

Excel is really powerful though. The more I've used it the more I've realized what it can do. There are probably a number of things I should really be using it for. Anything with a relatively small data set and a limited amount of programming is well suited for a spreadsheet.

billbenson
10-29-2008, 06:51 AM
Interesting Joel. Out of curiosity, do you know off hand how to delete redundant info? Say you had a parts list in excel, and the same part appeared twice and you want to delete the lower priced entry. Is that getting into VB territory? I don't know VB.

I'd do the above in php / mysql - mostly because I know it.

VG, I didn't mention apache, but that's the same configuration I have used. I never bought the pre packaged solutions for that, mostly because I like to stick data on Disc 2 and only the operating system on disc 1. In any case, under windows did you get server globals to work such as $_SERVER['PHP_SELF'] etc. I never was able to get that to work locally under windows although I'm sure its a line in the config file.

If you haven't, don't go searching as I'm going to try to set it up under Linux this time around.

vangogh
10-29-2008, 12:35 PM
Bill, Things like $_SERVER['PHP_SELF'] always worked for me, though I don't remember ever having to do anything specific to get them to work. You might have had the register globals on/off set the wrong way.

I have had to remove duplicates from Excel. I did some quick searching and found a VB script someone had written.

orion_joel
10-29-2008, 09:23 PM
Bill, to automate the function of removing redundant information i think you would need to step into the VB for Applications land. As trying to use Excel formula's for automation, except in calculations is kind of touch and go.

One way you may be able to make it easier though is using a "IF" formula in Excel. This can quickly throw up an idea as to what may be duplicates. If all your data is formatted on a one line one record basis try the following formula in the last column after your data. Unfortunately this formula was created to test if a number was the same as another number in that column, and should work for letters as well i think.

"=IF(A1="","",IF(COUNTIF(A:A,A1)>1,"Duplicate","Unique"))"

The primary reason why the formula is nested is to disable the display of error correction, and additionally have nothing display when there is no data for it to test. But putting this formula in every cell at the end of the products, assuming the product code is in column A, you should be able to quickly pick up the duplicates.

Using multi sheets, where one sheet provides the data and you are using this formula to test from one sheet to another will often cause long time to update, where as a single sheet should not cause to many delays unless it has literally over 4 or 5 thousand records.

If you cannot get it going, and can PM me just some details like what order the data is in and even just one or two sample records i should be able to manipulate the formula to at at least detect the duplicate records for you. Automating the removal of duplicates based on which is smaller is another thing that i don't think would be that easy to do without a lot of knowledge of the programming.

billbenson
10-30-2008, 03:04 PM
Thanks Joel. I saved that for the next time I need it.

We'll see if it works on my new machine when I get it put together VG.

vangogh
10-30-2008, 05:49 PM
Hopefully it does. It was probably just one line missing in one of the config files. Either httpd.conf or php.ini

orion_joel
10-31-2008, 01:33 AM
It should work fine i think, where you may use it every so often, I was using it to validate data was unique on a daily basis, and it was working great.