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
-
Can you highlight a list of keywords in reports?
Hello all, My client recently asked me if there was a way to highlight a specific list of keywords to put on the first page of every report. These would be the highest priority keywords that we would always want to know the status of for his site. In the reports section, I am only seeing options for organizing the keywords by rankings improved/declined and comparing to competitors. Would anyone know how to label/categorize this list of keywords and then produce them as part of the monthly reports? Thank you,
Moz Pro | | Level2Designs
Daniel0 -
MozScore. Why doesnt it let me Input all my keywords to get the Score? Why do i have to do it 5 at a time?
hey guys maybe someone can shed some light. why do i have to only input 5 words at a time in Keyword Analysis Tool rather then just inputing my Keyword list and getting the moz score for each one. ie: if i am initially just looking for the mozscore and nothing else? Any input?
Moz Pro | | BizDetox0 -
Keyword Difficulty tool
Hello Guys, Please someone explain how the keyword difficulty tool works and decides the difference super competitive v easy to rank keyword. Also the performance of this tool has been really bad since August i have been getting errors i.e. try again in 20 minutes. Thank you in advance for your replies 🙂
Moz Pro | | um090 -
Erroneous "Weekly Keyword Ranking & On-page Optimization Report" For Campaign
Hi, I just received an email alert from Seomoz telling me my "Weekly Keyword Ranking & On-page Optimization Report " for the period 11/06/12 - 11/13/12 is ready. It is just a copy of the previous report though, all rankings and ranking changes are the same. What is up with that? Best regards, Martin
Moz Pro | | TalkInThePark0 -
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 -
Keyword tool: SEOMOZ spacific month ? vs adword tool 12 month average but same data ???
Running a keyword analysis in SEOMOZ it shows my the folowing information "Local Search Volume (Dec)". I compared the data for the specific country , language and keyword with the adwords keyword tool and it exactly showed me the same numbers. The adwords keyword tool shows: "Local Monthly Searches: This column shows the approximate 12-month average number of search terms matching each keyword" http://support.google.com/adwords/bin/answer.py?hl=en&answer=25148 So if the numbers are the same in google keword tool and SEOMOZ why is SEOMOZ saying that for a specif month? If the data is the same one of both can not be right or probaly I didn't get the point. See screenshot: http://screencast.com/t/GyaaW7EkwV Thanks for help
Moz Pro | | n-media0 -
List sub-domains for a root domain.
Is there an easy way to find out all the sub domains registered for a given root domain. Our site (ea.com) has literally hundreds registered sub domains. I need to track down the ones that are public. It would be nice to get a list of public sub domains, along with some link metrics on each. Maybe this could be a new linkscape feature. Thanks, David Fricks
Moz Pro | | davidfricks0