JL Forums

Working on Keyword Snatcher in Excel (PC)

0 Members and 1 Guest are viewing this topic.

Working on Keyword Snatcher in Excel (PC)
« on: April 25, 2014, 01:28:40 PM »
Hi everyone,

I thought that I may share something with you, those who are Excel experts may stop reading just now, no breaking news here - just my humble experience from today.

So, lets get on it.

KS is a great tool, but we all know that results, especially after long term scrapping are various and need to be sorted out in some way.

Here is my short 'tutorial' on how to process gathered data in Excel. Precisely - how to remove unwanted key phrases from the list basing on 'unwanted' word(s).
I'm not an Excel expert by any mean, so I thought few people here may be also interested in some tricky features on how to remove unwanted keywords from the Keyword Snatcher results using Excel.

Ok, so what I'm talking about?

For this example I run few seconds (literally) of scrapping keywords for 'dog training london'



We have got:



As we can see right now, there is few words which aren't really 'money keywords' if you are ranking locally for London.
At first glance they are 'ohio' and 'ontario'. Yet 'south africa' and others. You've got the point.

Copy all the keywords (or download) and open in Excel.



Then click Filters from top menu in Excel



You'll see small arrow at the very first row of your keyword list, and click it.
Next, chose 'Text Filters' -> 'Contains' option



You will see dialog window.
The window will show 'contain' and a field to type in your 'unwanted' word. In our case (let's say) we don't want keywords containing 'ohio' and 'ontario'.
MAKE SURE YOU SELECT 'OR' not 'And'!!!



Type in your unwanted keywords and make sure that you change the operator to 'OR'!
Otherwise it will select only rows containing both 'ohio' and 'ontario' (In that case nothing would be selected, you can believe me).

When you click 'OK', you'll get something like this :



Fine. You are on PC - simply click 'Delete' and congrats! - job done.

If I remember - on Mac it would be Cmd+Backspace, but I'm not sure.

What you see now is your list with some 'gaps'. Those gaps (unless you sorted the list somehow) are empty spaces after deleted keywords. That's fine, everything is ok so far.



To sort it out and make nice and tidy, simply go to the menu and click A->Z icon, with column A (in this case) selected.



Ah, quick tip here. If you want to select only as much rows as exist in the spreadsheet - select cell A1 and click Ctrl-A (like 'select all' in Windows), it should select all your cells with the keywords.

Sorry image doesn't show 'selected' items, but you should end up with something like this:



After removing few more words:



I hope that helps a bit.

regards
Mac

Offline andrewwilson

  • *****
  • 1498
  • catching the zeitgeist since 2005
    • View Profile
    • We Build Money Making Websites
  • Skype: andrew.wilson41
Re: Working on Keyword Snatcher in Excel (PC)
« Reply #1 on: April 26, 2014, 05:47:15 AM »
Thank you Mac! A very useful tutorial. It may seem like a bit of a fag going through all the words one does not want but, in a list with thousands of terms, this method is a big time saver.

Re: Working on Keyword Snatcher in Excel (PC)
« Reply #2 on: April 28, 2014, 01:03:17 PM »
I'm glad someone has use for it :)

It's been created 'on the fly' I'd say... honestly I forgot to switch Keyword Snatcher off and was running for few hours in background for some car brand, and this bloody thing found nearly 100k keywords!
Honestly MAJORITY of them were excellent, apart from obvious localized ones, which I wanted to remove... the lazy way :)

Offline Meg

  • *****
  • 7145
  • Freedup to do what you really want
    • View Profile
Re: Working on Keyword Snatcher in Excel (PC)
« Reply #3 on: April 28, 2014, 01:25:09 PM »
I'm glad someone has use for it :)

It's been created 'on the fly' I'd say... honestly I forgot to switch Keyword Snatcher off and was running for few hours in background for some car brand, and this bloody thing found nearly 100k keywords!
Honestly MAJORITY of them were excellent, apart from obvious localized ones, which I wanted to remove... the lazy way :)

That's the BEST form of innovation!  ;D

Offline andrewwilson

  • *****
  • 1498
  • catching the zeitgeist since 2005
    • View Profile
    • We Build Money Making Websites
  • Skype: andrew.wilson41
Re: Working on Keyword Snatcher in Excel (PC)
« Reply #4 on: April 29, 2014, 02:18:41 AM »
One thought, given the volume of local searches now taking place on Google, is it not worth using those local terms even if our product or service is not particularly localised? How about 'internet marketing training in Poughkeepsie'? The searcher may not even think about the service not being location dependent when their fingers hit the keyboard. ;)

Re: Working on Keyword Snatcher in Excel (PC)
« Reply #5 on: April 29, 2014, 10:07:11 AM »
Andrew, I totally agree! That crossed my mind right after I removed all the localized keywords and saved the file ;D


I was looking for anchor texts for my tier-2,3 and, on one hand it was mistake to not use those localized keywords but on the other hand this particular project wasn't for adsense/affiliate site, but for real internet forum for UK based car owners, so.. well.. lesson taken anyway :)

Offline andrewwilson

  • *****
  • 1498
  • catching the zeitgeist since 2005
    • View Profile
    • We Build Money Making Websites
  • Skype: andrew.wilson41
Re: Working on Keyword Snatcher in Excel (PC)
« Reply #6 on: April 29, 2014, 01:02:24 PM »
Well, you can always redo the the search again in the future if you need it. To be honest when I wrote I was thinking as much of other readers of the thread as I was you.