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
-
Moz page optimizer - avoid keyword stuffing
Hi Moz community, A quesiton about our dutch travel insurance website. Two weeks ago we had a ranking drop from 17 to position 35. The project is still fresh (we started in may this year). I quess penguin 4.0 didn't like our rapid linkbuilding. But we also receive a message in page optimization tool for keyword stuffing. When we analyze the homepage www.reisverzekering.net for reisverzekering (meaning travel insurance) Moz suggests to avoid keywords stuffing. But I dont understand, we don't use the keyword that much and Yoast doesnt give this message. How much key
Moz Pro | | remkoallertz0 -
Branded Keywords: Should I Monitor My Products' Names?
I am setting up my keywords' tags and brand rules. Is there any reason not to consider my products' names as "branded keywords". For instance, if my brand is HealthNut and I have products (that people talk about by name), such as GreenGiant Juice, should I create a brand rule for "GreenGiant Juice"? Granted, most of the time, customers will be searching for "HealthNut GreenGiant Juice", but there must be at least a minority who would search directly for the product itself without using the brand name. Any thoughts are greatly appreciated. Thank you in advance!
Moz Pro | | EliteErikSEO0 -
How can I remove on-page reports from the Summary page?
Hi, I'd like to remove some on-page reports from the Summary page. I've already stopped them from running weekly. Is there a way to remove them completely?
Moz Pro | | csmm0 -
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 -
SEO process suggestions / keyword analysis
hi all, we've done a bit of SEO over the years for our site, and are looking to pick things up this year and spend more time on SEO. first off, we are working on revising our keyword list & would like to view what keywords specific competitors are ranking for and try to target those keywords as well. is this possible via SEOmoz (similar to spyfu)? please let me know if you feel this is a good first step in picking up our SEO efforts for the new year. i've been looking at the research tools & is seems the Keyword Difficulty Tool hasn't been fully functional for the past week or so ("Our Keyword Difficulty Tool is having issues displaying AdWords API data. We are working with the AdWords API folks to get a fix soon!"). also, i noticed i can enter competitor sites in open site explorer & compare link metrics, but i don't see anywhere to compare keyword metrics. is there something i'm missing? thanks again everyone!
Moz Pro | | lsat0 -
I'm having a hard time understanding branded keywords. When wouldn't I want to brand a keyword?
For example, why wouldn't I want to brand all my keywords with my company name?
Moz Pro | | PolarisWebSEO0 -
Structurize campaigns in SEOMOZ for different languages & keywords
I have a website in 3 different languages as www.mysite.com/fr annd www.mysite.com/en what is the best way to track keywords in different languages in SEOmoz how do i structurize the campaign? Do i create a seperate campaign for every language?
Moz Pro | | MiaClientdata0 -
Need a Google Doc That Imports List of Links From SEOMoz API
Does anyone have one they'd like to share. I've seen the great ones by Tom Anthony but I am looking for the list of the first 1000 links that are available as part of the free API to be brought back into a Google Doc.
Moz Pro | | belasco0