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
-
How can I limit the impact of Google's mobile friendly updates on keyword rankings?
Hi everyone, I have a client on a monthly SEO package that is taking a hard hit in the rankings as their website is not mobile friendly. We are currently building a new website for them that will be fully responsive, but face the predicament of how to keep the current site performing relatively well in the rankings in the meantime. The site is generally well optimised and we always follow SEO best practices, so we've hit a bit of a stumbling block as to how to limit any negative impact the mobile friendly updates have on the site. Does anyone have any suggestions for damage limitation? Any help will be greatly appreciated. Thanks.
Moz Pro | | keyzo0 -
Keyword rankings and main company tagline not matching
I have a question about keywords rankings and on page keywords. I don't rank very well for the keyword "Nutcracker". But, my tag line is "Your one stop Nutcracker and Ballet Gifts source". One of my most important keywords is "Nutcrackers" (see the s 😉 ) and I hot hit really hard in my rankings with the google algorithm update. Almost a 30 position difference from one day to the next. Am I shooting myself in the foot by having Nutcracker being in the forefront, even though my chances of ranking for that are very low as there is high competition with more actual "The Nutcracker" related content. I do have the word "nutcrackers" on my home page...but all of a sudden I feel like i'm wasting space, or I should have made the tagline "Your one stop Nutcrackers and Ballet Gifts source". It just doesn't sound as good. I just recently dove headfirst into SEO and moz, so I consider myself a noob, but I learn quite quickly. Thank you for your help! Esmee/Owner www.nutcrackerballetgifts.com 12-inch-nutcrackers-n1211-a.jpg
Moz Pro | | NutcrackerBalletGifts0 -
How can you tell how many pages rank for a particular keyword?
I know with ranktracker I can see a page that will rank for a certain keyword. What if I have multiple pages that rank for this keyword? How can I see all pages that rank for it?
Moz Pro | | Sika220 -
Ranking keyword ecommerce product
Hi guys, I run an ecommerce store footdistrict.com. We sell sneakers. My question relates to the name of some of my products and how to rank for them. This concerns to the shoes from New Balance The name of this products follows this format: New Balance XXNumberXXX . But actually I have realized I am ranking for New Balance XXNumber XXX . Note the space between the number and the last 3 characters. All my titles, h1, keywords follow this format. Example: New Balance WL574 RUP vs New Balance WL574RUP My questions are: 1. How would you determine which keyword to rank for: New Balance XXNumberXXX or New Balance XXNumber XXX? I have thought of using analytics data to see the pattern of the users search or maybe the difficulty of the keyword? I am receiving a decent amount of traffic for New Balance XXNumber XXX but I wonder if it'd be higher ranking for New Balance XXNumberXXX 2. Does the algorithmn take into account this space? According to the On page Grader, I have an A for New Balance XXNumber XXX and a F for New Balance XXNumber XXX but I assume the engine doesn't work this way 3. What would you do? Thanks very much Kind regards
Moz Pro | | footd0 -
How to change the compare date range on the keyword rankings report?
I'm new to SEO Moz, but not to SEO. I like SEOMoz's keyword rankings, but I also like to show clients improvement (hopefully) from the previous months' ranks. The default appears to be 1-week. How can you show improvement/decline from the previous month?
Moz Pro | | Brandon_Clay0 -
Google Algorithm Update July 30, 2012 - Anyone else notice a major drop in keyword rankings
From July 30th to the 31st many of my keyword rankings in Google plumited 30 spots or more. Has anyone else notice this on their site? Does anyone have any idea for the presumed penalty? I can PM my site information if needed for further assistance. As always thank you for the assistance, and any help in this matter is greatly appreciated!
Moz Pro | | BethA1 -
Getting PA & DA off of a list of links
I have a list of links that I want to get PA and DA for each individual link, can this be done in some way other than one at a time? I've heard this can be done with excel and using api but I don't know the specifics of this.. Help would be appreciated
Moz Pro | | Fergclaw2 -
Quick Keyword Difficulty Question
Hello good people. Quick question about the Keyword Difficulty Tool (which is great by the way). I am wondering specifically about the "keyword difficulty score" If I were to type in "blue widget," the tool returns a diffiiculty score of 51%. "red widget" on the other had returns a score of 40%. My question is how much more difficult is the term, "blue widget" when compared to "red widget?" 20% harder? more, less? I just don't know. I'm sure the info is somewhere on the seomoz site, but I have searched high and low and cannot find it. Thanks a lot, Joe
Moz Pro | | sawa00190