New Media Specialist Aquent US-CO-Denver Post A Job | Post A Resume

In Part 1 we examined how to have particular banners appear when particular search terms where used in a site search. In this part we'll examine how to record impressions and click-through statistics!

Recording Impressions and Click-throughs/Hits
As previously discussed, the recording of impressions and click-throughs is vitally important to advertisers. Now that you've targeted advertisers' audiences, you need to be able to record how many people are seeing their banners, and how many people are clicking on them. It is obviously also a bonus if you can generate graphs for advertisers to see, based on these two statistics.

Recording Impressions
Firstly, you'll need to add another table to your database to store the impressions (ie. each impression has a separate entry). I suggest a layout like this:

Banner_Impressions Table
Field	Type [Access]	Type [SQL Server]
ImpressionID	Autonumber	Integer [Identity]
BannerID	Number	Integer [4]
IP	Text [255]	varchar [255]
DateAndTime	Date/Time	DateTime

Now you need to add an entry into this table each time a banner is displayed. Naturally you now need to pull out the BannerID from the banners table. Here's the revised code:

<!-- #INCLUDE FILE="adovbs.inc" --> <% Dim objConn, objRS, arrBanners, intMax, intRand, strSQL, intBannerID Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=BannerRotator;" Set objRS = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT BannerID, ImageSrc, LinkURL, ALT FROM Banners" If CInt(Request.QueryString("catid")) <> 0 Then strSQL = strSQL & " WHERE CategoryID = " & Request.QueryString("catid") End If objRS.Open strSQL, objConn, adOpenKeyset, adLockOptimistic, adCmdText intMax = objRS.RecordCount arrBanners = objRS.GetRows() objRS.Close Set objRS = Nothing Randomize intRand = Round(Rnd(intMax) * (intMax - 1)) 'Add entry objConn.Execute "INSERT INTO Banner_Impressions (BannerID, IP, DateAndTime) " & _ "VALUES (" & arrBanners(0,intRand) & ", '" & _ Request.ServerVariables("REMOTE_ADDR") & "', #" & Now & "#)" objConn.Close Set objConn = Nothing %> <A HREF="<%=arrBanners(2,intRand)%>" TARGET="_top"> <IMG SRC="banners/<%=arrBanners(1,intRand)%>" WIDTH="468" HEIGHT="60" BORDER="0" ALT="<%=arrBanners(3,intRand)%>"> </A><BR>

Note: If you are using SQL Server, please replace the Access date deliminers (# - hash) with the appropriate ones (' - apostrophe)

It is important to note that I have adjusted the indices in the array to reflect the inclusion of the BannerID field in the resulting recordset and inherently, the array.

Recording Click-throughs/Hits
We'll also need a table to store banner click-throughs, or hits. This will take almost exactly the same form as that of the Banner_Hits table.

Banner_Hits Table
Field	Type [Access]	Type [SQL Server]
HitID	Autonumber	Integer [Identity]
BannerID	Number	Integer [4]
IP	Text [255]	varchar [255]
DateAndTime	Date/Time	DateTime

We now need to include an intermediatory step between when the banner is displayed and when the browser goes to the desired URL so that we can trap and record the hit. This is done by passing the BannerID and URL to another file using a querystring, which then logs the hit and redirects the user to the URL. Thus the banner is now displayed using the following link:

<A HREF="hit.asp?BannerID=<%=arrBanners(0,intRand)%>&URL=<%=arrBanners(2,intRand)%>" TARGET="_top"><IMG SRC="banners/<%=arrBanners(1,intRand)%>" WIDTH="468" HEIGHT="60" BORDER="0" ALT="<%=arrBanners(3,intRand)%>"></A><BR>

Notice that we are now linking not directly to the URL, but to hit.asp, which will look something like this:

<% Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Execute "INSERT INTO Banner_Hits (BannerID, IP, DateAndTime) " & _ "VALUES (" & Cint(Request.QueryString("BannerID")) & ", '" & _ Request.ServerVariables("REMOTE_ADDR") & "', #" & Now & "#)" objConn.Close Set objConn = Nothing Response.Redirect Request.QueryString("URL") %>

This simply adds a record to the Banner_Hits table, and then redirects the user to the specified URL.

Web-based Administration
I'm not going to go in-depth here, but suffice to say that all you need to do is add, delete and update the Banners table as you normally would, using ADO and SQL. The only two areas that you need to look out for are the uploading of the banner images to your server, and the deletion of these images when you delete a record from the Banners table. For the former, I strongly recommend the use of Persits Software's ASPUpload component (see links at the bottom of the article for information on obtaining it), although you can obviously use your regular upload component if you have done ASP uploads before. (For more information on uploading files from the client to the server, check out the Uploading Article Index!)

Deleting items is done via the FileSystemObject COM component in the Scripting library. The basic procedure is that you grab the ImageSrc field out of the record that you're going to delete, check if this file does exist (it could have been deleted using FTP etc), and then go ahead and delete it if it does. You'd then proceed to delete the record. Here's a basic idea of what the code would look like (I'll assume that the ID of the banner to be deleted has been passed as a form variable called hidID):

<% Dim objConn, objRS, objFSO Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=BannerRotator;" Set objRS = objConn.Execute("SELECT ImageSrc FROM Banners " & _ "WHERE BannerID = " & Request.Form("hidID")) If Not objRS.EOF Then Set objFSO = Server.CreateObject("Scripting.FileSystemObject") If objFSO.FileExists(Server.MapPath("banners/" + objRS("ImageSrc"))) Then objFSO.DeleteFile(Server.MapParh("banners/" + objRS("ImageSrc"))) End If End If objRS.Close Set objRS = Nothing objConn.Execute "DELETE FROM Banners WHERE BannerID = " & Request.Form("hidID") objConn.Close Set objConn = Nothing %>

For more information on deleting files with the FileSystemObject object, check out this popular FAQ at ASPFAQs.com: How can I delete a file from the Web server's file system?

Conclusion
As you've hopefully seen from above, the advantages of developing your own custom banner system are very great indeed, and well worth the effort. However, there are many areas that this article has not covered, where new and innovative ideas can be applied. Such things as displaying banners according to the viewer's geographical location (based on IP address) haven't been discussed, and are very exciting ways in which you can offer your advertisers better value for money. Another way in which this method may be improved is by the encapsulation of most of this code into a COM object, although with the advent of ASP.NET, ASP effectively becomes a compiled medium, so most speed advantages will be lost, although the other advantages, such as ease of redistribution, still apply. I hope that I've managed to teach you the basics of the system though, from which you will be able to expand and modify for your own needs.

Happy Programming!

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