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.
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
-
Best method for tracking true keyword ranking overtime?
It seems as though week to week ranking monitoring can be very volatile, and comparing over the course of a month only takes into account the absolute change and not whether the rank has been jumping up and down in the 3 weeks between. What is a good method (Moz tool or not) for tracking a true change in average rank?
Moz Pro | | ajranzato90 -
A question on keywords that rank 51+
Good afternoon everyone. I wanted to pose a question to the group about keywords and the "on-page optimization - grade a page tool." I have a list of keywords that I am trying to rank for. Some of them are not ranked in the top 50, so on the keyword ranking tool it gives you the 51+ message in the rank column. For the items that are ranked I can try to improve them by looking at grade a page and typing in the URL and keyword. It will then give me a score and suggestions on how to improve it. With that being said, is there an easy way to find out which pages I should be optimizing those keywords which rank at 51+ for, besides typing the keywords in Google and seeing what URL it associates with the specific keyword? I hope the question above is clear.
Moz Pro | | trumpfinc0 -
Automatically Check List of Sites For Links To Specific Domain
Hi all, Can anyone recommend a tool that will allow me to put in a list of about 200 domains that are then checked for a link back to a specific domain? I know I can do various link searches and use Google site: command on a site by site basis, but it would be much quicker if there was a tool that could take the list of domains I am expecting a link on and then find if that link exists and if so on what page etc. Hope this makes sense otherwise I have to spend a day doing it by hand - not fun! Thanks,
Moz Pro | | MrFrisbee
charles.0 -
Keyword Difficulty Score Not Available
What exactly does this mean? That the service is down? Or the score will never be available? It seems like I get that and then the tool gets stuck and I get no other data.
Moz Pro | | endlessrange0 -
Meta keywords no longer in use
Can someone point me to the official article explaining why meta keywords are no longer taken into account by search engines please? I know Moz has indicated that search engines ignore them, but I would like to read a bit more about it - what was the reason behind it and since when.
Moz Pro | | coremediadesign0 -
Do keywords in drop-down menus count?
In running one of my campaigns in SEOMoz Pro, it was recommended that I reduce the amount of times a keyword is used to 15. On the actual page, there are fewer than 15, but when you include the number of times it is used in drop-downs from the nav bar, the number is 53. I know there is really no hard and fast rule about how many instances of a keyword make for keyword stuffing and the drop-downs only use the term where needed. Without it's use, it would be difficult to navigate the site. Is this a problem or should I focus on more important fixes?
Moz Pro | | rdreich490 -
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 -
How to track keyword performance over time?
The SEOMoz keyword reports show week-to-week changed in keyword positions, but what report can I run to see trends over time so that I can evaluate the effectiveness of our SEO efforts?
Moz Pro | | mhkatz0