Moz Q&A is closed.
After more than 13 years, and tens of thousands of questions, Moz Q&A closed on 12th December 2024. Whilst we’re not completely removing the content - many posts will still be possible to view - we have locked both new posts and new replies. More details here.
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
-
Unsolved Link Tracking List Error
"I have been maintaining 5 directories of backlinks in the 'Link Tracking List' section for several months. However, I am unable to locate any of these links at this time. Additionally, the link from my MOZ profile is currently broken and redirects to an error page, no to Elche Se Mueve. Given the premium pricing of MOZ's services, these persistent errors are unacceptable."
Moz Pro | | Alberto D.0 -
Unsolved Questions about travel industry keywords
My question is regarding my current learning of SEO and keyword research. I have been leaning towards Keyword Everywhere, SEMrush, and Google Keyword Planner for keyword research. What do you recommend? I know you might suggest Moz, but I'm looking for something that will enhance and help me find valuable keywords for the travel niche. If I sign up with Moz, will someone from the team help me with keyword research for the travel niche and my website? I haven't found any resources or lessons specifically focused on travel websites and their keywords.
Moz Pro | | RavenBhutan0 -
What's the best way to search keywords for Youtube using Moz Keyword explorer?
I want to optimize my youtube channel using identified keywords, but I'm concerned that the keywords I'm identifying work well for SERP's but might not be how people search in Youtube. How do a distinguish my keywords to be targeted for Youtube?
Moz Pro | | Dustless0 -
Capitalization matters? Are Keywords treated as Case Sensitive?
I have searched the forum for this questions and basically found the following answer: “Neither Google nor SEOmoz treat keywords as case sensitive. For example, “Jobfair” and “jobfair” should return the same results.” http://www.seomoz.org/q/does-capitalization-matter http://www.seomoz.org/q/are-keywords-case-sensitive http://www.seomoz.org/q/capitalisation-of-campaign-keywords-why-does-this-affect-traffic-but-not-rankings How then can it be that I get the following results in the Rankings Report? (see example pic) And more importantly, which one is correct? sCx74sd.jpg
Moz Pro | | lwadmin0 -
Any Other PAID Keyword Difficulty Tool?
Why is The Keyword Difficulty Tool so unreliable? Anyone know of another PAID Keyword Difficulty Tool? Right now I feel like I am getting ripped off for $99 a month for a tool that I have no confidence in...PLEASE HELP ANYONE
Moz Pro | | Local-Interactive0 -
Keyword Difficulty Score Not Available
What exactly does this mean? That the service is down? Or the score will never be available? It seems like I get that and then the tool gets stuck and I get no other data.
Moz Pro | | endlessrange0 -
Colors in keyword Difficulty Report
Hi Everyone Two quick questions today 1. How can I find out hat the different colors within the keyword Difficulty Report represent and how can I see examples of how this information can help us with our data analysis? 2. The second question I have is regarding the Term Extractor. Seems when I ran a domain it provided the wrong data. For example, it stated that a certain keyword exists certain number of times within the description and title of the page but when I looked at the source this was not the case so it made studying the competition harder.
Moz Pro | | DRTBA
Any suggestions or has anyone else noticed this? Thanks in advance for all your help.0 -
What kind of keyword difficulty should I be aiming for when launching an new site?
I know that words in their 20's or 30's would be ideal, but it's proving hard for me to find relevant keywords with such scores (just a couple with scores in the 30's). Is going for words between 40-50 a waste of time? Thanks.
Moz Pro | | ZakGottlieb711