Persisting Behavioral Tracking Data

 

To record how online customers are interacting with your e-commerce site, you can record event information to a database. These kinds of events are called Behavior Tracking events. E-analytics and e-marketing systems can then analyze these events offline to evaluate customer behavior and transactional data. You can use the knowledge gained from analysis to create and optimize personalization rules, set up product offers, and develop interactive marketing campaigns. This section describes the requirements and database schema needed to log event data for analytical use.

This topic includes the following sections:

• Activating Behavior Tracking

• Data Storage

• Constraints and Indexes

• Scripts

 

---

Activating Behavior Tracking

Before Behavior Tracking events can be recorded to a database, you must enable the Behavior Tracking listener. This is accomplished by adding a class to the weblogiccommerce.properties file.

The weblogiccommerce.properties file contains the eventService.listeners property, which is a list of listeners that hear events transmitted through the event service. Each listener contains a list of one or more event types that the listener can receive from the event service. To enable Behavior Tracking, add the following class to the list:

com.bea.commerce.platform.tracking.listeners.BehaviorTrackingListener

Note: You must configure your database before activating Behavior Tracking. For information on how to do this, see Production Environment Scenario.

Event Properties in the weblogiccommerce.properties File

This section describes Behavior Tracking properties more fully and details the mechanism that persists Behavior Tracking event data to the database.

As previously mentioned, Behavior Tracking events are persisted to a database and then analyzed offline. The behaviorTracking.persistToDatabase property lists the events that are persisted to the database. The types in this list must match the type specified in the event; for example, the SessionBeginEvent has as its type the string "SessionBeginEvent".

Behavior tracking events are stored in a cache. The cache is intermittently swept into the database. The frequency of the sweeping of events from the cache is controlled by the following properties:

• behaviorTracking.cache.maxCount

• behaviorTracking.cache.checkIntervalSec

• behaviorTracking.cache.maxAgeSec

The sweeping is done as follows: Using the value of the checkIntervalSec property, a check is made to see if the size of the cache is greater than the value of the behaviorTracking.cache.maxCount property. If the size of the cache is greater than the value of the property, all Behavior Tracking events present in the cache are swept into the database in a single transaction. If the size criteria is not met after the checkIntervalSec interval has passed, the check is made again in checkIntervalSec seconds. Once the total amount of time since the last cache sweep is greater than maxAgeSec value, a cache sweep is performed regardless of the number of events in the cache.

The behaviorTracking.database.connectionPool property is the pool of database connections used when Behavior Tracking events in the cache are swept into the database. You can use a different connection pool. To do this, an additional pool must be set up in the WebLogic Server console, and its name substituted for the behaviorTracking.database.connectionPool property. For more information about creating a connection pool, see the WebLogic Server 6.0 Documentation Center.

 

---

Data Storage

This section provides an overview of relational databases and the database schemas and tables that are required for recording Behavior Tracking events.

Relational Databases

Relational databases have both logical and physical structures. Logically you may define one or more databases. Each database may contain one or more tables and indexes, and each table may have multiple columns and rows. The logical structure of databases is quite similar between vendors. However, the physical structure of a database is very vendor-specific. Essentially, the physical structure defines areas on disk drives where the data is stored. Each database environment uses its own terminology and implementation for storing data at the operating system level. For example, Oracle uses the term tablespace and the Microsoft SQL Server uses the term filegroup.

When a database structure is defined by a database administrator, attention must be paid to the location of specific tables. Some tables are static in that they do not change much; some tables are dynamic in that many rows are being added and deleted; and some tables are read frequently and some rarely. Depending on their behavior, tables should be placed on different physical locations. Some of the most highly-used tables in Campaign Manager for WebLogic, WebLogic Commerce Server, and WebLogic Personalization Server are used for Behavior Tracking. The activity of a single customer moving around your site may generate multiple table entries. Therefore, it is recommended that you place these tables on the fastest drives in the computer. Experienced database administrators are aware of many techniques for monitoring and configuring a database installation for optimal performance. If you do not have a database administrator working with your installation and you have a lot of activity on your site, you should bring in a well-qualified database administer for regular maintenance of your system.

Database Directory Paths

The default database directory paths are:

• %WL_COMMERCE_HOME%\db\<db vendor>\<db version>\... (Windows)

• $WL_COMMERCE_HOME/db/<db vendor>/<db version>/... (UNIX)

where WL_COMMERCE_HOME is the directory in which you installed Campaign Manager for WebLogic, WebLogic Commerce Server, and/or WebLogic Personalization Server.

For example, if you are using Oracle 8.16 on UNIX, the location would be $WL_COMMERCE_HOME/db/oracle/8.16/....

