10 Oracle Communications Data Model Data Mining Models

This chapter provides reference information about the data mining models provided with Oracle Communications Data Model.

This chapter includes the following sections:

About Data Mining in Oracle Communications Data Model
Oracle Communications Data Model Mining Result Tables
Model 1: Prepaid Churn Prediction
Model 2: Postpaid Churn Prediction
Model 3: Customer Profiling
Model 4: Targeted Promotion
Model 5: Customer Life Time Value
Model 6: Customer Life Time Survival Value
Model 7: Customer Sentiment
Oracle Communications Data Model Mining Setting Tables
Oracle Communications Data Model Data Mining Related Tables
Data Mining Package Customer Life Time Value Computation

About Data Mining in Oracle Communications Data Model

Oracle Communications Data Model data mining includes data mining intra-ETL package, data mining core package, source views, target tables, support tables, and setting tables. The source views are defined on source derived tables. These source views are used to train the models. The target tables contain the mining model rules, mining prediction results. Data mining core package uses source views as training data, builds, tests, and applies mining models. Mining target tables are populated with mining model rules and prediction results. The data in the target tables can be presented in reports.

Note:

Modified or new mining models are not supported by Oracle as part of the standard Oracle Communications Data Model support. However, it is recommended that you adapt the supplied mining models to your needs or create new mining models, as required.

As shown in Table 10-1, the Oracle Communications Data Model mining models use the specified algorithms for the specific problem.

Table 10-1 Oracle Communications Data Model Algorithm Types Used by Model

Model	Algorithms Used by Data Mining Model
Model 1: Prepaid Churn Prediction	Decision Tree(DT), Support Vector Machine(SVM)
Model 2: Postpaid Churn Prediction	Decision Tree(DT), Support Vector Machine(SVM)
Model 3: Customer Profiling	K-Means (KM)
Model 4: Targeted Promotion	Support Vector Machine (SVM)
Model 5: Customer Life Time Value	Generalized Linear Models(GLM)
Model 6: Customer Life Time Survival Value	Generalized Linear Models (GLM)
Model 7: Customer Sentiment	Support Vector Machine (SVM)

For more information on mining algorithms, see Oracle Data Mining Concepts and Oracle Data Mining Application Developer's Guide.

Understanding the Mining Model Architecture

Figure 10-1 shows the architecture of data mining in Oracle Communications Data Model. Oracle Communications Data Model schema, ocdm_sys, includes the following:

Mining Model Source Views: Views defined source derived tables, DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM. These views are used to train mining models. Source data is selected as of training day parameter provided in DWC_ETL_PARAMETER table for "BUILD-MINING-MODELS" process.
Mining Model Apply Views: Views defined source derived tables, DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM. These views are used to apply trained mining models. Apply data is selected as of apply day parameter provided in DWC_ETL_PARAMETER table for "BUILD-MINING-MODELS" process.
Mining Model Support Tables: Mining algorithm settings for different algorithms used in Oracle Communications Data Model are stored in the support tables. These support tables start with "DM". Building a mining model creates few tables and views which start with "DM$".

Note:

Do not delete tables and views that start with "DM" and "DM$". Deleting "DM$" tables and views would also delete the trained mining model.

Mining Model Target Tables: Mining model target tables used for storing mining model rules and prediction results. Mining model rules are generated from the trained model and predictions results are produced when a trained model is applied on apply data.
Mining Model Core Package: This is the core package for Oracle Communications Data Model data mining. Each mining model has separate procedure in this package. Each procedure builds, tests, and applied mining model. It uses source views as training data, and applies trained model on apply views.

Figure 10-1 Oracle Communications Data Model Mining Packages Tables and Views

Description of "Figure 10-1 Oracle Communications Data Model Mining Packages Tables and Views"

Refreshing the Oracle Communications Data Model Mining Model

Over time, the customer information and the customer behavior may change. Therefore, you may want to refresh the trained mining models based on the latest customer and usage data:

Make sure the source derived tables DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM are loaded with latest data.
Update "from_date_etl" and "end_date_etl" columns for "BUILD-MINING-MODELS" process in DWC_ETL_PARAMETER table.
- "from_date_etl" - Training data is selected as of this date
- "to_date_etl" - Apply data is selected as of this date
Refresh mining source views and apply views to get training data and apply data for the given training day and apply day:
```
exec pkg_mining_etl.refresh_mining_views(l_trnng_day_key,l_apply_day_key);
```
Build, test, and apply mining models. Make sure an intra-etl process is running, if not, start a process:
```
exec pkg_ocdm_mining.REFRESH_MODEL(l_apply_day_key,NULL);
```
For each mining procedure, an activity is created and the status of all mining activities are saved into DWC_INTRA_ETL_ACTIVITY table.

Oracle Communications Data Model Mining Result Tables

Table 10-2 Target Columns in DWD_CUST_DNA Data Mining Result Table

Name	Data Type	Description
`CLSTR_SGMNT_CD`	`VARCHAR2(8)`	The k-Means algorithm divides the set of all customers into segments. This value identifies the segment that the customer belongs to.
`CMPTD_LTV_VALUE`	`NUMBER`
`LT_SRVVL_CD`	`VARCHAR2(120)`	The band code of Customer Survival period (Life Expectancy), predicted by Life_Exp (GLMR) Model.
`LTV_BAND_CD`	`VARCHAR2(120)`	The band code of customer lifetime value, predicted by LTV Generalized Linear Models Regression. For more information, see Oracle Data Mining Concepts.
`MANUAL_SNTMNT_CTGRY`	`VARCHAR2(120)`	The manual score applied by end user. The end user generates this model. For example, an employee from the operator might generate this model. Usually this is the call center agent. For example, when the message is recorded, there could be a manual tag associated with the message indicating that the customer is happy or upset.
`PRDCT_CHURN_DT_IND`	`CHAR(1)`	Boolean value whether customer will churn in next three months according to DT model.
`PRDCT_CHURN_DT_ND_NBR`	`VARCHAR2(30)`	The ID of the node in the decision tree where the customer is assigned.
`PRDCT_CHURN_DT_PROB`	`NUMBER`	The probability value of how likely customer will churn in next 3 months. This is the probability that the DT prediction is correct.
`PRDCT_CHURN_SVM_IND`	`CHAR(1)`	Boolean value whether customer will churn in next three months according to SVM model.
`PRDCT_CHURN_SVM_PROB`	`NUMBER`	The probability value of how likely customer will churn in next 3 months. This is the probability that the SVM prediction is correct.
`PRDCT_LT_SRVVL_VAL`	`NUMBER(22,7)`	The value of Customer Survival period (Life Expectancy), predicted by Life_Exp (GLMR) Mode.
`PRDCT_LTV_VALUE`	`NUMBER`	The real value of Customer Lifetime value, predicted by LTV (GLMR) Mode.
`SNTMNT_CTGRY_CD`	`VARCHAR2(120)`	The customer sentiment category detected by Customer sentiment model (SVM + Text). This is an SVM model on transformed TEXTs (transformed into a words matrix).
`SNTMNT_PROB`	`NUMBER`	The probability of which customer is in possible model (Happy). This is the probability that customer is happy with their service. For example, a value of 60% means there is 60% chance that customer is happy with the service and a 40% chance that customer is not happy.

Table 10-3 DWD_CUST_PROD_AFFLTN Data Mining Result Table

Name	Data Type	Description
`AFFLTN_PROB`	`NUMBER(20,18)`	The likelihood, predicted by the SVM model, that the customer will purchase the product.
`BUY_IND`	`CHAR(1)`	Boolean value to indicate whether customer may purchase the product. This indicates that a value 1 is BUY and a value of 0 is "NOT to BUY".
`CUST_CD`	`VARCHAR2(120)`	Customer natural key to identify the customer.
`MO_KEY`	`NUMBER(30)`	Month key for which the target promotion model was trained.
`PROD_CD`	`VARCHAR2(120)`	The product code which was predicted against. This is target product for promotion.

Table 10-4 DWD_CHRN_SVM_FACTOR Data Mining Result Table

Name	Data Type	Description
`ACCT_TYP_CD`	`VARCHAR2(120)`	Account Type Code. For example: Prepaid, Postpaid
`ATTRIBUTE_NAME`	`VARCHAR2(4000)`	Name of the factor.
`ATTRIBUTE_SUBNAME`	`VARCHAR2(4000)`	Subname of the factor if there is any. For example, if the ATTRIBUTE_NAME has the value, "Payment_Method", then the ATTRIBUTE_SUBNAME could be and of the following: Debit_Card Cash Each ATTRIBUTE_SUBNAME has a different weight, coefficient, in the model.
`ATTRIBUTE_VALUE`	`VARCHAR2(4000)`	Value of the factor, if there is any. For example, for payment method, value of "cash" and "direct debit" might have different influence and ranking.
`COEFFICIENT`	`NUMBER`	Importance of the factor. The factors are ranked according to this value.
`MODEL_NAME`	`VARCHAR2(120)`	Churn model name

Table 10-5 DWD_PROMO_SVM_FACTOR Data Mining Result Table

Name	Data Type	Description
`ATTRIBUTE_NAME`	`VARCHAR2(4000)`	Name of the factor.
`ATTRIBUTE_SUBNAME`	`VARCHAR2(4000)`	Subname of the factor if there is any. For example, if the ATTRIBUTE_NAME has the value, "Payment_Method", then the ATTRIBUTE_SUBNAME could be and of the following: Debit_Card Cash Each ATTRIBUTE_SUBNAME has a different weight, coefficient, in the model.
`ATTRIBUTE_VALUE`	`VARCHAR2(4000)`	Value of the factor, if there is any. For example, for payment method, value of "cash" and "direct debit" might have different influence and ranking.
`COEFFICIENT`	`NUMBER`	Importance of the factor. The factors are ranked according to this value.
`MO_KEY`	`NUMBER(30)`	Month key for which the target promotion model was trained
`PROD_CD`	`VARCHAR2(50)`	The product code which was predicted against. This is target product for promotion.

Table 10-6 DWR_CUST_DT_NODE Data Mining Result Table

Name	Data Type	Description
`ACCT_TYP_CD`	`VARCHAR2(120)`	Account Type Code. For example: Prepaid, Postpaid
`CONFIDENCE`	`NUMBER`	Ratios of prediction_count to record_count
`IS_LEAF`	`VARCHAR2(10)`	Indicates whether the node is a leaf indicator. The prediction of lead node is the final prediction
`MODEL_NAME`	`VARCHAR2(120)`	Churn model name
`NODE_ID`	`VARCHAR2(50)`	Decision tree node number
`PREDICTION`	`NUMBER`	Prediction for the current node. If number of customers predicted to churn is higher than number of customers to retain, then this would be 1, otherwise it would be 0
`PREDICTION_COUNT`	`NUMBER`	Number of customers predicted to churn under the current node
`RECORD_COUNT`	`NUMBER`	Number of customer under the current node
`SUPPORT`	`NUMBER`	Ration of record_count to total number of customers

Table 10-7 DWD_CHRN_SVM_ROC Data Mining Result Table

Name	Data Type	Description
`ACCT_TYP_CD`	`VARCHAR2(120)`	Account Type Code. For example, Prepaid, Postpaid
`BASE_BENCHMARK_PERCENT`	`NUMBER`	Base Benchmark Percentage
`COST_THRESHOLD`	`NUMBER`	Cost Threshold
`GAIN_CUMULATIVE`	`NUMBER`	Cumulative Gain
`LIFT_CUMULATIVE`	`NUMBER`	Cumulative Lift
`LIFT_QUANTILE`	`NUMBER`	Quantile Lift
`MAX_SVM_TO_CHRN_PROB`	`NUMBER`	Maximum Churn Probability
`MIN_SVM_TO_CHRN_PROB`	`NUMBER`	Minimum Churn Probability
`MODEL_NAME`	`VARCHAR2(120)`	Churn Model Name
`NON_TARGETS_CUMULATIVE`	`NUMBER`	Cumulative Non-targets
`PERCENTAGE_RECORDS_CUMULATIVE`	`NUMBER`	Cumulative Percentage Records
`PRED_QUANTILE_TARGET_COUNT`	`NUMBER`	Predicted Target Count of the Quantile
`QUANTILE_NUMBER`	`NUMBER`	Quantile number
`QUANTILE_TARGET_COUNT`	`NUMBER`	Target Count of the current Quantile
`QUANTILE_TOTAL_COUNT`	`NUMBER`	Total Count in the current Quantile
`TARGET_DENSITY`	`NUMBER`	Target Density
`TARGET_DENSITY_CUMULATIVE`	`NUMBER`	Cumulative Target Density
`TARGETS_CUMULATIVE`	`NUMBER`	Cumulative Targets
`TEST_DATE`	`DATE`	Date when mining model trained and applied

Table 10-8 DWD_PROMO_SVM_ROC Data Mining Result Table

