Track Website File Downloads and Links Using ColdFusion
I've been fairly impressed with most of the blog software packages out there. They've allowed me to create this website without having to worry about a lot of the nitty-gritty tedious stuff and focus more on the look and content.
There is one thing that I wish they would do out of the box, but I think it's something that puts me in the minority. I want to be able to track the file downloads for my various projects. I know, it's silly, but I thought it would be neat to know what stuff you guys are more interested.
Now, the funny thing is that when I came up with this idea, I originally I was just going to track the file downloads, but I had a client call me saying that they would like to track various links on their website when users click on them. The ole brain gears kicked into high gear when I thought to myself, "Hmmm... download tracker..... link tracker... by-golly, that's almost like the same thing."
So, if you are looking to track file downloads or url links on your website or blog, here's a quick tutorial on how you can add your very own link manager.
First thing we need to do is to create a simple database table that will keep track of our file downloads. I use mySQL, so that's the example I'm going to provide, but it's simple enough that you should be able to modify it to suit your other DB needs. You can name your table whatever you like, but be sure to update it throughout all the files. Ok, let's create the table.
mySQL Create Table Script
CREATE TABLE `tblbloghitman` ( `id` varchar(35) NOT NULL default '0', `link` varchar(128) NOT NULL default '', `hits` int(11) NOT NULL default '0', PRIMARY KEY (`id`) )
Simple enough, right? Now we need to create a coldfusion file that will handle adding/editing the link entries into the database and redirect the user to the supplied link.
<!--- variable to hold your datsource and root domain ---> <cfset REQUEST.DSN = "YOURDSN"> <cfset REQUEST.DOMAIN = "http://www.YOURSITE.com/"> <!--- url parameter that holds the full relative path to the file (i.e. http://www.YOURSERVER.com/MYFILE.zip) ---> <cfparam name="url.link" default=""> <!--- check some basic link properties to make sure we should continue ---> <cfif Left(url.link,Len(REQUEST.DOMAIN)) eq REQUEST.DOMAIN and url.link neq "" and Len(url.link) lt 255> <cfquery name="getHits" datasource="#REQUEST.DSN#"> SELECT id, hits FROM tblbloghitman WHERE link = "#url.link#" LIMIT 1 </cfquery> <cfif getHits.RecordCount neq 0> <!--- link exists, update the hit count ---> <cfquery name="updateHits" datasource="#REQUEST.DSN#"> UPDATE tblbloghitman SET hits = #Val(getHits.hits+1)# WHERE id = "#getHits.id#" </cfquery> <cfelse> <!--- link does not exist, create a new entry for it ---> <cfquery name="addHit" datasource="#REQUEST.DSN#"> INSERT INTO tblbloghitman (id,link,hits) VALUES ("#CreateUUID()#","#url.link#",1) </cfquery> </cfif> <cfelse> <!--- error, the file name was blank or to long. handle the error... or keep going, your call ---> </cfif> <!--- follow the passed link, we are done ---> <cflocation url="#url.link#" addtoken="no"> <cfabort>
We need to perform some error checking so that people can't take advantage of the system and supply false links. I would suggest checking the referring link as well, but I didn't include that here to help keep it simple. Another thing I didn't want to do was manually add new entries each time I have a new file or link for people to follow, so the manager has the ability to add new link entries along with updating the existing entries.
That's it. To get the system working all you need to do is update the links you wish to track by adding the CJ Hitman path to the link like so:
<a href="http://www.YOURSITE.com/cj_hitman.cfm?link=http://www.YOURSITE.com/MYFILE.zip">Click to download My File</a>
Now, you should start seeing the tracking data in your database. This file can easily be modified if you require to track the users IP address, date and time and various other information if needed. You could also change it so it tracks every event as a new entry instead of updating an existing record. This might be helpful if you need some more robust user tracking. If you have any questions or see some errors, let me know in the comments.