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
-
Does google see the keyword "e cig" and "e-cig" as the same word? on MOZ it shows that they have a totally different amount of search quarries.
does google see the keyword "e cig" and "e-cig" as the same word? on MOZ it shows that they have a totally different amount of search quarries.
Moz Pro | | smokingllc0 -
Keyword stuffing - on page grader count
I used the on page grader for my homepage, www.cprnj.com, and it said the keyword "physical therapy" was used 26 times in the body of the page. But I can't find more than 4. How exactly is keyword stuffing accounted for, and is there some place that I am not seeing the keywords?
Moz Pro | | CPRNJ-JC0 -
Can you save a keyword in its own group?
Hi everyone, I have been doing competitive research, but after researching several hundred keywords, I realized that they were all combined. This made it difficult and time consuming to do further research because they were all grouped together. Is there any way to create each group of keywords in their own separate group? I see that you can create campaigns, but that asks for a comparison of websites. As I do not have a website that I want to track or compare, I prefer not to do that. Thanks so muck for the help. Don
Moz Pro | | rcman1 -
Non-branded keywords sending search visits???
Hi, You announce a number of keywords sending search visits much larger than what you show in the interface. How do I get the list of the keywords you found?
Moz Pro | | ResourceLab0 -
How do I delete labels from the list of labels we've used?
How do we delete labels from the pulldown in moz, that we already created, but no longer use. The function "delete labels" only deletes labels off of certain keywords. But we cant figure out how to remove unused labels completely from the list of labels. Thanks, Thanks, Andrew
Moz Pro | | jampaper0 -
Meta keywords no longer in use
Can someone point me to the official article explaining why meta keywords are no longer taken into account by search engines please? I know Moz has indicated that search engines ignore them, but I would like to read a bit more about it - what was the reason behind it and since when.
Moz Pro | | coremediadesign0 -
Still Getting Keyword Self-Cannibalization
I used the onsite optimization and made some changes to my post, but am still getting the Self-Cannibalization mention in my results. It lists this: "Real Estate Careers | Keller Williams | Career In Real Estate" as the link that is not supposed to be there. This is not a link, but the title of the post. What am I missing?
Moz Pro | | brentmitchell0 -
Regarding Avoiding Keyword Self-Cannibalization
I ran some of my pages through the 'on-page report card' and one thing that kept popping up was to Avoid Keyword Self-Cannibalization. I thought the best SEO practice was to have anchor text at the end of your article that matched your target keywords. Is this not accurate? Please let me know the best practice so I can make sure my pages are all optimized correctly. Thanks. Here is an example: http://www.youdrivethesuccess.com/culture/real-estate-commission-split/
Moz Pro | | brentmitchell0