BEA provides scripts to help set up the database schema needed for recording Behavior Tracking events, as well as the schema needed for recording data associated with Campaign Manager for WebLogic, WebLogic Commerce Server, and WebLogic Personalization Server. This data includes information from orders, catalogs, products, portals, and portlets.

For Oracle databases, the tablespaces created for Campaign Manager for WebLogic, WebLogic Commerce Server, and WebLogic Personalization Server data are the WLCS_DATA and WLCS_INDEX.

Note: WLCS_DATA and WLCS_INDEX are tablespace names created by BEA scripts. If you use a particular naming convention, you can rename them.

Behavior tracking uses a tablespace called WLCS_EVENT_DATA. This tablespace stores all Behavior Tracking tables, indexes, and constraints. Because of the potential for high volumes of data, this tablespace should be monitored closely.

Behavior Tracking Database Schema

Three tables are provided for the Behavior Tracking data. The EVENT table stores all event data. The EVENT_ACTION table logs actions used by third-party vendors against the recorded event data, and the EVENT_TYPE table references event types and categories in the EVENT table. Figure 4-1 shows a logical entity-relation diagram for the Behavior Tracking Database.

Figure 4-1 Entity-Relation Diagram for the Behavior Tracking Database

 
 

The EVENT Database Table

Table 4-1 describes the metadata for the EVENT table. This table stores all Behavior Tracking event data. It is an extremely active table.

Table 4-1 The EVENT Table Metadata

Column Name	Data Type	Description and Recommendations
EVENT_ID	NUMBER	A unique, system-generated number used as the record ID. This field is the table's primary key.
EVENT_TYPE	VARCHAR(30)	A string identifier that shows which event was fired.
EVENT_DATE	DATE	The date and time of the event.
WLS_SESSION_ID	NUMBER	A unique, WebLogic Server-generated number assigned to the session.
XML_DEFINITION	CLOB	An XML document that contains pertinent event information. It is stored as a CLOB (Character Large Object).
USER_ID	VARCHAR(50)	The user ID associated with the session and event. If the user has not logged in this column will be null.

 

As shown in Table 4-1, the EVENT table has six columns; each column corresponds to a specific event element. Five of the EVENT table's columns contain data common to every event type. The XML_DEFINITION column contains all information from these five columns plus event data that is unique to each event type. An XML document is created specifically for each event type. The data elements corresponding to each event type are captured in the XML_DEFINITION column of the EVENT table. These elements are listed in Table 4-2.

Table 4-2 XML_DEFINITION Data Elements

Event	Data Element
AddToCartEvent	event_date event_type session_id user_id sku quantity unit_list_price currency
BuyEvent	event_date event_type session_id user_id sku quantity unit_price currency application_name
CampaignUserActivityEvent	event_date event_type session_id user_id campaign_id
ClickCampaignEvent	event_date event_type session_id user_id document_type document_id campaign_id scenario_id application_name
ClickContentEvent	event_date event_type session_id user_id document_type
ClickProductEvent	event_date event_type session_id user_id document_type document_id sku category_id application_name
DisplayCampaignEvent	event_date event_type session_id user_id document_type document_id campaign_id scenario_id application_name
DisplayContentEvent	event_date event_type session_id user_id document_type document_id
DisplayProductEvent	event_date event_type session_id user_id document_type document_id sku category_id application_name
PurchaseCartEvent	session_id user_id event_date event_type total_price order_id currency application_name
RemoveFromCartEvent	event_date event_type session_id user_id sku quantity unit_price currency application_name
RuleEvent	event_date event_type session_id user_id ruleset_name rule_name
SessionBeginEvent	event_date event_type session_id user_id
SessionEndEvent	event_date event_type session_id user_id
SessionLoginEvent	event_date event_type session_id user_id
UserRegistrationEvent	event_date event_type session_id user_id

 

The EVENT_ACTION Database Table

Table 4-3 describes the metadata for the EVENT_ACTION table. This table logs actions used by third-party vendors against the recorded event data. It is a fairly static. It has two primary keys.

Table 4-3 EVENT_ACTION Table Metadata

Column Name	Data Type	Description and Recommendations
EVENT_ACTION	VARCHAR(30)	The event action taken such as BEGIN EXPORT or END EXPORT. This field is one of the table's primary keys.
EVENT_DATE	DATE	The date and time of the event. This field is one of the table's primary keys.
EVENT_ID	NUMBER	The ID of the event that corresponds with the event action taken.

 

The EVENT_TYPE Database Table

Table 4-4 describes the metadata for the EVENT_Type table. This table references event types and categories in the EVENT table. This table is static.

