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
-
Unsolved Getting keywords to rank on new landing pages
I've built new landing pages for a website, and have loaded them with researched keywords in the content, alt image attributes and metas etc - but, after a number of crawls, the keywords are currently being matched to other existing web pages on the website. Does anyone have any advice on 'unlatching' these keywords from these pages, and instead getting them to match with pages that have been optimised for them? Many thanks!
Moz Pro | | Darkstarr6660 -
What is the difference between my pages' keyword rank and keyword-specific SERP?
I've been tasked with identifying high volume keywords for products our company sells and optimizing our copy and listings to rank higher on Google. The Keyword Explorer and On-Page Grader have been fantastic for this, but as I write my reports, i'm not sure whether SERP or Moz's keyword page rank would be more accurate for reporting results and movement. For instance, one of our product pages ranks #12 for the highest (relevant) volume keyword, but when looking at the SERP analysis for that keyword, our product page actually shows up at #7 down the list. I've tried naturally searching in Google in case it was an issue of private results based on my user data but it seems to give the same result. Am I conflating two different tools? Or is the Moz rank just an estimate?
Moz Pro | | BJake0 -
Checking multiple keywords in Rank tracking
Besides the rankings in the campaigns i want to check 100+ keywords at once in rank tracking. Is this possible and if not why? It says i can check 400 keywords a day but manually entering them is time consuming and that's exactly why i use SEOMOZ, to save time.
Moz Pro | | FindFactory1 -
Keyword difficulty always high
HI there, I'm new here and am just getting used to the keyword difficulty tool. I've noticed that just about every keyword phrase I type in, including ridiculous ones such as "perspex underpants" or "guy debord's shoes" all have a difficulty well over 50% and many over 60%. It makes little difference what my phrase is. Am I doing something wrong perhaps? How am I to interpret this result? It would seem to suggest that It is always very hard to rank, even for a rare and ridiculous phrase.
Moz Pro | | WeGoBang0 -
Keyword Difficulty / Search Volume
Hello all, What do you think about using Keyword Difficulty divided by Search Volume as an alternative to keyword efficiency indexes? ETA: Obviously this wouldn't be a hard and fast metric, but a general indicator to be taken into account along with other data.
Moz Pro | | wattssw0 -
OSE lists dead links
Going over the link profile of a competitor who gets 5x the traffic we do.... of course frustrated that the majority of their links are spam blogs (full of words but don't communicate anything) and forum profiles. Thanks Google for telling me what not to do, then rewarding my competitor for doing it shamelessly. Question regarding sites listed by Open Site Explorer as linking to said competitor, but that don't even load when I visit their url. Some go to a godaddy parked page, like the domain name expired long ago. Is this simply a limitation of OSE, and can I assume Google has indexed differently and therefore awarding no link juice from these urls?
Moz Pro | | jotham20 -
Keyword Tracking
Hi there, I was just wondering if there was a way to go back to previous results of keywords I was tracking for a site? My HDD crashed and lost the reports and was hoping to download the old reports. Please let me know if this is possilbe. Thanks in advance!
Moz Pro | | Greg800 -
Keyword Difficulty Tool. Why my rank is low?
Keyword Difficulty Tool. Why my rank is lower than competitors even though I have higher numbers? Can somebody help me understand this... take a look at screenshot. http://www.traxnyc.com/images/keyword_difficulty.jpg What should I do to get to number 1 position? Keyword: Hip Hop Jewelry My Website: traxnyc.com
Moz Pro | | DiamondJewelryEmpire0