Transaction Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Last Name Enriquez Carder Angelo Bibb Rodman Matchtolff Macnai Mensik D'Amico Brunhoeber Lou Goertzen Darr Estrada Thompson Westvang Wakefield Lester Montague McMath Reyes Tickner Timanus Yates Yereshenko Wilson Stone Roselius Deyoe Rhames Roach Fees Hendrix Bolyard Freeman Langston Henning Rhames Nigel Roach First Name Antonio Vicky Donatica Robin Johnny Allean Leigh Ann Ronald Donald Robert Chia-Yi Keith Kevin Terrance Samuel Harry Lorraine James Carmen Olivia Rayna Rex Deona Gayle Zee Jay David Reginald Rayundo Sherman Marcie Brenda Becky Pat Jerry Kim Patricia Sherman Emmett Marcie Phone (972)380-2794 (972)380-2796 (972)380-5597 (972)380-4789 (972)380-2792 (972)380-5595 (972)380-5710 (972)380-3201 (972)380-2795 (972)380-5711 (972)380-3202 (972)380-2793 (972)380-5596 (972)380-4776 (972)380-4788 (972)380-4777 (972)380-5712 (972)380-3203 (972)380-5595 (972)380-5710 (972)380-5597 (972)380-5598 (972)380-5599 (972)380-5713 (972)380-3204 (972)380-3201 (972)380-4788 (972)380-4777 (972)380-2793 (972)380-4789 (972)380-2792 (972)380-3734 (972)380-1333 (972)380-5596 (972)380-5711 (972)380-5594 (972)380-3202 (972)380-4789 (972)380-4776 (972)380-2792 Garden Size Large Small Small Small Large Small Small Small Large Small Large Large Small Medium Medium Medium Small Medium Small Small Medium Medium Large Large Large Small Small Small Large Small Medium Large Medium Small Large Small Medium Small Small Medium Quantity Sold 2 1 7 2 2 2 1 1 5 3 5 2 3 5 3 2 2 2 2 4 5 1 7 9 9 2 4 4 5 3 1 2 2 5 4 1 8 2 5 12 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 Hughes Matchtolff Macnai Porterfield Hauch Ruaz Mensik Reyes Carder Gauger Angelo Mallouf Yereshenko Wakefield Fiddes Lester Mazza Tickner Bourque Rhames Lessert Roach Pomales Matchtolff Crouse Macnai Hornbeek Mensik Blake Thompson Watts Westvang Walker Goertzen Monac Darr Lax Brunhoeber Dubben Tickner Stanton Rhames Hulsman Frank Allean Leigh Ann Jimmy Joann Monica Ronald Rayna Vicky Samone Donatica Pedro Zee Lorraine Rick James Pamela Rex Elizabeth Sherman Linda Marcie Oseas Allean Aleta Leigh Ann Kent Ronald Barney Samuel Kelly Harry Donny Keith Levitica Kevin Rafael Robert Pamela Rex Catrina Sherman Russell (972)380-2794 (972)380-5595 (972)380-5710 (972)380-2795 (972)380-2796 (972)380-2792 (972)380-3201 (972)380-5597 (972)380-2796 (972)380-5712 (972)380-5597 (972)380-3203 (972)380-3204 (972)380-5712 (972)380-5598 (972)380-3203 (972)380-5599 (972)380-5598 (972)380-5713 (972)380-4789 (972)380-3204 (972)380-2792 (972)380-3734 (972)380-5595 (972)380-1333 (972)380-5710 (972)380-5594 (972)380-3201 (972)380-4789 (972)380-4788 (972)380-2792 (972)380-4777 (972)380-5595 (972)380-2793 (972)380-5710 (972)380-5596 (972)380-3201 (972)380-5711 (972)380-2796 (972)380-5598 (972)380-5597 (972)380-4789 (972)380-5712 Large Small Small Medium Small Small Small Medium Small Small Small Small Large Small Large Medium Small Medium Small Small Medium Medium Small Small Medium Small Medium Small Medium Medium Small Medium Small Large Small Small Small Small Small Medium Small Small Large 4 2 12 8 2 4 5 8 12 6 2 4 4 2 8 12 5 12 8 3 8 1 8 2 6 1 8 1 8 3 8 6 8 6 8 3 8 3 8 12 8 3 6 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 Roach Schoemann Rhames Vandrey Roach Jwang Matchtolff Ruaz Macnai Harpole Mensik Craddock Estrada Hendrix Langston Marcie Dale Sherman Yvonne Marcie Buyung Allean Monica Leigh Ann Frank Ronald David Terrance Becky Kim (972)380-2792 (972)380-3203 (972)380-4789 (972)380-5598 (972)380-2792 (972)380-4789 (972)380-5595 (972)380-2792 (972)380-5710 (972)380-5595 (972)380-3201 (972)380-5710 (972)380-4776 (972)380-1333 (972)380-5594 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 Darr Brunhoeber Lou Estrada Enriquez Montague D'Amico Carder Wilson Yates Angelo Darr Lester Tickner Timanus Pomales Brunhoeber Lou Estrada Hendrix Langston Rhames Roach Matchtolff Macnai Stanton Mensik Carder Kevin Robert Chia-Yi Terrance Antonio Carmen Donald Vicky Jay Gayle Donatica Kevin James Rex Deona Oseas Robert Chia-Yi Terrance Becky Kim Sherman Marcie Allean Leigh Ann Catrina Ronald Vicky (972)380-5596 (972)380-5711 (972)380-3202 (972)380-4776 (972)380-2794 (972)380-5595 (972)380-2795 (972)380-2796 (972)380-3201 (972)380-5713 (972)380-5597 (972)380-5596 (972)380-3203 (972)380-5598 (972)380-5599 (972)380-3734 (972)380-5711 (972)380-3202 (972)380-4776 (972)380-1333 (972)380-5594 (972)380-4789 (972)380-2792 (972)380-5595 (972)380-5710 (972)380-5597 (972)380-3201 (972)380-2796 Medium Medium Small Medium Medium Large Small Small Small Small Small Small Medium Medium Small Small Small Large Medium Large Small Large Small Small Large Small Small Medium Medium Large Small Small Large Medium Medium Small Small Medium Small Small Small Small Small 1 10 3 8 1 8 2 8 1 7 1 4 5 6 1 3 3 2 1 2 2 6 1 2 3 7 3 2 1 7 4 3 5 5 2 1 11 11 2 8 5 5 13 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 Schoemann Angelo Nigel Roach Lester Porterfield Tickner Hendrix Langston Rhames Roach Matchtolff Macnai Mensik Carder Angelo Wakefield Tickner Rhames Roach Matchtolff Macnai Mensik Darr Dale Donatica Emmett Marcie James Jimmy Rex Becky Kim Sherman Marcie Allean Leigh Ann Ronald Vicky Donatica Lorraine Rex Sherman Marcie Allean Leigh Ann Ronald Kevin (972)380-3203 (972)380-5597 (972)380-4776 (972)380-2792 (972)380-3203 (972)380-2795 (972)380-5598 (972)380-1333 (972)380-5594 (972)380-4789 (972)380-2792 (972)380-5595 (972)380-5710 (972)380-3201 (972)380-2796 (972)380-5597 (972)380-5712 (972)380-5598 (972)380-4789 (972)380-2792 (972)380-5595 (972)380-5710 (972)380-3201 (972)380-5596 Medium Small Small Medium Medium Medium Medium Medium Small Small Medium Small Small Small Small Small Small Medium Small Medium Small Small Small Small 2 2 5 2 13 7 13 2 1 11 11 2 5 5 13 2 2 10 3 1 2 1 1 3 Total Amount Paid $171.00 $75.00 $8.05 $14.50 $19.99 $70.50 $40.65 $22.33 $462.50 $16.50 $7.50 $250.50 $28.50 $4.45 $165.60 $125.00 $80.30 $70.50 $36.79 $39.35 $69.00 $40.65 $156.31 $496.80 $562.50 $45.70 $49.06 $59.75 $64.09 $3.45 $40.15 $250.50 $19.00 $71.86 $78.47 $5.50 $80.04 $16.50 $86.18 $90.79 Product Category Outdoor Plant Garden Tool Plant Seeds Plant Seeds Plant Seeds Outdoor Plant Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Plant Seeds Garden Tool Outdoor Plant Plant Seeds Garden Tool Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds Plant Seeds Garden Tool Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Sales Date 2-Jan-08 2-Jan-08 2-Jan-08 2-Jan-08 2-Jan-08 5-Jan-08 5-Jan-08 5-Jan-08 6-Jan-08 7-Jan-08 8-Jan-08 9-Jan-08 9-Jan-08 25-Jan-08 27-Jan-08 27-Jan-08 1-Feb-08 1-Feb-08 1-Feb-08 1-Feb-08 1-Feb-08 3-Feb-08 3-Feb-08 3-Feb-08 3-Feb-08 3-Feb-08 3-Feb-08 3-Feb-08 3-Feb-08 5-Feb-08 5-Feb-08 18-Feb-08 18-Feb-08 18-Feb-08 18-Feb-08 19-Feb-08 19-Feb-08 20-Feb-08 20-Feb-08 21-Feb-08 $88.90 $171.00 $256.50 $99.00 $103.56 $36.00 $375.00 $111.75 $14.95 $120.90 $80.30 $129.61 $56.00 $70.50 $134.98 $528.45 $135.00 $290.29 $150.18 $3.45 $154.96 $40.15 $156.45 $70.50 $156.57 $40.65 $163.14 $22.33 $167.43 $165.60 $196.78 $125.00 $208.04 $250.50 $222.15 $28.50 $244.04 $16.50 $248.94 $190.29 $286.00 $3.45 $299.44 Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Garden Tool Outdoor Plant Plant Seeds Outdoor Plant Outdoor Plant Garden Tool Plant Seeds Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Garden Tool Outdoor Plant Garden Tool Outdoor Plant Garden Tool Plant Seeds Outdoor Plant 21-Feb-08 22-Feb-08 22-Feb-08 22-Feb-08 22-Feb-08 22-Feb-08 1-Mar-08 1-Mar-08 2-Mar-08 2-Mar-08 3-Mar-08 3-Mar-08 3-Mar-08 4-Mar-08 4-Mar-08 5-Mar-08 5-Mar-08 6-Mar-08 6-Mar-08 7-Mar-08 7-Mar-08 8-Mar-08 8-Mar-08 9-Mar-08 9-Mar-08 10-Mar-08 10-Mar-08 11-Mar-08 11-Mar-08 12-Mar-08 12-Mar-08 13-Mar-08 13-Mar-08 14-Mar-08 14-Mar-08 15-Mar-08 15-Mar-08 16-Mar-08 16-Mar-08 17-Mar-08 17-Mar-08 18-Mar-08 18-Mar-08 $40.15 $338.53 $3.45 $338.99 $40.15 $356.57 $70.50 $359.40 $40.65 $541.43 $22.33 $560.00 $54.45 $190.00 $5.50 Garden Tool Outdoor Plant Plant Seeds Garden Tool Garden Tool Garden Tool Outdoor Plant Garden Tool Garden Tool Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds 19-Mar-08 19-Mar-08 20-Mar-08 20-Mar-08 21-Mar-08 21-Mar-08 22-Mar-08 22-Mar-08 23-Mar-08 23-Mar-08 24-Mar-08 24-Mar-08 25-Mar-08 26-Mar-08 27-Mar-08 $28.50 $16.50 $7.50 $4.45 $171.00 $30.00 $462.50 $75.00 $45.70 $49.00 $8.05 $38.50 $70.50 $40.65 $156.31 $65.00 $16.50 $7.50 $4.45 $19.00 $5.50 $16.50 $9.79 $171.00 $37.50 $135.00 $75.00 $14.95 Outdoor Plant Outdoor Plant Plant Seeds Plant Seeds Outdoor Plant Outdoor Plant Garden Tool Garden Tool Outdoor Plant Plant Seeds Plant Seeds Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds Plant Seeds Outdoor Plant Plant Seeds Plant Seeds Plant Seeds Garden Tool Plant Seeds Outdoor Plant Outdoor Plant Plant Seeds 28-Mar-08 29-Mar-08 30-Mar-08 31-Mar-08 1-Apr-08 1-Apr-08 2-Apr-08 3-Apr-08 3-Apr-08 3-Apr-08 4-Apr-08 5-Apr-08 6-Apr-08 7-Apr-08 8-Apr-08 8-Apr-08 9-Apr-08 10-Apr-08 11-Apr-08 12-Apr-08 13-Apr-08 14-Apr-08 15-Apr-08 16-Apr-08 17-Apr-08 17-Apr-08 18-Apr-08 19-Apr-08 $48.00 $80.30 $77.00 $39.79 $528.45 $78.00 $290.29 $19.00 $5.50 $16.50 $9.79 $171.00 $117.50 $375.00 $14.95 $80.30 $70.50 $290.29 $3.45 $40.15 $70.50 $40.65 $22.33 $28.50 Garden Tool Outdoor Plant Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds Plant Seeds Plant Seeds Garden Tool Outdoor Plant Garden Tool Plant Seeds Outdoor Plant Outdoor Plant Outdoor Plant Plant Seeds Garden Tool Outdoor Plant Garden Tool Outdoor Plant Outdoor Plant 19-Apr-08 20-Apr-08 20-Apr-08 21-Apr-08 22-Apr-08 22-Apr-08 23-Apr-08 24-Apr-08 25-Apr-08 26-Apr-08 27-Apr-08 28-Apr-08 29-Apr-08 30-Apr-08 1-May-08 2-May-08 3-May-08 5-May-08 6-May-08 7-May-08 8-May-08 9-May-08 10-May-08 11-May-08 Supplemental Reading: Business Intelligence 1 -Definition of Business Intelligence (BI) 2 -Three Types of Business Intelligence (BI) Applications 3 -Reporting applications ---RFM Analysis ---Online Analytical Processing (OLAP) 4 -Data-mining applications --Market-Basket Analysis --Decision-tree analysis 5 - Exercises: BI in Practice by Prof. Xuefei (Nancy) Deng, ISOM Department, CBAPP 9-1 8-1 BI in Practice: A Garden Store Owner's Frustration MARY NEEDS YOUR HELP! \"Tootsie was one of my best customers. I'd lost her, and I did not even know it! That really frustrated me.\" \"Is it inevitable that as I get bigger, I lose track of my customers? I don't think so.\" \"Somehow, I have to find out when regular customers aren't coming around.\" \"Had I known Tootsie has stopped shopping with us, I'd have called her to see what was going on. I need customers like her.\" Mary needs to discover business intelligence from her sales data. Which tools in this chapter will provide Mary the best value? 9-2 Sample of Raw Sales Data 9-3 Q1 - Defining Business Intelligence Business intelligence (BI): Business intelligence (BI) system: Information containing patterns, relationships, and trends. Examples of BI From the reading: An information system that employs business intelligence tools to produce and deliver information. Additional definition: a collection of information technology applications that focus on data collection, extraction and analysis, including query and reporting, online analytical processing (OLAP), data warehousing, and data mining (Turban et al. 2008) Organizational use of BI Systems The No. 2 two most important \"application and technology issue\" for 2009, after anti-virus protection (http://www.simnet.org) Major Commercial Vendors: SAP, IBM, SAS Institute, Microsoft, and Oracle ( http://www.gartner.com) 9-4 Q2 - Three Types of BI Tools Reporting tools Data-mining tools Process data: sorting, grouping, summing, filtering Format the data into structured reports that are delivered to users. used primarily for assessment, e.g., What happened? What is happening? How do they differ? RFM analysis and Online analytical processing (OLAP) process data using statistical (mathematically complex) techniques, search for patterns and relationships. make predictions, e.g., how likely will a customer default on a loan? How fast will a customer respond to a sales promotion? Market-Basket analysis and decision-tree analysis Knowledge-management tools store employee knowledge, make it available to whomever needs it. 9-5 Q3 -Reporting applications --- Example of Sales Data Sorted by Customer Name & Grouped by Number of Orders & Purchase Amount 9-6 Q3 -Reporting applications: Data Filtered and Formatted 9-7 Q3 -Reporting Applications: RFM Analysis RFM Analysis allows you to analyze and rank customers according to purchasing patterns. R = how recently a customer purchased your products F = how frequently a customer purchases your products M = how much money a customer typically spends on your products RFM scores range: 1-5. The lower the score, the better the customer. Fig 9-6 Example of RFM Score Data 9-8 Q3 -Reporting applications: RFM Purchasing patterns identified. Ajax: a good and regular customer; sales team should try to up-sell to Ajax. How about Bloominghams? Caruthers: Should the sales team ignore him? Davidson: Should the sales team spend a lot of time on him? Fig 9-6 Example of RFM Score Data 9-9 Q3 -Reporting applications: OLAP Online Analytical Processing (OLAP): More generic than RFM and provides you with the dynamic ability to sum, count, average, and perform other arithmetic operations on groups of data. Online: users can change the format of the report. OLAP Report (also called OLAP cubes), use Measures: data items of interest, e.g., Store Sales Net . Dimensions: characteristics of a measure, e.g., Product Family and Store Type. Fig 9-7 OLAP Product Family by Store Type 9-10 Q3 - Reporting applications: OLAP Alter the format of a report to provide users with the information needed. New Dimensions: Store country and store state Fig 9-8 OLAP Product Family & Store Location by Store Type 9-11 Q3 - Reporting applications: OLAP To Show Stores in California --- Divide data into more detail by drilling down through the data Fig 9-9 OLAP Product Family & Store Location by Store Type, Drilled Down to Show Stores in California 9-12 Q3 - Reporting applications: OLAP Servers Special products that read data from an operational database, perform some preliminary calculations, and then store the results in an OLAP database Fig 9-10 Role of OLAP Server & OLAP Database 9-13 Q4 - What is data-mining? Applications used to find patterns and relationships among data for classification and prediction. Convergence of disciplines: statistics and mathematics, artificial intelligence and machine-learning. Also called \"knowledge discovery in database (KDD)\" Fig 9-11 Convergence Disciplines for Data Mining 9-14 Q4 - Two types of data-mining techniques Unsupervised data-mining: No model or hypothesis exists before running the analysis Analysts apply data-mining techniques and then observe the results Analysts create a hypotheses after analysis is completed Example: Cluster analysis, a common technique groups entities together that have similar characteristics. Supervised data-mining: Analysts develop a model prior to their analysis Apply statistical techniques to estimate parameters of a model Example: Regression analysis measures the impact of a set of variables on another variable. Salary = B0 + B1 * Degree + B2 * Age + B3 * Gender + B4 * Major 9-15 Q4 -Data-mining Applications-------Market-Basket Analysis Market-Basket Analysis: A data-mining tool for determining sales patterns. To answer questions such as \"What products do customers tend to buy together?\" To identify cross-selling opportunities for businesses. Three Measures in Market-Basket Analysis: 1. Support the probability that two items will be purchased together Support = P (A & B) 2. Confidence a conditional probability estimate Confidence =P (A | B) 3. Lift - how much the base probability increases or decreases when the other products are purchased. Lift=P (A|B) / P(A) 9-16 Q4 -Market-Basket Analysis: Support Measure Probability that a customer will buy one item Mask (of the total 1000 items)? Probability that a customer will buy the two items Mask and fins together? P (Mask)= P (Mask & Fins)= How about the Support measure for Mask and Dive computer together? P (Mask & Dive computers)= Fig 9-12 Market-Basket Example 9-17 Q4 -Market-Basket Analysis - Confidence Measure 1) What proportion of the customers who bought a mask also bought fins? 2) What proportion of the customers who bought fins also bought masks? P (Fins | Masks) =150/270=0.5556 P (Masks | Fins) = 3) What proportion of the customers who bought Dive Computers also bought masks? P (Mask | Dive computers)= 9-18 Q4 -Typical data-mining applications- A decision tree An unsupervised data-mining technique that selects the most useful attributes for classifying entities on some criterion (hierarchically arranged). It uses \"if...then\" decision rules in the decision process. Correction: MIS class Grade > 3.0 Fig 9-13 Grades of Students from Past MIS Class (Hypothetical Data) Pearson Prentice Hall 2009 9-19 Q4 -Decision Tree Analysis: Decision rules from the \"Students Grade\" example: If a student is junior and works in a restaurant, then predict his/her grade >3.0; If a student is junior and does not work in a restaurant, then predict his/her grade <=3.0; If a student is senior and is a non-business major, then predict his/her grade <=3.0; If a student is senior and is a business major, then make no prediction. Correction: MIS class Grade > 3.0 Fig 9-13 Grades of Students from Past MIS Class (Hypothetical Data) 9-20 Group Exercise: Help Mary, the frustrated Garden Store Owner Mary's frustration: \"I've got all sorts of data in my sales database. It seems that the information I need is in there, but how do it get it out?\" (From BI in practice, page 332 in the reading material) Which tools in this lecture will provide Mary the best value? 1) FDF reports; 2) OLAP tool; 3) Decision-tree analysis; 4) RFM analysis; 5) Market-basket analysis Download data file: \"sale_data_BI.xls\" 9-21 Supplemental Reading: Business Intelligence 1 -Definition of Business Intelligence (BI) 2 -Three Types of Business Intelligence (BI) Applications 3 -Reporting applications ---RFM Analysis ---Online Analytical Processing (OLAP) 4 -Data-mining applications --Market-Basket Analysis --Decision-tree analysis 5 - Exercises: BI in Practice by Prof. Xuefei (Nancy) Deng, ISOM Department, CBAPP 9-1 8-1 BI in Practice: A Garden Store Owner's Frustration MARY NEEDS YOUR HELP! \"Tootsie was one of my best customers. I'd lost her, and I did not even know it! That really frustrated me.\" \"Is it inevitable that as I get bigger, I lose track of my customers? I don't think so.\" \"Somehow, I have to find out when regular customers aren't coming around.\" \"Had I known Tootsie has stopped shopping with us, I'd have called her to see what was going on. I need customers like her.\" Mary needs to discover business intelligence from her sales data. Which tools in this chapter will provide Mary the best value? 9-2 Sample of Raw Sales Data 9-3 Q1 - Defining Business Intelligence Business intelligence (BI): Business intelligence (BI) system: Information containing patterns, relationships, and trends. Examples of BI From the reading: An information system that employs business intelligence tools to produce and deliver information. Additional definition: a collection of information technology applications that focus on data collection, extraction and analysis, including query and reporting, online analytical processing (OLAP), data warehousing, and data mining (Turban et al. 2008) Organizational use of BI Systems The No. 2 two most important \"application and technology issue\" for 2009, after anti-virus protection (http://www.simnet.org) Major Commercial Vendors: SAP, IBM, SAS Institute, Microsoft, and Oracle ( http://www.gartner.com) 9-4 Q2 - Three Types of BI Tools Reporting tools Data-mining tools Process data: sorting, grouping, summing, filtering Format the data into structured reports that are delivered to users. used primarily for assessment, e.g., What happened? What is happening? How do they differ? RFM analysis and Online analytical processing (OLAP) process data using statistical (mathematically complex) techniques, search for patterns and relationships. make predictions, e.g., how likely will a customer default on a loan? How fast will a customer respond to a sales promotion? Market-Basket analysis and decision-tree analysis Knowledge-management tools store employee knowledge, make it available to whomever needs it. 9-5 Q3 -Reporting applications --- Example of Sales Data Sorted by Customer Name & Grouped by Number of Orders & Purchase Amount 9-6 Q3 -Reporting applications: Data Filtered and Formatted 9-7 Q3 -Reporting Applications: RFM Analysis RFM Analysis allows you to analyze and rank customers according to purchasing patterns. R = how recently a customer purchased your products F = how frequently a customer purchases your products M = how much money a customer typically spends on your products RFM scores range: 1-5. The lower the score, the better the customer. Fig 9-6 Example of RFM Score Data 9-8 Q3 -Reporting applications: RFM Purchasing patterns identified. Ajax: a good and regular customer; sales team should try to up-sell to Ajax. How about Bloominghams? Caruthers: Should the sales team ignore him? Davidson: Should the sales team spend a lot of time on him? Fig 9-6 Example of RFM Score Data 9-9 Q3 -Reporting applications: OLAP Online Analytical Processing (OLAP): More generic than RFM and provides you with the dynamic ability to sum, count, average, and perform other arithmetic operations on groups of data. Online: users can change the format of the report. OLAP Report (also called OLAP cubes), use Measures: data items of interest, e.g., Store Sales Net . Dimensions: characteristics of a measure, e.g., Product Family and Store Type. Fig 9-7 OLAP Product Family by Store Type 9-10 Q3 - Reporting applications: OLAP Alter the format of a report to provide users with the information needed. New Dimensions: Store country and store state Fig 9-8 OLAP Product Family & Store Location by Store Type 9-11 Q3 - Reporting applications: OLAP To Show Stores in California --- Divide data into more detail by drilling down through the data Fig 9-9 OLAP Product Family & Store Location by Store Type, Drilled Down to Show Stores in California 9-12 Q3 - Reporting applications: OLAP Servers Special products that read data from an operational database, perform some preliminary calculations, and then store the results in an OLAP database Fig 9-10 Role of OLAP Server & OLAP Database 9-13 Q4 - What is data-mining? Applications used to find patterns and relationships among data for classification and prediction. Convergence of disciplines: statistics and mathematics, artificial intelligence and machine-learning. Also called \"knowledge discovery in database (KDD)\" Fig 9-11 Convergence Disciplines for Data Mining 9-14 Q4 - Two types of data-mining techniques Unsupervised data-mining: No model or hypothesis exists before running the analysis Analysts apply data-mining techniques and then observe the results Analysts create a hypotheses after analysis is completed Example: Cluster analysis, a common technique groups entities together that have similar characteristics. Supervised data-mining: Analysts develop a model prior to their analysis Apply statistical techniques to estimate parameters of a model Example: Regression analysis measures the impact of a set of variables on another variable. Salary = B0 + B1 * Degree + B2 * Age + B3 * Gender + B4 * Major 9-15 Q4 -Data-mining Applications-------Market-Basket Analysis Market-Basket Analysis: A data-mining tool for determining sales patterns. To answer questions such as \"What products do customers tend to buy together?\" To identify cross-selling opportunities for businesses. Three Measures in Market-Basket Analysis: 1. Support the probability that two items will be purchased together Support = P (A & B) 2. Confidence a conditional probability estimate Confidence =P (A | B) 3. Lift - how much the base probability increases or decreases when the other products are purchased. Lift=P (A|B) / P(A) 9-16 Q4 -Market-Basket Analysis: Support Measure Probability that a customer will buy one item Mask (of the total 1000 items)? Probability that a customer will buy the two items Mask and fins together? P (Mask)= P (Mask & Fins)= How about the Support measure for Mask and Dive computer together? P (Mask & Dive computers)= Fig 9-12 Market-Basket Example 9-17 Q4 -Market-Basket Analysis - Confidence Measure 1) What proportion of the customers who bought a mask also bought fins? 2) What proportion of the customers who bought fins also bought masks? P (Fins | Masks) =150/270=0.5556 P (Masks | Fins) = 3) What proportion of the customers who bought Dive Computers also bought masks? P (Mask | Dive computers)= 9-18 Q4 -Typical data-mining applications- A decision tree An unsupervised data-mining technique that selects the most useful attributes for classifying entities on some criterion (hierarchically arranged). It uses \"if...then\" decision rules in the decision process. Correction: MIS class Grade > 3.0 Fig 9-13 Grades of Students from Past MIS Class (Hypothetical Data) Pearson Prentice Hall 2009 9-19 Q4 -Decision Tree Analysis: Decision rules from the \"Students Grade\" example: If a student is junior and works in a restaurant, then predict his/her grade >3.0; If a student is junior and does not work in a restaurant, then predict his/her grade <=3.0; If a student is senior and is a non-business major, then predict his/her grade <=3.0; If a student is senior and is a business major, then make no prediction. Correction: MIS class Grade > 3.0 Fig 9-13 Grades of Students from Past MIS Class (Hypothetical Data) 9-20 Group Exercise: Help Mary, the frustrated Garden Store Owner Mary's frustration: \"I've got all sorts of data in my sales database. It seems that the information I need is in there, but how do it get it out?\" (From BI in practice, page 332 in the reading material) Which tools in this lecture will provide Mary the best value? 1) FDF reports; 2) OLAP tool; 3) Decision-tree analysis; 4) RFM analysis; 5) Market-basket analysis Download data file: \"sale_data_BI.xls\" 9-21