This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

MIND


This article assumes you're familiar with ASP
Download the code (4KB)
Build E-commerce Sites that Predict Your Users' Preferences
Alan Saldanha

Intelligent cross-selling is one feature that makes a big difference in a full-featured e-commerce site. Site Server Commerce Edition provides tools that give your e-commerce store this professional feature.
How many times have you walked into a bookstore, found the book you'd wanted, then found an even better one a couple of shelves away? Often you end up buying both books, right? Another example of this cross-selling strategy is when a supermarket puts shortcake on their shelves next to the strawberries. Several very successful e-commerce sites have incorporated this cross-selling phenomena into their online selling presence. Now you can, too, using Microsoft® Site Server 3.0 Commerce Edition.

    In this article I'll show you how to add this type of promotion, intelligent cross-selling, to your online store. An intelligent cross-sell promotion suggests other items that a customer may want to purchase based on topics that the shopper has already expressed interest in. a component is used to correlate items that the customer has ordered with a database of items that other customers with similar interests have ordered previously.

    The Microsoft Press® sample site that comes with Site Server 3.0 Commerce Edition uses this technique on the page where book information is displayed; it suggests other items that a customer may have interest in buying. For example, when the information about a Visual C++® book is viewed, other books bought by other customers who bought the Visual C++ book are displayed.

    The intelligent cross-sell promotion is usually applied at two ASP pages: product.asp, where information about the product is displayed, and basket.asp, where the list of items added to the basket is displayed.