Name	Data Type	Description
`ACCT_TYP_CD`	`VARCHAR2(120)`	Account Type Code. For example, Prepaid, Postpaid
`BASE_BENCHMARK_PERCENT`	`NUMBER`	Base Benchmark Percentage
`COST_THRESHOLD`	`NUMBER`	Cost Threshold
`GAIN_CUMULATIVE`	`NUMBER`	Cumulative Gain
`LIFT_CUMULATIVE`	`NUMBER`	Cumulative Lift
`LIFT_QUANTILE`	`NUMBER`	Quantile Lift
`MAX_SVM_TO_BUY_PROB`	`NUMBER`	Maximum Buy Probability
`MIN_SVM_TO_BUY_PROB`	`NUMBER`	Minimum Buy Probability
`MO_KEY`	`NUMBER(30)`	Month Key
`MODEL_NAME`	`VARCHAR2(120)`	Churn Model Name
`NON_TARGETS_CUMULATIVE`	`NUMBER`	Cumulative Non-targets
`PERCENTAGE_RECORDS_CUMULATIVE`	`NUMBER`	Cumulative Percentage Records
`PRED_QUANTILE_TARGET_COUNT`	`NUMBER`	Predicted Target Count of the Quantile
`PROD_CD`	`VARCHAR2(50)`	Product code
`QUANTILE_NUMBER`	`NUMBER`	Quantile number
`QUANTILE_TARGET_COUNT`	`NUMBER`	Target Count of the current Quantile
`QUANTILE_TOTAL_COUNT`	`NUMBER`	Total Count in the current Quantile
`TARGET_DENSITY`	`NUMBER`	Target Density
`TARGET_DENSITY_CUMULATIVE`	`NUMBER`	Cumulative Target Density
`TARGETS_CUMULATIVE`	`NUMBER`	Cumulative Targets
`TEST_DATE`	`DATE`	Date when mining model trained and applied

Model 1: Prepaid Churn Prediction

The prepaid churn prediction model identifies the characteristics of a prepaid customer likely to churn. When you apply the model you get a prediction of how likely a particular customer is to churn. This is based on customer information such as customer demographic information, service quality, recharge history, calling usage, interaction, and other factors. Using the patterns learned, the model can also perform the calculation over current customer base (called 'Apply') to predict which customers are mostly like to churn in next four months. With this knowledge, operators can initiate certain retention programs to reduce the customer churn rate. However, the churn prediction produces a likely to churn value. Further processing may be required to determine if it is desirable to retain a customer that is likely to churn. For example, you may only want to initiate retention programs for high value customers.

Prepaid Churner Definition

There are several levels to define churn, namely Customer, Account, and subscription. For some operators with only limited business line, customer and account churn at same time, while subscription is at a lower level. Customer can stop using some products (termination of subscription) while continue to use the other products. In later case, operator still has the customer and may promote other products in the future. However, if customer completely stopped using any products from the operator, it is very difficult for operator to bring customer back.

In Oracle Communications Data Model, the churn is defined at Customer Level, which is, a customer is recognized as a churner only when he stop using any product from the operator.

If customers churn at a given month, the model may receive the data only three months after the actual Churn. So the time window should be adjusted.

Prepaid Churn Source

Table 10-9 shows the attributes identified from the Foundation Data Warehouse as input source variables for the prepaid churn model.

Table 10-9 DWV_PRPD_CUST_CHRN_SRC

Attribute	Description
`ACCPT_NWSLTR_IND`	Indicates whether customer accepts News Letter
`ADDR_LOC_CD`	Customer Address Location Code
`AGE_BND_CD`	Customer Age Band Code
`AGE_ON_NET_BND_CD`	Customer Age on Net Band Code
`AGE_ON_NET_NBR`	Customer Age on Net expressed in number of months
`ARPU_BND_CD`	Customer ARPU Band Code
`AVG_DRTN_BTWN_RCHRG`	Average duration between two recharges - in days
`BARNG_RSN_CD`	Customer Barring Reason Code
`BRDBND_IND`	Indicates whether Customer has Broadband connection
`BSNS_LEGAL_STAT_CD`	Legal Status Code of Enterprise Customers, for example, Public, Private
`CAR_DRVR_LICNS_IND`	Indicates whether customer has driver's license
`CAR_TYP_CD`	Car Type Code
`CHRN_IND`	Indicates whether a customer is a Churner or Non-churner
`CITY`	City
`CMPLNT_CNT_LAST_3MO`	Number of complaints made by customer in last 3 months
`CMPLNT_CNT_LAST_MO`	Number of complaints made by customer in this month
`CMPLNT_CNT_LFTM`	Number of complaints made by customer in his/her life span
`CNCT_ADDR_EFF_DT_NBR`	Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`CNTCT_ALLWD_IND`	Indicates whether customer allows to contact
`CNTRY`	Country
`COLL_ZIP_CD`	College ZIP Code
`CRDT_CTGRY_KEY`	Customer Credit Category
`CUST_BRANCH_CD`	Customer Branch Code
`CUST_CD`	Customer Identifier
`CUST_RVN_BND_CD`	Customer Revenue Band Code
`CUST_SCR_CD`	Customer SCR Code
`CUST_TYP_CD`	Customer Type Code
`DAYS_BFR_FIRST_RCHRG`	Days between first payment and first recharge
`DAYS_BFR_FIRST_USE`	Days between payment and first use
`DRPD_CALLS_CNT_LAST_3MO`	Number of dropped calls in last 3 months
`DRPD_CALLS_CNT_LAST_MO`	Number of dropped calls this month
`DRPD_CALLS_CNT_LFTM`	Number of dropped calls in customer life span
`DWLNG_OWNER`	Dwelling Owner
`DWLNG_STAT`	Dwelling Status
`DWLNG_SZ`	Dwelling Size
`DWLNG_TENR`	Dwelling Tenure
`DWLNG_TYP`	Dwelling Type
`DWNLD_DATA_LAST_3MO`	Data downloaded in KBs in last 3 months
`DWNLD_DATA_LAST_MO`	Data downloaded in KBs in last 1 month
`DWNLD_DATA_LFTM`	Data downloaded in KBs in lifetime
`ECNMCLY_ACTV_IND`	Indicates whether a customer is economically Active
`EDU_CD`	Education Qualification Code
`EFF_FROM_DT_NBR`	Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`END_OF_JB_AGRMNT_NBR`	End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`ESTMTD_ACQSTN_COST`	Customer Acquisition Estimated Cost
`ETHNCTY`	Customer Ethnicity
`ETHNIC_BCKGRND`	Customer Ethnic Background
`FORM_OF_EMPMNT`	Form of Employment
`GNDR_CD`	Individual Customer Gender Code
`HH_SZ`	Household Size
`HNGUP_CALLS_CNT_LAST_3MO`	Number of hangup calls in last 3 months
`HNGUP_CALLS_CNT_LAST_MO`	Number of hangup calls this month
`HNGUP_CALLS_CNT_LFTM`	Number of hangup calls in customer life span
`HOMTEL_IND`	Indicates whether Customer has Home Telephone
`IDD_IND`	Indicates whether Customer subscribed to International Direct Dialing
`JB_AGRMNT_TYP`	Job Agreement Type
`JB_CD`	Customer Job Code
`JB_POSN`	Customer Job Position
`LANG_CD`	Language Code
`LAST_INCOMING_USG_DT_NBR`	When was last incoming call occurred
`LAST_OUTGOING_USG_DT_NBR`	When was last outgoing call occurred
`LAST_RCHRG_AMT`	Last recharge amount
`LEGAL_TTL_TO_HSNG`	Legal Title to Housing
`LIFE_SPN`	Customer Life Span
`LIKE_SCP_IN_FCBK_IND`	Indicates whether customer likes Service Provide page in Facebook
`LVNG_AT_CURR_ADDR_SINCE_NBR`	Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`MAIL_ALWD_IND`	Indicates whether Customer allows Service Provider to send mail
`MAX_RCHRG_AMT`	Maximum recharge amount
`MIN_RCHRG_AMT`	Minimum recharge amount
`MMS_CNT_LAST_3MO`	MMSs sent in last 3 months
`MMS_CNT_LAST_MO`	MMSs sent in last 1 month
`MMS_CNT_LFTM`	MMSs sent over customer's lifetime
`MRTL_STAT_CD`	Individual Customer Marital Status Code
`MULT_PRTY_ROLE_IND`	Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider
`NAME_OF_WKPLC`	Name of Workplace
`NAME_PRFX`	Name Prefix
`NBR_CHLDRN_AT_COLL`	Total Number of Children at College
`NBR_CHLDRN_AT_SCHL`	Total Number of Children at School
`NBR_CHLDRN_AT_UNIV`	Total Number of Children at University
`NBR_OF_CHLDRN`	Total Number of Children
`NBR_OF_DPNDNT`	Total Number of Dependents
`NEW_ACCT_IND`	Indicates whether Customer is New
`NO_MTHS_CMPNY`	Number of months an Individual Customer is in his/her current Company
`NO_MTHS_POSN`	Number of months an Individual Customer is in his/her current Position
`NTNLTY_CD`	Customer Nationality Code
`OFFNET_AIRTIME_LAST_3MO`	Total offnet airtime in minutes in last 3 months
`OFFNET_AIRTIME_LAST_M`O	Total offnet airtime in minutes in last 1 month
`OFFNET_AIRTIME_LFTM`	Total offnet airtime in minutes in lifetime
`OFFNET_CALLS_LAST_3MO`	Number of offnet calls in last 3 months
`OFFNET_CALLS_LAST_MO`	Number of offnet calls in last 1 month
`OFFNET_CALLS_LFTM`	Number of offnet calls in lifetime
`ONNET_AIRTIME_LAST_3MO`	Total onnet airtime in minutes in last 3 months
`ONNET_AIRTIME_LAST_MO`	Total onnet airtime in minutes in last 1 month
`ONNET_AIRTIME_LFTM`	Total onnet airtime in minutes in lifetime
`ONNET_CALLS_LAST_3MO`	Number of onnet calls in last 3 months
`ONNET_CALLS_LAST_MO`	Number of onnet calls in last 1 month
`ONNET_CALLS_LFTM`	Number of onnet calls in lifetime
`OTH_DRVR_LICNS_IND`	Indicates whether customer has other vehicle License
`PAY_TV_IND`	Indicates whether Customer has Pay TV connection
`PLC_OF_BRTH`	Place of Birth
`PORT_IN_CNT`	Number of times the customer ported in
`PORT_IN_FROM`	Service Provide from who the customer ported out from
`PORT_OUT_CNT`	Number of times the customer ported out
`POSTCD_CD`	Postcard Code
`PREF_CNTCT_MDM`	Preferred Contact Method
`PREF_ENTMNT_IND`	Indicates whether customer given any Entitlement preference
`PREF_GVN_IND`	Indicates whether customer given any preference
`PREF_MSC_IND`	Indicates whether customer given any Music preference
`PREF_SPRT_IND`	Indicates whether customer given any sport preference
`PREF_TRVL_IND`	Indicates whether customer given any Travel preference
`PRMRY_STAT_CD`	Customer Primary Status Code
`PRMRY_STAT_RSN_CD`	Customer Primary Status Reason Code
`PRTY_AGE`	Customer Age expressed as number of years
`PRTY_TYP_CD`	Party Type Code, for example, Individual, Large Enterprise, Medium Enterprise, and so on.
`RLSD_CALLS_CNT_LAST_3MO`	Number of released calls in last 3 months
`RLSD_CALLS_CNT_LAST_MO`	Number of released calls this month
`RLSD_CALLS_CNT_LFTM`	Number of released calls in customer life span
`RMRK_CNT_LAST_3MO`	Numbers of remarks made against customer in last 3 months
`RMRK_CNT_LAST_MO`	Numbers of remarks made against customer in this month
`RMRK_CNT_LFTM`	Numbers of remarks made against customer in his/her life span
`SCHL_ZIP_CD`	School ZIP Code
`SCL_NTWK_USR_IND`	Indicates whether customer uses any Social Network site
`SL_CHNL_KEY`	Sales Channel
`SL_CHNL_RPRSTV_KEY`	Sales Channel Representative
`SMS_CNT_LAST_3MO`	SMSs sent in last 3 months
`SMS_CNT_LAST_MO`	SMSs sent in last 1 month
`SMS_CNT_LFTM`	SMSs sent over customer's lifetime
`SPRT_IND`	Indicates whether customer plays Sports
`SPRT_TYP_CD`	Sport Type Code
`SRC_OF_INCM`	Source of Income
`STATE`	State
`STRT_OF_EMPMNT_NBR`	Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`TOT_AGE_CHLDRN`	Total Age of Children
`TOT_CALL_CNT_LAST_3MO`	Total number of calls to Call center made by customer in last 3 months
`TOT_CALL_CNT_LAST_MO`	Total number of calls to Call center made by customer in this month
`TOT_CALL_CNT_LFTM`	Total number of calls to Call center made by customer in his/her life span
`TOT_CNTRBTN_LAST_MO`	Sum of total payments and total recharges made in the last one month
`TOT_CNTRBTN_LFTM`	Sum of total payments and total recharges made in customers life time
`TOT_DAYS_ACTVTY`	Total days of activity
`TOT_DAYS_ON_USE`	Total days on use
`TOT_DAYS_OUTGOING_ACTVTY`	Total days of outgoing activity
`TOT_DRTN_OF_USG`	Total durations of usage in customer life time
`TOT_EXP_AMT_EQVLT_VAL`	Total amount expired in customers life time
`TOT_HLD_DRTN_LAST_3MO`	Total hold duration in last 3 months in seconds
`TOT_HLD_DRTN_LAST_MO`	Total hold duration this month in seconds
`TOT_HLD_DRTN_LFTM`	Total hold duration in customer life span in seconds
`TOT_QUE_DRTN_LAST_3MO`	Total queue duration in the last 3 months in seconds
`TOT_QUE_DRTN_LAST_MO`	Total queue duration this month in seconds
`TOT_QUE_DRTN_LFTM`	Total queue duration in customer life span in seconds
`TOT_RCHRG_AMT_LAST_3MO`	Total recharge amount this month
`TOT_RCHRG_AMT_LAST_MO`	Total recharge amount in last 3 months
`TOT_RCHRG_AMT_LFTM`	Total recharge amount in customers life time
`TOT_RCHRG_CNT_LAST_3MO`	Number of recharges made in last 3 months
`TOT_RCHRG_CNT_LAST_MO`	Number of recharges made this month
`TOT_RCHRG_CNT_LFTM`	Number of recharges made in customer life time
`TOT_TALK_DRTN_LAST_3MO`	Total talk duration in the last 3 months in seconds
`TOT_TALK_DRTN_LAST_MO`	Total talk duration this month in seconds
`TOT_TALK_DRTN_LFTM`	Total talk duration in customer life span in seconds
`TOT_UTILZD_EQVLT_VAL`	Total utilized monetary value in customers life time
`UNIV_ZIP_CD`	University ZIP Code
`UPLD_DATA_LAST_3MO`	Data uploaded in KBs in last 3 months
`UPLD_DATA_LAST_MO`	Data uploaded in KBs in last 1 month
`UPLD_DATA_LFTM`	Data uploaded in KBs in lifetime
`VOI_INTERNAT_AIRTIME_LAST_3MO`	International voice air time including roaming in minutes in last three months
`VOI_INTERNAT_AIRTIME_LAST_MO`	International voice air time in minutes including roaming in last one month
`VOI_INTERNAT_AIRTIME_LFTM`	International voice air time in minutes including roaming over customer's life time
`VOI_NAT_AIRTIME_LAST_3MO`	National voice air time in minutes in last three months
`VOI_NAT_AIRTIME_LAST_MO`	National voice air time in minutes in last one month
`VOI_NAT_AIRTIME_LFTM`	National voice air time in minutes over customer's life time
`WRLS_IND`	Indicates whether Customer has Wireless Internet connection

