Does anyone know of an excel template for creating keyword combinations?
-
I am looking for an Excel template with Macros that would allow me to drop a column of keywords next to a column of modifiers and it would build out thousands of keywords with a Macro. Can anyone drop their best SEO/SEM Excel template spreadsheet links in the comments?
-
does this help you? http://textmechanic.com/Permutation-Generator.html
-
Hmmm, that's a bit more complicated. I'm sure there's a Macro out there that can do it, but I don't know one off the top of my head.
I think this can be done without a Macro but it will take a little bit of manual work. Here's what I would do:
Place your two sets of keywords in Columns A and B. Select all of Column B and cut (ctrl+x) it. Then in Cell B1, right click, select Paste Special > Transpose. Then, move Column A down one so the list starts in Cell A2.
This will create a matrix with one set of keywords in Column A and the modifiers in Row 1.
Next, in Cell B2, enter this formula:
=$A2&" "&B$1
Copy this across all cells in the matrix and you'll have 10,000 unique keywords. You will then have to remove the formulas by copying the entire sheet, right clicking in Cell A1 and selecting Past Special > Values.
Finally, you'll have to do some manual work of cutting and pasting all the separate columns into one long column of 10k keywords.
I know this isn't exactly what you're looking for, but it's the best I can think of for know. I've updated that spreadsheet example to give you a better idea of what I mean - https://docs.google.com/spreadsheet/ccc?key=0AsNfzI57aVSXdGFKME1pamRnZkJNandZWVpoVkFXZkE
Or, if you want to send your initial set of keywords to me, I wouldn't mind giving it a shot myself. Feel free to send it to tim@prontomarketing.com.
Best,
Tim
-
Hi Tim,
Thanks so much for your response. To be more clear, I would have a row of 100 keywords in column A, and 100 keywords in column B. I would like each of the 100 keywords in column A to be combined with each of the keywords in column B giving me 10,000 keywords.
-
If I'm correctly understanding your goal here, I think this can be accomplished by just using an Excel formula. You shouldn't need a Macro to combine two columns.
Place your keywords in Column A and the modifiers in Column B. Then, in Column C, enter the following formula:
=A1&" "&B1
Copy this formula down the list and it will combine the contents of the two cells separated by a space. Here's an example I put together in Google Docs: https://docs.google.com/spreadsheet/ccc?key=0AsNfzI57aVSXdGFKME1pamRnZkJNandZWVpoVkFXZkE
Hope this helps!
Tim
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
Keyword & negative keyword overlap
So I just read your blog on quality score and after reading the negative keyword section I'm a little confused and I need clarification. In that paragraph you mentioned about not overlapping your negative keywords with your active keywords and you used an example of dog food and dog bed. So my question is, if you put the word dog bed into the negative keyword list isn't the word dog the over lap word? Would you ad not show because the word dog is in the active keyword list?
Paid Search Marketing | | Vallerinspects0 -
AdWords Negative Keywords
Hello community! I got a bit stuck with negative keywords management today! I have updated the negative keyword list and as it can be shared among all my active campaign I put it into the shared library. But when I downloaded the recent changes on Editor I can't get to see the new negatives and I can't check their match types. Do I have to upload the list on Editor for each campaign? Thanks
Paid Search Marketing | | PremioOscar0 -
How Do I Track AdCenter Keyword Data in Google Analytics?
Like everyone else, I am looking for new ways to gather as much keyword data as I can in the wake of the dreaded (not provided) epidemic. This question focuses on keyword data from Paid Search. We all know this is very simple with AdWords because the data is right there in Google Analytics. MSN adCenter, however, is a different story as these parameters need to be added to the end of each destination URL. Here are the parameters I'm using: utm_source=Bing&utm_medium=cpc&utm_term={QueryString}&utm_content={AdID}&utm_campaign=Leads Everything is working well with the exception of {QueryString} which is returning nothing. I would really like to solve this issue because I do not have the time to add specific parameters for each keyword in all of my campaigns. Any advice would be greatly appreciated!
Paid Search Marketing | | IDASEO0 -
A heads-up about problems with the new Google Adwords Keyword Planner tool
I noticed that when I was using this tool, the data in the tables would change, often by a lot (up to 50%) when I sorted the columns. (Did nothing else, just sorted.) Since I was starting a new campaign and really needed data, I contacted Google about this and was told: "I understand that the estimates are changing in the Keyword Planner tool when you sort the data. I reproduced this on my end as well and was able to confirm that this is a known technical issue on our end." "Unfortunately the information in the Keyword Planner Tool is currently not the best source of data for your analysis." They then went on to suggest I just test things blindly. So, what do you do to get information about keyword traffic and ppc estimates?
Paid Search Marketing | | Linda-Vassily0 -
Adding nyc to a keyword in Google Adwords
Hello. If I have a keyword in Google Adwords that is using phrase match ("keyword"), could it be useful to add the keyword phrase "keyword nyc"? Even if I did not add the second keyword phrase, my ad would be triggered if someone searched for "keyword nyc." So would it be redundant to add the second keyword phrase? Thank you!
Paid Search Marketing | | nyc-seo0 -
Broad Match Modifier: Should I use the + on the first keyword?
I am playing with Adwords and want to use the broad match modifier, however I see some examples always using the + in front of every keyword and some with with it on just the secondary keyword or more e.g. personalised +towels
Paid Search Marketing | | Towelsrus
+personalised +towels Does this give different results? And which is best practice0 -
Best keyword traffic analysis tool for long tail search terms?
Please bare with me, this might turn into a bit of a waffle, but I'll get to my question... I promise! I've just been looking at our CPC traffic for April and 2 search terms jumped out at me. I recognised them from previous keyword research because they are search terms that I expected to be high traffic (from past experience), but Google Adwords keyword tools showed them to have no potential traffic, and next to no potential traffic (literally 0 local searches and 12 local searches per month). Last month search term A had 46 visits, with 19:25 average time on site and 8.70% bounce rate and search term B had 10 visits with 14:47 average time on site and 0% bounce rate. For very boring reasons we are not currently able to measure conversions on these terms since (they are related to consumer finance and when a customer applies for finance it is all done on our finance providers website) but despite the low volume, these are pretty good figures for on site behaviour and so it got me thinking... Is there a more accurate tool to estimate traffic volume that we should be using rather than the Adwords tools? I appreciate that the estimates are probably made based on historic search behaviour and April's traffic could just be a one off, but these particular terms used to be insanely popular 4-5 years ago when I worked at a competing company.
Paid Search Marketing | | DWJames1 -
Exact url dynamic keyword insertion
A competitor of ours is using dynamic keyword insertion for exact urls in our field. I'm not 100% clear on this, but isn't that not allowed in adwords? I am not one that usually turns in a competitor for shady doings, but I'm not sure what to do. Any advice?
Paid Search Marketing | | ClaytonKendall0