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

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

<FONT color=#7fffd4><FONT color=#000000> <P>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.</P> <P>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).</P></FONT></FONT> <P><FONT color=#7fffd4><FONT color=#008000>/* Pull all of the referral URLs for all entries; filtering out search engines, etc. */</FONT> </FONT>
<FONT color=#0000ff>SELECT</FONT>
  r.EntryID, r.<FONT color=#800080>Count</FONT>, c.Title, u.URL
<FONT color=#0000ff>FROM</FONT>
  blog_Referrals r
<FONT color=#0000ff>INNER</FONT> <FONT color=#808080>JOIN</FONT> blog_Content <FONT color=#0000ff>AS</FONT> c <FONT color=#0000ff>ON</FONT> r.EntryID = c.ID
<FONT color=#0000ff>INNER</FONT> <FONT color=#808080>JOIN</FONT> blog_URLs <FONT color=#0000ff>AS</FONT> u <FONT color=#0000ff>ON</FONT> r.UrlID = u.UrlID
<FONT color=#0000ff>WHERE</FONT> 
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’http://bloglines.com%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%google.%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT><FONT color=#ff0000> ‘%.yahoo.%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%altavista.%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%dogpile.%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%/alltheweb.%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’http://a9.com/%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%query%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%search%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT><FONT color=#ff0000> ‘%/results%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’%/awstats/%’</FONT> <FONT color=#808080>AND</FONT>
  URL <FONT color=#808080>NOT LIKE</FONT> <FONT color=#ff0000>’xxxx:%’
</FONT><FONT color=#0000ff>ORDER BY</FONT> r.EntryID <FONT color=#0000ff>DESC</FONT>, c.Title, u.URL</P> <P>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.</P> <P>Let me know what you think.</P>
This post is licensed under CC BY 4.0 by the author.