Prepaid Churn Output Target Attribute: CHRN_IND

Data in DWV_PRPD_CUST_CHRN_SRC view is used to train prepaid churn models using two algorithms, Support Vector Machines and Decision Tree. Data in DWV_PRPD_CUST_CHRN_TST view is used to compare the performance of old and new mining models and the best model is chosen as final trained model. Data in DWV_PRPD_CUST_CHRN_APPLY view is scored with the final trained model. Scoring results are saved into DWD_CUST_DNA table with the following columns:

DWD_CUST_DNA.PRDCT_CHURN_SVM_IND
DWD_CUST_DNA.PRDCT_CHURN_SVM_PROB
DWD_CUST_DNA.PRDCT_CHURN_DT_IND
DWD_CUST_DNA.PRDCT_CHURN_DT_ND_NBR

Prepaid Churn Algorithm

The following two algorithms are used separately to solve prepaid churn classification mining problem:

Support Vector Machines (SVM)
Decision Tree

Prepaid Churn Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following are the prepaid churn setting tables:

DM_STNG_CHURN_SVM - For Support Vector Machines algorithm
DM_STNG_CHURN_DT - For Decision Tree algorithm

Model 2: Postpaid Churn Prediction

The postpaid churn prediction model identifies the characteristics of a postpaid customer likely to churn. When you apply the model you get a prediction of how likely a particular customer is to churn. This is based on customer information such as customer demographic information, service quality, tariff plan, calling usage, interaction, and other factors. Using the patterns learned, the model can also perform the calculation over current customer base (called 'Apply') to predict which customers are mostly like to churn in next four months. With this knowledge, operators can initiate certain retention programs to reduce the customer churn rate. However, the churn prediction produces a likely to churn value. Further processing may be required to determine if it is desirable to retain a customer that is likely to churn. For example, you may only want to initiate retention programs for high value customers.

Postpaid Churner Definition

In Oracle Communications Data Model, the churn was defined at Customer Level, which is, a customer is recognized as a churner only when he stop using any product from the operator.

If customers churn at a given month, the model may receive the data only three months after the actual Churn. In this case the time window should be adjusted.

Postpaid Churner Source

Table 10-10 shows the attributes identified from the Foundation Data Warehouse as input source variables for the postpaid churn model.

Table 10-10 DWV_PSTPD_CUST_CHRN_SRC

Attribute	Description
`ACCPT_NWSLTR_IND`	Indicates whether customer accepts News Letter
`ACCT_LFT_VAL_LAST_3MO`	Account Left Value in last three months
`ACCT_LFT_VAL_LAST_MO`	Account Left Value in last month
`ADDR_LOC_CD`	Customer Address Location Code
`AGE_BND_CD`	Customer Age Band Code
`AGE_ON_NET_BND_CD`	Customer Age on Net Band Code
`AGE_ON_NET_NBR`	Customer Age on Net expressed in number of months
`AGRMNT_CNT_LAST_3MO`	Number of Agreements in last 3 months
`AGRMNT_CNT_LAST_MO`	Number of Agreements in last 1 month
`AGRMNT_LFT_DAYS_LAST_3MO`	Agreement Left Days in last 3 months
`AGRMNT_LFT_DAYS_LAST_MO`	Agreement Left Days in last 1 month
`ARPU_BND_CD`	Customer ARPU Band Code
`BARNG_RSN_CD`	Customer Barring Reason Code
`BLLG_ADDR_EFF_DT_NBR`	Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`BNKRPT_STAT`	Bankrupt Status
`BNKRPT_STRT_DT_NBR`	Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`BRDBND_IND`	Indicates whether Customer has Broadband connection
`BSNS_LEGAL_STAT_CD`	Legal Status Code of Enterprise Customers. For example, Public, Private
`CAR_DRVR_LICNS_IND`	Indicates whether customer has Car Driving License
`CAR_TYP_CD`	Car Type Code
`CHRN_IND`	Indicates whether a customer is a Churner or Non-churner
`CITY`	City
`CMPLNT_CNT_LAST_3MO`	Number of complaints made by customer in last 3 months
`CMPLNT_CNT_LAST_MO`	Number of complaints made by customer in this month
`CMPLNT_CNT_LFTM`	Number of complaints made by customer in his/her life span
`CMPNY_EMP_SZ_BND_CD`	Enterprise Customer Employee Size Band Code
`CMPNY_RVN_BND_CD`	Enterprise Customer Revenue Band Code
`CMPNY_TYP_CD`	Company Type Code
`CNCT_ADDR_EFF_DT_NBR`	Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`CNTCT_ALLWD_IND`	Indicates whether customer allows to contact
`CNTRY`	Country
`COLL_ZIP_CD`	College ZIP Code
`CRDT_CTGRY_KEY`	Customer Credit Category
`CUST_BRANCH_CD`	Customer Branch Code
`CUST_CD`	Customer Identifier
`CUST_PYMT_RESPBL_IND`	Indicates whether customer is responsible for making payments
`CUST_RVN_BND_CD`	Customer Revenue Band Code
`CUST_SCR_CD`	Customer SCR Code
`CUST_TYP_CD`	Customer Type Code
`DEBT_AGNG_BND_CD_LAST_3MO`	Debt Ageing Band Code in last 3 months
`DEBT_AGNG_BND_CD_LAST_MO`	Debt Ageing Band Code in last 1 month
`DEBT_VAL_LAST_3MO`	Debt Value in last 3 months
`DEBT_VAL_LAST_MO`	Debt Value in last 1 month
`DRPD_CALLS_CNT_LAST_3MO`	Number of dropped calls in last 3 months
`DRPD_CALLS_CNT_LAST_MO`	Number of dropped calls this month
`DRPD_CALLS_CNT_LFTM`	Number of dropped calls in customer life span
`DWLNG_OWNER`	Dwelling Owner
`DWLNG_STAT`	Dwelling Status
`DWLNG_SZ`	Dwelling Size
`DWLNG_TENR`	Dwelling Tenure
`DWLNG_TYP`	Dwelling Type
`DWNLD_DATA_LAST_3MO`	Data downloaded in KBs in last 3 months
`DWNLD_DATA_LAST_MO`	Data downloaded in KBs in last 1 month
`DWNLD_DATA_LFTM`	Data downloaded in KBs in lifetime
`ECNMCLY_ACTV_IND`	Indicates whether a customer is economically Active
`EDU_CD`	Education Qualification Code
`EFF_FROM_DT_NBR`	Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`END_OF_JB_AGRMNT_NBR`	End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`ESTMTD_ACQSTN_COST`	Customer Acquisition Estimated Cost
`ETHNCTY`	Customer Ethnicity
`ETHNIC_BCKGRND`	Customer Ethnic Background
`EXTRNL_ORG_TYP_CD`	External Organization Type Code
`FORM_OF_EMPMNT`	Form of Employment
`FUTRE_AGRMNT_CNT_LAST_3MO`	Number of Future Agreements in last 3 months
`FUTRE_AGRMNT_CNT_LAST_MO`	Number of Future Agreements in last 1 month
`FUTRE_AGRMNT_DAYS_LAST_3MO`	Future Agreement Days in last 3 months
`FUTRE_AGRMNT_DAYS_LAST_MO`	Future Agreement Days in last 1 month
`GNDR_CD`	Individual Customer Gender Code
`HH_SZ`	Household Size
`HNGUP_CALLS_CNT_LAST_3MO`	Number of hangup calls in last 3 months
`HNGUP_CALLS_CNT_LAST_MO`	Number of hangup calls this month
`HNGUP_CALLS_CNT_LFTM`	Number of hangup calls in customer life span
`HOMTEL_IND`	Indicates whether Customer has Home Telephone
`IDD_IND`	Indicates whether Customer subscribed to International Direct Dialing
`JB_AGRMNT_TYP`	Job Agreement Type
`JB_CD`	Customer Job Code
`JB_POSN`	Customer Job Position
`LANG_CD`	Language Code
`LEGAL_TTL_TO_HSNG`	Legal Title to Housing
`LIFE_SPN`	Customer Life Span
`LIKE_SCP_IN_FCBK_IND`	Indicates whether customer likes Service Provide page in Facebook
`LVNG_AT_CURR_ADDR_SINCE_NBR`	Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`LYLTY_PROG_BAL_LAST_3MO`	Loyalty Program Balance in last three months
`LYLTY_PROG_BAL_LAST_MO`	Loyalty Program Balance in last month
`MAIL_ALWD_IND`	Indicates whether Customer allows Service Provider to send mail
`MMS_CNT_LAST_3MO`	MMSs sent in last 3 months
`MMS_CNT_LAST_MO`	MMSs sent in last 1 month
`MMS_CNT_LFTM`	MMSs sent over customer's lifetime
`MO_RVN_LAST_3MO`	Monthly Revenue as of 3 months ago
`MO_RVN_LAST_MO`	Monthly Revenue as of 1 month ago
`MRTL_STAT_CD`	Individual Customer Marital Status Code
`MULT_PRTY_ROLE_IND`	Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider
`NAME_OF_WKPLC`	Name of Workplace
`NAME_PRFX`	Name Prefix
`NBR_CHLDRN_AT_COLL`	Total Number of Children at College
`NBR_CHLDRN_AT_SCHL`	Total Number of Children at School
`NBR_CHLDRN_AT_UNIV`	Total Number of Children at University
`NBR_EMP_SNC_CUST`	Numbers of Employees Since Customer
`NBR_OF_CHLDRN`	Total Number of Children
`NBR_OF_DPNDNT`	Total Number of Dependents
`NEW_ACCT_IND`	Indicates whether Customer is New
`NO_MTHS_CMPNY`	Number of months an Individual Customer is in his/her current Company
`NO_MTHS_POSN`	Number of months an Individual Customer is in his/her current Position
`NTNLTY_CD`	Customer Nationality Code
`OFFNET_AIRTIME_LAST_3MO`	Total offnet airtime in minutes in last 3 months
`OFFNET_AIRTIME_LAST_MO`	Total offnet airtime in minutes in last 1 month
`OFFNET_AIRTIME_LFTM`	Total offnet airtime in minutes in lifetime
`OFFNET_CALLS_LAST_3MO`	Number of offnet calls in last 3 months
`OFFNET_CALLS_LAST_MO`	Number of offnet calls in last 1 month
`OFFNET_CALLS_LFTM`	Number of offnet calls in lifetime
`ONNET_AIRTIME_LAST_3MO`	Total onnet airtime in minutes in last 3 months
`ONNET_AIRTIME_LAST_MO`	Total onnet airtime in minutes in last 1 month
`ONNET_AIRTIME_LFTM`	Total onnet airtime in minutes in lifetime
`ONNET_CALLS_LAST_3MO`	Number of onnet calls in last 3 months
`ONNET_CALLS_LAST_MO`	Number of onnet calls in last 1 month
`ONNET_CALLS_LFTM`	Number of onnet calls in lifetime
`OTH_DRVR_LICNS_IND`	Indicates whether customer has other vehicle License
`PAY_TV_IND`	Indicates whether Customer has Pay TV connection
`PLC_OF_BRTH`	Place of Birth
`PORT_IN_CNT`	Number of times the customer ported in
`PORT_IN_FROM`	Service Provide from who the customer ported out from
`PORT_OUT_CNT`	Number of times the customer ported out
`POSTCD_CD`	Postcard Code
`PREF_CNTCT_MDM`	Preferred Contact Method
`PREF_ENTMNT_IND`	Indicates whether customer given any Entitlement preference
`PREF_GVN_IND`	Indicates whether customer given any preference
`PREF_MSC_IND`	Indicates whether customer given any Music preference
`PREF_PYMT_MTHD_TYP_CD`	Preferred Payment Method Type Code
`PREF_SPRT_IND`	Indicates whether customer given any sport preference
`PREF_TRVL_IND`	Indicates whether customer given any Travel preference
`PRMRY_STAT_CD`	Customer Primary Status Code
`PRMRY_STAT_RSN_CD`	Customer Primary Status Reason Code
`PRTY_AGE`	Customer Age expressed as number of years
`PRTY_TYP_CD`	Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.
`PYMT_ACCT_OPEN_DT_NBR`	Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`RLSD_CALLS_CNT_LAST_3MO`	Number of released calls in last 3 months
`RLSD_CALLS_CNT_LAST_MO`	Number of released calls this month
`RLSD_CALLS_CNT_LFTM`	Number of released calls in customer life span
`RMRK_CNT_LAST_3MO`	Numbers of remarks made against customer in last 3 months
`RMRK_CNT_LAST_MO`	Numbers of remarks made against customer in this month
`RMRK_CNT_LFTM`	Numbers of remarks made against customer in his/her life span
`SBRP_CNT_LAST_3MO`	Subscription Count in last three months
`SBRP_CNT_LAST_MO`	Subscription Count in last month
`SCHL_ZIP_CD`	School ZIP Code
`SCL_NTWK_USR_IND`	Indicates whether customer uses any Social Network site
`SL_CHNL_KEY`	Sales Channel
`SL_CHNL_RPRSTV_KEY`	Sales Channel Representative
`SMS_CNT_LAST_3MO`	SMSs sent in last 3 months
`SMS_CNT_LAST_MO`	SMSs sent in last 1 month
`SMS_CNT_LFTM`	SMSs sent over customer's lifetime
`SOC_JB_CD`	SOC Job Code
`SPRT_IND`	Indicates whether customer plays Sports
`SPRT_TYP_CD`	Sport Type Code
`SRC_OF_INCM`	Source of Income
`SSPNSN_CNT_LAST_3MO`	Suspension Count in last three months
`SSPNSN_CNT_LAST_MO`	Suspension Count in last month
`STATE`	State
`STRT_OF_EMPMNT_NBR`	Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
`TOT_AGE_CHLDRN`	Total Age of Children
`TOT_CALL_CNT_LAST_3MO`	Total number of calls to Call center made by customer in last 3 months
`TOT_CALL_CNT_LAST_MO`	Total number of calls to Call center made by customer in this month
`TOT_CALL_CNT_LFTM`	Total number of calls to Call center made by customer in his/her life span
`TOT_HLD_DRTN_LAST_3MO`	Total hold duration in the last 3 months in seconds
`TOT_HLD_DRTN_LAST_MO`	Total hold duration this month in seconds
`TOT_HLD_DRTN_LFTM`	Total hold duration in customer life span in seconds
`TOT_PYMT_RVN_LAST_3MO`	Total Payment Revenue as of 3 months ago
`TOT_PYMT_RVN_LAST_MO`	Total Payment Revenue as of 1 month ago
`TOT_QUE_DRTN_LAST_3MO`	Total queue duration in the last 3 months in seconds
`TOT_QUE_DRTN_LAST_MO`	Total queue duration this month in seconds
`TOT_QUE_DRTN_LFTM`	Total queue duration in customer life span in seconds
`TOT_TALK_DRTN_LAST_3MO`	Total talk duration in the last 3 months in seconds
`TOT_TALK_DRTN_LAST_MO`	Total talk duration this month in seconds
`TOT_TALK_DRTN_LFTM`	Total talk duration in customer life span in seconds
`UNIV_ZIP_CD`	University ZIP Code
`UPLD_DATA_LAST_3MO`	Data uploaded in KBs in last 3 months
`UPLD_DATA_LAST_MO`	Data uploaded in KBs in last 1 month
`UPLD_DATA_LFTM`	Data uploaded in KBs in lifetime
`VOI_INTERNAT_AIRTIME_LAST_3MO`	International voice air time including roaming in minutes in last three month
`VOI_INTERNAT_AIRTIME_LAST_MO`	International voice air time in minutes including roaming in last one month
`VOI_INTERNAT_AIRTIME_LFTM`	International voice air time in minutes including roaming over customer's life time
`VOI_NAT_AIRTIME_LAST_3MO`	National voice air time in minutes in last three month
`VOI_NAT_AIRTIME_LAST_MO`	National voice air time in minutes in last one month
`VOI_NAT_AIRTIME_LFTM`	National voice air time in minutes over customer's life time
`WRLS_IND`	Indicates whether Customer has Wireless Internet connection

