Home arrow ASP arrow Page 6 - Case Study: An Affiliate System With ASP

Case Study: An Affiliate System With ASP

An affiliate system is essential for anyone selling products online. In this article Mitchell goes behind the scenes and shows us an affiliate system from one of his eCommerce web sites.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 55
June 17, 2002
  1. · Case Study: An Affiliate System With ASP
  2. · Project Overview
  3. · The ASP code
  4. · The ShowLogin function
  5. · Generating links
  6. · Tracking Links
  7. · Conclusion

print this article

Case Study: An Affiliate System With ASP - Tracking Links
(Page 6 of 7 )

The TechBuy affiliates program tracks in-bound affiliate links via the use of a URL and cookies. As you saw on the last page, each affiliate URL generated contains a query string variable called "at". This is the affiliates ID, and if it's detected in the URL then it is extracted and a new record is added to the AffiliateHits table:

dim trackId
trackId = Request.QueryString("at")

if isNumeric(trackId) and trackId <> "" then

Response.Cookies("trackId") = trackId
Response.Cookies("trackId").expires = date() + 7 'A week

affTrackId = trackId

'Add a record of this click thru to the AffiliateHits table
objComm.CommandType = adCmdText

strQuery = "INSERT INTO AffiliateHits(affAffiliateId, affIP, affDate, affTime) "

strQuery = strQuery & "VALUES(" & trackId & ", '" & Request.ServerVariables("REMOTE_ADDR") & "', '" & Date() & "', '" & Time() & "')"

objComm.CommandText = strQuery

end if

if Request.Cookies("trackId") = "" then
affTrackId = 0
affTrackid = CInt(Request.Cookies("trackId"))
end if

The code above sets a cookie containing the affiliates tracking ID. This cookie is valid for date() + 7 days, or one week.

Notice in the last couple of lines that a variable called affTrackId is created? This variable is used on the checkout page to flag the order as an affiliate order. The orders table looks partially like this: 

When an order is retrieved, its affiliateId field is checked. If this value is not NULL, then a sub-query pulls the details of the affiliate from the affiliates table and schedules a payment in the AffiliatePayments table. This occurs automatically in the back-end software for TechBuy, so it is out of the scope of this article.

Clicks and sales reports
If the query string variable where is set to "report", then the ShowReport function is called. This function shows two forms: One for the click thru rate and one for the sales.

Each form calls a different function of the affiliates.asp page when its respective form is submitted. Today we're only going to look at the click-thru form results, which are called by setting the hidden form variable "where" to ctreport:

<form name="frmCT" action="affiliates.asp" method="post">
<input type="hidden" name="where" value="ctreport">

Once the click-thru form is submitted, a valid range of dates is formulated from those selected on the form:

viewType = CInt(Request.Form("ctDate"))
startDate = Request.Form("fromMonth") & "/" & Request.Form("fromDay") & "/" & Request.Form("fromYear")

endDate = Request.Form("toMonth") & "/" & Request.Form("toDay") & "/" & Request.Form("toYear")

if viewType = 0 then
startDate = Date() - 7
endDate = Date() - 1
blnValid = true
elseif viewType = 1 then
startDate = Date() - 31
endDate = Date() - 1
blnValid = true
end if

The date range for a report must be within three months, so we check that with the datediff function:

if datediff("M", startDate, endDate) > 3 and blnMsg = false then
<span class="BlueHeader">Invalid Date Selected!</span><br><br>
<span class="bodyText">
The date range that you have selected spans more than three months. You can only
view a maximum of three months per report at any one time. Please go back and
choose a maximum of fifty days.
<a href="javascript:history.go(-1)"><img src="rel.gif" border="0"></a>

Once we've attained valid start and end dates, we call a stored procedure to retrieve all records from the AffiliateHits tables for this particular affiliate. The stored procedure is called sp_GetAffHitsByDateRange and it is called like this:

objRS.Open "sp_GetAffHitsByDateRange '" & startDate & "', '" & endDate & "', " & intAffId

The actual stored procedure is quit simple and looks like this:

CREATE PROC sp_GetAffHitsByDateRange
@dteStart DATETIME,
@intAffId INT
FROM AffiliateHits
WHERE affDate >= @dteStart
AND affDate <= @dteEnd
AND affAffiliateId = @intAffId
GROUP BY affDate


It uses SQL Server's GROUP BY clause to get the date and number of click-thrus for that date. The results from this stored procedure are output as part of a table using a while loop, like this:

while not objRS.EOF
<td width="80%" bgcolor="<%=bgcol%>" height="24">
<p style="margin-left:10">
<span class="bodyText"><%=objRS.Fields(0).value%></span>
<td width="20%" bgcolor="<%=bgcol%>" height="24">
<p style="margin-left:10">
<span class="bodyText"><%=objRS.Fields(1).value%></span>

if bgCol = "#EFEFEF" then
bgCol = "#FFFFFF"
bgCol = "#EFEFEF"
end if

totalClicks = totalClicks + objRS.Fields(1).value

The final output looks like this:

blog comments powered by Disqus

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials