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
-
Negative Keywords
This will end up being a two part question: We have been running a search marketing campaign for about 45 days. It seems no matter how many negative keywords we add (over 300 right now), we still are receiving borderline relevant traffic to the broad match ad group. We are in the printing industry and I think this in itself presents a challenge since there are thousands of competitors in our market, national & local. People also search for a variety of items, most of which end up having nothing to do with our business (i.e. animal prints, zebra prints etc.) We are running several different ad groups: one exact match, one broad and one phrase match. Does anyone have a resource or links they are willing to share that has general negative terms they use before creating any new campaign? I have ones I have found, but wondering if there is a very good master resource out there. How many key phrases do you typically add to an 'Exact Match' ad group? Thank you!
Paid Search Marketing | | SEOSponge0 -
PPC Keyword Phrases and Options
Sorry in advance.... I've used search on the SEOMoz site and Google but not found an exact answer to my question. I am looking at starting PPC campaign - do I need all variations of these keywords or is google clever enough to work on dashes etc... I want the keyword phrase to come up with loads of different locations. Do I need to input them all separately like I have below? 1. Keyword Sample Phrase 2. Keyword-Sample Phrase 3. KeywordSample Phrase 4. Keyword Sample Phrases 5. Keyword Sample Phrase Location 6. Keyword-Sample Phrase Location 7. KeywordSample Phrase Location 8. Keyword Sample Phrases Location Thanks in advance....
Paid Search Marketing | | JohnW-UK0 -
PPC : Do I have to create differents Adwords account for my 2 companies?
Hello Mozers, I have 2 small companies in 2 different fields (tourism and IT). Do I have to create 2 different accounts for my Adwords campaigns? Is it the good way of proceeding? Thank you for yours answers guys, Jonathan
Paid Search Marketing | | JonathanLeplang0 -
Could longtail keywords really produce up to 80% more organic traffic long-term?
I was listening to a podcast on site visibility's website and they were discussing www.hittail.com which is a piece of software which analyzes lists your
Paid Search Marketing | | whitbycottages
visitor stream in real-time and provides actionable list of precisely which
keywords the website should be targeting to dramatically grow your organic
search traffic using long tail key words. The say they can come up with a list of long tail keywords which the
website could easily rank for hopefully straightaway in the top five positions
on Google and other search engines by creating a blog post are some relevant
content. Or you could use the information to form some anchor text links etc They say it's possible to produce up to 80% more traffic organically
once you are aware of which keywords are being overlooked by the website and
then produce the relevant content. The theory is that most people focus on the high traffic short tail
keywords and overlook the long tail keywords and I got to admit I actually fall
into that category unfortunately. Anybody uses particular website? And what is your experience of targeting the
longtail keywords have they produce good results ?0 -
Can I pass keyword match type through the Adwords dest URL?
Hi guys, I'm currently using duplicates of the same keyword with different match types, and I'd like to pass the keyword match type through the query, the same way I can pass the keyword and creative. ie. http://my.site.com/?kw={keyword}&kwmt={matchtype}&aid={creative} As far as I can tell, Google allows you to pass the keyword, creative (ad id), network and a mobile flag. Microsoft allows you to pass the match type and and ad id, and I want to be able to do the same in my Adwords. Is this is possible, or is the only way to create different ad groups for each match type and pass a custom query? Cheers, Jez
Paid Search Marketing | | jez0000 -
Curious about Adwords keyword tool results.
Why the Google Adwords Keywords tool says there are 0 local monthly searches for a keyword or shows a dash, but in spite of this there's competition for those keywords (up to 0.42 in some cases) ? Thanks!!
Paid Search Marketing | | gerardoH0 -
How to set up Adwords Account for Keyword Research
Hello, At the advice of some knowledgable people around here, we are setting up a $200-$300 Adwords campaign to determine keyword research. Our site is already doing well but we want to make sure we are targeting all the appropriate terms. What do we need to keep in mind in setting up this campaign. I assume we would do some broad match terms with a bunch of negative matching. Thanks!
Paid Search Marketing | | BobGW0 -
Has anyone seen a recent study on % of clicks on Adwords vs Organic results?
HI, I am trying to find out what percentage of people click on organic search results on Google versus the percentage of people who click on advertisements. I have found many references online to studies that supposedly say 70-80% of clicks go to organic results and the rest go to ads, but I have never seen an actual study claiming this, let alone a recent one. Can anyone help? many thanks in advance, Annemieke
Paid Search Marketing | | AnnemiekevH0