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
-
Would you launch a paid search campaign with 'Exact Match' only keywords
Hi Mozzers, I'm building a new campaign for a business start-up, and search volumes with the industry are HUGE! I want to target high commercial intent keywords, to maximise the number of conversions / sales from my paid search campaigns. Using the forecasting tool in Adwords, it looks like I can [exact match] these high commercial intent keywords and still get the click volume I'm aiming for. Would you, therefore, use this approach - where you only match at an exact level to control the quality of traffic coming through from the paid search campaigns? I plan to achieve relevance by having ad groups broken down into clear themes with around 10 - 15 exact match keywords per ad group. Let me know your thoughts... Thanks!
Paid Search Marketing | | Zoope1 -
Adwords Duplicate Keywords with Different Match Types - Good or Bad?
If you have the following keywords in an Ad Group advertising for a product, let's for example call it "target" product [target product] "target product" +target +product I've found that the exact match keyword has the highest conversion rate in almost all circumstances. So it would make sense to have a higher max bid on the exact match then phrase or broad batch. Even with lots of negative search terms to maximize conversion on the broader matches, if the bid is the same as exact match, the cost per conversion will be much higher (too high.) However in chatting with an Adwords Support Rep (on a different matter) they stated after looking through my account at the end of the chat: " duplicate keywords will impact on quality score. your all keywords will compete with each other" However many of the ad groups in question these duplicate keywords have quality score of 9 and 10. So obviously if there is an effect it seems it may be minimal. I thought it was pretty common for people to bid higher on more exact match and lower on more broad match. What's the real story here? Was this support rep not seeing the big picture?
Paid Search Marketing | | JCCMoz1 -
Locate Poorly Trending Keywords in AdWords
At the campaign level I see my CTR has been suffering for the last week or two relative to the past few months. It could be a few bad actors casing the downward trend. How can I quickly and easily locate specific keywords that are trending in the wrong direction? Why filters will not work... They don't take change into account. I want to identify Keywords that were once doing well are now doing poorly. Filters will yield all keywords that are doing poorly which is not what I'm looking for. Obviously I could be missing something with filters but this is my understanding. A few other points to note... I have search partners turned on. I do not want to take this traffic into account in my analysis. I'd also like to determine if it might be an ad that's not performing as well as it once was. I assume the same method used to find poorly trending keywords can be applied to ads as well but if not, is there a solution for this?
Paid Search Marketing | | tatermarketing0 -
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 -
Arabic Keyword Research
Hi All, Can anyone recommend good SEOs who specialise in Arabic? I've had my client's website translated into Arabic but the translation company do not specialise in local keyword research. They've translated literally I think (obviously not being an Arabic speaker, I can't confirm this). I need someone to review the translated site (only 4 pages so far), with a marketing head on, and ensure it includes the relevant local keywords used in UAE countries. Will also require deeper keyword research for a Google.ae PPC campaign and Ad copy written in Arabic. If I fail to find someone here in the moz community I'll head over to oDesk, where I'll find everyone and their mother-in-law proclaiming to be the worlds best Arabic SEO 😕
Paid Search Marketing | | seowoody
I like recommendations! Thanks, Woody 🙂0 -
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 -
I want to try some Google PPC ads on other sites but don't know what i'm doing.
We have seen a massive drop in traffic this year and i am contemplation using banner ads to try to increase sales and also to help with marketing. the problem is i know nothing about doing this. I have used it in the past but to limited success and i was just stabbing in the dark. I have a few questions. Am i better to target keywords related to the product or to the people who may buy my products? Is it better for direct sales or brand awareness? What kind of ROI can i expect if i get it working well? Is it better to pay for the big keywords or pic all the low hanging fruit? Does it work? Should i employ an expert, are they worth it? Any insights into the world of PPc would be a massive help.
Paid Search Marketing | | mark_baird1 -
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
