Houston Petroleum Valve Company

1. Houston Petroleum Valve Company Data-Mining Project Mohammad H. Monakes Sam Houston State University Spring 2005

2. Objectives • Categorize customers based on their annual purchase. • Determine and measure manpower for current business continuation. • Identify and recommend adjustments or realignments of the companies resources to improve, and optimize the customer services. • Provide planning to execute adjustments to current departments and management teams.

3. Data • Based on management recommendations, a complete set of 2003 annual sales will be use for analysis and mining. • There were no application upgrade in 2003. • No major change in services. • There were no major gain of loss of customer accounts in 2003. • There were limited loss or gain of employees.

4. Summaries of the Collection • A total of around 20,000 records were collected from the data repository. • Total Sales = 12 Million $ • Total Cost of Goods Sold = 8.4 Million $ • Total Customers = 1,023 • Total Sales People = 42 • Total Employees in Customer Service, Sales and Accounting departments = 85

5. Data Cleansing • Three methods of cleansing were done on the data. • Attribute-oriented Induction, removal and generalization of attributes. • Removal of weakly related attributes. • Removal of invalid tuples.

6. Data Generalization • Attributes were grouped to calculate required summary of Sale, Quantity and Profit. • By Customer • By Region • By Sales Person

7. Data Warehouse • DW consists of five dimension and one fact table created in a star schema. • Sales table (fact table) • Customer table • Salesperson table • Time table • Item table • Location table

8. Measurements

9. Star Schema

10. Data Mining Reports • Accumulated sales. • Accumulated of % increase Sales and Cost of Goods. • Accumulated of % increase orders by customers

11. Accumulated Annual Sales

12. Accumulated Annual Sales graph

13. Customer Accumulated % Sales % of the total Company Sales

14. Customer Accumulated % Sales % of the total Company Sales

15. $ Sales and Cost by Customer

16. Customer Accumulated Orders

17. Orders processed by CS

18. Conclusion • About %50 of sales is from 100 customers (around %10). • Around %80 or sales orders are processes for top 300 of customers (around %30). • 5 salespersons from 42 are responsible for the top %10 customers.