SmartSurat.com # Smart Education Zone

Intro :
Learn how to track where your visitors come from. Traffic is the key to a successful website. It doesn't matter if you have the greatest site in the world if nobody visits it. One of the most important things you should do to increase your traffic is analyze where your current traffic is coming from. Is it search engines? Other sites on the same subject? The results are often amazing! Once you locate the sources of your traffic you can start building better ties to those sources to increase your traffic.

Counting referrals
We could store all the URLs we got in a big list, but that wouldn't be very easy to analyze. So what we will do is to make a counter for each URL that refers to our page. That way we can see which source gives us most traffic etc. The concept is the following:

Ref.asp is our counting script. We will include that in all the pages we want to monitor.
SomePage.asp is just that kind of page, so we have included Ref.asp in this page
ViewStats.asp is for our view only. It gives us a list over URLs and their respective count.

The code
So now to the coding, let's start with Ref.asp:

<%
'Get the reffering URL
RefURL = Request.ServerVariables("HTTP_REFERER")

'We connect to the database using a DSN less connection
DataSource = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ref.mdb") & ";"
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open DataSource

'Check if the person was referred by a page or not (e.g. bookmark)
IF Len(RefURL) = 0 THEN

 'The person was not referred by a page
 InsertIntoID = 1
ELSE

 'The person was referrd by a page
 'Check if the URL already is in the database
 'if it is get the URL's Ref_ID value
 'if not set InsertIntoID = 0
 SQL_query = "SELECT Ref_ID FROM RefTable WHERE URL = '" & RefURL & "'"
 Set RS = MyConn.Execute(SQL_query)
 IF NOT RS.EOF THEN
 InsertIntoID = TRIM(RS("Ref_ID"))
 ELSE
 InsertIntoID = 0
 END IF
END IF

'If InsertIntoID = 0 then the URL is a new URL
IF InsertIntoID = 0 THEN

 'Insert new url with count = 1
 SQL_query = "INSERT INTO RefTable (URL, Counter) VALUES ('" & RefURL & "',1)"
 MyConn.Execute(SQL_query)
ELSE

 'Update existing URL by adding 1 to the count
 SQL_query = "UPDATE RefTable SET Counter = Counter + 1 WHERE Ref_ID =" & InsertIntoID
 MyConn.Execute(SQL_query)
END IF
MyConn.Close
Set MyConn = nothing
%>

Here is the code for the SomePage.asp, very straight forward:


<HTML>
<HEAD>
<TITLE>Some page</TITLE>
</HEAD>
<BODY>
Welcome to this page!
</BODY>
</HTML>

In ViewStats.asp we can analyze the results generated from Ref.asp:
(The included comments should explain what it does.)

<HTML>
<HEAD>
<TITLE>Display referal stats</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#000000" VLINK="#000000" ALINK="#000000">
<CENTER>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=3>
<TR BGCOLOR="#000000">
<TD> </TD>
<TD>Referring URL:</TD>
<TD> </TD>
<TD>Count:</TD>
<TD> </TD>
<%
'We connect to the database using a DSN less connection
DataSource = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ref.mdb") & ";"
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open DataSource

'We select all the rows ordered by the count with the highest count first
'If you want the lowest count first, just replace DESC with ASC
SQL_query = "SELECT * FROM RefTable ORDER BY Counter DESC"
Set RS = MyConn.Execute(SQL_query)

'Then we loop through every row and writes it out
WHILE NOT RS.EOF

 'This little code lets us get different background color for every other row
 IF BGColor = "#F7F7E7" THEN
 BGColor = "#E7E7D6"
 ELSE
 BGColor = "#F7F7E7"
 END IF
 Db_URL = RS("URL")
 %>
 <TR BGCOLOR="<%=BGColor%>">
 <TD> </TD>
 <TD>
 
 <A HREF="<%=Db_URL%>"><%=Db_URL%></A>
 
 </TD>
 <TD> </TD>
 <TD ALIGN=RIGHT>
 
 <%=RS("Counter")%>
 
 </TD>
 <TD> </TD>
 <%
 RS.MoveNext
WEND
RS.Close
Set RS = nothing
MyConn.Close
Set MyConn = nothing
%>
</TABLE>
</CENTER>
</BODY>
</HTML>

Referral Tracking