Postpaid Churn Output Target Attribute: CHRN_IND

Data in DWV_PSTPD_CUST_CHRN_SRC view is used to train postpaid churn models using two algorithms, Support Vector Machines and Decision Tree. Data in DWV_PSTPD_CUST_CHRN_TST view is used to compare the performance of old and new mining models and the best model is chosen as final trained model. Data in DWV_PSTPD_CUST_CHRN_APPLY view is scored with the final trained model. Scoring results are saved into DWD_CUST_DNA table with the following columns:

DWD_CUST_DNA.PRDCT_CHURN_SVM_IND
DWD_CUST_DNA.PRDCT_CHURN_SVM_PROB
DWD_CUST_DNA.PRDCT_CHURN_DT_IND
DWD_CUST_DNA.PRDCT_CHURN_DT_ND_NBR

Postpaid Churner Algorithm

The following two algorithms are used separately to solve postpaid churn classification mining problem:

Support Vector Machines (SVM)
Decision Tree

Postpaid Churner Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following are the setting tables for postpaid churn:

DM_STNG_CHURN_SVM - For Support Vector Machines algorithm
DM_STNG_CHURN_DT - For Decision Tree algorithm

Model 3: Customer Profiling

The business problem is to group customers into generally homogeneous groups (Segments) based on customer demographic value, usage pattern and list of telecom products they subscribe to (customer subscriber history).Business Analysts can look into each segment to further understand the customer group discovered by the model and name each segments.

The discovered clustering rules draw a profile of the customers along with their product subscription. Thus, the clustering rules generated for each profile group will show the most important similar characteristics in each group. For example, an operator may have a group having significantly shorter message (SMS) usage than any other groups. Alternatively, there may be a group with extremely higher profit than any other group (covering high end customers).

Customer Profiling Source

Table 10-11 shows the attributes identified from the Foundation Data Warehouse as input source variables for the customer profiling model.

Table 10-11 DWV_CUST_PROFILE_SRC

