When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles [1.x] [2.0]
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
Headlines from ASPWire.com
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML Info
Information:
Advertise
Feedback
Author an Article
Technology Jobs



















internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.

Senior Web Content Specialist
Aquent
US-NJ-Parsippany

Justtechjobs.com Post A Job | Post A Resume

Published: Monday, January 29, 2001

Creating a Click-Through Tracking System


Introduction
Have you ever wondered how often users click a particular hyperlink on your site? Perhaps you have a banner whose click throughs you wish to monitor, or are curious how often various hyperlinks are clicked depending on their position on the Web page. I (Scott Mitchell) was wanting such a system for 4Guys so I decided to create one and present the entire application as an article.

- continued -

The Data Model
When designing the database structure for the link clickthrough tracking system I decided to create two tables. The first database table, tblLinks would contain information about each of the "tracked" hyperlinks. The second table, tblLinkClicks would contain a row for each time a user clicked a "tracked" hyperlink. The structure for these two database tables can be seen below:

tblLinks
LinkIDintPrimary Key / IDENTITY
Namevarchar(50) 
Descriptionvarchar(2000) 
SendToURLvarchar(150) 

tblLinkClicks
ClickIDintPrimary Key / IDENTITY
LinkIDintForeign Key to tblLinks.LinkID
DateClickeddatetimeContains a default value of getdate()

The tblLinks table contains a row for each hyperlink that you wish to track. Each tracked hyperlink can have a Name and Description. (These two columns are used solely for identifying the tracked link from an administration standpoint; these values will not be outputted into an ASP page.) The SendToURL column contains the URL that you want the user to be sent when they click on the tracked hyperlink. This is the URL you would have in the HREF HTML tag if the link was not being tracked.

The tblLinkClicks table has a row automatically added to it whenever a user clicks on a "tracked" hyperlink. Each row represents a clickthrough. The LinkID column identifies what "tracked" hyperlink was clicked, while the DateClicked column identifies the date and time the clickthrough occurred (which I setup with a default of getdate(), which returns the current date/time).

Adding New "Trackable" Links via an Administration Page
Now that we have these two tables, let's look at how we can easily add new "trackable" links. While we could simply add a new row to the table by hand through SQL Enterprise Manager, I went to the trouble of creating a simple administration page to facilitate adding new "trackable" links. This entire administration page can be created with a single ASP page using a post-back form. A screenshot of this administration page in action, as well as the source code for this page can be seen below. When creating this page you should name it AddLink.asp.

Once you've checked out the screenshot and sample code below, move on to Part 2, where we'll look at how to wire up our old hyperlinks into trackable hyperlinks.

  • Read Part 2!


    Screenshot of the Administration Page
    Screenshot of the administration page

    <%@ Language=VBScript %>
    <% Option Explicit %>
    <%
      Dim iID, strSQL, objConn
    
      'open a database connection
      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open "DSN=MyDSN"
      
      'Determine if the form has been submitted.  If it has, insert a new record
      If Len(Request("btnSubmit")) > 0 then
        'Add a new link
        strSQL = "INSERT INTO tblLinks (Name, Description, SendToURL) " & _
                 "VALUES('" & Replace(Request("txtName"),"'","''") & "','" & _
                 Replace(Request("txtDescription"),"'","''") & "','" & _
                 Request("txtSendToURL") & "');select @@identity"
    
        'We get the ID of the row just inserted using a method explained at:
        'http://www.4guysfromrolla.com/webtech/tips/t122600-1.shtml
        Dim objRSTemp
        Set objRSTemp = objConn.Execute(strSQL).NextRecordset
        
        iID = CInt(objRSTemp(0).Value)
        
        objRSTemp.Close
        Set objRSTemp = Nothing
      End If
    %>
    <HTML><BODY>
    <% If Len(iID) > 0 then %>
      <b><u>New Link URL</u></b><br>
      To create a new tracking link, use the following URL:<br>
      http://www.4GuysFromRolla.com/ASPScripts/Goto.asp?ID=<%=iID%>
      <p><hr><p>
    <% End If %>
    
    <form method=post action="AddLink.asp">
      <b><u>Add a Link</u></b><br>
      Password: <input type=password name="txtPassword">
      <br>Name: <input type=text name="txtName">
      <br>Description:<br>
      <textarea wrap=virtual cols=50 rows=5 name="txtDescription"></textarea>
      <br>
      URL: <input type=text name="txtSendToURL">
      <p>
      <input type=submit value="Create Link" name="btnSubmit">
    </form>
    
    <!--Display a list of existing links-->
    <p><hr><p>
    <b><u>Current Links in System</u></b><br>
    <%
      Dim objRS
      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open "SELECT * FROM tblLinks", objConn
      
      Do While Not objRS.EOF
        Response.Write objRS("LinkID") & ".) " & objRS("Name") & _
                       " - " & objRS("Description") & "(<a href=""" & _
                       objRS("SendToURL") & """>" & objRS("SendToURL") & "</a><br>"
                 
        objRS.MoveNext
      Loop
      
      objRS.Close
      Set objRS = Nothing
      
      objConn.Close
      Set objConn = Nothing
    %>
    </BODY></HTML>
    


    Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article



  • JupiterOnlineMedia

    internet.comearthweb.comDevx.commediabistro.comGraphics.com

    Search:

    Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

    Jupitermedia Corporate Info


    Legal Notices, Licensing, Reprints, & Permissions, Privacy Policy.

    Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers