Simple Statistics, Statisticus :: Article - 2eNetWorX

Statisticus, Why and How?
Part III - Simple Statistics

by Hakan Eskici

3. Simple Statistics

In the previous part, we described what we want to learn; pageviews, visitors and averages.

For pageviews and unique visitors, the idea is simple; get the totals. Depending on the criteria, we may filter our data.

Now, let's build a database structure to store our data.

At first, we begin with a single table called "Stats":

Field Name		Field Type
StatID		Long, AutoIncrement, Primary Key
Date		Date/Time
Time		Date/Time
Path		String
IP		String

With such a table structure, we will be able to learn "PageView and "Visitor" statistics. Here is the code using the "Stats" table to store the data;

<%
(...)

'Get the data into variables first
sPath = Request.ServerVariables("script_name")
sIP = Request.ServerVariables("remote_addr")

'Assuming we have an open connection and our recordset is rs;
sSQL = "SELECT * FROM Stats"
rs.Open,,, adCmdTable
rs.AddNew
rs("Date") = Date
rs("Time") = Time
rs("Path") = sPath
rs("IP") = sIP
rs.Update

(...)
%>

After we got the data in our table, it's time to analyse it and generate reports.

By executing the following SQL statements, we will get the PageView and Visitor information.

Total Page Views

SELECT COUNT(StatID) AS Total FROM Stats

Page Views for a Specific Page

SELECT COUNT(StatID) AS Total FROM Stats WHERE Path = '/index.asp'

Page Views Grouped by Pages

SELECT Path, COUNT(StatID) AS Total FROM Stats GROUP BY Path

Page views for a specific time period

SELECT COUNT(StatID) AS Total FROM Stats WHERE Date BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy#

Visitors for a Specific Page

SELECT COUNT(IP) AS Total FROM Stats WHERE Page = '/index.asp'

Visitors for each Page

SELECT Path, COUNT(IP) AS Total FROM Stats GROUP BY Path

Visitors for a specific time period

SELECT COUNT(IP) AS Total FROM Stats WHERE Date BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy#

Visitors for each page for a specific time period

SELECT Path, COUNT(IP) AS Total FROM Stats WHERE Date BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy# GROUP BY Path

Now we are able to get some basic statistics for our pages, we will use these SQL statements later for time domain reports; grouped by day, week, month and even hour. Before this, let's add some more data fields to our table such as Browser, Operating System, Color Depth, Screen Resolution and Referer.

We will need to use client-side scripting to capture some data using JavaScript which is supported by most browsers. Then we will learn a common way of executing an external ASP page with some parameters.

We will capture some more data in fourth part of the article.

Part I - Introduction
Part II - Measurement Tools
Part III - Simple Statistics
Part IV - Adding More Fields
Part V - Data Normalization Part VI - Data Gets Valuable