Attribute	Description
ACCPT_NWSLTR_IND	Indicates whether customer accepts News Letter
ACCT_TYP_CD	Account Type Code. For example, PSTPD - Postpaid, PRPD - Prepaid
ADDR_LOC_CD	Customer Address Location Code
AGE_BND_CD	Customer Age Band Code
AGE_ON_NET_BND_CD	Customer Age on Net Band Code
AGE_ON_NET_NBR	Customer Age on Net expressed in number of months
AGRMNT_CNT_LAST_3MO	Number of Agreements in last 3 months
AGRMNT_CNT_LAST_MO	Number of Agreements in last 1 month
AGRMNT_LFT_DAYS_LAST_3MO	Agreement Left Days in last 3 months
AGRMNT_LFT_DAYS_LAST_MO	Agreement Left Days in last 1 month
ARPU_BND_CD	Customer ARPU Band Code
AVG_DRTN_BTWN_RCHRG	Average duration between two recharges - in days
BARNG_RSN_CD	Customer Barring Reason Code
BLLG_ADDR_EFF_DT_NBR	Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
BNKRPT_STAT	Bankrupt Status
BNKRPT_STRT_DT_NBR	Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
BRDBND_IND	Indicates whether Customer has Broadband connection
BSNS_LEGAL_STAT_CD	Legal Status Code of Enterprise Customers. For example, Public, Private
CAR_DRVR_LICNS_IND	Indicates whether customer has Car Driving License
CAR_TYP_CD	Car Type Code
CITY	City
CMPLNT_CNT_LAST_3MO	Number of complaints made by customer in last 3 months
CMPLNT_CNT_LAST_MO	Number of complaints made by customer in this month
CMPLNT_CNT_LFTM	Number of complaints made by customer in his/her life span
CMPNY_EMP_SZ_BND_CD	Enterprise Customer Employee Size Band Code
CMPNY_RVN_BND_CD	Enterprise Customer Revenue Band Code
CMPNY_TYP_CD	Company Type Code
CNCT_ADDR_EFF_DT_NBR	Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
CNTCT_ALLWD_IND	Indicates whether customer allows to contact
CNTRY	Country
COLL_ZIP_CD	College ZIP Code
CRDT_CTGRY_KEY	Customer Credit Category
CUST_BRANCH_CD	Customer Branch Code
CUST_CD	Customer Identifier
CUST_PYMT_RESPBL_IND	Indicates whether customer is responsible for making payments
CUST_RVN_BND_CD	Customer Revenue Band Code
CUST_SCR_CD	Customer SCR Code
CUST_TYP_CD	Customer Type Code
DAYS_BFR_FIRST_RCHRG	Days between first payment and first recharge
DAYS_BFR_FIRST_USE	Days between payment and first use
DEBT_AGNG_BND_CD_LAST_3MO	Debt Ageing Band Code in last 3 months
DEBT_AGNG_BND_CD_LAST_MO	Debt Ageing Band Code in last 1 month
DEBT_VAL_LAST_3MO	Debt Value in last 3 months
DEBT_VAL_LAST_MO	Debt Value in last 1 month
DRPD_CALLS_CNT_LAST_3MO	Number of dropped calls in last 3 months
DRPD_CALLS_CNT_LAST_MO	Number of dropped calls this month
DRPD_CALLS_CNT_LFTM	Number of dropped calls in customer life span
DWLNG_OWNER	Dwelling Owner
DWLNG_STAT	Dwelling Status
DWLNG_SZ	Dwelling Size
DWLNG_TENR	Dwelling Tenure
DWLNG_TYP	Dwelling Type
DWNLD_DATA_LAST_3MO	Data downloaded in KBs in last 3 months
DWNLD_DATA_LAST_MO	Data downloaded in KBs in last 1 month
DWNLD_DATA_LFTM	Data downloaded in KBs in lifetime
ECNMCLY_ACTV_IND	Indicates whether a customer is economically Active
EDU_CD	Education Qualification Code
EFF_FROM_DT_NBR	Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
END_OF_JB_AGRMNT_NBR	End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
ESTMTD_ACQSTN_COST	Customer Acquisition Estimated Cost
ETHNCTY	Customer Ethnicity
ETHNIC_BCKGRND	Customer Ethnic Background
EXTRNL_ORG_TYP_CD	External Organization Type Code
FORM_OF_EMPMNT	Form of Employment
FUTRE_AGRMNT_CNT_LAST_3MO	Number of Future Agreements in last 3 months
FUTRE_AGRMNT_CNT_LAST_MO	Number of Future Agreements in last 1 month
FUTRE_AGRMNT_DAYS_LAST_3MO	Future Agreement Days in last 3 months
FUTRE_AGRMNT_DAYS_LAST_MO	Future Agreement Days in last 1 month
GNDR_CD	Individual Customer Gender Code
HH_SZ	Household Size
HNGUP_CALLS_CNT_LAST_3MO	Number of hangup calls in last 3 months
HNGUP_CALLS_CNT_LAST_MO	Number of hangup calls this month
HNGUP_CALLS_CNT_LFTM	Number of hangup calls in customer life span
HOMTEL_IND	Indicates whether Customer has Home Telephone
IDD_IND	Indicates whether Customer subscribed to International Direct Dialing
JB_AGRMNT_TYP	Job Agreement Type
JB_CD	Customer Job Code
JB_POSN	Customer Job Position
LANG_CD	Language Code
LAST_INCOMING_USG_DT_NBR	When was last incoming call occurred
LAST_OUTGOING_USG_DT_NBR	When was last outgoing call occurred
LAST_RCHRG_AMT	Last recharge amount
LEGAL_TTL_TO_HSNG	Legal Title to Housing
LIFE_SPN	Customer Life Span
LIKE_SCP_IN_FCBK_IND	Indicates whether customer likes Service Provide page in Facebook
LVNG_AT_CURR_ADDR_SINCE_NBR	Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
MAIL_ALWD_IND	Indicates whether Customer allows Service Provider to send mail
MAX_RCHRG_AMT	Maximum recharge amount
MIN_RCHRG_AMT	Minimum recharge amount
MMS_CNT_LAST_3MO	MMSs sent in last 3 months
MMS_CNT_LAST_MO	MMSs sent in last 1 month
MMS_CNT_LFTM	MMSs sent over customer's lifetime
MO_RVN_LAST_3MO	Monthly Revenue as of 3 months ago
MO_RVN_LAST_MO	Monthly Revenue as of 1 month ago
MRTL_STAT_CD	Individual Customer Marital Status Code
MULT_PRTY_ROLE_IND	Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider
NAME_OF_WKPLC	Name of Workplace
NAME_PRFX	Name Prefix
NBR_CHLDRN_AT_COLL	Total Number of Children at College
NBR_CHLDRN_AT_SCHL	Total Number of Children at School
NBR_CHLDRN_AT_UNIV	Total Number of Children at University
NBR_EMP_SNC_CUST	Numbers of Employees Since Customer
NBR_OF_CHLDRN	Total Number of Children
NBR_OF_DPNDNT	Total Number of Dependents
NEW_ACCT_IND	Indicates whether Customer is New
NO_MTHS_CMPNY	Number of months an Individual Customer is in his/her current Company
NO_MTHS_POSN	Number of months an Individual Customer is in his/her current Position
NTNLTY_CD	Customer Nationality Code
OFFNET_AIRTIME_LAST_3MO	Total offnet airtime in minutes in last 3 months
OFFNET_AIRTIME_LAST_MO	Total offnet airtime in minutes in last 1 month
OFFNET_AIRTIME_LFTM	Total offnet airtime in minutes in lifetime
OFFNET_CALLS_LAST_3MO	Number of offnet calls in last 3 months
OFFNET_CALLS_LAST_MO	Number of offnet calls in last 1 month
OFFNET_CALLS_LFTM	Number of offnet calls in lifetime
ONNET_AIRTIME_LAST_3MO	Total onnet airtime in minutes in last 3 months
ONNET_AIRTIME_LAST_MO	Total onnet airtime in minutes in last 1 month
ONNET_AIRTIME_LFTM	Total onnet airtime in minutes in lifetime
ONNET_CALLS_LAST_3MO	Number of onnet calls in last 3 months
ONNET_CALLS_LAST_MO	Number of onnet calls in last 1 month
ONNET_CALLS_LFTM	Number of onnet calls in lifetime
OTH_DRVR_LICNS_IND	Indicates whether customer has other vehicle License
PAY_TV_IND	Indicates whether Customer has Pay TV connection
PLC_OF_BRTH	Place of Birth
PORT_IN_CNT	Number of times the customer ported in
PORT_IN_FROM	Service Provide from who the customer ported out from
PORT_OUT_CNT	Number of times the customer ported out
POSTCD_CD	Postcard Code
PREF_CNTCT_MDM	Preferred Contact Method
PREF_ENTMNT_IND	Indicates whether customer given any Entitlement preference
PREF_GVN_IND	Indicates whether customer given any preference
PREF_MSC_IND	Indicates whether customer given any Music preference
PREF_PYMT_MTHD_TYP_CD	Preferred Payment Method Type Code
PREF_SPRT_IND	Indicates whether customer given any sport preference
PREF_TRVL_IND	Indicates whether customer given any Travel preference
PRMRY_STAT_CD	Customer Primary Status Code
PRMRY_STAT_RSN_CD	Customer Primary Status Reason Code
PRTY_AGE	Customer Age expressed as number of years
PRTY_TYP_CD	Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.
PYMT_ACCT_OPEN_DT_NBR	Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
RLSD_CALLS_CNT_LAST_3MO	Number of released calls in last 3 months
RLSD_CALLS_CNT_LAST_MO	Number of released calls this month
RLSD_CALLS_CNT_LFTM	Number of released calls in customer life span
RMRK_CNT_LAST_3MO	Numbers of remarks made against customer in last 3 months
RMRK_CNT_LAST_MO	Numbers of remarks made against customer in this month
RMRK_CNT_LFTM	Numbers of remarks made against customer in his/her life span
SCHL_ZIP_CD	School ZIP Code
SCL_NTWK_USR_IND	Indicates whether customer uses any Social Network site
SL_CHNL_KEY	Sales Channel
SL_CHNL_RPRSTV_KEY	Sales Channel Representative
SMS_CNT_LAST_3MO	SMSs sent in last 3 months
SMS_CNT_LAST_MO	SMSs sent in last 1 month
SMS_CNT_LFTM	SMSs sent over customer's lifetime
SOC_JB_CD	SOC Job Code
SPRT_IND	Indicates whether customer plays Sports
SPRT_TYP_CD	Sport Type Code
SRC_OF_INCM	Source of Income
STATE	State
STRT_OF_EMPMNT_NBR	Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
TOT_AGE_CHLDRN	Total Age of Children
TOT_CALL_CNT_LAST_3MO	Total number of calls to Call center made by customer in last 3 months
TOT_CALL_CNT_LAST_MO	Total number of calls to Call center made by customer in this month
TOT_CALL_CNT_LFTM	Total number of calls to Call center made by customer in his/her life span
TOT_CNTRBTN_LAST_MO	Sum of total payments and total recharges made in the last one month
TOT_CNTRBTN_LFTM	Sum of total payments and total recharges made in customers life time
TOT_DAYS_ACTVTY	Total days of activity
TOT_DAYS_ON_USE	Total days on use
TOT_DAYS_OUTGOING_ACTVTY	Total days of outgoing activity
TOT_DRTN_OF_USG	Total durations of usage in customer life time
TOT_EXP_AMT_EQVLT_VAL	Total amount expired in customers life time
TOT_HLD_DRTN_LAST_3MO	Total hold duration in the last 3 months in seconds
TOT_HLD_DRTN_LAST_MO	Total hold duration this month in seconds
TOT_HLD_DRTN_LFTM	Total hold duration in customer life span in seconds
TOT_PYMT_RVN_LAST_3MO	Total Payment Revenue as of 3 months ago
TOT_PYMT_RVN_LAST_MO	Total Payment Revenue as of 1 month ago
TOT_QUE_DRTN_LAST_3MO	Total queue duration in the last 3 months in seconds
TOT_QUE_DRTN_LAST_MO	Total queue duration this month in seconds
TOT_QUE_DRTN_LFTM	Total queue duration in customer life span in seconds
TOT_RCHRG_AMT_LAST_3MO	Total recharge amount this month
TOT_RCHRG_AMT_LAST_MO	Total recharge amount in last 3 months
TOT_RCHRG_AMT_LFTM	Total recharge amount in customers life time
TOT_RCHRG_CNT_LAST_3MO	Number of recharges made in last 3 months
TOT_RCHRG_CNT_LAST_MO	Number of recharges made this month
TOT_RCHRG_CNT_LFTM	Number of recharges made in customer life time
TOT_TALK_DRTN_LAST_3MO	Total talk duration in the last 3 months in seconds
TOT_TALK_DRTN_LAST_MO	Total talk duration this month in seconds
TOT_TALK_DRTN_LFTM	Total talk duration in customer life span in seconds
TOT_UTILZD_EQVLT_VAL	Total utilized monetary value in customers life time
UNIV_ZIP_CD	University ZIP Code
UPLD_DATA_LAST_3MO	Data uploaded in KBs in last 3 months
UPLD_DATA_LAST_MO	Data uploaded in KBs in last 1 month
UPLD_DATA_LFTM	Data uploaded in KBs in lifetime
VOI_INTERNAT_AIRTIME_LAST_3MO	International voice air time including roaming in minutes in last three month
VOI_INTERNAT_AIRTIME_LAST_MO	International voice air time in minutes including roaming in last one month
VOI_INTERNAT_AIRTIME_LFTM	International voice air time in minutes including roaming over customer's life time
VOI_NAT_AIRTIME_LAST_3MO	National voice air time in minutes in last three month
VOI_NAT_AIRTIME_LAST_MO	National voice air time in minutes in last one month
VOI_NAT_AIRTIME_LFTM	National voice air time in minutes over customer's life time
WRLS_IND	Indicates whether Customer has Wireless Internet connection

Customer Profile Output

Data in DWV_CUST_PROFILE_SRC view is used to train unsupervised customer segmentation model. The trained is applied on the DWV_CUST_LTV_SGMNT_APPLY view, which has only active customers' data, to get the segment code for each active customer. Customer segment code is saved into DWD_CUST_DNA table with the following column:

DWD_CUST_DNA. CLSTR_SGMNT_CD

Customer Profile Algorithm

The following algorithms is used to segment the active customer base:

K-Means Clustering

Customer Profile Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following is the setting table for customer profiling:

DM_STNG_PROFILE_KMEANS - For K-Means Clustering

Model 4: Targeted Promotion

The business problem is to identify the patterns of which products are typically purchased together or one after another over the lifetime of a customer. This helps in providing recommendations about which products should be presented to customers according to their potential acceptance score. A typical scenario is call center can call certain customers with some specific purpose to cross-sell some products. Operators need the list of customers to save promotion cost and improve efficiency.

The trained model generates recommendations about promotion target products. This is done based on what products the customer has subscribed to taking into account other factors such as customers credit history and the risk involved in offering the particular product to the customer.

Targeted Promotion Source

Table 10-12 shows the attributes identified from the Foundation Data Warehouse as input variables for targeted promotion model.

Table 10-12 DWV_PROD_MIX_SRC

Attribute	Description
ACCT_LFT_VAL_LAST_MO	Account Left Value in last 1 month
AGRMNT_ARPU_LAST_MO	Agreement ARPU as of 1 month ago
AGRMNT_CNT_LAST_MO	Number of Agreements in last 1 month
AGRMNT_LFT_DAYS_LAST_MO	Agreement Left Days in last 1 month
BARNG_RSN_CD	Customer Barring Reason Code
BSNS_LEGAL_STAT_CD	Legal Status Code of Enterprise Customers. For example Public, Private
CALL_BCK	Indicates whether Customer subscribed to Call Back
CALL_CNFRN	Indicates whether Customer subscribed to Call Conference
CALL_TRNSFR	Indicates whether Customer subscribed to Call Transfer
CB	Indicates whether Customer subscribed to CB
CF	Indicates whether Customer subscribed to Call Forward
CF_WHEN_BUSY	Indicates whether Customer subscribed to Call Forward When Busy
CF_WHEN_NO_RPLY	Indicates whether Customer subscribed to Call Forward When No Reply
CF_WHEN_NOT_RCHBLE	Indicates whether Customer subscribed to Call Forward When Not Reachable
CLI	Indicates whether Customer subscribed to Calling Line Identity
CLIR	Indicates whether Customer subscribed to Calling Line Identification Restriction
CMPLNT_CNT_LAST_MO	Number of complaints made by customer in this month
CMPLNT_CNT_LFTM	Number of complaints made by customer in his/her life span
CUST_CD	Customer Identifier
CW	Indicates whether Customer subscribed to Call Waiting
DEBT_VAL_LAST_MO	Debt Value in last 1 month
EDU_CD	Education Qualification Code
FAX	Indicates whether Customer subscribed to Fax
FUTRE_AGRMNT_CNT_LAST_MO	Number of Future Agreements in last 1 month
GNDR_CD	Individual Customer Gender Code
GPRS	Indicates whether Customer subscribed to GPRS
HH_SZ	Household Size
INTRNTL_CALL	Indicates whether Customer subscribed to International Call
JB_AGRMNT_TYP	Job Agreement Type
JB_CD	Customer Job Code
LFTM_ARPU_LAST_MO	Lifetime ARPU as of 1 month ago
LIFE_SPN	Customer Life Span
LYLTY_PROG_BAL_LAST_MO	Loyalty Program Balance in last 1 month
MMS	Indicates whether Customer subscribed to MMS
MO_RVN_LAST_MO	Monthly Revenue as of 1 month ago
MRTL_STAT_CD	Individual Customer Marital Status Code
NTNLTY_CD	Customer Nationality Code
PORT_IN_CNT	Number of times the customer ported in
PORT_IN_FROM	Service Provide from who the customer ported out from
PORT_OUT_CNT	Number of times the customer ported out
POSTCD_CD	Postcard Code
PRTY_TYP_CD	Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.
REMNG_AGRMNT_AMT_LAST_MO	Remaining Agreement Amount in last 1 month
SBRP_CNT_LAST_MO	Subscription Count in last three months
SMS	Indicates whether Customer subscribed to SMS
SSPNSN_CNT_LAST_MO	Suspension Count in last three months
STATE	State
TOT_PYMT_RVN_LAST_MO	Total Payment Revenue as of 1 month ago
WAP	Indicates whether Customer subscribed to WAP

Targeted Promotion Output

Data in DWV_PROD_MIX_SRC view is used to train target promotion classification model for the product chosen. Data in DWV_PROD_MIX_APPLY view scored with the trained model and the scoring results are saved into DWD_CUST_PROD_AFFLTN table with the following columns:

DWD_CUST_PROD_AFFLTN.AFFLTN_PROB
DWD_CUST_PROD_AFFLTN.BUY_IND

Targeted Promotion Algorithm

The following algorithm is used to solve train the targeted promotion model:

Support Vector Machines (SVM)

Targeted Promotion Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following is the setting table for targeted promotion:

DM_STNG_CHURN_SVM - For Support Vector Machines

Model 5: Customer Life Time Value

Service Provider wants know how much valuable a customer is to the Service Provider for next n years into future. Customer predicted Life Time Value can be combined with customer predicted churn behavior to make important business decision such as whether to retain a customer. Customers predicted to have high LTV and also predicted to churn should be retained, whereas Customer predicted to have low LTV and also predicted to churn need not be retained. This is a regression model, where a continuous value is predicted and the source data is all those customers who have been on net for at least n years. n could be three or five years.

Customer Life Time Value Source

Table 10-13 shows the attributes identified from the Foundation Data Warehouse as input variables for Customer Life Time Value model.

Table 10-13 DWV_CUST_LTV_SRC

