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
-
Difference keyword tool and related topics
Hello, Can someone explain me the difference between those 2 ? Because to me they are very similar. Thank you,
Moz Pro | | seoanalytics0 -
Using Keyword Tool Results
I love the keyword tool for giving me direction and helping to prioritize. My question: Once I have the prioritized keywords, do you recommend having a page for each of the top keywords, using the keyword as the page name? Or is it better to sprinkle the keywords into existing pages? Thank you.
Moz Pro | | bhsiao0 -
How to remove 404 pages wordpress
I used the crawl tool and it return a 404 error for several pages that I no longer have published in Wordpress. They must still be on the server somewhere? Do you know how to remove them? I think they are not a file on the server like an html file since Wordpress uses databases? I figure that getting rid of the 404 errors will improve SEO is this correct? Thanks, David
Moz Pro | | DJDavid0 -
Is the keyword ranking number used for MOZ Pro an average position taken across the time period?
Hi there I am just trying to confirm how the ranking given in the 'rankings' report is calculated (an average etc). Thanks Sarah
Moz Pro | | Laurie-Tomahawk0 -
Can I calculate "Keyword Difficulty" metric using Mozscape API data?
We already have a web application that pulls certain metrics about websites using the Mozscape API, but we are wanting to extend the usefulness of this application to enable users of the app to pull "Keyword Difficulty" metrics in bulk, instead of one at a time (or 5 at a time). I wouldn't mind the 5 at a time limitation if we could just automate the API calls and let the tool pull data for 50 or so keywords without user-interaction. I know that it's a "formula", but I don't know what SEOMoz uses for it's formula. Has anyone figured out a way to calculate this, based on the Mozscape API data? Has anyone ever tried to reverse engineer this metric?
Moz Pro | | brchap0 -
Is there a problem with the keyword rankings in the campaign?
It says last updated 19/08 and is updated every Friday but nothing has changed.
Moz Pro | | JasonHegarty0 -
Keyword ranking - authority information
How can you explain that a website (not mine) appears on 1st place in Google (on the Keyword Difficulty Tool/Ranking Analysis) but the Page Authority, Page Linking Root Domains, Domain Authority, and Root Domain Linking Root Domains are much lower than the other websites on the list. Even when running the On-Page Report Card, this website gets grade F comparing to the other (including mine) websites that get better grades. I just want to understand which criteria I'm missing that can explain why a website appears on first place when it seems it's not well optimized. Any input is appreciated. Thanks.
Moz Pro | | yeseniagn0 -
How to see rankings for a group of keywords over time?
I have around 1200 keywords in an seomoz campaign, chopped into various groups using labels. I am interested in tracking how keywords perform over time, especially groups of keywords. As far as I can tell, in the seomoz tools I can only see historical performance for a single keyword at a time. Is there a way to get a historical performance graph of more than one keyword at once? We often run optimizations for sets of words, so it seems like you would have better stats looking at groups of words vs picking one at a time. Is there a way to export historical data for more than keyword at a time? (So I could graph and analyze this sort of thing myself) Does anyone have other suggestions for tracking rankings in this way? Thanks for any help!
Moz Pro | | mlenz0