E-Business Opps: A Clickstream Data Warehouse Meta-Schema
In this installment, we introduce the concept of a clickstream data warehouse meta-schema that can be customized to fit any type of e-business. The best way to understand the clickstream data warehouse meta-schema is to start out with a familiar brick-and-mortar Sales and Marketing schema. This schema has a central Sales fact table, surrounded by the standard dimensions – Time, Geography and Product. To spice up this canonical schema, I generally add a Promotion dimension for tracking the effectiveness of any advertising promotions. With a schema like this, an enterprise can answer a question like, "How do the sales by quarter in 2000 compare to the sales by quarter in 1999?"
As late as 1997 or 1998, your enterprise could win an award for a good implementation of the above sales and marketing data warehouse, but this was soon to change with the arrival of the Customer Relationship Management (CRM) juggernaut. Interestingly, transforming this sales and marketing data warehouse schema into a CRM-oriented data warehouse schema requires the addition of only one new dimension, the Customer dimension.
With a CRM-oriented data warehouse schema one can answer a questions like, "Which customers responded to the Millennium promotion in the fourth fiscal quarter in the Eastern Region? This type of CRM data warehouse is cutting-edge technology. If your enterprise currently has a successful implementation of this type of CRM data warehouse, it could win an award right now.
Now, lets transform the CRM schema into a clickstream data warehouse Electronic Relationship Management (eRM) meta-schema. CRM sales facts become User Activity facts in the eRM environment. All user activity (causal browsing, directed searchers, etc.), not just sales events, can be recorded on the Web. User Activity Facts can be strung together into entire user visit histories, which then can be classified by type.
The CRM-style Customer dimension becomes the eRM User dimension in the meta-schema. Users come in many different types. With a User dimension you can ask business questions that distinguish between the different types of users, resulting in much richer business intelligence than is possible with a limited Customer dimension.
The CRM-style Geography dimension splits into three new dimensions in the eRM environment. Physical Geography is fairly analogous to the old-style CRM regional Geography dimension. The new Web Geography dimension maps the site entry and exit pages, including information about the external referring sites, so that you can see who drives traffic to your site and where the traffic goes when it leaves. Site Geography maps the parent/child relationships between all Web site pages, creating a map of the site and how it changes over time.
The Time dimension splits into the Fiscal Time dimension and the User Time dimension in the ERM meta-schema. The enterprise-centric Fiscal Time dimension is analogous to the old CRM corporate Time dimension, but the new User Time dimension recognizes the worldwide nature of user access, which is definitely not done according to traditional corporate time rules. Internet users can be in any time zone and their seasons and holidays vary widely across their time zones and physical geographies. The User Time dimension makes it possible to analyze user activity by specific time zone and seasonalities.
The CRM Product dimension splits into the Content and Activity dimensions in the eRM meta-schema. Because Web sites are pure information entities, they are made up of different types of Content and sets of Activities that you can perform on that content. Each type of content has a specific set of activities that correspond to it, and these are often interesting to analyze separately. Examples of activities include a simple look at a page, a click-through on an ad, abandonment of a shopping cart, a purchase transaction, entering the site, exiting the site, etc.
Many people have asked me if it is okay to put a Product dimension into their eRM schema, usually in lieu of the Content dimension. I think this is okay, as long as you realize that other types of site content are not getting tracked when you narrow the scope of this dimension to only Products.
Finally, the Promotion dimension is split into the Internal and External Promotion dimensions in the new meta-schema. In the Internet environment, promotions can exist either internally or externally, like banner ads and affiliated links. It is interesting to analyze each type of promotion separately, because the internal and external environments are so different. Because of the wide-ranging nature of advertising engines, your banner ad can appear on sites you never dreamed of, and click-throughs from these exotic sites need to be separately analyzed from the "Internet Special" promotion you do within your own site.