Attribute	Description
ACCPT_NWSLTR_IND	Indicates whether customer accepts News Letter
ACCT_TYP_CD	Account Type Code. For example, PSTPD - Postpaid, PRPD - Prepaid
ADDR_LOC_CD	Customer Address Location Code
AGE_BND_CD	Customer Age Band Code
AGE_ON_NET_BND_CD	Customer Age on Net Band Code
AGE_ON_NET_NBR	Customer Age on Net expressed in number of months
AGRMNT_CNT_LAST_3MO	Number of Agreements in last 3 months
AGRMNT_CNT_LAST_MO	Number of Agreements in last 1 month
AGRMNT_LFT_DAYS_LAST_3MO	Agreement Left Days in last 3 months
AGRMNT_LFT_DAYS_LAST_MO	Agreement Left Days in last 1 month
ARPU_BND_CD	Customer ARPU Band Code
AVG_DRTN_BTWN_RCHRG	Average duration between two recharges - in days
BARNG_RSN_CD	Customer Barring Reason Code
BLLG_ADDR_EFF_DT_NBR	Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
BNKRPT_STAT	Bankrupt Status
BNKRPT_STRT_DT_NBR	Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
BRDBND_IND	Indicates whether Customer has Broadband connection
BSNS_LEGAL_STAT_CD	Legal Status Code of Enterprise Customers. For example, Public, Private
CAR_DRVR_LICNS_IND	Indicates whether customer has Car Driving License
CAR_TYP_CD	Car Type Code
CITY	City
CMPLNT_CNT_LAST_3MO	Number of complaints made by customer in last 3 months
CMPLNT_CNT_LAST_MO	Number of complaints made by customer in this month
CMPLNT_CNT_LFTM	Number of complaints made by customer in his/her life span
CMPNY_EMP_SZ_BND_CD	Enterprise Customer Employee Size Band Code
CMPNY_RVN_BND_CD	Enterprise Customer Revenue Band Code
CMPNY_TYP_CD	Company Type Code
CNCT_ADDR_EFF_DT_NBR	Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
CNTCT_ALLWD_IND	Indicates whether customer allows to contact
CNTRY	Country
COLL_ZIP_CD	College ZIP Code
CRDT_CTGRY_KEY	Customer Credit Category
CUST_BRANCH_CD	Customer Branch Code
CUST_CD	Customer Identifier
CUST_PYMT_RESPBL_IND	Indicates whether customer is responsible for making payments
CUST_RVN_BND_CD	Customer Revenue Band Code
CUST_SCR_CD	Customer SCR Code
CUST_TYP_CD	Customer Type Code
DAYS_BFR_FIRST_RCHRG	Days between first payment and first recharge
DAYS_BFR_FIRST_USE	Days between payment and first use
DEBT_AGNG_BND_CD_LAST_3MO	Debt Ageing Band Code in last 3 months
DEBT_AGNG_BND_CD_LAST_MO	Debt Ageing Band Code in last 1 month
DEBT_VAL_LAST_3MO	Debt Value in last 3 months
DEBT_VAL_LAST_MO	Debt Value in last 1 month
DRPD_CALLS_CNT_LAST_3MO	Number of dropped calls in last 3 months
DRPD_CALLS_CNT_LAST_MO	Number of dropped calls this month
DRPD_CALLS_CNT_LFTM	Number of dropped calls in customer life span
DWLNG_OWNER	Dwelling Owner
DWLNG_STAT	Dwelling Status
DWLNG_SZ	Dwelling Size
DWLNG_TENR	Dwelling Tenure
DWLNG_TYP	Dwelling Type
DWNLD_DATA_LAST_3MO	Data downloaded in KBs in last 3 months
DWNLD_DATA_LAST_MO	Data downloaded in KBs in last 1 month
DWNLD_DATA_LFTM	Data downloaded in KBs in lifetime
ECNMCLY_ACTV_IND	Indicates whether a customer is economically Active
EDU_CD	Education Qualification Code
EFF_FROM_DT_NBR	Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
END_OF_JB_AGRMNT_NBR	End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
ESTMTD_ACQSTN_COST	Customer Acquisition Estimated Cost
ETHNCTY	Customer Ethnicity
ETHNIC_BCKGRND	Customer Ethnic Background
EXTRNL_ORG_TYP_CD	External Organization Type Code
FORM_OF_EMPMNT	Form of Employment
FUTRE_AGRMNT_CNT_LAST_3MO	Number of Future Agreements in last 3 months
FUTRE_AGRMNT_CNT_LAST_MO	Number of Future Agreements in last 1 month
FUTRE_AGRMNT_DAYS_LAST_3MO	Future Agreement Days in last 3 months
FUTRE_AGRMNT_DAYS_LAST_MO	Future Agreement Days in last 1 month
GNDR_CD	Individual Customer Gender Code
HH_SZ	Household Size
HNGUP_CALLS_CNT_LAST_3MO	Number of hangup calls in last 3 months
HNGUP_CALLS_CNT_LAST_MO	Number of hangup calls this month
HNGUP_CALLS_CNT_LFTM	Number of hangup calls in customer life span
HOMTEL_IND	Indicates whether Customer has Home Telephone
IDD_IND	Indicates whether Customer subscribed to International Direct Dialing
JB_AGRMNT_TYP	Job Agreement Type
JB_CD	Customer Job Code
JB_POSN	Customer Job Position
LANG_CD	Language Code
LAST_INCOMING_USG_DT_NBR	When was last incoming call occurred
LAST_OUTGOING_USG_DT_NBR	When was last outgoing call occurred
LAST_RCHRG_AMT	Last recharge amount
LEGAL_TTL_TO_HSNG	Legal Title to Housing
LIFE_SPN	Customer Life Span
LIKE_SCP_IN_FCBK_IND	Indicates whether customer likes Service Provide page in Facebook
LVNG_AT_CURR_ADDR_SINCE_NBR	Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
MAIL_ALWD_IND	Indicates whether Customer allows Service Provider to send mail
MAX_RCHRG_AMT	Maximum recharge amount
MIN_RCHRG_AMT	Minimum recharge amount
MMS_CNT_LAST_3MO	MMSs sent in last 3 months
MMS_CNT_LAST_MO	MMSs sent in last 1 month
MMS_CNT_LFTM	MMSs sent over customer's lifetime
MO_RVN_LAST_3MO	Monthly Revenue as of 3 months ago
MO_RVN_LAST_MO	Monthly Revenue as of 1 month ago
MRTL_STAT_CD	Individual Customer Marital Status Code
MULT_PRTY_ROLE_IND	Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider
NAME_OF_WKPLC	Name of Workplace
NAME_PRFX	Name Prefix
NBR_CHLDRN_AT_COLL	Total Number of Children at College
NBR_CHLDRN_AT_SCHL	Total Number of Children at School
NBR_CHLDRN_AT_UNIV	Total Number of Children at University
NBR_EMP_SNC_CUST	Numbers of Employees Since Customer
NBR_OF_CHLDRN	Total Number of Children
NBR_OF_DPNDNT	Total Number of Dependents
NEW_ACCT_IND	Indicates whether Customer is New
NO_MTHS_CMPNY	Number of months an Individual Customer is in his/her current Company
NO_MTHS_POSN	Number of months an Individual Customer is in his/her current Position
NTNLTY_CD	Customer Nationality Code
OFFNET_AIRTIME_LAST_3MO	Total offnet airtime in minutes in last 3 months
OFFNET_AIRTIME_LAST_MO	Total offnet airtime in minutes in last 1 month
OFFNET_AIRTIME_LFTM	Total offnet airtime in minutes in lifetime
OFFNET_CALLS_LAST_3MO	Number of offnet calls in last 3 months
OFFNET_CALLS_LAST_MO	Number of offnet calls in last 1 month
OFFNET_CALLS_LFTM	Number of offnet calls in lifetime
ONNET_AIRTIME_LAST_3MO	Total onnet airtime in minutes in last 3 months
ONNET_AIRTIME_LAST_MO	Total onnet airtime in minutes in last 1 month
ONNET_AIRTIME_LFTM	Total onnet airtime in minutes in lifetime
ONNET_CALLS_LAST_3MO	Number of onnet calls in last 3 months
ONNET_CALLS_LAST_MO	Number of onnet calls in last 1 month
ONNET_CALLS_LFTM	Number of onnet calls in lifetime
OTH_DRVR_LICNS_IND	Indicates whether customer has other vehicle License
PAY_TV_IND	Indicates whether Customer has Pay TV connection
PLC_OF_BRTH	Place of Birth
PORT_IN_CNT	Number of times the customer ported in
PORT_IN_FROM	Service Provide from who the customer ported out from
PORT_OUT_CNT	Number of times the customer ported out
POSTCD_CD	Postcard Code
PREF_CNTCT_MDM	Preferred Contact Method
PREF_ENTMNT_IND	Indicates whether customer given any Entitlement preference
PREF_GVN_IND	Indicates whether customer given any preference
PREF_MSC_IND	Indicates whether customer given any Music preference
PREF_PYMT_MTHD_TYP_CD	Preferred Payment Method Type Code
PREF_SPRT_IND	Indicates whether customer given any sport preference
PREF_TRVL_IND	Indicates whether customer given any Travel preference
PRMRY_STAT_CD	Customer Primary Status Code
PRMRY_STAT_RSN_CD	Customer Primary Status Reason Code
PRTY_AGE	Customer Age expressed as number of years
PRTY_TYP_CD	Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.
PYMT_ACCT_OPEN_DT_NBR	Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
RLSD_CALLS_CNT_LAST_3MO	Number of released calls in last 3 months
RLSD_CALLS_CNT_LAST_MO	Number of released calls this month
RLSD_CALLS_CNT_LFTM	Number of released calls in customer life span
RMRK_CNT_LAST_3MO	Numbers of remarks made against customer in last 3 months
RMRK_CNT_LAST_MO	Numbers of remarks made against customer in this month
RMRK_CNT_LFTM	Numbers of remarks made against customer in his/her life span
SCHL_ZIP_CD	School ZIP Code
SCL_NTWK_USR_IND	Indicates whether customer uses any Social Network site
SL_CHNL_KEY	Sales Channel
SL_CHNL_RPRSTV_KEY	Sales Channel Representative
SMS_CNT_LAST_3MO	SMSs sent in last 3 months
SMS_CNT_LAST_MO	SMSs sent in last 1 month
SMS_CNT_LFTM	SMSs sent over customer's lifetime
SOC_JB_CD	SOC Job Code
SPRT_IND	Indicates whether customer plays Sports
SPRT_TYP_CD	Sport Type Code
SRC_OF_INCM	Source of Income
STATE	State
STRT_OF_EMPMNT_NBR	Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
TOT_AGE_CHLDRN	Total Age of Children
TOT_CALL_CNT_LAST_3MO	Total number of calls to Call center made by customer in last 3 months
TOT_CALL_CNT_LAST_MO	Total number of calls to Call center made by customer in this month
TOT_CALL_CNT_LFTM	Total number of calls to Call center made by customer in his/her life span
TOT_CNTRBTN_LAST_MO	Sum of total payments and total recharges made in the last one month
TOT_CNTRBTN_LFTM	Sum of total payments and total recharges made in customers life time
TOT_DAYS_ACTVTY	Total days of activity
TOT_DAYS_ON_USE	Total days on use
TOT_DAYS_OUTGOING_ACTVTY	Total days of outgoing activity
TOT_DRTN_OF_USG	Total durations of usage in customer life time
TOT_EXP_AMT_EQVLT_VAL	Total amount expired in customers life time
TOT_HLD_DRTN_LAST_3MO	Total hold duration in the last 3 months in seconds
TOT_HLD_DRTN_LAST_MO	Total hold duration this month in seconds
TOT_HLD_DRTN_LFTM	Total hold duration in customer life span in seconds
TOT_PYMT_RVN_LAST_3MO	Total Payment Revenue as of 3 months ago
TOT_PYMT_RVN_LAST_MO	Total Payment Revenue as of 1 month ago
TOT_QUE_DRTN_LAST_3MO	Total queue duration in the last 3 months in seconds
TOT_QUE_DRTN_LAST_MO	Total queue duration this month in seconds
TOT_QUE_DRTN_LFTM	Total queue duration in customer life span in seconds
TOT_RCHRG_AMT_LAST_3MO	Total recharge amount this month
TOT_RCHRG_AMT_LAST_MO	Total recharge amount in last 3 months
TOT_RCHRG_AMT_LFTM	Total recharge amount in customers life time
TOT_RCHRG_CNT_LAST_3MO	Number of recharges made in last 3 months
TOT_RCHRG_CNT_LAST_MO	Number of recharges made this month
TOT_RCHRG_CNT_LFTM	Number of recharges made in customer life time
TOT_TALK_DRTN_LAST_3MO	Total talk duration in the last 3 months in seconds
TOT_TALK_DRTN_LAST_MO	Total talk duration this month in seconds
TOT_TALK_DRTN_LFTM	Total talk duration in customer life span in seconds
TOT_UTILZD_EQVLT_VAL	Total utilized monetary value in customers life time
UNIV_ZIP_CD	University ZIP Code
UPLD_DATA_LAST_3MO	Data uploaded in KBs in last 3 months
UPLD_DATA_LAST_MO	Data uploaded in KBs in last 1 month
UPLD_DATA_LFTM	Data uploaded in KBs in lifetime
VOI_INTERNAT_AIRTIME_LAST_3MO	International voice air time including roaming in minutes in last three month
VOI_INTERNAT_AIRTIME_LAST_MO	International voice air time in minutes including roaming in last one month
VOI_INTERNAT_AIRTIME_LFTM	International voice air time in minutes including roaming over customer's life time
VOI_NAT_AIRTIME_LAST_3MO	National voice air time in minutes in last three month
VOI_NAT_AIRTIME_LAST_MO	National voice air time in minutes in last one month
VOI_NAT_AIRTIME_LFTM	National voice air time in minutes over customer's life time
WRLS_IND	Indicates whether Customer has Wireless Internet connection

