Figure 1: Reporting on site referrals
ColdFusion users have an application.cfm file which makes building an application much easier in many respects than other languages. The reason is that this file can do a variety of tasks because it is always the first file executed no matter what page was requested in your application. You can use this to your advantage in an affiliate tracking application, such as in an e-store. There are several possible situations where this will be helpful:
Affiliate banner ads are a great way to get other sites to put up a banner for your site — you give the site owner a small fee for every click through or every click through that results in a sale. The business side of this concept is beyond the scope of the article, but you can take a few pointers from Amazon.com and Google.com.
The technique I'll use in this tutorial is simply scanning the incoming web request for a URL variable named referral (or whatever else you decide to name it). If the variable does not exist, nothing happens. If the variable does exist, a session variable is set and the referring site and variable are inserted into a database. The reason you put the URL variable into a session variable is so that you can track whether or not a sale occurs. The sale might occur several pages later. To accurately record the referral, you'll also have to update the referral table when the sale does occur.
The first step is to set up the database table holding the referrals. This will be a simple table holding the following information:
Note: In SQL Server, you could avoid the use of a timestamp and simply use the @@identity variable instead. Timestamps are used to keep the application compatible with other databases.
The following statement will create the table in the database for SQL Server:
CREATE TABLE Referrals (
ref_id int IDENTITY (1, 1) NOT NULL ,
ref_time datetime NULL ,
ref_url varchar (255) NULL ,
ref_affiliateid varchar (50) NULL ,
ref_cust_id varchar (50) NULL,
ref_timestamp varchar(50) NULL
)
The same table code for MySQL would be as follows:
CREATE TABLE Referrals (
ref_id int PRIMARY KEY AUTO_INCREMENT NOT NULL,
ref_time datetime NULL ,
ref_url varchar (255) NULL ,
ref_affiliateid varchar (50) NULL ,
ref_cust_id varchar (50) NULL,
ref_timestamp varchar(50) NULL
);
The ColdFusion code for the referral resides in two places: the Application.cfm and the order page of your particular application. I can address the first and give you the steps required for the second.
The code required for the Application.cfm file is as follows, and is commented inline (available in the zip package as Application.txt. Include the code in your Application.cfm file):
<cfif isdefined("url.referral")>
<!--- Wrap in a try/catch block to prevent errors. --->
<cftry>
<!--- Use a cfparam to generate an error if the URL variable is tampered with --->
<cfparam name="url.referral" default="" type="variablename">
<!--- The getTickCount() function is used to find the referral later
if a purchase is made --->
<cfset session.referral = getTickCount()>
<!--- Insert the referral into the database and retrieve the autonumber ID--->
<cfquery name="insertReferral" datasource="myDSN">
INSERT INTO Referrals (ref_affiliateid,
ref_url,
ref_time,
ref_timestamp)
VALUES ('#url.referral#',
'#cgi.http_referer#',
'#dateformat(now(),"mm/dd/yyyy")# #timeformat(now())#',
'#session.referral#')
</cfquery>
<cfcatch>
<cflog file="referral" text="Referral error: #cgi.http_referer#">
</cfcatch>
</cftry>
</cfif>
Note: Using MySQL, you may have to use a different date/time format for the ref_time field, such as yyyy/mm/dd.
Now, when a user clicks a link that looks like this:
http://www.yoursite.com/somepage.cfm?referral=joe
The data "joe" will be inserted into the database, and if the referral results in a sale, Joe might end up with a small piece of the pie. Nice, huh? Alternatively, if you put the link in an email and send it out to prospective customers, the referral link will show up in your database if the user clicks the email link. It's as simple as that.
It's beyond the scope of the article to build an e-store application, but if you have an existing application there is no doubt a place in the application where an order is inserted into the database after the customer pays you with his credit card. On that page, after the transaction is recorded, you can update your Referral table to include the transaction id or customer id of the person who made the purchase. A simple update statement is all you need:
<cfif isdefined("session.referral")>
<cfquery name="rsReferral" datasource="myDSN">
UPDATE referrals SET ref_cust_id = '#rsOrder.theCustomerID#'
WHERE ref_timestamp = #session.referral#
</cfquery>
</cfif>
That's all there is to it. Every transaction that was referred by a link or banner can now be tracked by customer id, order id, or transaction id (your choice). All you need now is a way to display the information to keep track of it.
A simple report page will show you all the referrals that your site has. We'll set up the query so that you can report by referral id, time frame (past month, etc), and also show referrals whether they resulted in a sale or not. Because I'm lazy and like to do things the easy way, I'll set up referrals.cfm using some built-in Dreamweaver functions and then modify it by hand later in code view.
1. Create a recordset for the page using the following SQL:
SELECT *
FROM Referrals
ORDER BY ref_time DESC
2. Add a Dynamic Table to the page (Insert > Application Objects > Dynamic Data > Dynamic Table). Choose All Records. You can change the table headers to make them more user friendly after applying the object.
3. Add a form to the page above the dynamic table with a text field named timeframe, a text field named affiliateid, a checkbox named onlysales and a submit button. The timeframe text field can have some descriptive text in front of it like "Since what date?". The affiliateid text field should have descriptive text that says "Affiliate ID", and the checkbox should have descriptive text that says "Show only referrals that resulted in sales".
4. Modify the SQL statement in the <cfquery> tag in CODE view to read like the following:
<cfquery name="Recordset1" datasource="myDSN">
SELECT *
FROM Referrals
WHERE 1=1
<cfif isdefined("form.timeframe") and form.timeframe NEQ "">
AND ref_time > '#form.timeframe#'
</cfif>
<cfif isdefined("form.affiliateid") and form.affiliateid NEQ "">
AND ref_affiliateid = '#form.affiliateid#'
</cfif>
<cfif isdefined("form.onlysales")>
AND ref_cust_id <> '' AND ref_cust_id IS NOT NULL
</cfif>
ORDER BY ref_time DESC
</cfquery>
You can see that we are changing the SQL depending on what is in the form fields, or simply displaying all records if nothing is in the form fields. The completed report page can be seen in Figure 1:
Figure 1: Reporting on site referrals
The report page is finished, but you can obviously add many more features, such as sorting the table, recordset paging, etc. You could add more information to the query as well, such as joining with your orders table to display the amount of the sale, the products purchased, etc. There is no validation of form field data in the current sample app, because it is an administration page -- you, as the administrator, are responsible for typing the correct information into the form fields. A more robust and secure application would have all fields validated.
Referral and affiliate tracking is easily accomplished in ColdFusion using functionality of the Application.cfm file.