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
-
Is It Necessary to Remove The Inbound links With Spammy Score?
Hi Friends, I am new to this community. I just checked my inbound links using Moz tool and I came to know that there are some inbound links with spammy score. So, should I remove those links using disavow tool? Awaiting for the reply.
Moz Pro | | Flyin.com0 -
15-20 images on a page / Alt Tag them or not with the keyword phrase?
I have a site that easily has 15-20 images per product page, giving the users ideas of what they can do with the product design. All of them have an alt tag with the keyword phrase in it. There is also an H1, H2, sometimes an H3, body copy with the keyword phrase 1-3 times, bold when it seems like a good time to emphasize it. Just in the images alone, we've exceeded the recommended 15 keyword phrases on a page. Moz On Page Grader says the following: Avoid Keyword Stuffing in Document We've seen evidence that excessive use of keywords can negatively impact rankings and thus suggest moderation. Recommendation: Remove instances of the targeted keyword(s) from the document text of this page to bring it below 15 What's the recommendation for the image alt tags? We'd like the images to show up in Google Images, so they should have the tag, right? What's the right way to handle this for SEO purposes? Someone's suggested naming 1/2 of the images one keyword phrase, and the other 1/2 an altogether different one, not searched nearly as often as the primary keyword phrase.
Moz Pro | | INCart0 -
Branded keywords
I'm trying to understand this concept a little further. Say if I create a branded keyword, "dc widgets" I then start to add keywords such as "maker" "toy" and apply the branded keyword to it. Does this mean that they then become "maker dc widgets" and "toy dc widgets" ? What purpose does branded keywords offer?
Moz Pro | | priceseo0 -
Competitor Adwords Keyword Analysis
Hi everyone, My company is exploring moving into a new industry, and I'm struggling a bit with where to start regarding adwords / keywords. I'm wondering if you have any recommendations regarding a tool for analyzing what others in the space are using for keywords in Adwords (preferably including some detail regarding CPC, volume, etc...). There appear to be several options (both free and paid). I'm just looking for what you'd consider a good product (whether paid or otherwise). Thanks in advance and greatly appreciate any insight you may have. Best, Jason
Moz Pro | | ba_seomoz1 -
No longer in top 100 for branded keyword - How!?
Got a weird one here... Won't give away actual URL but for this example let's say client's site is: www.purplebananas.com Many of the longtail keyword-targeted subpages/posts are still ranking pretty well. We recently went through and optimized the site based on poor scores we found from crawl reports from SEO Moz, which included renaming some sub-pages, eliminating duplicate content pages, archives, etc (it's a wordpress site, about 4 years old). This site used to rank at the top or near it for the search "purple bananas". However, after we optimized the site it's almost completely fallen off the radar of Google, turns up way over 100 for the search "purple bananas". Strangely, if we search "purplebananas" we're at the top still. Any idea what's going on here? Are we potentially over-optimized or something weird for our own brand!? Any troubleshooting tips? FWIW the changes were made about 6 weeks ago and while we're steadily building links, I don't think this is the standard "Google dance". Thanks!
Moz Pro | | alpen0 -
What's up with Yahoo and Bing in my keyword rankings?
SEOmoz is good I've learned a lot from it. I'm on my 3rd month and now have 8 keywords in top 50 for one site and 1 keyword in top 50 for another. This is for Google US. For Bing and Yahoo! US however I am still not in the top 50 for anything. I have read elsewhere that meta-tag page words can be regarded by these engines as a spam signal. In my meta I have used competative keywords that are relevant to my content, some of which are in the body, but are not my actual targeted keywords as a red-herring to potential competitors. Also might be usefull for other smaller places that still use meta-tags. Could not appearing in BingHoo! have something to do with their alogrithms and the meta-tags? or is it an SEOmoz issue? I have another question also. The " / " after a domain name doesn't appear in my Firefox address bar (I think I did a 301 redirect), but when I copy and paste it into an entry box (e.g. for a link submission) it puts the " / " there at the end which I take off. Does this make any difference?
Moz Pro | | Zoolander0 -
Should I worry about limiting link count on product listing/category pages?
I've noticed that my link count is high (165ish for some) on my category listing pages. I've been scouring my page to see if there's any way that I can reduce the link count without restricting functionality to the end user. Each product listing on the category page has 5 links currently: A link to the product in the title A link to the product from the image An 'add to compare' link An 'add to cart' link An 'add to wishlist' link When the customer chooses to show 30 products per page, the link tally goes off the scale. So I have two questions: Firstly - is it appropriate to keep link count down in this scenario? To elaborate - is it just inevitable that product listing pages will have lots of links, and should I just assume that Google knows this and forget about these warnings. Secondly - There are two links to the same page (the title and image links to the product page). Does SEOmoz include this in the link count, and more importantly, will Google take heed of these when deciding whether the page is too link-heavy?
Moz Pro | | SimonGreer0 -
Avoid Keyword Self-Cannibalization
I'm still having issues with this self-cannibalization...it's referencing my tags as links that are doing this... should i remove the tags on my posts that are exact keyword matches to my article? for example, this post generates an 'a' through the on-page optimization tool for the keyword 'kw cares'...they only thing i receive a warning on is the self-cannibalization. http://www.youdrivethesuccess.com/tag/kw-cares/ Cannibalizing link - "kw cares" is it best practice not to have your tags match your primary keywords? any guidance is appreciated!
Moz Pro | | brentmitchell0