Case Study: An Affiliate System With ASP

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
objComm.Execute

end if

if Request.Cookies("trackId") = "" then
affTrackId = 0
else
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.
<br><br>
<a href="javascript:history.go(-1)"><img src="rel.gif" border="0"></a>
</span>
<%

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,
@dteEnd DATETIME,
@intAffId INT
AS
SELECT LEFT(CAST(affDate AS CHAR), 11), COUNT(*)
FROM AffiliateHits
WHERE affDate >= @dteStart
AND affDate <= @dteEnd
AND affAffiliateId = @intAffId
GROUP BY affDate
ORDER BY affDate ASC

GO

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
%>
<tr>
<td width="80%" bgcolor="<%=bgcol%>" height="24">
<p style="margin-left:10">
<span class="bodyText"><%=objRS.Fields(0).value%></span>
</td>
<td width="20%" bgcolor="<%=bgcol%>" height="24">
<p style="margin-left:10">
<span class="bodyText"><%=objRS.Fields(1).value%></span>
</td>
</tr>
<%

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

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

The final output looks like this:


blog comments powered by Disqus