The Application Of Data Mining To Support Custemer Relationship Management At Ethiopian Airlines

140 Pages·2008·1.21 MB·English

ADDIS ABABA UNIVERSITY
SCHOOL OF GRAGUATE STUDIES
FACULTY OF INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE

THE APPLICATION OF DATA MINING TO SUPPORT CUSTEMER RELATIONSHIP
MANAGEMENT AT ETHIOPIAN AIRLINES

BY

DENEKEW ABERA JEMBERE

JUNE, 2003
THE APPLICATION OF DATA MINING TO SUPPORT CUSTEMER RELATIONSHIP
MANAGEMENT AT ETHIOPIAN AIRLINES

BY

DENEKEW ABERA JEMBERE

A Thesis Submitted to the School of Graduate Studies, Addis Ababa University,
Department of Information Science in Partial Fulfillment of the Requirements for
the Degree of Master of Science in Information Science

June, 2003
ADDIS ABABA UNIVERSITY
SCHOOL OF GRAGUATE STUDIES

THE APPLICATION OF DATA MINING TO SUPPORT CUSTEMER RELATIONSHIP
MANAGEMENT AT ETHIOPIAN AIRLINES

BY

DENEKEW ABERA JEMBERE

FACULTY OF INFORMATICS
DEPARTMENT OF INFORMATION SCIENCE

________________________ ______________
Approved by Signature

_____________________________ ________________
Advisor Signature

_____________________________ _________________
Advisor Signature

_____________________________ __________________
Advisor Signature

_____________________________ __________________
Examiner Signature

ii
DEDICATION

