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) "
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:
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:
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:
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
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: