Сounting downloads from your site

If you have some downloadable content on your site (installation packages), you’ll probably want to monitor the statistics of downloads. But the popular Google Analytics system cannot count that as it is working only inside html-pages. Another option is analyzing server logs, but that’s something you’ll probably choose last of all… That’s why we are offering rather a simple way of counting downloads with Helicon Ape.

The steps to be accomplished are:

• create a table in the database to store the download counters for the products;
• create an .htaccess in Helicon Ape which will make records into the table upon each download with the help of mod_dbd module;
• create a simple aspx-page to show downloads statictics.

Preconditions

In the root of your site you have a downloads folder containing files for download (e.g., AudioCoder.msi, AudioDecoder.msi, AdditionalCodes.zip).
You have Helicon Ape installed on your Windows Server 2008 (IIS7).
You have SQL Server that will store the DB with downloads statistics.

Table for storing statistics

Run Microsoft SQL Server Management Studio and connect to your SQL server.

Right click on ‘Databases’, select ‘Create Database’, enter database name (for example ‘DownloadsCounter’) and click ‘OK’.

Now we’ll create the table itself:
Unfold the DownloadsCounter in Object Explorer, right click on ‘Tables’ and select ‘New Table…’. Name the table ‘Downloads’ and add 4 fields: id (int), moment (datetime), filename (nvarchar(50)) and ipaddress (nvarchar(50)) as shown on the pic below. You can find sql script to create table in attached DownloadsCounterExample.zip.

Done with the table!

mod_dbd configuration

Start Helicon Ape Manager, select your site from the tree and click on ‘downloads’ folder. Now write the following config in .htaccess on the right.

The config instructs mod_dbd to connect to DB, catches data from request (filename and client ip-address) and writes them into the DB.

# Helicon Ape version 3.0.0.59

# Connection settings
DBDriver mssql
DBDParams "Data Source=db2003\MSSQLSERVER2008;Initial Catalog=DownloadsCounter;\
           User ID=sa;Password=123123"

# Save Filename (for .msi & .zip files only)
SetEnvIfNoCase REQUEST_URI ^/downloads/(.+\.(?:msi|zip))$ FileNameENV=$1
SetEnvIfNoCase REMOTE_ADDR ^(.*)$ IpAddrENV=$1

# Sql query to save download event
DBDPrepareSQL "INSERT INTO DownloadsCounter.dbo.Downloads\
    (moment, filename, ipaddress)\
    VALUES (\
        GETDATE(),\
        '%{FileNameENV}e',\
        '%{IpAddrENV}e'\
    )\
" InsertDownload

# Execute sql query if request uri is .msi or .zip file
SetEnvIf request_uri \.(?:msi|zip)$ dbd_execute=InsertDownload

# limit access to statistics page to localhost only
<Files stat.aspx>
Order Deny,Allow
Deny from all
Allow from ::1 127.0.0.1 localhost
</Files>

Save your .htaccess and try to download something from the browser. If you now throw a glimpse at the ‘downloads’ table in SQL Server Management Studio, you’ll see the records appearing in it:

How it works?

Helicon Ape module is processing all requests coming to /downloads/ folder. If .zip or .msi file is requested, Ape memorizes the filename (FileNameENV) and client IP (IpAddrEnv), from which the file was requested, and inserts these data into the table by means of SQL query.

Statistics viewer

The archive attached to the article includes stat.aspx which does a very simple task—shows records from ‘downloads’ table.

Of course, that’s not the sort of viewer you need, as it must be capable of showing statistics by days, months, products etc. But designing a real-life solution is beyond the scope of this article, so feel free to advance stat.aspx by yourself.

Resulting archive

Below is the archive DownloadsCounterExample.zip containing all files from this article. To do some testing just unzip it into the root of your site and replace the database password from ‘123123’ to the one you have in both .htaccess and web.config.

DownloadsCounterExample.zip

Why is this option better?

As we already noticed, Google Analytics and like services are based on JavaScript working on the page. They know nothing about static content and other files loaded from the server.

Another option (yet another extreme) is server logs. They are not always accessible, not always enabled, besides, they require writing of special parsers or analyzers. Moreover, server logs do not provide live (current moment) info. Usually log file is created daily so their analysis is only possible at the beginning of the next day.

Summing up

The method explained above is a quick and effortless way to screw downloads counter to your site using Helicon Ape mod_dbd. The example is easily expendable, e.g. you can add more fields to the table to save Referer, User-Agent, etc., or develop a customizable statistics viewer.

We’ve just given you the basement, now it’s time for you to build a house.

Best regards,
Ruslan—Helicon Tech Team