To all .TEXT users... How to see referrers filtering out search engines.

Using the Statistics, Referrers in .TEXT is pretty good; however, if most of you receive a lot of traffic from search engines, it’s hard to see what other community sites are actually linking to you.

I’ve been playing around with an SQL statement that would help filter out the search engine noise and show just the ‘real’ sites referring to each of the entries. I may build an ASP.NET page that utilizes this statement and presents the results in a friendlier format (right now, just looking at the results using query analyzer).

/* Pull all of the referral URLs for all entries; filtering out search engines, etc. */ 
SELECT 
 r.EntryID, r.Count, c.Title, u.URL
FROM 
 blog_Referrals r 
INNER JOIN blog_Content AS c ON r.EntryID = c.ID
INNER JOIN blog_URLs AS u ON r.UrlID = u.UrlID
WHERE 
 URL NOT LIKE 'http://bloglines.com%' AND
 URL NOT LIKE '%google.%' AND
 URL NOT LIKE '%.yahoo.%' AND
 URL NOT LIKE '%altavista.%' AND
 URL NOT LIKE '%dogpile.%' AND
 URL NOT LIKE '%/alltheweb.%' AND
 URL NOT LIKE 'http://a9.com/%' AND
 URL NOT LIKE '%query%' AND
 URL NOT LIKE '%search%' AND 
 URL NOT LIKE '%/results%' AND
 URL NOT LIKE '%/awstats/%' AND
 URL NOT LIKE 'xxxx:%'
ORDER BY r.EntryID DESC, c.Title, u.URL

Basically, it takes three tables (blog_Referrals, blog_Content and blog_URLs) and combines the results filtering out all of the URL that match the make shift list. It shouldn’t be too hard for you to add any additional filters. Also, bear in mind, this isn’t perfect, since, as you can probably tell, it’s possible to generate false positives in the filtering. Overall the results are exactly what I was looking for.

Let me know what you think.