Table 4-4 EVENT_TYPE Table Metadata

Column Name	Data Type	Description and Recommendations
EVENT_TYPE	VARCHAR(30)	A unique, system-generated number used as the record ID. This field is the table's primary key.
EVENT_GROUP	VARCHAR(10)	The event category group associated with the event type.
DESCRIPTION	VARCHAR(50)	A description of the EVENT_TYPE.

 

Note: To record custom events, you must create an entry in this table. If a custom event does not have a record in this table, you cannot persist it to the EVENT table.

Constraints and Indexes

There is a single foreign key constraint between the EVENT_TYPE columns in the EVENT and EVENT_TYPE tables. As previously mentioned, if a custom event does not have a record in the EVENT_TYPE table, it cannot be persisted to the EVENT table.

Other than Primary Keys on each of the tables, there are only two indexes on the EVENT table. One index is on the EVENT.EVENT_DATE column and the other index is comprised of the EVENT.EVENT_TYPE and EVENT.EVENT_DATE columns.

 

---

Scripts

BEA provides scripts to create the Behavior Tracking database schema and tables for Oracle databases. This section provides information about the structures used in both a development and a production environment.

Development Environment Scenario

In a development environment, you may not want or need separate databases or tablespaces for recording Behavior Tracking events from the databases or tablespaces used for Campaign Manager for WebLogic, WebLogic Commerce Server, and WebLogic Personalization Server. Accordingly, you can include the Behavior Tracking database objects along side the database objects of these products. The easiest way to accomplish this is to execute the create_all script found in the event directory of your database installation.

Log into Oracle using SQL*Plus and execute the create_all.sql script in this location:

%WL_COMMERCE_HOME%/db/oracle/8.1.6/event/create_all.sql

where WL_COMMERCE_HOME is the directory in which you installed Campaign Manager for WebLogic, WebLogic Commerce Server, and/or WebLogic Personalization Server.

The create_all scripts in the event subdirectory executes the following scripts:

• drop_event.sql: Drops all the Behavior Tracking database objects.

• create_event.sql: Creates all the Behavior Tracking database objects.

• insert_event_type.sql: Populates the EVENT_TYPE table with base data.

Production Environment Scenario

This scenario is intended for use in an Oracle production environment where multiple tablespaces and their corresponding elements, such as tables and indexes, can reside in separate tablespaces and potentially on a different database server than Campaign Manager for WebLogic, WebLogic Commerce Server, or WebLogic Personalization Server database objects.

Before enabling the Behavior Tracking events, complete the following steps:

1. Identify the server and database used for recording Behavior Tracking events.

2. In the WL_COMMERCE_HOME/db/oracle/8.1.6/event directory where WL_COMMERCE_HOME is the directory in which you installed the Campaign Manager for WebLogic, WebLogic Commerce Server, and/or WebLogic Personalization Server:

   1. Edit the create_event_tablespaces.sql script to properly define the tablespace path and data filenames.

   2. Execute the create_event_tablespaces.sql to create the tablespaces.

   3. Edit the create_event_users.sql to ensure the correct user account will be created when this script is executed (the account name by default is WLCS_EVENT).

   4. Execute the create_event_users.sql.

3. Using SQL*Plus, connect as the user defined in create_event_users.sql and execute the script create_all.sql. This script will call drop_event.sql, create_event.sql, and insert_event_type.sql.

4. Change your JDBC connection pool information to point to this host, database instance, and user account. For more information, see Event Properties in the weblogiccommerce.properties File.

Description of Each Script

The Oracle scripts are described in the following list:

• WL_COMMERCE_HOME/db/oracle/8.1.6/event/create_all.sql

  Executes the following scripts: drop_event.sql, create_event.sql, and insert_event_type.sql.

• WL_COMMERCE_HOME/db/oracle/8.1.6/event/create_event.sql

  Creates the tables, indexes, and constraints associated with Behavior Tracking events.

• WL_COMMERCE_HOME/db/oracle/8.1.6/event/create_event_tablespaces.sql

  Creates tablespaces for storage of Behavior Tracking events information.

• WL_COMMERCE_HOME/db/oracle/8.1.6/event/create_event_users.sql

  Creates the WLCS_EVENT database user and grants the appropriate privileges for working with the Behavior Tracking event tables.

• WL_COMMERCE_HOME/db/oracle/8.1.6/event/drop_event.sql

  Drops the Behavior Tracking event tables.

• WL_COMMERCE_HOME/db/oracle/8.1.6/event/insert_event_type.sql

  Populates the EVENT_TYPE table with base data.