| Oracle® Airlines Data Model Reference 11g Release 2 (11.2) Part Number E26208-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Airlines Data Model Reference 11g Release 2 (11.2) Part Number E26208-02 |
|
|
PDF · Mobi · ePub |
This chapter provides reference information about the data mining models provided with Oracle Airlines Data Model.
This chapter includes the following sections:
Oracle Airlines Data Model mining models include mining packages, mining source tables (MV), and target tables.
The source materialized views are defined on 3NF layer tables (Base, Reference) and analytical layer tables (Derived, Reference) of Oracle Airlines Data Model. The procedures in mining package pull data from source tables to train mining models. The trained mining models are applied on apply tables (MV), which are also defined on 3NF and analytical layer tables. The data in source tables and apply tables is differed by time. The target tables contain mining results data, which could be rules derived from trained models and also results of applying trained model on apply data.
Note:
Oracle Airlines Data Model does not support modified or new data models. Consequently, do not change the data models that are defined and delivered with Oracle Airlines Data Model, but, instead, to create a data model copy a delivered data model.As shown in Table 9-2, the Oracle Airlines Data Model mining models use the specified algorithms for specific problem types.
Table 9-1 Oracle Airlines Data Model Algorithm Types Used by Model
| Model | Problem Type | Algorithms Used by Data Mining Model |
|---|---|---|
|
Model 1: Customer Segmentation Analysis |
Clustering |
K-Means Clustering |
|
Model 2: Customer Loyalty Analysis |
Classification |
Decision Tree (DT), Support Vector Machine (SVM) |
|
Model 3: Customer Life Time Value Analysis |
Classification & Regression |
Decision Tree (DT), Generalized Linear Model Regression (GLMR) |
|
Model 4: Frequent Flyer Passenger Prediction |
Classification |
Decision Tree (DT), Support Vector Machine (SVM) |
Oracle Airlines Data Model consists of one schema, oadm_sys. Table 9-1shows how mining source tables (MV) are mapped and how mining packages function.
The oadm_sys schema includes the following:
OADM main model: This includes all base, reference, lookup, derived, and aggregate tables.
Mining Model Package (pkg_oadm_mining): Given data in mining source tables and apply tables, the mining package trains models using source tables, generates mined rules, and applies trained models on apply data and generate predicted results.
Mining Model Source and Apply Tables: Materialized views are defined on OADM main model tables (base and reference of 3NF layer, derived and reference of Analytical layer).
Mining Support Tables: The mining support tables are intermediate tables, which are used by mining package while training mining models. The names of these support tables have a prefix "DM".
Mining Result Tables: Mining result tables save mined rules from trained models. These tables also save the results of applying trained models on apply data.
For more information about the Oracle Mining training and Scoring (applying) process, see Oracle Data Mining Concepts.
Figure 9-1 Oracle Airlines Data Model Mining Package Overview
Overtime, customer information and customer behavior may change. Therefore, you may want to refresh trained mining models using latest customer data and usage data. By refresh training mining model, we mean that re-training mining model on latest data. Re-trained mining model and older trained model are tested on latest source data and best one of them is picked. The mining model refresh process is divided into three tasks:
Data Preparation: Load and transform the data into a format, which is understood by mining algorithms. Also, user needs to prepare two sets of data, each corresponding to one of next two tasks:
Training Data
Scoring (Apply) Data
Training: Recent data of customers is used as training data and an algorithm is used to train a model on chosen training data.
Scoring (Apply): Most recent data of customer is used as scoring data and trained mining model is applied on chosen scoring data to predict target variable for supervised problems and to predict groupings/rules for unsupervised problems.
To refresh all mining models based on latest customer and non frequent flyer data, call the procedure pkg_oadm_mining.refresh_model. This procedure performs following tasks for each model:
Refreshes all source materialized views based on latest data from 3NF and Analytical layers tables.
Trains each model again using the new training data. Applies newly trained mining model and older main mining model on new training data to check which model performs better. The best one of two models is picked as main mining model.
Applies each main model on latest apply data.
The errors occurred during mining model refresh are saved into a control table, dwc_intra_etl_activity.
Table 9-2 Oracle Airlines Data Model Algorithm Types Used by Model
| Model | Problem Type | Algorithms Used by Data Mining Model |
|---|---|---|
|
Model 1: Customer Segmentation Analysis |
Clustering |
K-Means Clustering |
|
Model 2: Customer Loyalty Analysis |
Classification |
Decision Tree (DT), Support Vector Machine (SVM) |
|
Model 3: Customer Life Time Value Analysis |
Classification & Regression |
Decision Tree (DT), Generalized Linear Model Regression (GLMR) |
|
Model 4: Frequent Flyer Passenger Prediction |
Classification |
Decision Tree (DT), Support Vector Machine (SVM) |
Table 9-3 shows the dwd_cust_mnng data mining result table.
Table 9-3 dwd_cust_mnng Data Mining Result Table
| Column Name | Data Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
month code, when model was trained |
|
FF_CARD_KEY |
NUMBER(38) |
frequent flyer card key, to uniquely identify frequent flyer passengers |
|
CUST_SGMNT_CD |
VARCHAR2(30) |
Customer segment code |
|
CUST_LYLTY_DT_PRED |
VARCHAR2(30) |
Customer loyalty prediction using decision tree |
|
CUST_LYLTY_DT_ND_NBR |
VARCHAR2(30) |
Customer loyalty prediction node number in tree using decision tree |
|
CUST_LYLTY_SVM_PRED |
VARCHAR2(30) |
Customer loyalty prediction using support vector machine |
|
CUST_LYLTY_SVM_PROB |
NUMBER(10,8) |
Customer loyalty prediction probability using support vector machine |
|
LTV_BAND_CD |
VARCHAR2(30) |
Life time value band code |
|
LTV_VALUE |
NUMBER(16,2) |
Life time value, it is a continuous value |
|
LT_SRVVL_CD |
VARCHAR2(30) |
Life time survival value code |
|
LT_SRVVL_VALUE |
NUMBER(16,2) |
Life time survival value, it is a continuous value |
Table 9-4 shows the dwr_cust_sgmnt result table.
Table 9-4 dwr_cust_sgmnt Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
CUST_SGMNT_KEY |
NUMBER(30) |
Customer segmentation key, generated by a sequence |
|
CUST_SGMNT_CD |
VARCHAR2(30) |
Customer segmentation code |
|
CUST_SGMNT_NAME |
VARCHAR2(50) |
Customer segmentation name |
|
CUST_SGMNT_DESC |
VARCHAR2(50) |
Customer segmentation description |
|
CUST_SGMNT_PROFILE |
VARCHAR2(4000) |
Customer segmentation profile, formed by mean & mode values of all attributes of customers in a segment |
|
SGMNT_DISPRSN |
NUMBER(10,4) |
Segment dispersion, which tells how similar the customers in a segment are. |
|
SPRTNG_REC_CNT |
NUMBER(16) |
Supporting record count, which is number of customers in a segment |
|
TREE_LVL |
NUMBER(4) |
Level of tree in hierarchical k-means clustering. |
|
IS_LEAF_IND |
CHAR(1) |
Leaf level indicator |
Table 9-5 shows the dwd_cust_lylty_dt_rules data mining result table.
Table 9-5 dwd_cust_lylty_dt_rules Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
Month code, when model was trained |
|
ANALYSIS_NAME |
VARCHAR2(100) |
Name of the analysis |
|
MODEL_NAME |
VARCHAR2(100) |
Mining model name |
|
RULE_ID |
NUMBER(10) |
Rule identifier number |
|
PERFORMANCE_MEASURE |
VARCHAR2(100) |
Target measure column name |
|
MEASURE_VALUE |
VARCHAR2(100) |
Target measure value |
|
PROFILE |
VARCHAR2(1000) |
Profile of customer, formed by concatenating decisions at each tree node |
|
IS_LEAF |
CHAR(10) |
Leaf level indicator |
|
PREDICTION_COUNT |
NUMBER(10) |
Number of customers, who fall under this node, with prediction same as prediction of the node |
|
RECORD_COUNT |
NUMBER(10) |
Number of customers, who fall under this node |
|
SUPPORT |
NUMBER(10,5) |
Ratio of record_count to the total number of customers |
|
CONFIDENCE |
NUMBER(10,5) |
Ratio of prediction_count to record_count |
|
RULE_DISPLAY_ORDER |
NUMBER(10) |
Rule display order |
Table 9-6 shows the dwd_cust_lylty_svm_factor data mining result table.
Table 9-6 dwd_cust_lylty_svm_factor Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
Month code, when model was trained |
|
TARGET_VALUE |
VARCHAR2(100) |
Target measure value |
|
ATTRIBUTE_NAME |
VARCHAR2(4000) |
|
|
ATTRIBUTE_SUBNAME |
VARCHAR2(4000) |
|
|
ATTRIBUTE_VALUE |
VARCHAR2(4000) |
|
|
COEFFICIENT |
NUMBER |
Table 9-7 shows the dwd_cust_ltv_dt_rules data mining result table.
Table 9-7 dwd_cust_ltv_dt_rules Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
|
|
ANALYSIS_NAME |
VARCHAR2(100) |
Name of the analysis |
|
MODEL_TYPE |
VARCHAR2(100) |
Type of mining model |
|
MODEL_NAME |
VARCHAR2(100) |
Mining model name |
|
RULE_ID |
NUMBER(10) |
Rule identifier number |
|
PERFORMANCE_MEASURE |
VARCHAR2(100) |
Target measure column name |
|
MEASURE_VALUE |
VARCHAR2(100) |
Target measure value |
|
PROFILE |
VARCHAR2(1000) |
Profile of non-frequent flyer passenger, formed by concatenating decisions at each tree node |
|
IS_LEAF |
CHAR(10) |
Leaf level indicator |
|
PREDICTION_COUNT |
NUMBER(10) |
Number of non-frequent flyer passengers, who fall under this node, with prediction same as prediction of the node |
|
RECORD_COUNT |
NUMBER(10) |
Number of non-frequent flyer passengers, who fall under this node |
|
SUPPORT |
NUMBER(10,5) |
Ratio of record_count to the total number of non-frequent flyer passengers |
|
CONFIDENCE |
NUMBER(10,5) |
Ratio of prediction_count to record_count |
|
RULE_DISPLAY_ORDER |
NUMBER(10) |
Rule display order |
Table 9-8 shows the dwd_cust_ltv_svm_factor data mining result table.
Table 9-8 dwd_cust_ltv_svm_factor Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
Month code, when model was trained |
|
MODEL_NAME |
VARCHAR2(100) |
Mining model name |
|
TARGET_COLUMN |
VARCHAR2(100) |
Target measure value |
|
TARGET_COLUMN_ABBR |
VARCHAR2(30) |
Target measure value abbreviation |
|
ATTRIBUTE_NAME |
VARCHAR2(4000) |
Customer attribute name |
|
ATTRIBUTE_SUBNAME |
VARCHAR2(4000) |
Customer attribute sub name, if any. |
|
ATTRIBUTE_VALUE |
VARCHAR2(4000) |
Value of Customer attribute |
|
COEFFICIENT |
NUMBER |
Attribute coefficient predicted by support vector machine algorithm |
Table 9-9 shows the dwd_ffp_pred_dt_rules data mining result table.
Table 9-9 dwd_cust_lylty_svm_factor Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
|
|
ANALYSIS_NAME |
VARCHAR2(100) |
Name of the analysis |
|
MODEL_TYPE |
VARCHAR2(100) |
Type of mining model |
|
MODEL_NAME |
VARCHAR2(100) |
Mining model name |
|
RULE_ID |
NUMBER(10) |
Rule identifier number |
|
PERFORMANCE_MEASURE |
VARCHAR2(100) |
Target measure column name |
|
MEASURE_VALUE |
VARCHAR2(100) |
Target measure value |
|
PROFILE |
VARCHAR2(1000) |
Profile of non-frequent flyer passenger, formed by concatenating decisions at each tree node |
|
IS_LEAF |
CHAR(10) |
Leaf level indicator |
|
PREDICTION_COUNT |
NUMBER(10) |
Number of non-frequent flyer passengers, who fall under this node, with prediction same as prediction of the node |
|
RECORD_COUNT |
NUMBER(10) |
Number of non-frequent flyer passengers, who fall under this node |
|
SUPPORT |
NUMBER(10,5) |
Ratio of record_count to the total number of non-frequent flyer passengers |
|
CONFIDENCE |
NUMBER(10,5) |
Ratio of prediction_count to record_count |
|
RULE_DISPLAY_ORDER |
NUMBER(10) |
Rule display order |
Table 9-10 shows the dwd_ffp_pred_svm_factor data mining result table.
Table 9-10 dwd_ffp_pred_svm_factor Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
Month code, when model was trained |
|
ATTRIBUTE_NAME |
VARCHAR2(4000) |
non-frequent flyer passenger attribute name |
|
ATTRIBUTE_SUBNAME |
VARCHAR2(4000) |
non-frequent flyer passenger attribute sub name, if any. |
|
ATTRIBUTE_VALUE |
VARCHAR2(4000) |
Value of non-frequent flyer passenger attribute |
|
COEFFICIENT |
NUMBER |
Attribute coefficient predicted by support vector machine algorithm |
Table 9-11 shows the dwd_non_ffp_mnng data mining result table.
Table 9-11 dwd_non_ffp_mnng Data Mining Result Table
| Name | Type | Description |
|---|---|---|
|
MO_CD |
VARCHAR2(30) |
Month code, when model was trained |
|
TRVL_DOC_NBR |
VARCHAR2(30) |
Travel document number, which is to be shown by passengers for identification |
|
FST_NM |
VARCHAR2(40) |
non-frequent flyer passenger first name |
|
LAST_NM |
VARCHAR2(40) |
non-frequent flyer passenger last name |
|
FFP_DT_PRED |
VARCHAR2(10) |
Prediction of "would be frequent flyer passengers" among non-frequent flyer passenger using decision tree |
|
FFP_DT_ND_NBR |
VARCHAR2(30) |
Node number of prediction in decision tree |
|
FFP_SVM_PRED |
VARCHAR2(10) |
Prediction of "would be frequent flyer passengers" among non-frequent flyer passenger using Support vector machine |
|
FFP_SVM_PROB |
NUMBER(10,8) |
Prediction probability of "would be frequent flyer passengers" among non-frequent flyer passenger using support vector machine |
The business problem is to group customers into generally homogeneous groups based on customer demographics, flown history, and so on. Business Analysts can look into each segment to further understand the customer group discovered by the model and name each segment.
The customers are clustered using Clustering algorithm - K-Means. The discovered clustering rules draw the profile of customers.
The following table shows the columns identified from the 3NF layer (Base, Reference) and analytical layer (Derived, Reference) of data warehouse as source for K-Means model.
Table 9-12 shows the Materialized View, dmv_cust_profile_src, columns identified as input source variables for the model.
Table 9-12 Customer Segmentation Source: dmv_cust_profile_src
| Column Name | Description |
|---|---|
|
ff_card_key |
Frequent flyer card key, a unique identifier generated by sequence |
|
ff_nbr |
Frequent flyer identification number, a business key |
|
clndr_month_key |
Calendar month key of the data collected |
|
gndr |
Gender of a frequent flyer |
|
income_lvl |
Income level of a frequent flyer |
|
marital_sts |
Marital status of a frequent flyer |
|
edu |
Education of a frequent flyer |
|
occupation |
Occupation of a frequent flyer |
|
age |
Age of a frequent flyer |
|
card_carr |
|
|
carr_cd |
|
|
rqst_typ |
Request type made by a frequent flyer |
|
sts_cd |
Status code |
|
airl_mbshp_lvl |
Airline member ship level of a frequent flyer |
|
airl_prorty_cd |
Frequent flyer airlines priority code |
|
airl_tier_desc |
Airline tier description |
|
airl_cust_value |
Airline customer value |
|
alan_membr_lvl |
|
|
all_airl_prorty_cd |
|
|
alan_tier_desc |
|
|
cert_nbr |
|
|
alanc_cd |
|
|
stk_cntrl_nbr |
|
|
cls_bef_upgrd |
Booking class of before upgrade, if there is any upgrade |
|
miles_cr_ind |
Miles indicator |
|
city_nm |
Frequent flyer city name |
|
ctry_nm |
Frequent flyer country name |
|
cont_nm |
Frequent flyer continent name |
|
sales_chnl_id |
Sales channel indicator, through which frequent flyer makes booking |
|
tot_ernd_miles_amt |
Total miles amount earned by a frequent flyer |
|
mo_ernd_miles_amt |
Miles amount earned by a frequent flyer in the "clndr_month_key" |
|
tot_redeem_miles_amt |
Total miles amount redeemed by a frequent flyer |
|
mo_redeem_miles_amt |
Miles amount redeemed by a frequent flyer in the "clndr_month_key" |
|
tot_expired_miles_amt |
Total miles amount expired of a frequent flyer |
|
mo_expired_miles_amt |
Miles amount expired of a frequent flyer in the "clndr_month_key" |
|
tot_conf_bkgs |
Total number of confirmed bookings among bookings made by a frequent flyer |
|
mo_conf_bkgs |
number of confirmed bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_grp_bkgs |
Total number of group bookings among bookings made by a frequent flyer |
|
mo_grp_bkgs |
number of group bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_night_bkgs |
Total number of night bookings among bookings made by a frequent flyer |
|
mo_night_bkgs |
number of night bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_dead_bkgs |
Total number of dead bookings among bookings made by a frequent flyer |
|
mo_dead_bkgs |
number of dead bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_bsns_cls_bkgs |
Total number of business class bookings among bookings made by a frequent flyer |
|
mo_bsns_cls_bkgs |
number of business class bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_ecnmy_cls_bkgs |
Total number of economy class bookings among bookings made by a frequent flyer |
|
mo_ecnmy_cls_bkgs |
number of economy class bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_cdsh_bkgs |
Total number of code share bookings among bookings made by a frequent flyer |
|
mo_cdsh_bkgs |
number of code share bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_brdng_cnt |
Total number of times a frequent flyer boarded flight |
|
mo_brdng_cnt |
number of times a frequent flyer boarded flight in the "clndr_month_key" |
|
tot_open_bkgs |
Total number of open bookings among bookings made by a frequent flyer |
|
mo_open_bkgs |
number of open bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_info_bkgs |
Total number of info bookings among bookings made by a frequent flyer |
|
mo_info_bkgs |
number of info bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_avg_days_btwn_bkg_dprtr |
Total average days between booking made and departure of flight |
|
mo_avg_days_btwn_bkg_dprtr |
average days between booking made and departure of flight in the "clndr_month_key" |
|
tot_bkgs_at_rdy_to_leave |
Total number bookings made at ready to leave by a frequent flyer |
|
mo_bkgs_at_rdy_to_leave |
number bookings made at ready to leave by a frequent flyer in the "clndr_month_key" |
|
tot_cpn_amt |
Total coupon amount |
|
mo_cpn_amt |
Coupon amount in the "clndr_month_key" |
The materialized view, dmv_cust_profile_src is derived from following tables:
dwb_lylty_acct_bal_hist_h
dwd_bkg_fact
dwm_frequent_flyer
dwm_clndr
dwm_geogry
dwc_etl_parameter
The mined rules are saved into following target table:
dwr_cust_sgmnt
The scoring results are saved into following column(s) of target table, dwd_cust_mnng.
dwd_cust_mnng. cust_sgmnt_cd
The business problem is to build a profile of customers to explain impact of customers' characteristics on their loyalty to Airlines. Using Oracle Data Mining, the KPIs are modeled using two popular Classification Algorithms - Decision Tree (DT) and Support Vector Machines (SVM). This analysis identifies which key attributes of a customer influence his loyalty to Airlines. This model mines the various attributes of customers.
The output from the model is twofold:
The discovered rules provide correlation between the customer loyalty to Airlines and Customer attributes.
The prediction can be made on current base customer's data for the next month/quarter using the model built on historical data.
The rules are designed to be generated monthly/quarterly. Therefore, one SVM and one DT models are created every month across all customers using the following variables as targets:
Target variable for Decision Tree (DT) is:
Passenger Loyalty Code, cust_lylty_cd
Target variable for Support Vector Machines (SVM) is:
Passenger Loyalty Code, cust_lylty_cd
Customer Loyalty model use dmv_cust_loyalty_src materialized view as source. This materialized has all columns from dmv_cust_profile_src materialized view along with following columns:
cust_rfmp_cd
cust_lylty_score
cust_lylty_cd
The mined rules are saved into following target table(s):
dwd_cust_lylty_dt_rules
dwd_cust_lylty_svm_factor
The scoring results are saved into following column(s) of target table, dwd_cust_mnng
cust_lylty_dt_pred
cust_lylty_dt_nd_nbr
cust_lylty_svm_pred
cust_lylty_svm_prob
The business problem is to identify/predict the customers who are likely to represent the highest value of revenue over their life time based on criteria such as customer demographic information, flown history, and service quality and so on.
This analysis identifies which key attributes of a customer influence his or her Life Time Value. Life Time Value is continuous value (total revenue contributed by the customer). The Life Time Value is converted into categorical values using standard binning operations. The categorical variables are modeled as a classification model to identify or predict the impact of various independent variables (attributes) on the dependent target variable (KPI - categorical). Using Oracle Data Mining (11g Release 2), the target variables, Categorical Life Time Value and Life Time Survival Value are modeled using classification algorithm, Decision Tree (DT).
The continuous Life Time Value and Life Time Survival Value are modeled as regression models using regression algorithm, for Generalized Linear Model Regression (GLMR).
The mining models are built every month using the customer latest data and the mining models are applied on current base customers' data to predict which customer is likely to represent the highest value of revenue over their life time.
The output from the model is two-fold:
The discovered rules to outline the profile of customers who are most likely to represent the highest value of revenue over their life time.
The prediction can be made on customer data once the model was trained.
The rules are designed to be generated monthly. Therefore, two GLMR and two DT models are created every month across all the customers using the following variables as targets:
Target variables for Decision Tree (DT) are:
Life Time Value Code, cust_ltv_bnd
Life Time Survival Value Code
Target variables for Generalized Linear Model Regression (GLMR) are:
Life Time Value, tot_cpn_amt
Life Time Survival Value
Customer Loyalty model use dmv_cust_ltv_src materialized view as source. This materialized has all columns from dmv_cust_profile_src materialized view along with following column(s):
cust_ltv_bnd
The mined rules are saved into following target table(s):
dwd_cust_ltv_dt_rules
dwd_cust_ltv_svm_factor
The scoring results are saved into following column(s) of target table, dwd_cust_mnng
ltv_band_cd
ltv_value
lt_srvvl_cd
lt_srvvl_value
The business problem is identify/predict the Non-FFP (Non Frequent Flyer Passengers) passengers who are likely to become FFP passenger based on their demographic attributes, flight usage, revenue per user, and so on.
This analysis also identifies which key attributes of a Non-FFP passenger are important in predicting whether Non-FFP passenger would likely to become FFP. The training data would be mix of Non-FFP passengers and FFP passengers. FFP passengers are those who became FFP from Non-FFP in the last 1 year time period. The target variable is FFP_IND; it is 1 for FFP passengers and 0 for Non-FFP passengers. The target variable FFP_IND is modeled using classification algorithms, Support Vector Machines (SVM) and Decision Tree (DT).
The two mining models are built every month using latest FFP and Non-FFP data and the mining models are applied on current Non-FFP passengers to predict who would likely to become FFP passenger.
The output from the model is two-fold:
The discovered rules outline the profile of Non-FFP passengers who would likely to become FFP.
The prediction can be made on current Non-FFP passengers once the model was trained.
The rules are designed to be generated monthly. Therefore, one SVM and one DT models are created every month using the following variable as target:
Frequent Flyer Passenger Indicator, ff_ind
The following table shows the columns identified from the 3NF layer (Base, Reference) and analytical layer (Derived, Reference) of data warehouse as source for K-Means model.
Table 9-13 shows the Materialized View: dmv_ffp_pred_src, columns identified as input source variables for the model.
Table 9-13 Frequent Flyer Passenger Prediction Source: dmv_ffp_pred_src
| Column Name | Description |
|---|---|
|
case_id |
Unique identifier |
|
trvl_doc_typ |
Travel document type |
|
trvl_doc_nbr |
Travel document number, which is to be shown by passengers for identification |
|
ff_nbr |
Frequent flyer number, a business key |
|
idfn_cd |
Identification code |
|
pax_typ |
Passenger type |
|
typ_cd |
Type code |
|
gndr |
Gender of a passenger |
|
age |
age of a passenger |
|
curr_sts |
Current status of a passenger |
|
ff_ind |
Frequent flyer indicator, it is 1 for passengers who are ffps now, but were non-ffps in past, 0 for current non-ffps |
|
clndr_month_key |
Calendar month key of the data collected |
|
sales_chnl_id |
Sales channel indicator, through which passenger makes booking |
|
tot_conf_bkgs |
Total number of confirmed bookings among bookings made by a frequent flyer |
|
mo_conf_bkgs |
number of confirmed bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_grp_bkgs |
Total number of group bookings among bookings made by a frequent flyer |
|
mo_grp_bkgs |
number of group bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_night_bkgs |
Total number of night bookings among bookings made by a frequent flyer |
|
mo_night_bkgs |
number of night bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_dead_bkgs |
Total number of dead bookings among bookings made by a frequent flyer |
|
mo_dead_bkgs |
number of dead bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_bsns_cls_bkgs |
Total number of business class bookings among bookings made by a frequent flyer |
|
mo_bsns_cls_bkgs |
number of business class bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_ecnmy_cls_bkgs |
Total number of economy class bookings among bookings made by a frequent flyer |
|
mo_ecnmy_cls_bkgs |
number of economy class bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_cdsh_bkgs |
Total number of code share bookings among bookings made by a frequent flyer |
|
mo_cdsh_bkgs |
number of code share bookings among bookings made by a frequent flyer in the "clndr_month_key" |
|
tot_avg_days_btwn_bkg_dprtr |
Total average days between booking made and departure of flight |
|
mo_avg_days_btwn_bkg_dprtr |
average days between booking made and departure of flight in the "clndr_month_key" |
|
tot_bkgs_at_rdy_to_leave |
Total number bookings made at ready to leave by a frequent flyer |
|
mo_bkgs_at_rdy_to_leave |
number bookings made at ready to leave by a frequent flyer in the "clndr_month_key" |
|
tot_cpn_amt |
Total coupon amount |
|
mo_cpn_amt |
Coupon amount in the "clndr_month_key" |
The mined rules are saved into following target table(s):
dwd_ffp_pred_dt_rules
dwd_ffp_pred_svm_factor
The scoring results are saved into following column(s) of target table, dwd_non_ffp_mnng
ffp_dt_pred
ffp_dt_nd_nbr
ffp_svm_pred
ffp_svm_prob
|
 Copyright © 2010, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
