Moz Q&A is closed.
After more than 13 years, and tens of thousands of questions, Moz Q&A closed on 12th December 2024. Whilst we’re not completely removing the content - many posts will still be possible to view - we have locked both new posts and new replies. More details here.
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 Questions about travel industry keywords
My question is regarding my current learning of SEO and keyword research. I have been leaning towards Keyword Everywhere, SEMrush, and Google Keyword Planner for keyword research. What do you recommend? I know you might suggest Moz, but I'm looking for something that will enhance and help me find valuable keywords for the travel niche. If I sign up with Moz, will someone from the team help me with keyword research for the travel niche and my website? I haven't found any resources or lessons specifically focused on travel websites and their keywords.
Moz Pro | | RavenBhutan0 -
Removing Domains From Disavow File
We may have accidentally included the wrong domains in our Disavow file and have since removed most domains leaving the only very highly rated spammy links (using moz's new spam score)in the file. How long can it take for to google to recognise this change?ThanksMike
Moz Pro | | mlb70 -
Whether or not to remove a link from a website with high spam score on Open Site Explorer
Hello Moz! I just subscribed for your Moz Pro program. Amazing stuff! On open site explorer, I found a number of links to my site from a page called with a very high page authority and high domain authority, but also a high spam score (8 or 9, one with a 10). I say multiple spam scores, because it's strange, there are what appears variations of the same url, and each one is considered a link. For instance, there's an abc.linkstomysite.com and xyz.linktomysite.com, and 123.linktomysite.com... there are about 15 of these (all with the spam scores mentioned above)! This must have been some old SEO work done I payed for back in the prehistoric SEO days. However, my fear is the following: Removing these links, and then losing some potentially strong link juice. I don't have many high DA or PA links to my site, and these are some major ones. The domain in question "linktomysite.com", when entered into OSE, only has a spam score of 4, and it has a domain authority of 45 and page authority of 37. My site has a spam score of 2 and no messages from google regarding a penalty, but an overall reduction in google traffic over the years (just keeps slowly dropping... as if a weight is pulling me down?) What do you think, should I leave, or remove? The linkstomysite page is just a LONG page full of links, with short descriptions, nothing of value, but with a an old domain age (relatively). Most important for me is keeping at least some ranking/visibility, while I personally work on building quality links and helpful content. thanks!
Moz Pro | | DavidC.0 -
Items listed based on size - Use "inches" or " " "
I sell Decorative nutcrackers of various sizes. At this time, I use the term "inches". I was looking at my competitor the other day, and when I did a search on his site, using the term inches did not provide me with a result. I was forced to use ex. "10" nutcracker" instead of "10 inch nutcracker". Is there a preferable usage for seo purposes. Thanks! my site: http://www.nutcrackerballetgifts.com/category/5/10-Inch-Nutcrackers.html his ite: http://www.kurtadler.com/Search?search=10"+nutcracker
Moz Pro | | NutcrackerBalletGifts0 -
Keyword Research and Planning Flow
Hello, First post here. I am a IT infrastructure engineer that does application and deployment work mostly, but new to SEO. That being said, I am a firm believer in demming wheel and defined process/flow charts. I have a website up, built as best practice, its a Magento CE open source Ecommerce store. After some initial research, it seems I really need to define my keywords before I start branding my landing pages and such. So i am going to have to go back and implement the keywords and evaluate. But as I am reading it seems most SEO people try their keywords in google ad words first? Then implement? I am hoping to find some flow or process sheets on how successful Keyword research is done via step by step process and evaluation. And explanations of each. How many keywords to start with per landing page, how many to try and get in the H1 headers, paragraphs, URL. How long to leave keywords in to test? Also, I am a start up company that is competing with the big boys in my market space. I know I cant compete for the big keywords in my market? What is my best strategy for getting any kind of ranking as a small business in a global market? SEO is a mysterious, intriguing thing to me! Very much reminding me of one of my favorite whimsical quotes I will leave you will now, and thanks for reading. -Alex "Invention, my dear friends, is 93% perspiration, 6% electricity, 4% evaporation, and 2% butterscotch ripple." - Willy Wonka
Moz Pro | | iamgreenminded0 -
Is there any way to move keywords from one Campaign to another?
We recently moved content off a subdomain onto our main www subdomain. Each of these was previously tracked as its own campaign. Now that the content is consolidated, I'd like to move the keywords that we were tracking on the first campaign over to the second. I don't see an option to migrate, or export/import keywords. Is there any (non-manual) way to do this? Thanks
Moz Pro | | doxo2 -
How to track all domain keywords?
Hello, lets say I have domain www.example.com I want to track rankings of www.example.com/product and www.example.com/anotherproduct with different keywords, so do I have to set up 2 different campaigns? Or is it possilble to track whole root domain with paths? Thanks!
Moz Pro | | Rokas0 -
What is mT/mR good for In the keyword tool difficulty full report results?
So the ratio is MozTrust to MozRank, but what is this good for? What can I deduce from this and what can I use it for?
Moz Pro | | MarloSchneider2