Any desktop app that extracts keywords from referring URLs?

It's 2011. There must be an app for that...

[Winooski]

(Apologies in advance if this is something that everybody else already knows...)

So a client of mine has an analytics package set up that's capturing good referring URL data but not organic keyword data. What I have is lots and lots of referring URLs from organic search engines, but the precious keyword data is still "locked up" inside those URLs.

I'm thinking, "Hey, it's 2011, there *must* be some desktop app out there that'll take a column of search engine referrer data and then extract the keywords into a new column." However, so far I'm striking out.

What do you think, esteemed forum members?

[engine]

Do you have access to the raw server data?
I usually download the raw logs and import it into a spreadsheet. Of course, it does depend on the size of the raw data file.

[Winooski]

Hey engine. Sure, but that's not quite what I'm getting at. I end up with a column of referrers, e.g.:

- www.google.com/search?q=keyword1+keyword2
- search.yahoo.com/search?p=keyword3+keyword4+keyword5
- www.bing.com/search?q=keyword6, etc.

(Removed all the "http://"s so WebmasterWorld wouldn't try to render the URLs as links.)

What I could really use is some way to convert that into another column of keywords. Sticking with my example, it would look like this:

- keyword1 keyword2
- keyword3 keyword4 keyword5
- keyword6

Hopefully that makes sense. It seems hard to believe that there wouldn't be some simple app out there that can do this, right?

[airun1213]

I need such a software to collect all my keywords,for now i use excel.

[lucy24]

Hey engine. Sure, but that's not quite what I'm getting at. I end up with a column of referrers, e.g.:

- www.google.com/search?q=keyword1+keyword2
- search.yahoo.com/search?p=keyword3+keyword4+keyword5
- www.bing.com/search?q=keyword6, etc.

Holy ###. How did you manage to screen out all the accompanying garbage that's wrapped around your ordinary search-engine referer? It's taken me months and I still have to weed out the occasional false positive-- or delve into raw logs to find something essential that got deleted because it disguised itself as something I'm not interested in.

At this point, all you need is a RegEx search for \?(q\w*|text) and maybe a couple of others depending on which search engines you get. Replace that with a column break according to whatever database format you're using. And while you're at it, change the \+ back into the space that it started out as.

But don't get too optimistic about that "keyword1 keyword2 keyword3" business. It's just as likely to be "how can I keyword3 on my keyword2 when I need to keyword1 but I don't have a keyword4?" or "irrelevant1 irrelevant2 keyword irrelevant3".

[fwc_uk]

A company I work for (disclosure), is a desktop application that provides the capability to track what a user does on a web page(s) inside the firewall.IE only - also extract URLS, see how long they spend there, can even monitor and switch between win32/java/mainframe/web etc apps. It can tell you what fields they go to and what buttons they click as well. It can even automate what a user does to speed up their work. Commonly used in Call Centers. www.openspan.com - OpenSpan Desktop Analytics and OpenSpan Desktop Automation.

[Winooski]

Hey lucy24, no no, that's just a simplified representation of the referrer strings! Of course the real referrer strings have garbage o'plenty around them, which is precisely why there's such a great need for such a tool!

Sure, I could fool around with RegEx...but the point is, by now you'd think there's an app that can do just that.

Meanwhile, here's an update for everyone:

engine was kind enough to send me a StickyMail and explain that there is indeed a list of SEO tools, including some that deal with this sort of keyword-extraction issues, but the list is kept in the supporters-only section. I think the rationale for doing so is that it reduces the likelihood of people doing drive-by spam links on a more public page.

I understand and sympathize. If it were up to me, I would do the opposite and allow those links to be published publicly only after vetting, etc. ...but I know that's easy to say for the guy who doesn't have to spend any time moderating a forum.

Still, I'd think a bit of pre-approval investigating, just to make sure a link itself wasn't churning out malware, etc., would be feasible and would enhance WBW's reputation significantly. I'm sure this is an issue that's been gone over ad infinitum in these pages, so I won't add to the noise, just go on the record as being disappointed but accepting of the way things are right now.

[engine]

including some that deal with this sort of keyword-extraction issues,

Actually, there weren't any there.

[hot101]

If you know your PHP and/or VB then it would be fairly straightforward to do what you're asking. By all means feel free to StickyMail me. I just so happen to have recently coded up a PHP page or two to do just that. Bringing the data onto/into the desktop environment is where VB comes in with simple string manipulation etc. And of course it’ll be bespoke, so it can be as simple or as complicated as you like, depending upon what you want to do with it!