I would like to dedicate this paper to Ebenezer who I have always been and will also be
thinking about.
iii
ACKNOWLEDGEMENTS
Above all, I would like to glorify the almighty GOD for giving me the ability to be where I am. You have
done so much for me, O Lord. No wonder I am glad! I sing for joy, Hallelujah!
I would like to thank my advisors Ato Mesfin Getachew, Ato Ermias Abebe and Ato Henok Wobishet for
their constructive comments and overall guidance. But special thanks go to Ato Henok Wobishet,
without whom this research would have not been a success. Henok, your helpful personality will always
be a role in my heart.
I wish to thank Ato Ermias Alemu for being glad to provide his support. I would also like to thank Ato
Mesfin Tassew, the Chief Information Officer of Ethiopian Airlines, for allowing me to carry out this
research using the required data from the Airline.
I am very much grateful to my father Ato Abera Jembere, my mother W/ro Etaferahu Gichamo, my
sisters W/ro Debrework Abera and W/rt Birhane Abera for their care and understanding during my
study times. I am also grateful to Ato Engida Kasa (Gashe) and Ato Adamu Jember (Wondim Gashe)
for they have been there to assist me morally and materially whenever I needed.
My heartfelt thanks also go to all my instructors and classmates at SISA for the lovely time and classes
we have had together.
At last, but by no means the least, I would like to thank my brothers, and friends such as Nigusie
Gezahegn, Mulugeta Jawoire, Admasu Engida, Habtamu Hailu and Misrak Genene for the constant
assistance and encouragement they rendered to me since the time of my admission to the
postgraduate program.
iv
TABLE OF CONTENTS
Content Page
DEDICATION......................................................................................................................................................III
ACKNOWLEDGEMENTS....................................................................................................................................IV
TABLE OF CONTENTS........................................................................................................................................V
LIST OF TABLES...............................................................................................................................................VII
LIST OF FIGURES............................................................................................................................................VIII
LIST OF ABBRIVATIONS....................................................................................................................................IX
CHAPTER ONE........................................................................................................................................................1
INTRODUCTION.......................................................................................................................................................1
1.1. BACKGROUND..........................................................................................................................................1
1.2. STATEMENT OF THE PROBLEM AND JUSTIFICATION..........................................................................................8
1.3. OBJECTIVES......................................................................................................................................11
1.3.1. General Objective............................................................................................................................11
1.3.2. Specific Objectives..........................................................................................................................11
1.4. RESEARCH METHODOLOGY......................................................................................................................12
1.4.1. Review of Related Literature.............................................................................................................12
1.4.3. Identifying Available Data Sources....................................................................................................13
1.4.4. Data Collection and Preparation........................................................................................................14
1.4.5. Training and Building the Model........................................................................................................15
1.4.6. Performance Evaluation (Testing) the Model......................................................................................16
1.4.7. Prototype Development....................................................................................................................16
1.5. SCOPE AND LIMITATION...........................................................................................................................17
1.6. RESEARCH CONTRIBUTION.......................................................................................................................17
1.7. THESIS ORGANIZATION............................................................................................................................18
CHAPTER TWO......................................................................................................................................................19
DATA MINING........................................................................................................................................................19
2.1. INTRODUCTION.......................................................................................................................................19
2.2. DATA MINING.........................................................................................................................................20
2.3. DATA MINING AND KNOWLEDGE DISCOVERY IN DATABASES (KDD)..................................................................21
2.3.1. The Knowledge discovery process....................................................................................................23
2.4. DATA MINING AND DATABASE MANAGEMENT.................................................................................................27
2.5. DATA MINING AND DATA WAREHOUSING.......................................................................................................27
2.6. DATA MINING AND ON-LINE ANALYTICAL PROCESSING (OLAP).......................................................................29
2.7. DATA MINING, ARTIFICIAL INTELLIGENCE (AI) AND STATISTICS........................................................................31
2.8. DATA MINING ACTIVITIES.........................................................................................................................32
2.9. APPLICATION OF DATA MINING TECHNOLOGY...............................................................................................33
2.9.1. Application of Data Mining in the Airline Industry.................................................................................36
CHAPTER THREE..................................................................................................................................................38
CUSTOMER RELATIONSHIP MANAGEMENT.........................................................................................................38
3.1. LOYALTY AND CUSTOMER RELATIONSHIP MANAGEMENT................................................................................38
3.1.1 Overview........................................................................................................................................38
3.1.2 Loyalty and CRM in the Airline Industry..............................................................................................39
3.2. SURVEY OF THE FREQUENT FLYER PROGRAM OF ETHIOPIAN.......................................................................41
3.2.1 Business Processes of the Frequent Flyer Program............................................................................43
3.2.2 Overview of ShebaMiles’ Database System........................................................................................45
3.2.3 Findings of the Survey......................................................................................................................46
CHAPTER FOUR....................................................................................................................................................49
REVIEW OF APPLICABLE TECHNIQUES AND A RELATED RESEARCH.................................................................49
v
4.1 INTRODUCTION...........................................................................................................................................49
4.2 CLUSTERING TECHNIQUES............................................................................................................................49
4.2.1 The K-Means Algorithm....................................................................................................................51
4.2.2 Self-Organizing Map (SOM)..............................................................................................................56
4.3 CLASSIFICATION..........................................................................................................................................57
4.3.1 Decision Trees................................................................................................................................57
4.3.2 Decision Tree Induction.................................................................................................................59
4.3.3 Decision Trees and Attribute Selection..........................................................................................60
4.3.4 Controlling Tree Size.....................................................................................................................63
4.3.5 Advantages of Decision Trees.......................................................................................................64
4.3.6 Limitations of Decision Trees.........................................................................................................64
4.4 SUMMARY OF A RELATED RESEARCH...............................................................................................................65
4.4.1 The Data Files Used........................................................................................................................65
4.4.2 The Experiments Carried out............................................................................................................66
4.4.3 The Input Parameters Used and the Sub-Experiments........................................................................67
4.4.4 Output of the Sub-Experiment Selected.............................................................................................68
4.4.5 The Class/Concept Description of each Cluster..................................................................................69
4.5 CONCLUSION..............................................................................................................................................70
CHAPTER FIVE......................................................................................................................................................71
EXPERIMENTATION..............................................................................................................................................71
5.1 OVERVIEW.................................................................................................................................................71
5.2 DATA MINING GOALS...................................................................................................................................72
5.3 DATA MINING TOOL SELECTION.....................................................................................................................73
5.3.1 Description of the Data in Tables of the MS Access Database.............................................................75
5.3.2 Verification of Data Quality...............................................................................................................77
5.4 DATA PREPARATION....................................................................................................................................77
5.4.1 Data Preprocessing.........................................................................................................................78
5.4.2 Preparing Data for Analysis..............................................................................................................79
5.4.3 Data Formatting...............................................................................................................................82
5.5 MODELING.................................................................................................................................................83
5.5.1 The Clustering Sub-Phase................................................................................................................84
Automatic Cluster Detection.........................................................................................................................................86
The Knowledge Studio Clustering Experiment................................................................................................................86
The Weka-3-2 Clustering Experiment............................................................................................................................89
Comparison of Results based on Distribution of Records in each Cluster..........................................................................90
Interpretation of the Cluster Indexes.............................................................................................................................93
5.5.2 The Classification Sub-Phase...........................................................................................................95
Decision Tree Model Building.......................................................................................................................................97
Experiments using Weka-3-2.......................................................................................................................................98
5.6 THE CUSTOMER CLASSIFICATION SYSTEM: A PROTOTYPE................................................................................102
CHAPTER SIX......................................................................................................................................................105
CONCLUSION AND RECOMMENDATIONS...........................................................................................................105
6.1 CONCLUSION............................................................................................................................................105
6.2 RECOMMENDATIONS..................................................................................................................................108
BIBILIOGRAPHY..................................................................................................................................................111
APPENDICES.......................................................................................................................................................114
APPENDIX I........................................................................................................................................................114
APPENDIX II.......................................................................................................................................................115
vi
LIST OF TABLES

