11.2 Case Study 1: "Our Most Valued Customers Come First"
In this section we describe the first case study.
11.2.1 Company Scenario
This company has a services unit where customers can report problems or file complaints by
Calling in, speaking to a customer service representative who fills out and submits a Web form
Filling out and submitting the form on the Web themselves
The type of information that is submitted is customer identification number, product ID, and severity of the problem (1=high to 4=low). Once the problem is submitted to the system, the customer relationship manager assigned to that customer ID is notified to respond to that customer.
Currently, the customer relationship managers respond to clients' problems on the basis of first come, first served. What the CEO of this services company wants is for the staff to act on the problems from their most important customers first.
What Does It Take to Solve This Problem?
What is stopping staff from responding to problems from their most important customers first? Each of the customer relationship managers covers a lot of individual clients. They need to be able to ask, "Is this customer with a high-severity problem one of my best customers?" so that they can respond to those clients first. To provide an answer to that question, the data on how valuable the customer is to the company has to be joined with the data about the problem, as shown in Table 11.1.
11.2.2 How This Business Problem Is Addressed
This section describes how the company solved this problem. The overall flow of the process is
The problem is submitted using the Web form.
The data are stored as an XML document.
The XML document is decomposed into relational data in the DB2 database where the customer history data is kept.
A trigger alerts the assigned customer relationship manager that a key client has submitted a high-severity problem.
The customer relationship manager responds to the client.
In this case, the DB2 XML Extender is used to decompose the problem report document into relational data.
Problem Reports: XML Documents
The client's problem is submitted as an XML document. This document contains this information:
Customer information (name, e-mail, phone number)
Product information (product name, version)
Problem information (severity, symptoms)
Listing 11.1 presents an XML document.
Listing 11.1 XML Problem Submission Document
<?xml version="1.0"?>
<!DOCTYPE Problem SYSTEM "c:\dxx\dtd\newproblem.dtd">
<Problem key="123">
<Customer>
<CustName>John Doe</CustName>
<Email>parts@doe.com</Email>
<Phone>4085552727</Phone>
</Customer>
<Product>
<ProdName>GrandPlan</ProdName>
<Version>3.5</Version>
</Product>
<SevCode>1</SevCode>
<Symptoms>broken, cannot install
</Symptoms>
</Problem>
The XML document in Listing 11.1 is decomposed into the DB2 table in Table 11.2. With the combination of DB2 and the XML Extender, decomposition is done using the XML Extender's dxxShredXML stored procedure.
Notice that the symptoms, which are in the XML document, are not decomposed into the table. The company chooses to do this because the symptoms are not used to determine whether the customer relationship representative responds quickly to the client who submitted this report. Therefore, they do not have to be stored in the relational database table.
The symptoms will be useful when the customer relationship manager has already been alerted and is investigating the situation. For this reason, the company makes the decision to retain the XML problem documents intact for future use. For example, the customer relationship manager might use it later to display the symptoms that are reported.
|
Column name |
Data type |
Length |
Description |
|---|---|---|---|
|
CUSTNAME |
VARCHAR |
10 |
Customer name |
|
|
VARCHAR |
20 |
Customer e-mail |
|
PHONE |
INTEGER |
Customer phone number |
|
|
PRODUCT |
VARCHAR |
8 |
Product name |
|
VERSION |
DECIMAL |
3,1 |
Version of product with reported problem |
|
SEVCODE |
SMALLINT |
Severity of reported problem |
Order Data: Relational Data Stored in DB2
The importance of the client is judged by how much the client has spent on the company's products. In this company, customer order data has traditionally been stored in tables in the DB2 database. Some of the columns of their existing ORDERS table are listed in Table 11.3.
|
Column name |
Data type |
Length |
Description |
|---|---|---|---|
|
INVCID |
INTEGER |
Invoice ID |
|
|
CUSTID |
INTEGER |
Customer ID |
|
|
CUSTNAME |
VARCHAR |
10 |
Customer name |
|
PRODID |
INTEGER |
Product ID |
|
|
PRODUCT |
VARCHAR |
8 |
Product name |
|
TOTCOST |
DECIMAL |
6,2 |
Total cost for this order |
|
REPID |
INTEGER |
ID for the customer relationship representative for this sale |
Triggered Actions
When a row that has a SEVCODE = 1 is inserted into the PROBLEMS table, the SEV_PROBLEMS trigger is invoked and inserts a row into a table named SEVERE_ PROBLEMS. Listing 11.2 shows how this can be done.
Listing 11.2 Create Trigger
CREATE TRIGGER SEV_PROBLEMS
AFTER INSERT ON PROBLEMS
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.SEVCODE = 1)
BEGIN ATOMIC
INSERT INTO SEVERE_PROBLEMS
VALUES (N.CUSTNAME, N.PRODUCT,(SELECT REPID FROM REPS
WHERE (REPS.CUSTNAME = N.CUSTNAME)),
(SELECT SUM(ORDERS.TOTCOST) FROM ORDERS
WHERE (ORDERS.CUSTNAME = N.CUSTNAME)));
END
The REPS table in the database stores data about the customer relationship representatives: their IDs, names, location, and so on. As shown in Table 11.4, the trigger uses that table to associate the customer that reported the problem with the ID of the representative who handles their relationship with the company.
When a row is inserted into SEVERE_PROBLEMS (Table 11.4), a trigger on it is invoked that sends a page to the customer relationship representative if the TOTPRICE exceeds a particular amount to alert the customer relationship representative. The customer representative can then immediately respond to the important customer.
|
Column name |
Data type |
Length |
Description |
|---|---|---|---|
|
CUSTNAME |
VARCHAR |
10 |
Customer name |
|
PRODUCT |
VARCHAR |
8 |
Product name |
|
REPID |
INTEGER |
ID for the customer relationship representative for this sale |
|
|
TOTSPENT |
DECIMAL |
7.2 |
Total amount of money that this customer has spent with the company. This is the sum of all TOTCOST for a particular CUSTID |
11.2.3 Future Extensions
After solving the immediate problem and improving their response time to address the complaints from their most valued customers, the company has plans to expand this system into the following areas.
Immediate Response to the Customer
A trigger can be added to SEVERE_PROBLEMS so that when a row is inserted, an e-mail is sent to the customer that informs the customer that the assigned customer relationship manager has received their message and will be responding immediately. Using the XML composition functions, an XML document would be composed from the data in PROBLEMS for that customer, including the name of the customer relationship manager assigned to this case, and sent off to the customer's e-mail address.
More Accurate Marketing Strategies
From the data stored from the XML problem reports, the company's marketing department can easily see what customers have opened a number of problem reports about which products. If the marketing department is about to do a promotion about a product that a customer has complained about, the company can avoid further antagonizing this customer by automatically removing the customer's name from the list of people who would get that mailing.
Better Business Decisions for Future Product Lines
DB2 can be used to search XML documents stored intact external to DB2. If the XML problem report documents are stored intact outside the database, the <SYMPTOMS> element can be searched to find out if any patterns of symptoms are reported. That information can be used to influence plans for future models and product lines.
A product inventory table already exists in their DB2 database to track inventory. Data from the INVENTORY table can be joined with the PROBLEMS table to see which product is receiving the most reported problems.
| Top |
