Remove geographic modifiers from keyword list
-
I just pulled a search term report for all of 2013 from my PPC account. What I got was 673,000 rows of terms that have garnered at least 1 impression in 2013. This is exactly what I was looking for.
My issue is that the vast majority of terms are geo-modified to include the city, the city and state or the zip code. I am trying to remove the geographic information to get to a list of root words people are interested in based on their search query patterns.
Does anyone know how to remove all city, state and zip codes quickly without having to do a find and replace for each geo-modifier in excel?
for example, if i could get a list of all city and state combinations in the US and a list of all zip codes, and put that list on a separate tab and then have a macro find and remove from the original tab any instances of anything from the second tab, that would probably do the trick. Then I could remove duplicates and have my list of root words.
-
If you filter and show only cells that have been formatted a particular way, say red, all you should have to do is copy and paste the results into a new column. Then you should be able to find and replace the city state with nothing once. That should yield your root keyword list.
Just make a copy and delete the other columns if they're presenting an issue.
-
I believe this will only solve half of my problem. I would still have to do a find and replace (with nothing) one by one. The only difference is that my working list would be smaller. Right?
What I need is a way to do a bulk find and replace for partial matches. So, if my working list of exported search queries is in column A and my list of city and state and zip code combinations are in column C, I need some function that will take the full list of column C and find those as partial matches in column A. If found, it should remove that part of the query.
for example, if I have the query "hamburgers phoenix az" in column A and in column C i have "detroit michigan", "phoenix az", "des moines ia", because "phoenix az" is an option from column C and partial matches the query in column A, the net result should be "hamburgers"
Hope this makes sense
-
This is going to be something of a hack, but likely a time saver. Apply conditional formatting based upon a partial match, likely city state zip. Enter chosen city state zip into column B. The link should walk you through the specifics.
Next, show only conditional formatted cells. I'm pretty sure you can do this with a filter. I haven't used Excel in a while. I like Sheets now.
Select all formatted cells, which should just be a shift+down input. Once you've copied and pasted it into another workbook, you can do a find and replace (with nothing) for the city state and zip. If it works like I remember, you should have your root keywords.
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 Suggestion Tool
I want to know is their any good keyword suggestion tool other than Google keyword suggestion tool and keyword planner. I need to have a list of free ones only for now.
Moz Pro | | csfarnsworth0 -
Keyword Rankings
Hi there, Are you able to request keyword rankings for a particular day? This would help me with client reporting. If you have any additional information, I'd greatly appreciate it. Regards, Andrew
Moz Pro | | JuiceBoxOM0 -
Keywords Best Practices for On-Page Optimization
Hi guys, we've successfully optimized our home page such that it receives a Grade A for 3 completely different, high traffic keywords. Looking forward to seeing the results! The keywords in question were identified by using the monthly searches reported from the Google Keyword Tool. For one of the keywords, the Google Keyword Tool differentiates between what I thought would be seen as being the same. For example, let's say Google reports these three keywords as high traffic keywords: tea cup
Moz Pro | | yacpro13
tea cups
the tea cup Using the On-Page Report Card, we get a Grade A for 'tea cup', but we get an F for the other 2 terms! I thought Google searches didn't really care about the plural form or adding the word 'the' in front. How should we interpret the result from the On-Page Report Card for the plural form of the keyword and with the word 'the' added in front? Would you track all 3 instances of the keyword independtly in your campaign, or would you just track 'tea cup'? Thanks!0 -
Is the keyword difficulty tool the most helpful in all situations?
I understand that the scores it generates are essentially based on the difficulty of appearing on the first SERP for the keyword in question. That said, I am having a lot of difficulty finding keywords in my niche which return a score that would make this easily achievable for a site of my size.... The reason I'm pointing this out is because theoretically, a keyword could have a HIGHLY competitive first SERP, with a significant drop-off on the second SERP, which would make achieving a top ranking on that page substantially easier. So my question really is, is the importance of appearing on the first SERP so unequivocally important that it is a pointless activity to attempt deliberately to rank for keywords on the second SERP, which is ignored by the keyword difficulty tool? I know the breakdown of clicks goes something like 40% for top spot, 12% for second and downwards from there, but if a certain query has over a million searches per month, for example, it would still be possible to get considerable amounts of traffic by trying to rank highly on the second SERP, which the keyword difficulty tool cannot help with. So is this really a useless activity?
Moz Pro | | ZakGottlieb710 -
I need a tool/tools to extract keywords from say 50 sites in one niche and then check the rank tor those sites
This is for telemarketing of seo services I want to have some insight into an industry before I call them could this be done with the adwords keyword tool api and then exported to excel. It would also be nice to have data on backlinks say from seo moz opensite explorer.. Its just that the research you do before you even call a potential client is so time consuming and you can never really check to see how they are ranking for there main keywords manually. We are trying to automate as much of this initial research as possible... Any Idea's Thanks
Moz Pro | | duncan2740 -
Is there a way to manually update the keyword rankings tool in SEOmoz?
It appears that our keyword rankings update weekly on Thursdays. Is this the same across the board or is there a way to manually run an update?
Moz Pro | | Motivators0 -
SEOmoz keyword difficulty tool
Is anyone else having problems with this? Every search I do seems to throw up an error in the traffic fields.
Moz Pro | | neooptic0 -
SEOmoz mobile search keyword tracking?
Does anyone know if we can expect keyword rank tracking in Google mobile? It would be a cool feature for SEOmoz Pro. Does anybody know of any reliable software that does this at the moment?
Moz Pro | | A_Q0