TABLE 3.1: THE THREE CLUB LEVELS OF SHEBAMILES, ELIGIBILITY AND AWARDS...........................................................................43
TABLE 4.1 SUMMARY OF THE DATA FILES IN HENOK’S MS ACCESS DATABASE..............................................................................66
TABLE 4.2 SUMMARY OF THE BASIC EXPERIMENTS OF CLUSTERING BY HENOK (2002)...................................................................67
TABLE 4.3 SUMMARY OF THE BASIC AND SUB- EXPERIMENTS BY HENOK (2002).............................................................................68
TABLE 4.4 SUMMARY OF THE CLUSTERING SUB- EXPERIMENT 4-2 BY HENOK (2002).....................................................................68
TABLE 4.5 THE CLASS/CONCEPT DESCRIPTION AND REMARK OF EACH CLUSTER............................................................................69
TABLE 5.1 ATTRIBUTES OF THE TRIPS TABLE..............................................................................................................................75
TABLE 5.2 ATTRIBUTES OF THE MEMBER TABLE (PARTIALLY)......................................................................................................76
TABLE 5.3: ATTRIBUTES OF THE POINTS TABLE..........................................................................................................................77
TABLE 5.4 SELECTED ATTRIBUTES FOR CLUSTER MODELING.......................................................................................................78
TABLE 5.5: ATTRIBUTES OF THE TRIPS TABLE AGGREGATED AT MEMBER LEVEL.............................................................................80
TABLE 5.6: THE DISTRIBUTION AND RELATIVE FREQUENCY OF RECORDS IN THE 5 CLUSTERS..........................................................88
TABLE 5.7: THE DISTRIBUTION AND RELATIVE FREQUENCY OF RECORDS IN THE 5 CLUSTERS..........................................................90
(FROM KNOWLEDGE STUDIO).....................................................................................................................................................90
TABLE 5.8: ARRANGEMENT OF CLUSTER INDEXES BASED ON RELATIVE DISTRIBUTION OF RECORDS..............................................91
TABLE 5.9: CLUSTER CENTROIDS FROM WEKA AND THE CALCULATED MEAN FROM THE MS EXCEL.................................................92
TABLE 5.105: CLUSTER INDEXES AND THEIR CORRESPONDING CLUSTER CENTROIDS.....................................................................93
TABLE 5.11: THE ORDINAL VALUES AND CLUSTER CENTROIDS OF THE CLUSTERS IN THE PREVIOUS AND CURRENT RESEARCHES
RESPECTIVELY................................................................................................................................................................94
TABLE 5.12: THE CLUSTER INDICES OF BOTH THE CURRENT AND PREVIOUS RESEARCHES AND THEIR CORRESPONDING
CLASS/CONCEPT DESCRIPTION.........................................................................................................................................95
TABLE 5.13: PROPORTIONAL SELECTION OF TRAINING AND TEST SUB-DATA SETS FOR WEKA-3-2...................................................98
TABLE 5.14: INPUT PARAMETERS AND THE RESULTING DECISION TREES’ OUTPUT PARAMETERS...................................................100
TABLE 5.15: INPUT PARAMETERS AND THE RESULTING EXTRACTED DECISION RULES’ OUTPUT PARAMETERS.................................101

vii
LIST OF FIGURES

FIGURE 2.1: PHASES OF THE CRISP-DM PROCESS CYCLE..........................................................................................................24
FIGURE 3.1: BUSINESS PROCESS OF THE SHEBAMILES FFP PROGRAM AT ETHIOPIAN................................................................46
FIGURE 3.2: THE DATA FLOW OF SHEBAMILES’ DATABASE SYSTEM...............................................................................................47
FIGURE 4.1: INITIAL CLUSTER SEEDS........................................................................................................................................56
FIGURE 4.2: CLUSTER SEEDS AFTER ONE ITERATION..................................................................................................................56
FIGURE 4.3: A DECISION TREE WITH DECISION (NI) AND LEAF (LI) NODES, AND DECISIONS (DI)......................................................60
FIGURE 5.1: THE SHEBAMILES DATA MART DATA MODEL............................................................................................................86
FIGURE 5.2: (A) TOTALYEARTRIP SPLIT......................................................................................................................................91
FIGURE 5.2: (B) TREE WITH THE 10 LEAVES FOR DIFFERENT TOTALYEARTRIP RANGES..................................................................91
FIGURE 5.3: PORTION OF THE FINAL DATA SET AFTER BEING CLUSTERED.......................................................................................92

viii
LIST OF ABBRIVATIONS
CLD- Customer Loyality Department
CRM- Customer Relation Management
CRISP-DM- CRoss Industry Standard Process
DBMS- Database Management System
OLAP- On-Line Analytical Processing
SQL- Strucvtured Query Language
EDDS- Electronic Data Distribution System
GLC- Golden Lion Club
ix