This chapter is from the book
This chapter is from the book
This chapter is from the book
Data Model
Most of the data model for e-commerce is similar to the standard retail data model. The data that we need for product recommendations is simply a Sales fact that shows products that are sold over time. The interesting new areas in e-commerce are the facts that allow us to understand the behavior of visitors to the Web site.
Many BI solutions for Internet applications focus on how the site is used. Information such as the order that people visit pages, which page they start at, what Web site they came from—all of this can help companies to improve the effectiveness of their Web sites. Tracking information such as "click-through" rates, which measure how often users click an advertisement, can produce more optimized campaigns and a better experience for users.
However, for this solution, we focus on the other side of the Web equation: Who are the people visiting our site? To really be able to understand customers well enough to produce rich customer profiles, we need to keep track of the visits that users make to our site.
How Often Are Users Visiting the Web Site?
The new fact table in the data warehouse is Visit, which has one record for each completed customer visit to the site, as shown in Figure 10-2. So, if a customer signed on at 3:10 p.m. and then clicked through several pages with the last page hit logged at 3:25 p.m., the e-commerce application's log parser will see the last page hit and create a single record that spans the whole time period.
)
Figure 10-2 Visit fact
The measures that we will be tracking are the duration of the visit and the number of requests (or page hits) during the visit. Because we are interested in the date that the visit took place as well as the time, we will use the approach explained in earlier chapters and have separate Date and Time of Day dimensions. We can also include the Referrer Domain that the user came from which helps us to determine which Web sites are sending the most traffic to our site, and the type of browser platform that the customer was using, including browser version and operating system. This dimension is often called User Agent rather than Browser Platform because other software such as search spiders can also visit the site; however, we always use business-friendly names in the data warehouse rather than terms such as User Agent, which probably only makes sense to Web geeks.
If your log parser supports it, one useful dimension that we can add is Visit Result, which has values such as Browsed, Abandoned Transaction, and Completed Transaction. This is somewhat difficult for parsers to derive from the Web logs, however, because they would need to look for specific marker pages in the log, such as a confirmation page when the user completes a transaction.
The e-commerce application's database also includes another table with the actual page hits that took place, so in some ways it seems we are breaking one of the cardinal rules of dimensional modeling—always use the most detailed grain available. By using the summarized Visit table, we are losing the ability to analyze by a Page dimension, which shows which pages the user hit. Although powerful, the Page Hits fact table will inevitably be huge, and we would need a good business case to go to the trouble of managing this volume of data. Also, the kinds of analysis that Page Hits would enable are often already provided directly by e-commerce applications, and in this case don't need to be augmented with the extra information stored in the data warehouse.
One question that arises is whether we can tie the Visit fact back to the Sales Transaction fact. If we could do that, maybe we could show information such as how profitable visits were. It turns out that for SQL Server 2005, it doesn't really matter if you have the information on the same fact record. Because a single cube can contain measure groups for both Sales and Visits, if a user has selected a date range and a customer or grouping of customers, measures such as total visits and total revenue display properly anyway.
In other words, the common dimensions between the two fact tables provide the means to tie the information together, and we don't actually need to link the two fact records. The site activity is tied to the sales transactions by way of the fact that they occurred in the same time interval to the same customer.
Who Is Using the Web Site?
The most important dimension is certainly Customer, but data warehouse architects face an interesting challenge when it comes to the Web—we often don't know who the customers are. Most e-commerce sites require users to create an online profile, and if the customer must sign in before placing any orders, we can usually associate the Web site activity after the user signs in with the customer's profile. However, online profiles usually contain little mandatory information (and as many online retailers will testify, the information they contain is often meaningless).
The goal for effective BI from the Web site visit information is to augment the minimal Internet profile information with rich, accurate demographic information. In our solution, the site profile includes an optional loyalty card number that is issued by the physical stores. Because customers build up credits to earn free CDs, this information is well populated and can be used to connect the online behavior from the Web site's customer profile with the data warehouse Customer dimension. (In case you have ever wondered why stores are so keen to hand out loyalty cards, now you know—they are trying to build a good Customer dimension!)
For customers who don't have a loyalty card number and an existing Customer record in the data warehouse, we have two choices: Either we can create new Customer records for each of the unmatched customer profiles with minimal information or we can use a single "Unknown Internet Customer" record that groups all these customer together. Because even the minimal online profile will allow us to track information such as how long they have been using our Web site, we will still be able to achieve some of our segmentation goals such as identifying frequent visitors, and so we will go ahead and create new Customer records for every distinct online profile that doesn't have a loyalty card.
Note that the CustomerKey will be blank for all visits where the user did not sign on but just browsed the site. If the user actually places an order, he must sign on and therefore there will be a customer key, but we will allocate all the other facts to an "Unknown Customer" record. It is important not to just discard these fact records, because even without the customer information, the Visit fact table is a valuable source of information about peak traffic levels on the site.
Alternatively, we could have solved the problem by modeling a separate "Internet Customer" dimension that is only used for this area and not related to other facts such as Sales, and thus avoid creating extra records in our Customer dimension. However, this would mean that we couldn't create a cube with a common Customer dimension that combines measure groups for Internet visit measures with sales and other facts for the business, which is really the central goal of this solution.
What Interesting Attributes Can We Track?
The first attribute we can add to the data warehouse Customer dimension is one of the easiest but most interesting: an InternetUser flag that indicates whether the customer has created a profile on the site, as shown in Figure 10-3. This is easy to populate and will enable analysts to start to understand the characteristics of people who use the Internet channel versus those who don't.
)
Figure 10-3 Customer dimension
A related InternetPurchaser attribute can be derived by looking at the Sales transactions and flagging every customer who has made any purchases using the Internet channel. All InternetPurchasers will, of course, be InternetUsers, but the reverse is not true because some users will have created a profile but not yet made a purchase online. Although analysts could easily and flexibly get a list of customers who had purchased through the Internet by browsing the Sales cube and selecting the Internet channel and a time period, it is still a good idea to add the InternetPurchaser flag so that it is easy for both analysts and data mining models to distinguish those customers who have ever used the Internet channel from those who haven't.
Other interesting customer attributes are DateFirstInternetVisit, which tells us how long they have been using our site, and DateLatestInternetVisit, which tells us how recently they have visited. Both of these attributes are derived from the underlying Visit fact table, but will be added to the Customer table to make the dimension easy to query. Note that this means we will be updating our customer records much more often, so one way of simplifying the ETL process would be to create a view over Customer and the Visit fact table that returns the maximum date for each customer and is used as the source for the Analysis Services Customer dimension. We can also add equivalent date columns for the date of the first actual online purchase, and the most recent online purchase.