11.2 Case Study 1: "Our Most Valued Customers Come First"

  Previous section   Next section

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

  1. The problem is submitted using the Web form.

  2. The data are stored as an XML document.

  3. The XML document is decomposed into relational data in the DB2 database where the customer history data is kept.

  4. A trigger alerts the assigned customer relationship manager that a key client has submitted a high-severity problem.

  5. 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.

Table 11.2. PROBLEMS Table

Column name

Data type

Length

Description

CUSTNAME

VARCHAR

10

Customer name

EMAIL

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.

Table 11.3. ORDERS Table

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.

Table 11.4. SEVERE_PROBLEMS Table

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

Part IV: Applications of XML
 
ASPTreeView.com
 
Evaluation has СБСґјјexpired.
Info...