Customer Life Time Value Output Target Attribute: TOT_PYMT_RVN_LAST_MO

Data in DWV_CUST_LTV_SRC view is used to train Customer Life Time Value regression mining model. Data in DWV_CUST_LTV_SGMNT_APPLY view, which has all active customers' data, is scored using the trained model and scoring results are saved into DWD_CUST_DNA table with the following column:

DWD_CUST_DNA.PRDCT_LTV_VALUE

Customer Life Time Value Algorithms

The following algorithm is used to predict the Life Time Value of active customer base:

Generalized Linear Model Regression

Customer Life Time Value Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following is the setting table for customer Life Time Value:

DM_STNG_LTV_GLMR - For Generalized Linear Model Regression

Model 6: Customer Life Time Survival Value

When the Service Provider wants to know how long a customer uses the services offered, rather than leaving you use the Life Time Survival Value rating. This is a regression model, where a continuous value is predicted and the source data is all those customers who have been on net for at least n years. where n could be 3 or 5 years.

Customer Life Time Value (LTV) Survival Source

Table 10-14 shows the attributes identified from the Foundation Data Warehouse as input variables for Customer Life Time Survival Value model.

Table 10-14 DWV_CUST_LTV_SRC

Attribute	Description
ACCPT_NWSLTR_IND	Indicates whether customer accepts News Letter
ACCT_TYP_CD	Account Type Code. For example, PSTPD - Postpaid, PRPD - Prepaid
ADDR_LOC_CD	Customer Address Location Code
AGE_BND_CD	Customer Age Band Code
AGE_ON_NET_BND_CD	Customer Age on Net Band Code
AGE_ON_NET_NBR	Customer Age on Net expressed in number of months
AGRMNT_CNT_LAST_3MO	Number of Agreements in last 3 months
AGRMNT_CNT_LAST_MO	Number of Agreements in last 1 month
AGRMNT_LFT_DAYS_LAST_3MO	Agreement Left Days in last 3 months
AGRMNT_LFT_DAYS_LAST_MO	Agreement Left Days in last 1 month
ARPU_BND_CD	Customer ARPU Band Code
AVG_DRTN_BTWN_RCHRG	Average duration between two recharges - in days
BARNG_RSN_CD	Customer Barring Reason Code
BLLG_ADDR_EFF_DT_NBR	Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
BNKRPT_STAT	Bankrupt Status
BNKRPT_STRT_DT_NBR	Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
BRDBND_IND	Indicates whether Customer has Broadband connection
BSNS_LEGAL_STAT_CD	Legal Status Code of Enterprise Customers. For example, Public, Private
CAR_DRVR_LICNS_IND	Indicates whether customer has Car Driving License
CAR_TYP_CD	Car Type Code
CITY	City
CMPLNT_CNT_LAST_3MO	Number of complaints made by customer in last 3 months
CMPLNT_CNT_LAST_MO	Number of complaints made by customer in this month
CMPLNT_CNT_LFTM	Number of complaints made by customer in his/her life span
CMPNY_EMP_SZ_BND_CD	Enterprise Customer Employee Size Band Code
CMPNY_RVN_BND_CD	Enterprise Customer Revenue Band Code
CMPNY_TYP_CD	Company Type Code
CNCT_ADDR_EFF_DT_NBR	Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
CNTCT_ALLWD_IND	Indicates whether customer allows to contact
CNTRY	Country
COLL_ZIP_CD	College ZIP Code
CRDT_CTGRY_KEY	Customer Credit Category
CUST_BRANCH_CD	Customer Branch Code
CUST_CD	Customer Identifier
CUST_PYMT_RESPBL_IND	Indicates whether customer is responsible for making payments
CUST_RVN_BND_CD	Customer Revenue Band Code
CUST_SCR_CD	Customer SCR Code
CUST_TYP_CD	Customer Type Code
DAYS_BFR_FIRST_RCHRG	Days between first payment and first recharge
DAYS_BFR_FIRST_USE	Days between payment and first use
DEBT_AGNG_BND_CD_LAST_3MO	Debt Ageing Band Code in last 3 months
DEBT_AGNG_BND_CD_LAST_MO	Debt Ageing Band Code in last 1 month
DEBT_VAL_LAST_3MO	Debt Value in last 3 months
DEBT_VAL_LAST_MO	Debt Value in last 1 month
DRPD_CALLS_CNT_LAST_3MO	Number of dropped calls in last 3 months
DRPD_CALLS_CNT_LAST_MO	Number of dropped calls this month
DRPD_CALLS_CNT_LFTM	Number of dropped calls in customer life span
DWLNG_OWNER	Dwelling Owner
DWLNG_STAT	Dwelling Status
DWLNG_SZ	Dwelling Size
DWLNG_TENR	Dwelling Tenure
DWLNG_TYP	Dwelling Type
DWNLD_DATA_LAST_3MO	Data downloaded in KBs in last 3 months
DWNLD_DATA_LAST_MO	Data downloaded in KBs in last 1 month
DWNLD_DATA_LFTM	Data downloaded in KBs in lifetime
ECNMCLY_ACTV_IND	Indicates whether a customer is economically Active
EDU_CD	Education Qualification Code
EFF_FROM_DT_NBR	Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
END_OF_JB_AGRMNT_NBR	End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
ESTMTD_ACQSTN_COST	Customer Acquisition Estimated Cost
ETHNCTY	Customer Ethnicity
ETHNIC_BCKGRND	Customer Ethnic Background
EXTRNL_ORG_TYP_CD	External Organization Type Code
FORM_OF_EMPMNT	Form of Employment
FUTRE_AGRMNT_CNT_LAST_3MO	Number of Future Agreements in last 3 months
FUTRE_AGRMNT_CNT_LAST_MO	Number of Future Agreements in last 1 month
FUTRE_AGRMNT_DAYS_LAST_3MO	Future Agreement Days in last 3 months
FUTRE_AGRMNT_DAYS_LAST_MO	Future Agreement Days in last 1 month
GNDR_CD	Individual Customer Gender Code
HH_SZ	Household Size
HNGUP_CALLS_CNT_LAST_3MO	Number of hangup calls in last 3 months
HNGUP_CALLS_CNT_LAST_MO	Number of hangup calls this month
HNGUP_CALLS_CNT_LFTM	Number of hangup calls in customer life span
HOMTEL_IND	Indicates whether Customer has Home Telephone
IDD_IND	Indicates whether Customer subscribed to International Direct Dialing
JB_AGRMNT_TYP	Job Agreement Type
JB_CD	Customer Job Code
JB_POSN	Customer Job Position
LANG_CD	Language Code
LAST_INCOMING_USG_DT_NBR	When was last incoming call occurred
LAST_OUTGOING_USG_DT_NBR	When was last outgoing call occurred
LAST_RCHRG_AMT	Last recharge amount
LEGAL_TTL_TO_HSNG	Legal Title to Housing
LIFE_SPN	Customer Life Span
LIKE_SCP_IN_FCBK_IND	Indicates whether customer likes Service Provide page in Facebook
LVNG_AT_CURR_ADDR_SINCE_NBR	Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
MAIL_ALWD_IND	Indicates whether Customer allows Service Provider to send mail
MAX_RCHRG_AMT	Maximum recharge amount
MIN_RCHRG_AMT	Minimum recharge amount
MMS_CNT_LAST_3MO	MMSs sent in last 3 months
MMS_CNT_LAST_MO	MMSs sent in last 1 month
MMS_CNT_LFTM	MMSs sent over customer's lifetime
MO_RVN_LAST_3MO	Monthly Revenue as of 3 months ago
MO_RVN_LAST_MO	Monthly Revenue as of 1 month ago
MRTL_STAT_CD	Individual Customer Marital Status Code
MULT_PRTY_ROLE_IND	Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider
NAME_OF_WKPLC	Name of Workplace
NAME_PRFX	Name Prefix
NBR_CHLDRN_AT_COLL	Total Number of Children at College
NBR_CHLDRN_AT_SCHL	Total Number of Children at School
NBR_CHLDRN_AT_UNIV	Total Number of Children at University
NBR_EMP_SNC_CUST	Numbers of Employees Since Customer
NBR_OF_CHLDRN	Total Number of Children
NBR_OF_DPNDNT	Total Number of Dependents
NEW_ACCT_IND	Indicates whether Customer is New
NO_MTHS_CMPNY	Number of months an Individual Customer is in his/her current Company
NO_MTHS_POSN	Number of months an Individual Customer is in his/her current Position
NTNLTY_CD	Customer Nationality Code
OFFNET_AIRTIME_LAST_3MO	Total offnet airtime in minutes in last 3 months
OFFNET_AIRTIME_LAST_MO	Total offnet airtime in minutes in last 1 month
OFFNET_AIRTIME_LFTM	Total offnet airtime in minutes in lifetime
OFFNET_CALLS_LAST_3MO	Number of offnet calls in last 3 months
OFFNET_CALLS_LAST_MO	Number of offnet calls in last 1 month
OFFNET_CALLS_LFTM	Number of offnet calls in lifetime
ONNET_AIRTIME_LAST_3MO	Total onnet airtime in minutes in last 3 months
ONNET_AIRTIME_LAST_MO	Total onnet airtime in minutes in last 1 month
ONNET_AIRTIME_LFTM	Total onnet airtime in minutes in lifetime
ONNET_CALLS_LAST_3MO	Number of onnet calls in last 3 months
ONNET_CALLS_LAST_MO	Number of onnet calls in last 1 month
ONNET_CALLS_LFTM	Number of onnet calls in lifetime
OTH_DRVR_LICNS_IND	Indicates whether customer has other vehicle License
PAY_TV_IND	Indicates whether Customer has Pay TV connection
PLC_OF_BRTH	Place of Birth
PORT_IN_CNT	Number of times the customer ported in
PORT_IN_FROM	Service Provide from who the customer ported out from
PORT_OUT_CNT	Number of times the customer ported out
POSTCD_CD	Postcard Code
PREF_CNTCT_MDM	Preferred Contact Method
PREF_ENTMNT_IND	Indicates whether customer given any Entitlement preference
PREF_GVN_IND	Indicates whether customer given any preference
PREF_MSC_IND	Indicates whether customer given any Music preference
PREF_PYMT_MTHD_TYP_CD	Preferred Payment Method Type Code
PREF_SPRT_IND	Indicates whether customer given any sport preference
PREF_TRVL_IND	Indicates whether customer given any Travel preference
PRMRY_STAT_CD	Customer Primary Status Code
PRMRY_STAT_RSN_CD	Customer Primary Status Reason Code
PRTY_AGE	Customer Age expressed as number of years
PRTY_TYP_CD	Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.
PYMT_ACCT_OPEN_DT_NBR	Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
RLSD_CALLS_CNT_LAST_3MO	Number of released calls in last 3 months
RLSD_CALLS_CNT_LAST_MO	Number of released calls this month
RLSD_CALLS_CNT_LFTM	Number of released calls in customer life span
RMRK_CNT_LAST_3MO	Numbers of remarks made against customer in last 3 months
RMRK_CNT_LAST_MO	Numbers of remarks made against customer in this month
RMRK_CNT_LFTM	Numbers of remarks made against customer in his/her life span
SCHL_ZIP_CD	School ZIP Code
SCL_NTWK_USR_IND	Indicates whether customer uses any Social Network site
SL_CHNL_KEY	Sales Channel
SL_CHNL_RPRSTV_KEY	Sales Channel Representative
SMS_CNT_LAST_3MO	SMSs sent in last 3 months
SMS_CNT_LAST_MO	SMSs sent in last 1 month
SMS_CNT_LFTM	SMSs sent over customer's lifetime
SOC_JB_CD	SOC Job Code
SPRT_IND	Indicates whether customer plays Sports
SPRT_TYP_CD	Sport Type Code
SRC_OF_INCM	Source of Income
STATE	State
STRT_OF_EMPMNT_NBR	Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept `DATE` data type attributes, hence expressed as number.
TOT_AGE_CHLDRN	Total Age of Children
TOT_CALL_CNT_LAST_3MO	Total number of calls to Call center made by customer in last 3 months
TOT_CALL_CNT_LAST_MO	Total number of calls to Call center made by customer in this month
TOT_CALL_CNT_LFTM	Total number of calls to Call center made by customer in his/her life span
TOT_CNTRBTN_LAST_MO	Sum of total payments and total recharges made in the last one month
TOT_CNTRBTN_LFTM	Sum of total payments and total recharges made in customers life time
TOT_DAYS_ACTVTY	Total days of activity
TOT_DAYS_ON_USE	Total days on use
TOT_DAYS_OUTGOING_ACTVTY	Total days of outgoing activity
TOT_DRTN_OF_USG	Total durations of usage in customer life time
TOT_EXP_AMT_EQVLT_VAL	Total amount expired in customers life time
TOT_HLD_DRTN_LAST_3MO	Total hold duration in the last 3 months in seconds
TOT_HLD_DRTN_LAST_MO	Total hold duration this month in seconds
TOT_HLD_DRTN_LFTM	Total hold duration in customer life span in seconds
TOT_PYMT_RVN_LAST_3MO	Total Payment Revenue as of 3 months ago
TOT_PYMT_RVN_LAST_MO	Total Payment Revenue as of 1 month ago
TOT_QUE_DRTN_LAST_3MO	Total queue duration in the last 3 months in seconds
TOT_QUE_DRTN_LAST_MO	Total queue duration this month in seconds
TOT_QUE_DRTN_LFTM	Total queue duration in customer life span in seconds
TOT_RCHRG_AMT_LAST_3MO	Total recharge amount this month
TOT_RCHRG_AMT_LAST_MO	Total recharge amount in last 3 months
TOT_RCHRG_AMT_LFTM	Total recharge amount in customers life time
TOT_RCHRG_CNT_LAST_3MO	Number of recharges made in last 3 months
TOT_RCHRG_CNT_LAST_MO	Number of recharges made this month
TOT_RCHRG_CNT_LFTM	Number of recharges made in customer life time
TOT_TALK_DRTN_LAST_3MO	Total talk duration in the last 3 months in seconds
TOT_TALK_DRTN_LAST_MO	Total talk duration this month in seconds
TOT_TALK_DRTN_LFTM	Total talk duration in customer life span in seconds
TOT_UTILZD_EQVLT_VAL	Total utilized monetary value in customers life time
UNIV_ZIP_CD	University ZIP Code
UPLD_DATA_LAST_3MO	Data uploaded in KBs in last 3 months
UPLD_DATA_LAST_MO	Data uploaded in KBs in last 1 month
UPLD_DATA_LFTM	Data uploaded in KBs in lifetime
VOI_INTERNAT_AIRTIME_LAST_3MO	International voice air time including roaming in minutes in last three month
VOI_INTERNAT_AIRTIME_LAST_MO	International voice air time in minutes including roaming in last one month
VOI_INTERNAT_AIRTIME_LFTM	International voice air time in minutes including roaming over customer's life time
VOI_NAT_AIRTIME_LAST_3MO	National voice air time in minutes in last three month
VOI_NAT_AIRTIME_LAST_MO	National voice air time in minutes in last one month
VOI_NAT_AIRTIME_LFTM	National voice air time in minutes over customer's life time
WRLS_IND	Indicates whether Customer has Wireless Internet connection

