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
-
What are Keywordbasket.com & Keyword-suggest-tool.com. Competitors have Tons of these
My client is in the Custom Scale Model industry. They build Custom Aircraft Models, Custom Ship Models and more. Their competitor's Backlinks reports have a Ton of metrics coming from: http://www.keywordbasket.com and https://www.keyword-suggest-tool.com There is line after line of these entries in the backlink report. As much as 50 line entries. what exactly is happening here? Are they manually using these tools to generate search entries and caching this data in the form of Backlinks? Please explain. is this a Gray Hat Tactic?
Moz Pro | | chachakhota0 -
Comparing New vs. Old Keyword Difficulty Scores
We've had a few questions regarding the new Keyword Difficulty score used in Keyword Explorer, and how it compares to the old score in our stand-alone Keyword Difficulty tool. Specifically, people want to know why some scores are much lower using the new tool. There a general discussion of the math behind the tool in this post: Keyword Research in 2016: Going Beyond Guesswork One of the problems we had with the original Keyword Difficulty score is that, because it's based on our Page Authority (PA) score and PA tends toward the middle of the 0-100 range, Difficulty got a bit bunched up. A Difficulty score in the low-to-mid 20s (via the old tool) is actually very low. So, we set out to re-scale the new tool to broaden that score and use more of the 0-100 range. We hoped this would allow more granularity and better comparisons. While the logic is sound, we're concerned that we may have been too aggressive in this re-scaling, given recent feedback. So, we're going to be analyzing a large set of keywords (anonymously, of course) that people have run through the tool to see if too many Difficulty scores seem too low. If they do, we'll make some adjustments to the math. In the meantime, please be aware that low scores may appear lower in the new tool and very high scores may appear higher. We wanted to address some of the limitations in V1 and feedback over the years, and so the old and new scores really can't be compared directly in a meaningful way. We're sorry for any confusion that has caused, and we will re-evaluate if necessary.
Moz Pro | | Dr-Pete3 -
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 -
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 -
Any Other PAID Keyword Difficulty Tool?
Why is The Keyword Difficulty Tool so unreliable? Anyone know of another PAID Keyword Difficulty Tool? Right now I feel like I am getting ripped off for $99 a month for a tool that I have no confidence in...PLEASE HELP ANYONE
Moz Pro | | Local-Interactive0 -
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 -
Is there a way to see what keywords users of my site are using to find it online?
Since Google Analytics no longer shows the keywords used by people to find a site online, does the SEOMoz toolset provide somethng to show this data?
Moz Pro | | Mionkeybot0 -
What kind of keyword difficulty should I be aiming for when launching an new site?
I know that words in their 20's or 30's would be ideal, but it's proving hard for me to find relevant keywords with such scores (just a couple with scores in the 30's). Is going for words between 40-50 a waste of time? Thanks.
Moz Pro | | ZakGottlieb711