ArsDigita Archives

Clickstream Data Warehousing

by Richard Li (, Jon Salz (

Submitted on: 2000-07-01
Last updated: 2000-08-14

ArsDigita : ArsDigita Systems Journal : One article

Planning, development, and promotion are the most obvious tasks involved in building a web service. But analyzing how end users use a service may be more important to that service's success. Understanding how users reach your site and how they behave once they are there allows you to improve future users' experiences with the site. It also allows you to improve your ROI by increasing sales and increasing the "stickiness" of user visits.

We turn to clickstreams, the streams of requests (clicks) users generate as they move from page to page within a web site, to provide this kind of information. Clickstreams contain a tremendous amount of quantitative data that can be used to improve users' experiences with a site. When properly mined, clickstream analysis can provide answers to very specific questions such as:

  • What are the most popular pages on a site? the most popular products?
  • How do people arrive at the site? Where do they go when they leave? How long do they stay?
  • What percentage of sessions resulted in a sales transaction?
  • Which pages on our web site seem to be "session killers," where the remote user stops the session and leaves? Do a certain percentage of people leave my web site with loaded shopping carts at a single Web page?
  • Which browsers and operating systems do visitors use?
  • What are the most common paths users take throughout the site?
  • How successful are various banner ad campaigns? How much do people spend on my site if they arrived via a particular banner ad?

The answers tell us where to direct development and promotion efforts in the future. If we find that particular services we've put a lot of effort into are being neglected by users, we want to feature them near the top of the site to improve their popularity. If we find that users are leaving their shopping carts full at a certain point in the E-commerce checkout process, we may want to hire a usability consultant to simplify the UI so users will complete their transactions.

The standard approach today to analyzing user behavior and usage on a web site is through log file analysis of web server logs. Web servers log each HTTP request into a text file in the filesystem; a log file analyzer can analyze the log files and present the data in a variety of useful ways. However, web server log files can only answer a subset of the questions that can be answered by clickstream analysis (see Chapter 9: User Tracking in Philip and Alex's Guide to Web Publishing,

Standard log file analyzers cannot provide the level of analysis made possible by clickstream data warehousing for several reasons. First, a standard log file does not contain all the information that the web server knows about a visitor, such as user identity. In addition, building a data warehouse for clickstream data enables the clickstream data to be correlated from different sources. For example, we can correlate the clickstream behavior of a particular group of users to the dollar value they currently have in their shopping cart.

Introduction to Data Warehousing

A data warehouse is a relational database whose goal is the analysis of large amounts of data. This goal is distinct from the other common use of databases, transaction processing, whose goal is to maintain records of transactions. A data warehouse contains data that is derived from transaction data, but may also include data from other sources. A data warehouse is used to analyze data for patterns and to answer questions that may not be asked until after the data are actually available. In other words, a data warehouse must be a flexible structure for storing vast quantities of data so that it can be prepared to answer any question that may be posed to it, questions that inevitably will not be anticipated at design time.

A star schema is the standard proven design for a data warehouse. The heart of the star schema is the fact table, which has one row for each fact in the system. Choosing the level of detail in the information that goes into a particular row is known as determining the granularity of the data warehouse. Each column of the fact table is a foreign key that references a particular dimension; data about a particular dimension is stored in dimension tables that are joined with the fact table as needed. For instance, a data warehouse for a consumer products retailer company might have a granularity of one product sale per row. The dimensions of the data warehouse may include which product was sold, where it was sold, the price at which it was sold, and the date when it was sold. When a query requires data from multiple dimensions, these dimensions are joined by the database to form what appears to be one large table with the necessary data.

The process of building a data warehouse can be broken down into three phases: gathering data, data transformation and data warehouse population, and querying the database. In the example above, the data gathering process would collect data about every sale transaction, store location, and any other data required by the data warehouse. The data gathering process usually will require that data to be gathered from multiple sources: sales information is collected from each retailer, geographic information comes from a company database, and so forth. The data transformation phase takes all the data gathered in the previous step and loads the data into the warehouse. This process reconciles any inconsistencies in the data (for example, data from one source might use code BLU to indicate an item with the color blue, while another source may use the code BLUE to mark the same attribute), computes any necessary values from the raw data, and populates the data warehouse with the processed data. The third phase is targeted at the end user, and involves providing user interfaces that allow users to query and analyze the data in the warehouse. Each of these phases is actually an ongoing process; new data are continually gathered, transformed, and analyzed.

Additional reading:

Design Goals

Four key considerations drive the design of the clickstream data warehouse architecture.
  • Performance and scalability On a heavily trafficked site, tens or hundreds of millions of clicks can occur every day. This enormous amount of data requires that the data model be robust enough to support queries involving billions of rows of data, and that the data population scheme is efficient and scalable.
  • Transparency The system should be transparent to both the end user and to the web developer once it is installed and properly configured. If the system requires developers to adopt additional coding practices to support clickstream logging, it will likely provide inaccurate data or be unused.
  • Tools-agnostic Different web sites use different technologies; the clickstream system should not preclude the use of any particular software. Moreover, integrating the clickstream system on a live site should not require rearchitecting substantial parts of the original web site. The particular implemetnation we discuss here will rely on AOLserver and Oracle.
  • Extensibility The system should support ad hoc queries into a very large data warehouse without incurring substantial performance penalties even if the query is written by an end user inexperienced in tuning SQL.

Collecting and Analyzing Data

As mentioned before, clickstreams provide a tremendous amount of data. We have one data point per hit to a site -- potentially millions per day and billions per year -- so if we're not careful, analyzing our data could be very expensive. Typically, the life cycle of any particular data point is as follows:

  1. Immediately after the user requests a page on the web site, the server writes a line to a log file containing very detailed information about the request.
  2. Periodically a separate data warehousing server requests the latest chunk of the log file, loading them into a event log table (cs_event_log) in the Oracle database.
  3. Entries in the event log table are analyzed by the clickstreaming core code in Oracle, which consists of a few thousand lines of PL/SQL. This involves populating the dimensions -- page, session, user agent, etc. -- and inserting a row into the data warehousing fact table for each row in the event log table.
  4. Someone queries the data warehouse, obtaining some sort of report.
Let's take a look under the hood at each of these steps.

Step 1: Event Logging

We use AOLserver filters (the ns_register_filter API) to trap requests to HTML pages so we can log information about them to files in the filesystem. Hits are generally logged into files with names like servername-cs.log.2000-04-25.14:00 (this example is for the period of time between 2:00 and 3:00 p.m. on 25 April 2000). Each line has the following fields:
  • The time at which the page was requested
  • The time at which the download of the page was completed
  • The URL requested
  • The IP address of the request
  • The user ID of the user, if known
  • The query string in the request URL
  • The content type of the page delivered
  • The number of bytes delivered
Every 24 hours, the individual clickstream logs are concatenated into one large log file and compressed.

Step 2: Loading data into the warehouse

We use HTTP to transfer the compressed clickstream log files from the production web server(s) to a dedicated data warehouse machine. We use a dedicated data warehouse machine because we don't want to slow the production web server down while we're populating the database, and because we configure Oracle differently for data warehousing. Once the clickstream logs have been transferred, we generate a SQL*LOADER .ctl file and do a direct path load of the data into the cs_event_log table.

Step 3: Populating the warehouse

Once there is data to be processed inside the cs_event_log table, we run a PL/SQL procedure to populate the data warehouse. We use PL/SQL to maximize performance, since a considerable amount of computation occurs in this step. The PL/SQL procedure performs several major operations:
  1. First, it calls PL/SQL procedures that populate the pages, referrers, and user_agents dimensions of the warehouse. These procedures obtain a list of all entries in the appropriate dimension, and compare this list of entries to the incoming data set. If there is any data in the cs_event_log table that is not in the dimension, the procedures insert the additional data. For instance, there is one row in the referrer dimension for each unique referrer. The cs_create_pages_and_referrers procedure scans the cs_event_log table and inserts any new referrers into the cs_dim_referrers table. Since SQL queries in PL/SQL against large dimensions are quite slow (because of context-switching overhead), we build dynamic PL/SQL procedures that cache all the necessary data directly inside the PL/SQL procedure.
  2. Once the dimensions are properly populated, the fact table is updated with any new facts.
  3. Once the necessary rows have been copied into the fact table, the dynamic sitemap tree is created in the cs_sitemap table (see the Sitemap section below.

Step 4: Analysis

Analyzing the data in the warehouse is extremely expensive -- not only are there millions of rows to scan and aggregate, but joins against one or more of the dimension tables are usually required as well. We attempted to address these performance issues in several ways.

We attempted to maximize the amount of computation that was done prior to querying, and cache the results of this precomputation. The primary way we performed precomputation was through the use of materialized views. Materialized views are views that precompute aggregates and joins, storing the results in tables. As new rows are inserted into the underlying tables, the materialized view can be incrementally refreshed. Unfortunately, restrictions imposed by Oracle on the use of materialized views preclude using materialized views for every aggregate and join needed for queries. In addition, we had substantial problems in getting materialized views to work consistently, and we were forced to periodically recreate materialized views from scratch, an extremely time-consuming process.

We also attempted to optimize the queries with data warehouse hints such as /*+ STAR */, but, upon further analysis, we determined that the performance of these queries was I/O bound, and that these hints were not substantially improving the performance of the queries. We believe that there is room in this area for substantial improvement, since we were developing on a shared development machine with an Oracle installation configured for OLTP.

Since the queries we were executing against the data warehouse grew exponentially slower as more rows were loaded into the warehouse, we decided to store only the results of analyses against the data. In this new scheme, all the data in the data warehouse is deleted on a daily basis, and clickstream data for the most recent 24-hour period is loaded. This data are then analyzed; the results of each analysis is stored in an HTML file for the given day. Thus the data warehouse holds only the clickstream data for one day at any given time. The price paid for the scalability, reliability, and performance that this approach offers is that aggregation across multiple days becomes more complex, and new reports cannot be performed on previously collected data.

The Data Model

The cs_event_log table contains all the data that is directly loaded into the warehouse with SQL*LOADER, and is identical in format to the clickstream log files:
create table cs_event_log (
	event_id		integer,
	event_time		integer,
	end_time		integer,
	date_id			integer,
	url			varchar(4000),
	instance_id		varchar(4000),
	user_ip			varchar(50),
	user_id			integer,
	query			varchar(4000),
	bytes			integer,
	content_type		varchar(200),
	session_id		integer,
	browser_id		integer,
	user_agent_string	varchar(4000),
	accept_language		varchar(10),
	referring_url		varchar(4000),
	method			varchar(10),
	status			integer,
	secure_p		char(1)
The data warehouse itself is a standard star schema, and consists of a single fact table and multiple dimensions. Note that the diagram below illustrating the star schema shows only a subset of the dimensions that we have in the data model.

clickstream data model

The granularity of the fact table is one page view. Each column of the fact table that references a particular dimension is a foreign key. The only way that data are entered into the data warehouse is via the PL/SQL procedures. Since the PL/SQL guarantees the preservation of the constraints, we disable all constraints to enhance performance. We maintain the constraints in the data model in the disabled state so that the Oracle cost-based optimizer can take advantage of this information when it optimizes queries.
create table cs_fact_table (
	cs_id			integer primary key,
	-- load start/end, dwell time if able to determine
	load_start		integer,
	load_time		integer,
	dwell_time		integer,
	page_id			integer constraint cs_ft_page_id_fk references
		cs_dim_pages not null disable,
	-- which instance of the page is it?
	instance_id		varchar(3000),
	instance_provided_p	char(1) default 'f' not null check(instance_provided_p
		in ('t','f')) disable,
	referrer_id		integer constraint cs_ft_referrer_id_fk references
		cs_dim_referrers disable,
	date_id			integer 
                                constraint cs_ft_date_id_nn not null disable
                                constraint cs_ft_date_id_fk references cs_dim_dates disable,
	session_id		integer
                                constraint cs_ft_session_id_fk references cs_dim_sessions
	user_agent_id		integer
                                constraint cs_ft_user_agent_id_fk references
		cs_dim_user_agents disable,
	user_ip                 varchar(50)
                                constraint cs_ft_user_ip_nn not null disable,
	browser_id		integer,
        user_id			integer,
	-- HTTP method (GET, POST, HEAD)
        method                  varchar(10)
                                constraint cs_ft_method_nn not null disable,
	-- HTTP status code
        status                  integer
                                constraint cs_ft_status_nn not null disable,
        bytes                   integer,
	content_type		varchar(200),
	-- might want to turn accept_language into a dimension?
	accept_language         varchar(10),
	secure_p		char(1) 
                                constraint cs_ft_secure_p_nn not null disable,
                                constraint cs_ft_secure_p_ck check(secure_p in ('t','f'))
	-- how many css into the session are we? 1, 2, 3...
	cs_within_session	integer,
	user_state_id		integer
                                constraint cs_ft_user_state_id_fk references cs_dim_user_state
The cs_dim_user_agents dimension is an example of a typical dimension in the clickstream data warehouse. This dimension records information about the particular user agent for each particular HTTP request:
create table cs_dim_user_agents (
	user_agent_id		 integer
                                 constraint cs_dua_user_agent_id_pk primary key,
	-- The string returned by the browser
	user_agent_string	 varchar(3000) not null,
	-- Mozilla, Opera, IE
	browser_type		 varchar(3000),
	browser_version		 varchar(3000),
	-- The major part of the browser version. For MSIE and Mozilla, this is just
        -- the first three characters.
	browser_version_major    varchar(3000),
	-- MacOS, Win32, Unix
        operating_system	 varchar(50),
	-- MacOS 8, Windows 95, Windows 98, Windows NT, Linux, Solaris, etc.
	operating_system_variant varchar(50)
The current data model includes the following dimensions:
  • Page dimension: What was the name of the page requested? What type of page was requested? What was the url? What was the content type?
  • Referrer dimension: What kind of referrer was it (e.g., search engine)? What was the referring url? What search text, if any, was used? If it was from inside the site, what is the referring page id?
  • Date dimension: Which day of the week was the request? Day in the month? Day in the year? Which week in the year?
  • User agent dimension: Which browser is the user using? Which version? Which operating system?
  • Session dimension: When did a particular session start? When did it end? Which user? How many clicks were part of the session? What was the total value of the sales in the session?
  • User state dimension: What is the current value of the shopping cart?
  • User demographics dimension: What is the income level of the user? What is his/her race?

An example analysis: the dynamic sitemap (screenshot)

The dynamic site map illustrates the most popular paths users follow when navigating the site. For each page in the site, we determine which page is the most common referrer to that page. If this "best referrer" is external, the page shows up as a root-level node in the sitemap (since people typically get to the page from some external source); if the best referrer is internal, the page shows up beneath that referrer in the sitemap tree.

cs_create_sitemap builds this tree breadth-first: first it inserts into the sitemap all pages which belong in the top level (i.e., have an external "best referrer"). Then it inserts into the second level of the sitemap any pages that have a top-level node as a best referrer; then it inserts any pages which have a second-level node as best referrer, etc. It loops until it can't insert any more nodes.

When this process is complete, there may still be some pages which haven't been inserted into the tree: consider the case where two pages have each other as best referrers. When cycles such as these occur, we pick the node in the cycle which is most commonly hit (call it X), and we delete as potential parents all other nodes in the cycle. We then continue the loop described in the previous paragraph. The node X is guaranteed at this point to be inserted somewhere in the site map, since none of the nodes in the cycle can possibly be X's best referrer any more. We repeat this process until all cycles are resolved.

The end result is a dynamically built sitemap illustrating the paths users most frequently take navigating the site.

An example query: Entry Pages (screenshot)

How users typically enter a web site is a common question for web site operators. We can easily answer this question with a single query:
select    p.page_id, p.page_url name, count(*) value
from      cs_fact_table f, cs_dim_pages p, cs_dim_sessions s
where     f.page_id = p.page_id
and       f.session_id = s.session_id
and       f.cs_within_session = 1
and       p.exclude_p = 'f'
and       s.clicks > 1
group by  p.page_id, p.page_url
order by  3 desc
This query performs a join between the main fact table and two dimension tables, the cs_dim_pages table and the cs_dim_sessions table. Let's dissect the remaining where clauses:
  • f.cs_within_session = 1
    ensures that the page_id retrieved is the first page clicked in a given session

  • p.exclude_p = 'f'
    excludes requests that should not be counted (e.g., requests for images)

  • s.clicks > 1
    ensures that we are only analyzing sessions that have registered more than one page request. We throw away one-hit sessions because we cannot distinguish between first time visitors who hit the web site once and visitors who have non-cookied browsers. If we included one-hit sessions, our data would be skewed by non-cookied visitors who are browsing the site, since every page request from these visitors would look like an entry page.

Future work

Several of our clients currently use our clickstream data warehouse architecture, and rely on it to answer many of the important business questions described in the overview. We plan on improving the architecture by:
  • Refining and developing a comprehensive set of queries that answer most of the questions that users want.
  • Extending the data population code to include all dimensions in the data model and determining what additional dimensions should be added.
  • Defining a standard way to extend the data model by adding custom dimensions, or augment existing ones; and developing a mechanism to transfer additional data necessary to populate these extra fields from the production web server to the database.
  • Validating the data collected and analyzed by comparing the results against other log analyzers.
A development snapshot of the clickstream software discussed in this article is available for download.

Related Links