Appendix J –Business Intelligence Systems
Page J-32
To test this procedure use:
Execute Calculate_R;
SELECT * FROM CUSTOMER_R;
Appendix J –Business Intelligence Systems
/***************** PROCEDURE Calculate_F **********************/
CREATE PROCEDURE Calculate_F
AS
/* Compute F_Score */
UPDATE CUSTOMER_F
SET F_Score = 2
WHERE CustomerID IN
(Select Top 25 PERCENT CustomerID
FROM CUSTOMER_F
WHERE F_Score IS NULL
ORDER BY OrderCount DESC);
UPDATE CUSTOMER_F
SET F_Score = 3
WHERE CustomerID IN
(Select Top 33 PERCENT CustomerID
FROM CUSTOMER_F
WHERE F_Score IS NULL
ORDER BY OrderCount DESC);
Appendix J –Business Intelligence Systems
Page J-34
To test this procedure use:
Execute Calculate_F;
SELECT * FROM CUSTOMER_F;
IMPORTANT NOTE: Although the procedure works correctly, the data set is far too small for
the results to be meaningful. Note that CustomerIDs 3, 4, 6, 7, 8, 9 and 10 all have the same
OrderCount of 1, but have four different F scores!
Appendix J –Business Intelligence Systems
Page J-35
/***************** PROCEDURE Calculate_M **********************/
CREATE PROCEDURE Calculate_M
AS
/* Compute M_Score */
UPDATE CUSTOMER_M
SET M_Score = 2
WHERE CustomerID IN
(SELECT TOP 25 PERCENT CustomerID
FROM CUSTOMER_M
WHERE M_Score IS NULL
ORDER BY AverageOrderAmount DESC);
UPDATE CUSTOMER_M
SET M_Score = 3
WHERE CustomerID IN
(SELECT TOP 33 PERCENT CustomerID
FROM CUSTOMER_M
WHERE M_Score IS NULL
ORDER BY AverageOrderAmount DESC);
Appendix J –Business Intelligence Systems
Page J-36
To test this procedure use:
Execute Calculate_M;
SELECT * FROM CUSTOMER_M;
Appendix J –Business Intelligence Systems
Page J-37
/***************** PROCEDURE RFM_Analysis **********************/
CREATE PROCEDURE RFM_Analysis
AS
/* *** Dipslay Results *************************************/
SELECT R_Score, Count(*)AS R_Count
FROM CUSTOMER_R
GROUP BY R_Score;
SELECT F_Score, Count(*)AS F_Count
FROM CUSTOMER_F
GROUP BY F_Score;
SELECT M_Score, Count(*) AS M_Count
FROM CUSTOMER_M
GROUP BY M_Score;
UPDATE CUSTOMER_RFM
SET F =
(SELECT F_Score
FROM CUSTOMER_F
WHERE CUSTOMER_RFM.CustomerID = CUSTOMER_F.CustomerID);
Appendix J –Business Intelligence Systems
To test this procedure use:
EXECUTE RFM_Analysis;
SELECT R, F, M, COUNT(*) AS NumberOfCustomers
FROM CUSTOMER_RFM
GROUP BY R, F, M
ORDER BY NumberOfCustomers DESC;
IMPORTANT NOTE: Although the procedure works correctly, the data set is far too small for
the results to be meaningful. See the comment on the F scores above!
Appendix J –Business Intelligence Systems
Page J-39
C. Show SQL to process the table generated in your answer to B to display the names and
e-mail data for all customers having an RFM score of {5 1 1} or {4 1 1}.
The SQL code is:
SELECT CRFM.CustomerID, LastName, FirstName, EmailAddress, R, F, M
FROM CUSTOMER AS C JOIN CUSTOMER_RFM AS CRFM
ON C.CustomerID = CRFM.CustomerID
We can, of course, pick the criteria to match the customers, as shown in this screen shot:
Appendix J –Business Intelligence Systems
Page J-40
D. Describe, in general terms, how a market basket analysis can be useful to The Queen
Anne Curiosity Shop.
A market basket analysis is a technique for determining patterns of consumer behavior that shows
products or services that consumers tend to purchase at the same time. This is true of The Queen
E. Suppose that the Queen Anne Curiosity Shop wants to be able to predict which sales
are likely to be paid on time. Payments are due on the SaleDate listed in the SALE table.
As part of this effort, they will add a DatePaid column to the SALE table. Describe, in
general terms, how a decision tree, in conjunction with this new data, might help the
Queen Anne Curiosity Shop to predict which sales will be paid on time.
After they have enough data from people who have both paid on time and have not paid on time,
they could employ data mining decision tree software to build a decision tree that will predict
Appendix J –Business Intelligence Systems
Page J-41
❖ ANSWERS TO MORGAN IMPORTING PROJECT QUESTIONS
Note that since the Morgan Importing database that we have created is intended to track
purchases and shipping rather than customer purchases, neither RFM nor market basket
analysis can be applied to customer data.
A. Describe how an RFM analysis could be useful to Morgan Importing. What part of the
business would you apply RFM analysis to?
While Morgan Importing does have a retail sales component, we have been dealing only with the
procurement part of the business. If we had the data, we could apply an RFM analysis to the retail
sales part of Morgan Importing. For Morgan Importing sales, an RFM analysis would work just
as it does for any other retail operation. By ranking customers on the RFM scale, we would know
how to focus our sales and marketing efforts.
(1) Who has been a good customer in the past and should be contacted to restore
their use of our service, and
(2) Who is a lower value customer and needs to be encouraged to use more services.
B. Describe in general terms how a market basket analysis could be useful to Morgan
Importing. What part of the business would you apply market basket analysis to?
While Morgan Importing does have a retail sales component, we have been dealing only with the
Appendix J –Business Intelligence Systems
Page J-42
C. Morgan Importing would like to be able to characterize certain shipments as high-risk
based on prior experience. Describe, in general terms, how a decision tree might help
them to do this.
After they have enough data from shipments have both arrived undamaged and have not arrived
undamaged, they could employ data mining decision tree software to build a decision tree that