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
-
What is your process for finding the best keywords for which to optimize and track?
(I searched but didn't find anything similar here) When you start a campaign, what is your method for finding the best keywords to optimize on your site and to track? Please share methodologies and tools on Moz that you use. I of course use Keyword explorer, and choose the higher volume keywords, but I am not sure I am looking in the most effective places. Any discussion on this would be greatly appreciated.
Moz Pro | | bizmarquee1 -
Improving ranking for a more specific keyword - eg 1 word
Hi there Ive managed to get my site ranking in top5 of Organic search for a 2 word keyword "keywordname australia" for example or "buy keywordname" - however finding it a lot slower process improving rankings for just the actual 'keywordname' Any suggestions as to what might be the best thing to focus on to improve a more competitive term?
Moz Pro | | IsaCleanse0 -
Can we scan for Competitor keywords with Moz Pro?
Hi All, Can we scan for Competitor keywords with Moz Pro? Wanted to know if we can check all keywords a site is ranking for... Thanks
Moz Pro | | jomin740 -
How Tro transfer Keywords from one campaign to the other
I have defined a list of keywords (along with labels) in a campaign that I no longer use. I would like to use the same keywords along with the brands and labels in a new campaign . Is there an automated way to do this ? Or do I have to do it manually one after the other ?
Moz Pro | | Chaits0 -
Why are the rankings of my keywords different to rankings from other programmes such as 'Access my Lan' and 'Firefly'? Does SEOMoz count 'Google Places and Maps' in their rank count? Im thinking this could explain the difference
Why are the rankings of my keywords different to rankings from other programmes such as 'Access my Lan' and 'Firefly'? Does SEOMoz count 'Google Places and Maps' in their rank count? Im thinking this could explain the difference
Moz Pro | | john_Digino0 -
Batch lookup domain authority on list of URL's?
I found this site the describes how to use excel to batch lookup url's using seomoz api. The only problem is the seomoz api times out and returns 1 if I try dragging the formula down the cells which leaves me copying, waiting 5 seconds and copying again. This is basically as slow as manually looking up each url. Does anyone know a workaround?
Moz Pro | | SirSud1 -
Keyword tool results - broad match or exact?
Do the SEOmoz keyword rankings display results for exact match or broad match?
Moz Pro | | ClaytonKendall0 -
On Page Report Card... with or w/o local modifiers?
Hey all! So I am curious how you recommend using the "on page report card" (which is really helpful) along with the concept of local modifiers. IE, here is a term I am going after: business forums but really I care about a specific location: business forums | Greensboro NC So the word I hear is typically to do your keyword research & page optimization FOR the primary term, but then tack on your local modifiers after. So which do you run reports on? Probably both is the best answer, eh? Obviously my local sites won't have a shot at ranking nationally/internationally for such a broad term as "business forums", especially with some monster sites out there with some serious clout. This is more of a best practices question. Thanks dudes.
Moz Pro | | nsmcseo20