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
-
Since Removed?
Hello, everyone. This is Brad. I am new to Moz. I have a question. When I check my rankings in Moz Pro I get lots of high-ranking keyword phrases that are listed as "since removed". What does this mean? I am attaching a screen shot.
Moz Pro | | b3bailey0 -
Getting my top keywords separated out in SEOmoz reports
I am using the standard functionality to produce weekly Moz reports. There does not seem to be a setting to show rankings of my most important keywords. It would be nice to have those high-level keywords on the first page. For example, I have 200 keywords in an account. I want the report to show on a page my 10 most important keywords. Is there a way to set up a Label to my keywords in order to product a report page just for those keywords?
Moz Pro | | clicktoshop0 -
Branded keywords
I'm trying to understand this concept a little further. Say if I create a branded keyword, "dc widgets" I then start to add keywords such as "maker" "toy" and apply the branded keyword to it. Does this mean that they then become "maker dc widgets" and "toy dc widgets" ? What purpose does branded keywords offer?
Moz Pro | | priceseo0 -
Keyword report
What's the purpose of the keyword difficulty report? I've typed in some of my keywords and discoverd their "highly competetive." As if I didn't already know this. 😉 The question is, what do I do about it?
Moz Pro | | sakeith0 -
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 -
Best Keyword Difficulty Tool?
Hi All, I'm a bit frustrated with the fact that I can only enter 5 words at a time into SEOmoz's keyword difficulty tool. Does anyone know of a better way (or tool) to analyze keyword difficulty for hundreds of keywords?
Moz Pro | | nicole.healthline0 -
What's up with Yahoo and Bing in my keyword rankings?
SEOmoz is good I've learned a lot from it. I'm on my 3rd month and now have 8 keywords in top 50 for one site and 1 keyword in top 50 for another. This is for Google US. For Bing and Yahoo! US however I am still not in the top 50 for anything. I have read elsewhere that meta-tag page words can be regarded by these engines as a spam signal. In my meta I have used competative keywords that are relevant to my content, some of which are in the body, but are not my actual targeted keywords as a red-herring to potential competitors. Also might be usefull for other smaller places that still use meta-tags. Could not appearing in BingHoo! have something to do with their alogrithms and the meta-tags? or is it an SEOmoz issue? I have another question also. The " / " after a domain name doesn't appear in my Firefox address bar (I think I did a 301 redirect), but when I copy and paste it into an entry box (e.g. for a link submission) it puts the " / " there at the end which I take off. Does this make any difference?
Moz Pro | | Zoolander0 -
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