The Predictor Component

    Intelligent cross-sell promotions are implemented using the Predictor object. The object, which comes with Site Server 3.0 Commerce Edition, examines the customer order database to find orders that are similar to the current customer's order, and generates a list of recommended products. The object also ensures that none of the products already in the current customer's order are suggested.

    The Predictor object makes its predictions based on the site purchase history of other shoppers and the customer interest list of the current shopper—that is, the current product item that the shopper is viewing or has displayed interest in through an action (like adding the item to the user's shopping basket).

    Before going into the details of the Predictor object and how it is used to make predictions, four concepts need to be introduced: product family, stock-keeping unit (SKU), Shopper ID, and SQLItemADO.

    A product family is either a single item or a group of closely related items, for instance a T-shirt that comes in different sizes and colors. Microsoft Windows NT® 4.0 is another example of a product family. Products within a product family have attributes that further define them. In the case of the T-shirt it could be the color and the size, while with Windows NT 4.0 it may be the number of client licenses in the box or the target hardware platform.

    A product variant within a product family is a particular combination of attributes that are available for a certain product. a T-shirt could be medium-sized and blue. Ten licenses on Intel hardware or ten licenses on Alpha hardware are variants within the Windows NT product family. Each variant is given a unique SKU. So a medium blue T-shirt may be given the SKU 001-002, while a medium green T-shirt is given the SKU 001-003. Windows NT Workstation 4.0 SP4 currently has the SKU 236-01563.

    Site Server 3.0 Commerce Edition includes a component called the Site Builder Wizard. This wizard creates a database schema based on a designer's input. In the default database, the SKU is stored in the field sku, and the product family in pf_id.

    To maintain information about shoppers across multiple sessions or visits to a site, the Shopper ID is used to identify the shopper. This unique ID is initially generated when the shopper first registers at the site using the StandardSManager object. It's a 32-character string generated from a random component and a globally unique identifier. More information about the StandardSManager object is provided in the online documentation that's included with Site Server Commerce Edition.

Figure 1: SQL Item ADO Properties
      Figure 1: SQL Item ADO Properties

    SQLItemADO is a pipeline component that can run a query for each item in a shopping basket. As shown in Figure 1, SQLItemADO needs a connection string to connect to a database (if left blank, the default connection string is used), and a query string, which specifies the SQL query or metaquery to use (usually defined in the InitQuery function in global.asa). The Parameter List specifies the values for the query. This data can come from the OrderForm object that the component will use in the query specified in the Query field of the wizard.

    For example, if you wanted to enter the order ID and quantity order for each item, you would enter the following in the Query textbox, where tablename would be the table into which you wanted to insert the information: 

insert into tablename (order_id, quantity) values (?,?)
In the Parameter List field you would enter the following for the order ID and the quantity for each item. 
order.order_id item.quantity
      Other valid elements of the order and item can be accessed by using order.name and item.name, respectively, with the name property being any valid name/value pair in the OrderForm object. You can also search "Using the OrderForm Object" in the Site Server Commerce Edition documentation to see the object's schema. Figure 1 shows the settings for an SQLItemADO component that inserts item information into the receipt table.

    To add the SQLItemADO component to a pipeline configuration file (for example, plan.pcf or purchase.pcf in the config directory of your store) you can start the Pipeline Editor by selecting Start | Programs | Microsoft Site Server | Commerce | Pipeline Editor, or through the Web interface that Site Server provides for site management.

Implementing Intelligent Cross-sell Promotions

    Now let's explore how intelligent cross-sell promotions can be implemented with a schema that uses an SKU to identify a product, then look at a case where the SKU is not available. The latter would be applicable to a site that is developed using the Site Builder Wizard.

    Here are some features the implementation must include:

  • Creating a database table that will act as the knowledge base and will store the site history.
  • Adding information to the site purchase history (information about all previous purchases at the site) each time a user makes a purchase.
  • Initializing the Predictor object with the site purchase history and creating a knowledge base of the site purchase history.
  • Creating from the shopper's interest list a list of items that the current customer has already ordered or expressed interest in.
  • Adding scripting to suggest correlating items based on the site purchase history and the shopper's interest list.

Site Purchase History

    The site purchase history contains information about all previous purchases at the site. It serves as the knowledge base of the Predictor component. It must contain, at minimum, the Shopper ID, a string that uniquely identifies the product (SKU, pf_id, or some other field associated with the product), and the quantity purchased.

    The first step in setting up the history is to create the table that aggregates the site purchase history. This can be done by issuing the following statement against the SQL Server™ database of your store: 

Create table YourTableName(
     Shopper_id VARCHAR(32),
     Sku VARCHAR(30),
     Quantity int,
     Purchased datetime
 )
 go
The table name (YourTableName) is usually of the format sitename_predictor_data, where sitename is the name of your store or site.

    The pipeline component SQLItemADO is used to add information to the site purchase history table that was created previously. You will need to add the following line in the InitQueryMap function that is located in global.asa to create a new metaquery that will be referenced by the SQLItemADO component: 

Set MSCSQueryMap.insert_predictor_data=AddQuery(
     "insert into sitename_predictor_data values (?, ?, ?, {fn Now()})")
a metaquery has the syntax of a valid query, without all the actual values plugged in. In this case, the question marks will be replaced by specific values before the query is executed. For insert_predictor_data, the three question marks will be substituted by the values specified by the name/value pairs in the Parameters List field before being executed.

    The actual point at which the information is added to the knowledge base will depend upon the design of your application and how you use the Predictor object. If you are using it in an online store to make recommendations based on the purchases made by other shoppers, the information would be added to the knowledge base when a shopper makes their actual purchase.

    To make recommendations or predictions based on a user's choices, you must create a site purchase history knowledge base. This is done using the InitPredictor method of the Predictor object, which is usually called in the Application_OnStart event of global.asa. It is then assigned to the Application object so that the same information can be reused for predictions within various pages without having to reload the knowledge base each time.

    The parameters that need to be passed into the InitPredictor method include the table name in the database that contains the site purchase history, the column name for the Shopper ID, the product that was ordered (usually the SKU), the quantity purchased, and the maximum amount of memory to allocate to the knowledge base. (The online documentation provides suggested memory settings.)

    The code that accomplishes these tasks in the Application_OnStart function of global.asa looks like this: 

Dim MSCSPredictor
 REM — Create the predictive cross selling engine
 Set MSCSPredictor = Server.CreateObject("Commerce.Predictor")
 call MSCSPredictor.InitPredictor(MSCSSite.DefaultConnectionString, _
     "sitename_predictor_data", _
     "shopper_id", _
     "sku", _
     "quantity", _
     2000)
Once the object is initialized, it is assigned to the Application object. 
Set Application("MSCSPredictor") = MSCSPredictor

The Shopper's Interest List

    The shopper's interest list usually consists of the current product being viewed and the items in their shopping basket. For variety, you can also display the shopper's past purchases. The interest list is represented in a Site Server SimpleList object, with each item consisting of a Dictionary object with name/value pairs corresponding to the SKU of the item and quantity. The following code illustrates how the SimpleList and Dictionary objects are created and how the name/value pair for the current item is added to the SimpleList object:

 
 set interestlist = Server.CreateObject("Commerce.SimpleList")
 set thisitem = Server.CreateObject("Commerce.Dictionary")

 thisitem.sku = CStr(sku)
 thisitem.quantity = 100

 interestlist.add(thisitem)
      In order to get the items in the shopping basket, you will have to include the ASP file i_util.asp, which contains the functions UtilGetOrderFormStorage and UtilGetOrderForm storage. The following code iterates through the contents of the shopper's basket and adds an item to the SimpleList, which is itself a Dictionary object, for each: 
set orderformstorage = UtilGetOrderFormStorage()
 set orderform = UtilGetOrderForm(orderformstorage, created)
 set orderitem = orderform.items

for i = 0 to orderitem.Count - 1
     set thisitem = Server.CreateObject("Commerce.Dictionary")
     thisitem.quantity = orderitem(i).quantity
     thisitem.sku = orderitem(i).sku
     interestlist.add(thisitem)
 next

Making Predictions

    To suggest products to the shopper, you can use the following code for the Predictor, which has been initialized to the knowledge base I created previously. 

<% REM Display a list of predictive data 
     predictor = NULL
     on error resume next
The initialized Predictor with the knowledge base comes from the Application object. 
set predictor = Application("MSCSPredictor")
 on error goto 0
If the Predictor that's returned from the Application object has been initialized with a knowledge base, you can proceed to create the shopper's interest list. 
if Not IsNull(predictor) and Not IsEmpty(predictor) then
      As I mentioned earlier, the shopper's interest list consists of the product currently being viewed and items in the shopping basket. a SimpleList object is used to create the shopper's interest list. 
set interestlist = Server.CreateObject("Commerce.SimpleList")
 set thisitem = Server.CreateObject("Commerce.Dictionary")
 
 thisitem.sku = CStr(sku)
 thisitem.quantity = 100
 
 interestlist.add(thisitem)
 
 set orderformstorage = UtilGetOrderFormStorage()
 set orderform = UtilGetOrderForm(orderformstorage, created)
 set orderitem = orderform.items
 
 for i = 0 to orderitem.Count - 1
     set thisitem = Server.CreateObject("Commerce.Dictionary")
     thisitem.quantity = orderitem(i).quantity
     thisitem.sku = orderitem(i).sku
     interestlist.add(thisitem)
 next
      Now that I've initialized the Predictor to use the knowledge base and have the current shopper's interest list, it's time to get the predictions by using the aptly named GetPredictions method. The online documentation explains the parameters that are passed into the GetPredictions method. They include the interest list of the shopper, the maximum number of predictions, a popular product filter (expressed as a decimal value between 0 and 1), and the minimum number of similar customers that needs to be matched. These are passed in as parameters as follows: 
set predictions = predictor.GetPredictions(interestlist, 6, 0.3, 2)
If the GetPredictions method returns a list of products, the products that other customers purchased who also bought the current product are displayed (see Figure 2).

Adding Predictions to the Product Display Page

    So far I've assumed that your products are defined by an SKU. So what happens when you have a table schema that does not include a SKU field, but uses some other field name, say pf_id, to identify the product to be displayed in the product page? (The product page, product.asp, is shown in Figure 3.) In such a case, you should follow the basic procedures outlined previously, but replace the references made to SKU with pf_id. This makes changes to the product display page generated by the Site Builder Wizard, and adds the functionality of the Predictor object.

    The knowledge base table would have to include this additional field. If the additional field is named pf_id, the table schema would look like the following: 

Create table YourTableName(
     shopper_id VARCHAR(32),
     sku VARCHAR(30),
     quantity int,
     purchased datetime,
     pf_id VARCHAR(30)
 
 )
 go
The initialization of the Predictor object is made as follows, using pf_id instead of SKU: 
Call MSCSPredictor.InitPredictor(MSCSSite.DefaultConnectionString, _
     "sitename_predictor_data", _
     "shopper_id", _
     "pf_id", _
     "quantity", _
     2000)
      Since an additional value (pf_id) is being added to the site purchase history, the metaquery used by the SQLItemADO object needs to be changed to 
Set MSCSQueryMap.insert_predictor_data=AddQuery
 ("insert into sitename_predictor_data values 
 (?, ?, ?, {fn Now()},?)")
and the Parameter List of the SQLItemADO object needs to be modified. The parameter order.pf_id is added to the Parameter List, as shown in Figure 4.

    In most cases you will need to include the i_util.asp at the top of product.asp so that the Util* functions can be used while creating the interest list.

Figure 4: Adding Product Identification Data
      Figure 4: Adding Product Identification Data

       If a product on a site is identified by the pf_id field, code similar to that shown in Figure 5 would be responsible for making the predictions. When I made these changes on a site that was developed using the Site Builder Wizard, the result looked like Figure 6.
Figure 6: Site with Predictions Added
      Figure 6: Site with Predictions Added

Odds and Ends

      Since the knowledge base used by the Predictor object usually is used in the Application_OnStart function in global.asa, you will need to include logic in your application so that it is refreshed periodically. You can do this by reloading the application or by calling the InitPredictions method to reinitialize the knowledge base. In addition, since the Predictor object does not allow a data-based creation of the knowledge base, entries in the site purchase history will need to be purged on a regular basis to keep the information fresh.

    In using the Predictor object's functionality, you may find it frustrating that the GetPredictions method does not seem to return any predictions, even though there are entries in sitename_predictor_table. This is usually because the number of unique shopper shopping baskets in the knowledge base table sitename_predictor_data is less than the minimum number of similar customers needed, which is specified in the last parameter of the GetPredictions method. One way around that is to reduce the value specified. Better yet, begin the site purchase history with some pseudo data, like the Microsoft Press sample site. In that site, a SQL script (Config/SQL/SQLSvr/mp_predictor_data.sql) is used to load the dummy site purchase history.

Conclusion

    The Predictor object is an excellent way to increase awareness of, and interest in, products that a shopper may not have found by themselves. The same idea can be applied to all sorts of applications. For example you can use the Predictor object in an online career site to provide a list of jobs that other applicants applied for when a user is viewing a certain job posting. Any way you choose to use it, the Predictor object can quickly transport your e-commerce site to the next level of customer satisfaction.

MSDN
http://msdn.microsoft.com/msdn-online/workshop/server/commerce/promotions101.asp
and
http://www.microsoft.com/Mind/0199/servercommerce/servercommerce.htm

From the June 1999 issue of Microsoft Internet Developer.