Customer Life Time Value Survival Output Target Attribute: AGE_ON_NET_NBR

Data in DWV_CUST_LTV_SRC view is used to train Customer Life Time Survival Value regression mining model. Data in DWV_CUST_LTV_SGMNT_APPLY view, which has all active customers' data, is scored using the trained model and scoring results are saved into DWD_CUST_DNA table with the following column:

DWD_CUST_DNA.PRDCT_LT_SRVVL_VAL

Customer Life Time Value Survival Algorithms

The following algorithm is used to predict the Lifetime Value of active customer base:

Generalized Linear Model Regression

Customer Life Time Value Survival Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following is the setting table for customer Life Time Survival Value:

DM_STNG_LTV_GLMR - For Generalized Linear Model Regression

Model 7: Customer Sentiment

The business problem is to measure customer sentiment regarding the service quality according to any text message received from the customer. Those text messages may be emails from customer, or written down by call center agents during call center calls, and so on.

This model leverages Text mining capability provided by Oracle database. For more information on mining algorithms, see Oracle Data Mining Concepts and Oracle Data Mining Application Developer's Guide.

Customer Sentiment Source

The table DM_CUST_CMMNT is the main source table for Customer Sentiment classification model. The data in this table further transformed using Oracle Text to make it ready to be accepted by Oracle Data Mining.

Table 10-15 shows DM_CUST_CMMNT attributes.

Table 10-15 DM_CUST_CMMNT Customer Sentiment Source Table

Attribute	Data Type	Description
CUST_CMMNT	VARCHAR2(4000)	The text messages all together from the customer.
CUST_KEY	NUMBER(30)	Customer Key
MANUAL_SCORE	VARCHAR2(40)	Manual scores or manually adjusted after reading
MINUSPROB	NUMBER(20,16)	The probability of customer belonging to un-happy group
PLUSPROB	NUMBER(20,16)	The probability of customer belonging to happy group
SENTIMENT	VARCHAR2(40)	Sentiment scored by Mining Model

Customer Sentiment Output

The text mining sentiment analysis can be refined by extending the dictionary table DWD_CUST_SNTMNT_MANUAL_SCORE to improve the performance of the model. Predefined dictionary is used to manually score customers, who are not scored by customer call center. Data of manually scored and customer call center scored customers is used to train Customer Sentiment mining model. The trained mining model is then used to score customers' comments and the scoring results are saved into DWD_CUST_DNA table with the following columns:

DWD_CUST_DNA.SNTMNT_CTGRY_CD
DWD_CUST_DNA.MANUAL_SNTMNT_CTGRY
DWD_CUST_DNA. SNTMNT_PROB

Customer Sentiment Algorithm

This model uses the Oracle Text option first to transform Customer comments, then uses the SVM algorithm to train the model.

Oracle Text
Support Vector Machines

Customer Sentiment Algorithm Setting Tables

Algorithm settings tables allow you to override default values of different settings for mining algorithms. The following is the setting table for customer sentiment:

DM_STNG_SENTIMENT_SVM - For Generalized Linear Model Regression

Oracle Communications Data Model Mining Setting Tables

The algorithm setting tables allow you to override the default values of different settings for the Oracle Communications Data Model data mining algorithms.

Table 10-16 shows the data mining setting table setting structure for each of the following algorithms:

DM_STNG_USER_ALL
DM_STNG_SENTIMENT_SVM
DM_STNG_LTV_GLMR
DM_STNG_CHURN_SVM
DM_STNG_CHURN_DT
DM_STNG_PROFILE_KMEANS
DM_STNG_ATRIB_IMPORTANCE

Table 10-16 Data Mining Algorithm Setting Table Structure

Name	Data Type	Description
SETTING_NAME	VARCHAR2(500)	Setting Name
SETTING_VALUE	VARCHAR2(500)	Setting Value

Table 10-17 shows the data mining setting table structure for DM_STNG_CHURN_SVM_PRIORS.

Table 10-17 DM_STNG_CHURN_SVM_PRIORS Data Mining Algorithm Setting Table

Name	Data Type	Description
TARGET_VALUE	NUMBER	Target Value. For SVM Churn model, target value is either 1 or 0.
PRIOR_PROBABILITY	NUMBER	Prior probability of the target value.

Table 10-18 shows the data mining setting table structure for DM_STNG_CHURN_DT_COST.

Table 10-18 DM_STNG_CHURN_DT_COST Data Mining Algorithm Setting Table

Name	Data Type	Description
ACTUAL_TARGET_VALUE	NUMBER	Actual Target Value. For DT Churn model, target value is either 1 or 0.
PREDICTED_TARGET_VALUE	NUMBER	Target Value predicted by DT Churn model. Predicted target value is either 1 or 0.
COST	NUMBER	Cost of misclassification

Oracle Communications Data Model Data Mining Related Tables

In addition to result tables described in "Oracle Communications Data Model Result Tables" section, there are few more result tables, few lookup tables, and a table for predefined dictionary to manually score customers' comments.

Table 10-19 shows the DWD_CUST_SNTMNT_MANUAL_SCORE table. This predefined dictionary table manually scores customers' comments.

Table 10-19 DWD_CUST_SNTMNT_MANUAL_SCORE Data Mining Source Table

Name	Data Type	Description
SENTIMENT_EXPRSN	VARCHAR2(100)	Sentiment expression that customer give in e-mail or on phone.
SENTIMENT_SCORE	VARCHAR2(30)	Sentiment score for the expression. It is "-" or "+"

Table 10-20 DWL_MNNG_CHRN_TYP Data Mining Lookup Table

Name	Data Type	Description
LANG_CD	VARCHAR2(30)	Language Code
CHRN_TYP_CD	VARCHAR2(120)	Churn Type Code. It is 1 or 0.
CHRN_TYP_NAME	VARCHAR2(200)	Churn Type Name. It is "Churner" or "Non-Churner"
CHRN_TYP_DSCR	VARCHAR2(400)	Churn Type Description

Table 10-21 DWL_MNNG_SNTMNT_CTGRY Data Mining Lookup Table

Name	Data Type	Description
LANG_CD	VARCHAR2(30)	Language Code
SNTMNT_CTGRY_CD	VARCHAR2(30)	Sentiment Category Code. For e.g. Angry, Satisfied
SNTMNT_CTGRY_NAME	VARCHAR2(50)	Sentiment Category Name
SNTMNT_CTGRY_DSCR	VARCHAR2(500)	Sentiment Category Description

Table 10-22 DWL_MNNG_LTV_BAND Data Mining Result Table

Name	Data Type	Description
LANG_CD	VARCHAR2(30)	Language Code
LTV_BAND_CD	VARCHAR2(30)	Life Time Value Band Code
LTV_BAND_NAME	VARCHAR2(50)	Life Time Value Band Name
LTV_BAND_DSCR	VARCHAR2(500)	Life Time Value Band Description

Table 10-23 DWL_MNNG_LT_SRVVL_BAND Data Mining Result Table

Name	Data Type	Description
LANG_CD	VARCHAR2(30)	Language Code
LT_SRVVL_BAND_CD	VARCHAR2(30)	Life Time Survival Value Band Code
LT_SRVVL_BAND_NAME	VARCHAR2(50)	Life Time Survival Value Band Name
LT_SRVVL_BAND_DSCR	VARCHAR2(500)	Life Time Survival Value Band Description

Table 10-24 Sequences Defined for Data Mining

Table Name	Sequence Name
DWR_CUST_SGMNT	CUST_SGMNT_SEQ

Data Mining Package Customer Life Time Value Computation

In addition to the prebuilt data mining models, Oracle Communications Data Model core data mining package also includes a procedure for prepaid and postpaid customers to compute the Customer Life Time Value:

PKG_OCDM_MINING.COMPUTE_PRPD_CUST_LTV
PKG_OCDM_MINING.COMPUTE_PSTPD_CUST_LTV

The packages compute a value for a Service Provider that wants to know how valuable a customer would be to the Service Provider for next n months into future. The computation of the Life Time Value is different for prepaid and postpaid customers.

Prepaid Customer Life Time Value Computation

The computation of prepaid customer Life Time Value involves the parameters shown in Table 10-25, set in the DM_STNG_USER_ALL table. These settings can be updated according to Service Provider requirements.

Table 10-25 Prepaid Customer Life Time Value Computation

Parameter	Description
PREPAID_MARGIN_RATE (m)	% Profit margin of all prepaid customers
PREPAID_HISTORY_MONTHS (h)	Historical data to calculate Average Monthly Recharge Amount for each customer
ANNUAL_DISCOUNT_RATE (i)	Specifies the interest rate used in discounted cash flow analysis to determine the present value of cash flows. Usually it falls between 8% and 15%
CUST_LIFETIME_VALUE_COMPUATION_TIME_MONTHS (T)	Specifies for how long the Service Provider wants to calculate the Life Time Value

In addition to the parameters shown in Table 10-25, the prepaid customer Life Time Value calculation also involves the values shown in Table 10-26.

Table 10-26 Prepaid Customer Life Time Value Additional Required Parameter Values

Parameter	Description
RETENTION RATE (r)	1- the Predicted Churn Probability. The Predicted Churn Probability is retrieved from the `DWD_CUST_DNA` table.
AVERAGE MONTHLY RECHARGE AMOUNT or REVENUE (R)	Ratio of "Sum of all recharges in last PREPAID_HISTORY_MONTHS" to "PREPAID_HISTORY_MONTHS"

Prepaid Customer Life Time Value Calculation

Prepaid Customer Life Time Value =

Description of the illustration cdmrf_dm1.png

The computed prepaid Customer Life Time Value is saved to DWD_CUST_DNA table in the following column:

DWD_CUST_DNA. CMPTD_LTV_VALUE

Postpaid Customer Life Time Value Computation

The computation of postpaid customer Life Time Value involves the parameters shown in Table 10-27, which are all settings in the DM_STNG_USER_ALL table. These settings can be updated according to Service Provider requirements.

Table 10-27 Postpaid Customer Life Time Value Computation

Parameter	Description
POSTPAID_MARGIN_RATE (m)	% Profit margin of all postpaid customers
POSTPAID_HISTORY_MONTHS (h)	Specifies the historical data to calculate Average Monthly Billed Amount for each customer
ANNUAL_DISCOUNT_RATE (i)	Specifies the interest rate used in discounted cash flow analysis to determine the present value of cash flows. Usually it falls between 8% and 15%
CUST_LIFETIME_VALUE_COMPUATION_TIME_MONTHS (T)	Specifies how long the Service Provider wants to calculate the Life Time Value

In addition to the parameters specified in Table 10-27, the calculation also involves the parameters shown in Table 10-28.

Table 10-28 Postpaid Customer Life Time Value Additional Required Parameter Values

Parameter	Description
RETENTION RATE (r)	1- the Predicted Churn Probability. The Predicted Churn Probability is retrieved from the `DWD_CUST_DNA` table.
AVERAGE MONTHLY BILLED AMOUNT or REVENUE (R)	Ratio of "Sum of all Billed amounts in last POSTPAID_HISTORY_MONTHS" to "POSTPAID_HISTORY_MONTHS"

Postpaid Customer Life Time Value Calculation

Postpaid Customer Life Time Value =

Description of the illustration cdmrf_dm2.png

The computed postpaid Customer Life Time Value is saved in the table DWD_CUST_DNA with the following column:

DWD_CUST_DNA. CMPTD_LTV_VALUE