11 Communications Data Mining with Data Miner
Describes Data Mining with Data Miner
- Data Mining Miner Architecture
Covers Data Mining Miner Architecture - Data Model Mining Result Tables
Provides a description of the Oracle Communications Data Model Data Mining Result Tables. - Additional Data Model Mining Tables
Provides a description of several additional Oracle Communications Data Model Data Mining tables for use with Oracle Data Miner. - Prepaid Churn Prediction with Oracle Data Miner
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. - Postpaid Churn Prediction with Oracle Data Miner
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. - Customer Profiling with Oracle Data Miner
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 segment. - Customer Lifetime Value
- Customer Lifetime Survival Value
- 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. - 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.
Data Mining Miner Architecture
Covers Data Mining Miner Architecture
Oracle Communications Data Model Deliverables
-
ocdm_mining_init.sql
-
Initializes mining environment
-
Creates mining target tables, setting tables, mining utility package, and dictionary for scoring customer comments
-
-
cust_sntmnt_manual_score.sql
-
Creates dictionary for scoring customer comments
-
-
pkg_ocdm_mining_util.sql: Data Mining utility package that consists of procedures to:
-
Prepare data for customer sentiment analysis
-
Score customer comments using dictionary
-
Update mining target tables with model details, extracted rules, and prediction results
-
-
Oracle Data Miner workflows xml files:
-
prepaid_churn_dt_and_svm.xml
-
postpaid_churn_dt_and_svm.xml
-
customer_segmentation_kmeans.xml
-
customer_ltv_srvl_val_glmr.xml
-
customer_sentiment_svm.xml
-
target_promotion_svm.xml
-
-
ocdm_import_odm_workflow.sql
-
Creates data miner project
-
Imports OCDM Oracle Data Miner workflows into data miner project created in the above step
-
-
pkg_ocdm_mining_odmr_util.sql: Data Miner utility package that consists of procedures to:
-
Run Oracle Data Miner workflows
-
Extract workflows status and inserting it into dwc_odmr_wf_exctn control table
-
Check status of each workflow and invoke update mining target table procedure in pkg_ocdm_mining_util package
-
Steps for Setting Up Mining Environment
-
Execute ocdm_mining_init.sql script, which initializes mining environment. Creates mining target tables, setting tables, mining utility package, and dictionary for scoring customer comments.
-
Import Oracle Data Miner workflows using ocdm_import_odm_workflow.sql script.
-
Update from_date_etl and to_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
-
Steps of Mining Models Creation
-
Invoke pkg_ocdm_mining_odmr_util.odmr_run_wf procedure, which
-
Splits data into training, test, and apply data sets
-
Trains model using training data
-
Extracts mining model information and creates a database view to store the model information
-
Applies trained model on apply data set and creates a database view to store apply results
-
-
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure to extract Oracle Data Miner workflows status. Check dwc_odmr_wf_exctn table for workflows status.
-
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of each workflow and update mining target tables of the models created in the workflow if workflow execution finished successfully.
Mining Model Components
In the diagram, Oracle Data Miner Workflows is responsible for generating the Knowledge (mining rules) from the data available in Oracle Communications Data Model Main Model. Mining Utility Package has procedures to update mining target tables for all mining models. Mining models information, extracted rules, and prediction results are stored in Mining Result Tables.
Data Model Mining Result Tables
Provides a description of the Oracle Communications Data Model Data Mining Result Tables.
Section Title
Table 11-1 DWD_CUST_DNA Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
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_CHURN_DT_IND |
CHAR(1) |
Boolean value whether customer will churn in next three months according to DT model. |
|
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_DT_ND_NBR |
VARCHAR2(30) |
The ID of the node in the decision tree where the customer is assigned. |
|
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. |
|
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. |
|
PRDCT_LTV_VALUE |
NUMBER |
The real value of Customer Lifetime value, predicted by LTV (GLMR) Mode. |
|
LT_SRVVL_CD |
VARCHAR2(120) |
The band code of Customer Survival period (Life Expectancy), predicted by Life_Exp (GLMR) Model. |
|
PRDCT_LT_SRVVL_VAL |
NUMBER(22,7) |
The value of Customer Survival period (Life Expectancy), predicted by Life_Exp (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). |
|
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. |
|
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 11-2 DWD_CUST_PROD_AFFLTN - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
MO_KEY |
NUMBER(30) |
Month key for which the target promotion model was trained |
|
CUST_CD |
VARCHAR2(120) |
Customer natural key to identify the customer |
|
PROD_CD |
VARCHAR2(120) |
The product code which was predicted against. This is target product for promotion. |
|
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". |
Table 11-3 DWD_CHRN_SVM_FACTOR - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
MODEL_NAME |
VARCHAR2(120) |
Churn model name |
|
ACCT_TYP_CD |
VARCHAR2(120) |
Account Type Code. For e.g. 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:
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. |
Table 11-4 DWD_PROMO_SVM_FACTOR - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
PROD_CD |
VARCHAR2(50) |
The product code which was predicted against. This is target product for promotion. |
|
MO_KEY |
NUMBER(30) |
Month key for which the target promotion model was trained |
|
MODEL_NAME |
VARCHAR2(200) |
Target promotion model name |
|
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:
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. |
Table 11-5 DWR_CUST_DT_NODE - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
MODEL_NAME |
VARCHAR2(120) |
Churn model name |
|
ACCT_TYP_CD |
VARCHAR2(120) |
Account Type Code. For e.g. Prepaid, Postpaid |
|
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 |
|
IS_LEAF |
VARCHAR2(10) |
Indicates whether the node is a leaf indicator. The prediction of lead node is the final prediction |
|
CONFIDENCE |
NUMBER |
Ratios of prediction_count to record_count |
|
SUPPORT |
NUMBER |
Ration of record_count to total number of customers |
|
RULE |
VARCHAR2(4000) |
Decision Tree rule |
Table 11-6 DWD_PRPD_CHRN_SVM_ROC - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
PROBABILITY |
NUMBER |
Prepaid churn probability threshold |
|
TRUE_POSITIVES |
NUMBER |
Number of true positive cases |
|
FALSE_NEGATIVES |
NUMBER |
Number of false negative cases |
|
FALSE_POSITIVES |
NUMBER |
Number of false positive cases |
|
TRUE_NEGATIVES |
NUMBER |
Number of true negative cases |
|
TRUE_POSITIVE_FRACTION |
NUMBER |
Ratio of “Number of true positives” to the “Number of total actual positives” |
|
FALSE_POSITIVE_FRACTION |
NUMBER |
Ration of “Number of false positives” to the “Number of actual negatives” |
Table 11-7 DWD_PSTPD_CHRN_SVM_ROC - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
PROBABILITY |
NUMBER |
Postpaid churn probability threshold |
|
TRUE_POSITIVES |
NUMBER |
Number of true positive cases |
|
FALSE_NEGATIVES |
NUMBER |
Number of false negative cases |
|
FALSE_POSITIVES |
NUMBER |
Number of false positive cases |
|
TRUE_NEGATIVES |
NUMBER |
Number of true negative cases |
|
TRUE_POSITIVE_FRACTION |
NUMBER |
Ratio of “Number of true positives” to the “Number of total actual positives” |
|
FALSE_POSITIVE_FRACTION |
NUMBER |
Ration of “Number of false positives” to the “Number of actual negatives” |
Table 11-8 DWD_TARGET_PROMO_SVM_ROC - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
PROBABILITY |
NUMBER |
Target product buy probability threshold |
|
TRUE_POSITIVES |
NUMBER |
Number of true positive cases |
|
FALSE_NEGATIVES |
NUMBER |
Number of false negative cases |
|
FALSE_POSITIVES |
NUMBER |
Number of false positive cases |
|
TRUE_NEGATIVES |
NUMBER |
Number of true negative cases |
|
TRUE_POSITIVE_FRACTION |
NUMBER |
Ratio of “Number of true positives” to the “Number of total actual positives” |
|
FALSE_POSITIVE_FRACTION |
NUMBER |
Ration of “Number of false positives” to the “Number of actual negatives” |
Table 11-9 DWD_PRPD_CHRN_SVM_LIFT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
QUANTILE_NUMBER |
NUMBER |
Quantile number |
|
PROBABILITY_THRESHOLD |
NUMBER |
Prepaid churn probability threshold |
|
GAIN_CUMULATIVE |
NUMBER |
Cumulative Gain |
|
QUANTILE_TOTAL_COUNT |
NUMBER |
Total Count in the current Quantile |
|
QUANTILE_TARGET_COUNT |
NUMBER |
Target Count of the current Quantile |
|
PERCENTAGE_RECORDS_CUMULATIVE |
NUMBER |
Cumulative Percentage Records |
|
LIFT_CUMULATIVE |
NUMBER |
Cumulative Lift |
|
TARGET_DENSITY_CUMULATIVE |
NUMBER |
Cumulative Target Density |
|
TARGETS_CUMULATIVE |
NUMBER |
Cumulative Targets |
|
NON_TARGETS_CUMULATIVE |
NUMBER |
Cumulative Non-targets |
|
LIFT_QUANTILE |
NUMBER |
Quantile Lift |
|
TARGET_DENSITY |
NUMBER |
Target Density |
Table 11-10 DWD_PSTPD_CHRN_SVM_LIFT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
QUANTILE_NUMBER |
NUMBER |
Quantile number |
|
PROBABILITY_THRESHOLD |
NUMBER |
Postpaid churn probability threshold |
|
GAIN_CUMULATIVE |
NUMBER |
Cumulative Gain |
|
QUANTILE_TOTAL_COUNT |
NUMBER |
Total Count in the current Quantile |
|
QUANTILE_TARGET_COUNT |
NUMBER |
Target Count of the current Quantile |
|
PERCENTAGE_RECORDS_CUMULATIVE |
NUMBER |
Cumulative Percentage Records |
|
LIFT_CUMULATIVE |
NUMBER |
Cumulative Lift |
|
TARGET_DENSITY_CUMULATIVE |
NUMBER |
Cumulative Target Density |
|
TARGETS_CUMULATIVE |
NUMBER |
Cumulative Targets |
|
NON_TARGETS_CUMULATIVE |
NUMBER |
Cumulative Non-targets |
|
LIFT_QUANTILE |
NUMBER |
Quantile Lift |
|
TARGET_DENSITY |
NUMBER |
Target Density |
Table 11-11 DWD_TARGET_PROMO_SVM_LIFT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
QUANTILE_NUMBER |
NUMBER |
Quantile number |
|
PROBABILITY_THRESHOLD |
NUMBER |
Target product buy probability threshold |
|
GAIN_CUMULATIVE |
NUMBER |
Cumulative Gain |
|
QUANTILE_TOTAL_COUNT |
NUMBER |
Total Count in the current Quantile |
|
QUANTILE_TARGET_COUNT |
NUMBER |
Target Count of the current Quantile |
|
PERCENTAGE_RECORDS_CUMULATIVE |
NUMBER |
Cumulative Percentage Records |
|
LIFT_CUMULATIVE |
NUMBER |
Cumulative Lift |
|
TARGET_DENSITY_CUMULATIVE |
NUMBER |
Cumulative Target Density |
|
TARGETS_CUMULATIVE |
NUMBER |
Cumulative Targets |
|
NON_TARGETS_CUMULATIVE |
NUMBER |
Cumulative Non-targets |
|
LIFT_QUANTILE |
NUMBER |
Quantile Lift |
|
TARGET_DENSITY |
NUMBER |
Target Density |
Table 11-12 DWD_PRPD_CHRN_SVM_LIFT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
ACTUAL_TARGET_VALUE |
CHAR(1) |
Actual prepaid churn indicator |
|
PREDICTED_TARGET_VALUE |
VARCHAR2(1) |
Predicted prepaid churn indicator |
|
VALUE |
NUMBER |
Number of customers |
Table 11-13 DWD_PSTPD_CHRN_SVM_LIFT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
ACTUAL_TARGET_VALUE |
CHAR(1) |
Actual postpaid churn indicator |
|
PREDICTED_TARGET_VALUE |
VARCHAR2(1) |
Predicted postpaid churn indicator |
|
VALUE |
NUMBER |
Number of customers |
Table 11-14 DWD_PSTPD_CHRN_SVM_LIFT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
ACTUAL_TARGET_VALUE |
CHAR(1) |
Actual target product buy indicator |
|
PREDICTED_TARGET_VALUE |
VARCHAR2(1) |
Predicted target product buy indicator |
|
VALUE |
NUMBER |
Number of customers |
Table 11-15 DWR_CUST_SGMNT - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
CUST_SGMNT_KEY |
NUMBER |
Customer segment key generated by database sequence |
|
CUST_SGMNT_CD |
VARCHAR2(120) |
Customer segment code |
|
CUST_SGMNT_NAME |
VARCHAR2(400) |
Customer segment name |
|
CUST_SGMNT_DSCR |
LONG |
Customer segment description |
|
SGMNT_DISPRSN |
VARCHAR2(500) |
Segment dispersion. Measures how closely the customers in the segment have their characteristics |
|
SPPRTG_REC_CNT |
NUMBER |
Supporting record count |
|
TREE_LVL |
NUMBER |
Tree level in hierarchical cluster |
|
IS_LEAF_IND |
CHAR(1) |
Indicates whether the segment is at leaf level |
Table 11-16 DWR_CUST_SGMNT_DTL - Data Mining Results Table
| Name | Data Type | Description |
|---|---|---|
|
SGMNT_ID |
NUMBER |
Segment identifier |
|
ATTRIBUTE_NAME |
VARCHAR2(4000) |
Attribute name |
|
MEAN |
NUMBER |
Mean value of all the customers in the segment (if attribute is numeric) |
|
MODE_VALUE |
VARCHAR2(4000) |
Mode value of all the customers in the segment (if attribute is categorical) |
Additional Data Model Mining Tables
Provides a description of several additional Oracle Communications Data Model Data Mining tables for use with Oracle Data Miner.
Data Model Mining Control Tables
In addition to result tables described in, there are several additional Data Mining result tables, lookup tables, setting tables, and a table for the dictionary to manually score customer comments.
Table 11-17 DWC_ODMR_WF_EXCTN – Data Miner Workflow Status Log Table
| Name | Data Type | Description |
|---|---|---|
|
PROJECT_ID |
NUMBER |
Data miner project identifier |
|
PROJECT_NAME |
VARCHAR2(120) |
Data miner project name |
|
WORKFLOW_ID |
NUMBER |
Data miner workflow identifier |
|
WORKFLOW_NAME |
VARCHAR2(120) |
Data miner workflow name |
|
WF_JOB_NAME |
VARCHAR2(30) |
Data miner workflow job name |
|
WF_EXCTN_STATUS |
VARCHAR2(30) |
Data miner workflow execution status |
|
WF_ERROR_DTL |
VARCHAR2(2000) |
Data miner workflow error detail |
|
WF_EXCTN_START_TIME |
TIMESTAMP(6) |
Data miner workflow execution start time |
|
WF_EXCTN_END_TIME |
TIMESTAMP(6) |
Data miner workflow execution end time |
|
WF_EXCTN_DRTN_SECONDS |
NUMBER |
Data miner workflow execution duration in seconds |
Table 11-18 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 11-19 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 11-20 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 11-21 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 11-22 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 11-23 DM_STNG_USER_ALL – Data Mining Setting Table
| Name | Data Type | Description |
|---|---|---|
|
SETTING_NAME |
VARCHAR2(500) |
Setting Name |
|
SETTING_VALUE |
VARCHAR2(500) |
Setting Value |
| Table Name | Sequence Name |
|---|---|
|
DWR_CUST_SGMNT |
CUST_SGMNT_SEQ |
Prepaid Churn Prediction with Oracle Data Miner
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 Churn Prediction Model and 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 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, we may receive the data only 3 months after the actual Churn. So time window should be adjusted.
-
Support Vector Machines
-
Decision Tree
Table 11-24 Attributes Identified from DWD_CUST_DNA for Prepaid Churn
| Attribute | Description |
|---|---|
|
CUST_CD |
Customer Identifier |
|
PRTY_TYP_CD |
Party Type Code, For example: Individual, Large Enterprise, Medium Enterprise, and so on. |
|
BSNS_LEGAL_STAT_CD |
Legal Status Code of Enterprise Customers, For example:. Public, Private |
|
HH_SZ |
Household Size |
|
LANG_CD |
Language 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. |
|
ESTMTD_ACQSTN_COST |
Customer Acquisition Estimated Cost |
|
BRDBND_IND |
Indicates whether Customer has Broadband connection |
|
PAY_TV_IND |
Indicates whether Customer has Pay TV connection |
|
IDD_IND |
Indicates whether Customer subscribed to International Direct Dialing |
|
HOMTEL_IND |
Indicates whether Customer has Home Telephone |
|
WRLS_IND |
Indicates whether Customer has Wireless Internet connection |
|
NEW_ACCT_IND |
Indicates whether Customer is New |
|
PORT_IN_FROM |
Service Provide from who the customer ported out from |
|
PORT_IN_CNT |
Number of times the customer ported in |
|
PORT_OUT_CNT |
Number of times the customer ported out |
|
CRDT_CTGRY_KEY |
Customer Credit Category |
|
ARPU_BND_CD |
Customer ARPU Band Code |
|
SL_CHNL_KEY |
Sales Channel |
|
SL_CHNL_RPRSTV_KEY |
Sales Channel Representative |
|
CUST_RVN_BND_CD |
Customer Revenue Band Code |
|
LIFE_SPN |
Customer Life Span |
|
CUST_TYP_CD |
Customer Type Code |
|
MULT_PRTY_ROLE_IND |
Indicates whether customer has multiple party roles, e.g. both an employee and customer of Service Provider |
|
PRMRY_STAT_CD |
Customer Primary Status Code |
|
PRMRY_STAT_RSN_CD |
Customer Primary Status Reason 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 |
|
AGE_BND_CD |
Customer Age Band 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. |
|
MAIL_ALWD_IND |
Indicates whether Customer allows Service Provider to send mail |
|
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. |
|
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. |
|
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. |
|
NO_MTHS_POSN |
Number of months an Individual Customer is in his/her current Position |
|
NO_MTHS_CMPNY |
Number of months an Individual Customer is in his/her current Company |
|
ECNMCLY_ACTV_IND |
Indicates whether a customer is economically Active |
|
ADDR_LOC_CD |
Customer Address Location Code |
|
CUST_SCR_CD |
Customer SCR Code |
|
GNDR_CD |
Individual Customer Gender Code |
|
MRTL_STAT_CD |
Individual Customer Marital Status Code |
|
NTNLTY_CD |
Customer Nationality Code |
|
ETHNIC_BCKGRND |
Customer Ethnic Background |
|
ETHNCTY |
Customer Ethnicity |
|
PLC_OF_BRTH |
Place of Birth |
|
JB_CD |
Customer Job Code |
|
JB_POSN |
Customer Job Position |
|
LEGAL_TTL_TO_HSNG |
Legal Title to Housing |
|
EDU_CD |
Education Qualification Code |
|
POSTCD_CD |
Postcard Code |
|
CITY |
City |
|
STATE |
State |
|
CNTRY |
Country |
|
NAME_PRFX |
Name Prefix |
|
FORM_OF_EMPMNT |
Form of Employment |
|
CUST_BRANCH_CD |
Customer Branch Code |
|
NAME_OF_WKPLC |
Name of Workplace |
|
JB_AGRMNT_TYP |
Job Agreement Type |
|
BARNG_RSN_CD |
Customer Barring Reason Code |
|
SRC_OF_INCM |
Source of Income |
|
DWLNG_OWNER |
Dwelling Owner |
|
DWLNG_STAT |
Dwelling Status |
|
DWLNG_TYP |
Dwelling Type |
|
DWLNG_TENR |
Dwelling Tenure |
|
DWLNG_SZ |
Dwelling Size |
|
CAR_DRVR_LICNS_IND |
Indicates whether customer has Car Driving License |
|
OTH_DRVR_LICNS_IND |
Indicates whether customer has other vehicle License |
|
SPRT_IND |
Indicates whether customer plays Sports |
|
SPRT_TYP_CD |
Sport Type Code |
|
NBR_OF_CHLDRN |
Total Number of Children |
|
NBR_OF_DPNDNT |
Total Number of Dependents |
|
TOT_AGE_CHLDRN |
Total Age of Children |
|
NBR_CHLDRN_AT_SCHL |
Total Number of Children at School |
|
SCHL_ZIP_CD |
School ZIP Code |
|
NBR_CHLDRN_AT_COLL |
Total Number of Children at College |
|
COLL_ZIP_CD |
College ZIP Code |
|
NBR_CHLDRN_AT_UNIV |
Total Number of Children at University |
|
UNIV_ZIP_CD |
University ZIP Code |
|
CAR_TYP_CD |
Car Type Code |
|
CNTCT_ALLWD_IND |
Indicates whether customer allows to contact |
|
PREF_CNTCT_MDM |
Preferred Contact Medium |
|
ACCPT_NWSLTR_IND |
Indicates whether customer accepts News Letter |
|
PREF_GVN_IND |
Indicates whether customer given any preference |
|
PREF_SPRT_IND |
Indicates whether customer given any sport preference |
|
PREF_ENTMNT_IND |
Indicates whether customer given any Entitlement preference |
|
PREF_TRVL_IND |
Indicates whether customer given any Travel preference |
|
PREF_MSC_IND |
Indicates whether customer given any Music preference |
|
SCL_NTWK_USR_IND |
Indicates whether customer uses any Social Network site |
|
LIKE_SCP_IN_FCBK_IND |
Indicates whether customer likes Service Provide page in Facebook |
|
TOT_CALL_CNT_LFTM |
Total number of calls to Call center made by customer in his/her life span |
|
CMPLNT_CNT_LFTM |
Number of complaints made by customer in his/her life span |
|
DRPD_CALLS_CNT_LFTM |
Number of dropped calls in customer life span |
|
HNGUP_CALLS_CNT_LFTM |
Number of hangup calls in customer life span |
|
RLSD_CALLS_CNT_LFTM |
Number of released calls in customer life span |
|
TOT_QUE_DRTN_LFTM |
Total queue duration in customer life span in seconds |
|
TOT_HLD_DRTN_LFTM |
Total hold duration in customer life span in seconds |
|
TOT_TALK_DRTN_LFTM |
Total talk duration in customer life span in seconds |
|
RMRK_CNT_LFTM |
Numbers of remarks made against customer in his/her life span |
|
PRTY_AGE |
Customer Age expressed as number of years |
|
TOT_CALL_CNT_LAST_3MO |
Total number of calls to Call center made by customer in last 3 months |
|
CMPLNT_CNT_LAST_3MO |
Number of complaints made by customer in last 3 months |
|
DRPD_CALLS_CNT_LAST_3MO |
Number of dropped calls in last 3 months |
|
HNGUP_CALLS_CNT_LAST_3MO |
Number of hangup calls in last 3 months |
|
RLSD_CALLS_CNT_LAST_3MO |
Number of released calls in last 3 months |
|
TOT_QUE_DRTN_LAST_3MO |
Total queue duration in in last 3 months in seconds |
|
TOT_HLD_DRTN_LAST_3MO |
Total hold duration in in last 3 months in seconds |
|
TOT_TALK_DRTN_LAST_3MO |
Total talk duration in in last 3 months in seconds |
|
RMRK_CNT_LAST_3MO |
Numbers of remarks made against customer in last 3 months |
|
TOT_CALL_CNT_LAST_MO |
Total number of calls to Call center made by customer in this month |
|
CMPLNT_CNT_LAST_MO |
Number of complaints made by customer in this month |
|
DRPD_CALLS_CNT_LAST_MO |
Number of dropped calls this month |
|
HNGUP_CALLS_CNT_LAST_MO |
Number of hangup calls this month |
|
RLSD_CALLS_CNT_LAST_MO |
Number of released calls this month |
|
TOT_QUE_DRTN_LAST_MO |
Total queue duration this month in seconds |
|
TOT_HLD_DRTN_LAST_MO |
Total hold duration this month in seconds |
|
TOT_TALK_DRTN_LAST_MO |
Total talk duration this month in seconds |
|
RMRK_CNT_LAST_MO |
Numbers of remarks made against customer in this month |
|
TOT_RCHRG_AMT_LFTM |
Total recharge amount in customers life time |
|
TOT_RCHRG_AMT_LAST_MO |
Total recharge amount in last 3 months |
|
TOT_RCHRG_AMT_LAST_3MO |
Total recharge amount this month |
|
LAST_RCHRG_AMT |
Last recharge amount |
|
MIN_RCHRG_AMT |
Minimum recharge amount |
|
MAX_RCHRG_AMT |
Maximum recharge amount |
|
TOT_RCHRG_CNT_LFTM |
Number of recharges made in customer life time |
|
TOT_RCHRG_CNT_LAST_MO |
Number of recharges made this month |
|
TOT_RCHRG_CNT_LAST_3MO |
Number of recharges made in last 3 months |
|
TOT_EXP_AMT_EQVLT_VAL |
Total amount expired in customers life time |
|
TOT_UTILZD_EQVLT_VAL |
Total utilized monetary value in customers life time |
|
TOT_CNTRBTN_LFTM |
Sum of total payments and total recharges made in customers life time |
|
TOT_CNTRBTN_LAST_MO |
Sum of total payments and total recharges made in the last one month |
|
TOT_DRTN_OF_USG |
Total durations of usage in customer life time |
|
DAYS_BFR_FIRST_RCHRG |
Days between first payment and first recharge |
|
DAYS_BFR_FIRST_USE |
Days between payment and first use |
|
AVG_DRTN_BTWN_RCHRG |
Average duration between two recharges - in days |
|
TOT_DAYS_ON_USE |
Total days on use |
|
TOT_DAYS_ACTVTY |
Total days of activity |
|
TOT_DAYS_OUTGOING_ACTVTY |
Total days of outgoing activity |
|
LAST_OUTGOING_USG_DT_NBR |
When was last outgoing call occurred |
|
LAST_INCOMING_USG_DT_NBR |
When was last incoming call occurred |
|
VOI_NAT_AIRTIME_LFTM |
National voice air time in minutes over customer's life time |
|
VOI_NAT_AIRTIME_LAST_MO |
National voice air time in minutes in last one month |
|
VOI_NAT_AIRTIME_LAST_3MO |
National voice air time in minutes in last three months |
|
VOI_INTERNAT_AIRTIME_LFTM |
International voice air time in minutes including roaming over customer's life time |
|
VOI_INTERNAT_AIRTIME_LAST_MO |
International voice air time in minutes including roaming in last one month |
|
VOI_INTERNAT_AIRTIME_LAST_3MO |
International voice air time including roaming in minutes in last three months |
|
SMS_CNT_LFTM |
SMSs sent over customer's lifetime |
|
SMS_CNT_LAST_MO |
SMSs sent in last 1 month |
|
SMS_CNT_LAST_3MO |
SMSs sent in last 3 months |
|
MMS_CNT_LFTM |
MMSs sent over customer's lifetime |
|
MMS_CNT_LAST_MO |
MMSs sent in last 1 month |
|
MMS_CNT_LAST_3MO |
MMSs sent in last 3 months |
|
DWNLD_DATA_LFTM |
Data downloaded in KBs in lifetime |
|
DWNLD_DATA_LAST_MO |
Data downloaded in KBs in last 1 month |
|
DWNLD_DATA_LAST_3MO |
Data downloaded in KBs in last 3 months |
|
UPLD_DATA_LFTM |
Data uploaded in KBs in lifetime |
|
UPLD_DATA_LAST_MO |
Data uploaded in KBs in last 1 month |
|
UPLD_DATA_LAST_3MO |
Data uploaded in KBs in last 3 months |
|
OFFNET_CALLS_LFTM |
Number of offnet calls in lifetime |
|
OFFNET_CALLS_LAST_MO |
Number of offnet calls in last 1 month |
|
OFFNET_CALLS_LAST_3MO |
Number of offnet calls in last 3 months |
|
ONNET_CALLS_LFTM |
Number of onnet calls in lifetime |
|
ONNET_CALLS_LAST_MO |
Number of onnet calls in last 1 month |
|
ONNET_CALLS_LAST_3MO |
Number of onnet calls in last 3 months |
|
OFFNET_AIRTIME_LFTM |
Total offnet airtime in minutes in lifetime |
|
OFFNET_AIRTIME_LAST_MO |
Total offnet airtime in minutes in last 1 month |
|
OFFNET_AIRTIME_LAST_3MO |
Total offnet airtime in minutes in last 3 months |
|
ONNET_AIRTIME_LFTM |
Total onnet airtime in minutes in lifetime |
|
ONNET_AIRTIME_LAST_MO |
Total onnet airtime in minutes in last 1 month |
|
ONNET_AIRTIME_LAST_3MO |
Total onnet airtime in minutes in last 3 months |
|
CHRN_IND |
Indicates whether a customer is a Churner or Non-churner |
Oracle Data Miner Workflow for PREPAID_CHURN_DT_AND_SVM
Workflow Description:
-
In data miner workflow, relevant columns are selected from DWD_CUST_DNA in Data Source node
-
Data is split into source and apply datasets
-
Source dataset is further split into training and test datasets
-
Training dataset is used to train prepaid churn models using two algorithms, Support Vector Machines and Decision Tree
-
Models are PRPD_CUST_CHRN_DT and PRPD_CUST_CHRN_SVM
-
Decision Tree Churn model, PRPD_CUST_CHRN_DT, details are extracted and stored in DWV_PRPD_CHRN_DT_NODE database view
-
Support Vector Machines Churn model, PRPD_CUST_CHRN_SVM, details are extracted and stored in DWV_PRPD_CHRN_SVM_FACTOR database view
-
Test dataset is stored in DWV_PRPD_CUST_CHRN_TST database view. This data is later used to extract ROC, LIFT, and Confusion Matrix from Support Vector Machines Churn model, PRPD_CUST_CHRN_SVM
-
Apply dataset is scored using trained prepaid churn models. PRPD_CUST_CHRN_DT model apply results are stored in DWV_PRPD_CHRN_DT_APPLY database view and PRPD_CUST_CHRN_SVM model apply results are stored in DWV_PRPD_CHRN_SVM_APPLY database view
Data Model Mining Target Tables
prepaid_churn_dt_and_svm workflow is executed when pkg_ocdm_mining_odmr_util.odmr_run_wf procedure invoked. Workflows status is extracted and stored into dwc_odmr_wf_exctn table by invoking pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure.
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of workflow execution. If workflow is executed successfully without any errors, then the procedure will invoke pkg_ocdm_mining_util.updt_mnng_trgt_prpd_chrn_dt and pkg_ocdm_mining_util.updt_mnng_trgt_prpd_chrn_svm procedures to update mining target tables for PRPD_CUST_CHRN_DT and PRPD_CUST_CHRN_SVM models.
Following table gives the list of target tables (columns) that get updated/loaded/created
Table 11-25 Target Tables (columns) that get updated/loaded/created
| Model Name | Target Table Name | Database Operation | Columns |
|---|---|---|---|
|
PRPD_CUST_CHRN_DT |
DWR_CUST_DT_NODE |
Insert |
No value |
|
PRPD_CUST_CHRN_DT |
DWD_CUST_DNA |
Update |
PRDCT_CHURN_DT_IND PRDCT_CHURN_DT_PROB PRDCT_CHURN_DT_ND_NBR |
|
PRPD_CUST_CHRN_SVM |
DWD_CHRN_SVM_FACTOR |
Insert |
No value |
|
PRPD_CUST_CHRN_SVM |
DWD_PRPD_CHRN_SVM_ROC |
Create |
No value |
|
PRPD_CUST_CHRN_SVM |
DWD_PRPD_CHRN_SVM_LIFT |
Create |
No value |
|
PRPD_CUST_CHRN_SVM |
DWD_PRPD_CHRN_SVM_CNF_MTRX |
Create |
No value |
|
PRPD_CUST_CHRN_SVM |
DWD_CUST_DNA |
Update |
PRDCT_CHURN_SVM_IND PRDCT_CHURN_SVM_PROB |
Postpaid Churn Prediction with Oracle Data Miner
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 Churn Prediction Model and 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 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, we may receive the data only 3 months after the actual Churn. So time window should be adjusted.
-
Support Vector Machines
-
Decision Tree
Table 11-26 Source Attributes Identified from DWD_CUST_DNA table for Postpaid Churn
| Attribute | Description |
|---|---|
|
CUST_CD |
Customer Identifier |
|
FUTRE_AGRMNT_CNT_LAST_3MO |
Number of Future Agreements in last 3 months |
|
AGRMNT_CNT_LAST_3MO |
Number of Agreements in last 3 months |
|
AGRMNT_LFT_DAYS_LAST_3MO |
Agreement Left Days in last 3 months |
|
FUTRE_AGRMNT_DAYS_LAST_3MO |
Future Agreement Days in last 3 months |
|
DEBT_VAL_LAST_3MO |
Debt Value in last 3 months |
|
TOT_PYMT_RVN_LAST_3MO |
Total Payment Revenue as of 3 months ago |
|
MO_RVN_LAST_3MO |
Monthly Revenue as of 3 months ago |
|
DEBT_AGNG_BND_CD_LAST_3MO |
Debt Ageing Band Code in last 3 months |
|
FUTRE_AGRMNT_CNT_LAST_MO |
Number of Future Agreements in last 1 month |
|
AGRMNT_CNT_LAST_MO |
Number of Agreements in last 1 month |
|
AGRMNT_LFT_DAYS_LAST_MO |
Agreement Left Days in last 1 month |
|
FUTRE_AGRMNT_DAYS_LAST_MO |
Future Agreement Days in last 1 month |
|
DEBT_VAL_LAST_MO |
Debt Value in last 1 month |
|
TOT_PYMT_RVN_LAST_MO |
Total Payment Revenue as of 1 month ago |
|
MO_RVN_LAST_MO |
Monthly Revenue as of 1 month ago |
|
DEBT_AGNG_BND_CD_LAST_MO |
Debt Ageing Band Code in last 1 month |
|
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. |
|
BNKRPT_STAT |
Bankrupt Status |
|
BLLG_ADDR_EFF_DT_NBR |
Billing 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. |
|
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. |
|
CUST_PYMT_RESPBL_IND |
Indicates whether customer is responsible for making payments |
|
CMPNY_TYP_CD |
Company Type Code |
|
SOC_JB_CD |
SOC Job Code |
|
EXTRNL_ORG_TYP_CD |
External Organization Type Code |
|
CMPNY_EMP_SZ_BND_CD |
Enterprise Customer Employee Size Band Code |
|
CMPNY_RVN_BND_CD |
Enterprise Customer Revenue Band Code |
|
NBR_EMP_SNC_CUST |
Numbers of Employees Since Customer |
|
PREF_PYMT_MTHD_TYP_CD |
Preferred Payment Method Type Code |
|
PRTY_TYP_CD |
Party Type Code, e.g. Individual, Large Enterprise, Medium Enterprise, etc. |
|
BSNS_LEGAL_STAT_CD |
Legal Status Code of Enterprise Customers, e.g. Public, Private |
|
HH_SZ |
Household Size |
|
LANG_CD |
Language 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. |
|
ESTMTD_ACQSTN_COST |
Customer Acquisition Estimated Cost |
|
BRDBND_IND |
Indicates whether Customer has Broadband connection |
|
PAY_TV_IND |
Indicates whether Customer has Pay TV connection |
|
IDD_IND |
Indicates whether Customer subscribed to International Direct Dialing |
|
HOMTEL_IND |
Indicates whether Customer has Home Telephone |
|
WRLS_IND |
Indicates whether Customer has Wireless Internet connection |
|
NEW_ACCT_IND |
Indicates whether Customer is New |
|
PORT_IN_FROM |
Service Provide from who the customer ported out from |
|
PORT_IN_CNT |
Number of times the customer ported in |
|
PORT_OUT_CNT |
Number of times the customer ported out |
|
CRDT_CTGRY_KEY |
Customer Credit Category |
|
ARPU_BND_CD |
Customer ARPU Band Code |
|
SL_CHNL_KEY |
Sales Channel |
|
SL_CHNL_RPRSTV_KEY |
Sales Channel Representative |
|
CUST_RVN_BND_CD |
Customer Revenue Band Code |
|
LIFE_SPN |
Customer Life Span |
|
CUST_TYP_CD |
Customer Type Code |
|
MULT_PRTY_ROLE_IND |
Indicates whether customer has multiple party roles, e.g. both an employee and customer of Service Provider |
|
PRMRY_STAT_CD |
Customer Primary Status Code |
|
PRMRY_STAT_RSN_CD |
Customer Primary Status Reason 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 |
|
AGE_BND_CD |
Customer Age Band 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. |
|
MAIL_ALWD_IND |
Indicates whether Customer allows Service Provider to send mail |
|
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. |
|
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. |
|
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. |
|
NO_MTHS_POSN |
Number of months an Individual Customer is in his/her current Position |
|
NO_MTHS_CMPNY |
Number of months an Individual Customer is in his/her current Company |
|
ECNMCLY_ACTV_IND |
Indicates whether a customer is economically Active |
|
ADDR_LOC_CD |
Customer Address Location Code |
|
CUST_SCR_CD |
Customer SCR Code |
|
GNDR_CD |
Individual Customer Gender Code |
|
MRTL_STAT_CD |
Individual Customer Marital Status Code |
|
NTNLTY_CD |
Customer Nationality Code |
|
ETHNIC_BCKGRND |
Customer Ethnic Background |
|
ETHNCTY |
Customer Ethnicity |
|
PLC_OF_BRTH |
Place of Birth |
|
JB_CD |
Customer Job Code |
|
JB_POSN |
Customer Job Position |
|
LEGAL_TTL_TO_HSNG |
Legal Title to Housing |
|
EDU_CD |
Education Qualification Code |
|
POSTCD_CD |
Postcard Code |
|
CITY |
City |
|
STATE |
State |
|
CNTRY |
Country |
|
NAME_PRFX |
Name Prefix |
|
FORM_OF_EMPMNT |
Form of Employment |
|
CUST_BRANCH_CD |
Customer Branch Code |
|
NAME_OF_WKPLC |
Name of Workplace |
|
JB_AGRMNT_TYP |
Job Agreement Type |
|
BARNG_RSN_CD |
Customer Barring Reason Code |
|
SRC_OF_INCM |
Source of Income |
|
DWLNG_OWNER |
Dwelling Owner |
|
DWLNG_STAT |
Dwelling Status |
|
DWLNG_TYP |
Dwelling Type |
|
DWLNG_TENR |
Dwelling Tenure |
|
DWLNG_SZ |
Dwelling Size |
|
CAR_DRVR_LICNS_IND |
Indicates whether customer has Car Driving License |
|
OTH_DRVR_LICNS_IND |
Indicates whether customer has other vehicle License |
|
SPRT_IND |
Indicates whether customer plays Sports |
|
SPRT_TYP_CD |
Sport Type Code |
|
NBR_OF_CHLDRN |
Total Number of Children |
|
NBR_OF_DPNDNT |
Total Number of Dependents |
|
TOT_AGE_CHLDRN |
Total Age of Children |
|
NBR_CHLDRN_AT_SCHL |
Total Number of Children at School |
|
SCHL_ZIP_CD |
School ZIP Code |
|
NBR_CHLDRN_AT_COLL |
Total Number of Children at College |
|
COLL_ZIP_CD |
College ZIP Code |
|
NBR_CHLDRN_AT_UNIV |
Total Number of Children at University |
|
UNIV_ZIP_CD |
University ZIP Code |
|
CAR_TYP_CD |
Car Type Code |
|
CNTCT_ALLWD_IND |
Indicates whether customer allows to contact |
|
PREF_CNTCT_MDM |
Preferred Contact Medium |
|
ACCPT_NWSLTR_IND |
Indicates whether customer accepts News Letter |
|
PREF_GVN_IND |
Indicates whether customer given any preference |
|
PREF_SPRT_IND |
Indicates whether customer given any sport preference |
|
PREF_ENTMNT_IND |
Indicates whether customer given any Entitlement preference |
|
PREF_TRVL_IND |
Indicates whether customer given any Travel preference |
|
PREF_MSC_IND |
Indicates whether customer given any Music preference |
|
SCL_NTWK_USR_IND |
Indicates whether customer uses any Social Network site |
|
LIKE_SCP_IN_FCBK_IND |
Indicates whether customer likes Service Provide page in Facebook |
|
TOT_CALL_CNT_LFTM |
Total number of calls to Call center made by customer in his/her life span |
|
CMPLNT_CNT_LFTM |
Number of complaints made by customer in his/her life span |
|
DRPD_CALLS_CNT_LFTM |
Number of dropped calls in customer life span |
|
HNGUP_CALLS_CNT_LFTM |
Number of hangup calls in customer life span |
|
RLSD_CALLS_CNT_LFTM |
Number of released calls in customer life span |
|
TOT_QUE_DRTN_LFTM |
Total queue duration in customer life span in seconds |
|
TOT_HLD_DRTN_LFTM |
Total hold duration in customer life span in seconds |
|
TOT_TALK_DRTN_LFTM |
Total talk duration in customer life span in seconds |
|
RMRK_CNT_LFTM |
Numbers of remarks made against customer in his/her life span |
|
PRTY_AGE |
Customer Age expressed as number of years |
|
TOT_CALL_CNT_LAST_3MO |
Total number of calls to Call center made by customer in last 3 months |
|
CMPLNT_CNT_LAST_3MO |
Number of complaints made by customer in last 3 months |
|
DRPD_CALLS_CNT_LAST_3MO |
Number of dropped calls in last 3 months |
|
HNGUP_CALLS_CNT_LAST_3MO |
Number of hangup calls in last 3 months |
|
RLSD_CALLS_CNT_LAST_3MO |
Number of released calls in last 3 months |
|
TOT_QUE_DRTN_LAST_3MO |
Total queue duration in last 3 months in seconds |
|
TOT_HLD_DRTN_LAST_3MO |
Total hold duration in last 3 months in seconds |
|
TOT_TALK_DRTN_LAST_3MO |
Total talk duration in last 3 months in seconds |
|
RMRK_CNT_LAST_3MO |
Numbers of remarks made against customer in last 3 months |
|
TOT_CALL_CNT_LAST_MO |
Total number of calls to Call center made by customer in this month |
|
CMPLNT_CNT_LAST_MO |
Number of complaints made by customer in this month |
|
DRPD_CALLS_CNT_LAST_MO |
Number of dropped calls this month |
|
HNGUP_CALLS_CNT_LAST_MO |
Number of hangup calls this month |
|
RLSD_CALLS_CNT_LAST_MO |
Number of released calls this month |
|
TOT_QUE_DRTN_LAST_MO |
Total queue duration this month in seconds |
|
TOT_HLD_DRTN_LAST_MO |
Total hold duration this month in seconds |
|
TOT_TALK_DRTN_LAST_MO |
Total talk duration this month in seconds |
|
RMRK_CNT_LAST_MO |
Numbers of remarks made against customer in this month |
|
SBRP_CNT_LAST_3MO |
Subscription Count in last three months |
|
SSPNSN_CNT_LAST_3MO |
Suspension Count in last three months |
|
ACCT_LFT_VAL_LAST_3MO |
Account Left Value in last three months |
|
LYLTY_PROG_BAL_LAST_3MO |
Loyalty Program Balance in last three months |
|
SBRP_CNT_LAST_MO |
Subscription Count in last month |
|
SSPNSN_CNT_LAST_MO |
Suspension Count in last month |
|
ACCT_LFT_VAL_LAST_MO |
Account Left Value in last month |
|
LYLTY_PROG_BAL_LAST_MO |
Loyalty Program Balance in last month |
|
VOI_NAT_AIRTIME_LFTM |
National voice air time in minutes over customer's life time |
|
VOI_NAT_AIRTIME_LAST_MO |
National voice air time in minutes in last one month |
|
VOI_NAT_AIRTIME_LAST_3MO |
National voice air time in minutes in last three month |
|
VOI_INTERNAT_AIRTIME_LFTM |
International voice air time in minutes including roaming over customer's life time |
|
VOI_INTERNAT_AIRTIME_LAST_MO |
International voice air time in minutes including roaming in last one month |
|
VOI_INTERNAT_AIRTIME_LAST_3MO |
International voice air time including roaming in minutes in last three month |
|
SMS_CNT_LFTM |
SMSs sent over customer's lifetime |
|
SMS_CNT_LAST_MO |
SMSs sent in last 1 month |
|
SMS_CNT_LAST_3MO |
SMSs sent in last 3 months |
|
MMS_CNT_LFTM |
MMSs sent over customer's lifetime |
|
MMS_CNT_LAST_MO |
MMSs sent in last 1 month |
|
MMS_CNT_LAST_3MO |
MMSs sent in last 3 months |
|
DWNLD_DATA_LFTM |
Data downloaded in KBs in lifetime |
|
DWNLD_DATA_LAST_MO |
Data downloaded in KBs in last 1 month |
|
DWNLD_DATA_LAST_3MO |
Data downloaded in KBs in last 3 months |
|
UPLD_DATA_LFTM |
Data uploaded in KBs in lifetime |
|
UPLD_DATA_LAST_MO |
Data uploaded in KBs in last 1 month |
|
UPLD_DATA_LAST_3MO |
Data uploaded in KBs in last 3 months |
|
OFFNET_CALLS_LFTM |
Number of offnet calls in lifetime |
|
OFFNET_CALLS_LAST_MO |
Number of offnet calls in last 1 month |
|
OFFNET_CALLS_LAST_3MO |
Number of offnet calls in last 3 months |
|
ONNET_CALLS_LFTM |
Number of onnet calls in lifetime |
|
ONNET_CALLS_LAST_MO |
Number of onnet calls in last 1 month |
|
ONNET_CALLS_LAST_3MO |
Number of onnet calls in last 3 months |
|
OFFNET_AIRTIME_LFTM |
Total offnet airtime in minutes in lifetime |
|
OFFNET_AIRTIME_LAST_MO |
Total offnet airtime in minutes in last 1 month |
|
OFFNET_AIRTIME_LAST_3MO |
Total offnet airtime in minutes in last 3 months |
|
ONNET_AIRTIME_LFTM |
Total onnet airtime in minutes in lifetime |
|
ONNET_AIRTIME_LAST_MO |
Total onnet airtime in minutes in last 1 month |
|
ONNET_AIRTIME_LAST_3MO |
Total onnet airtime in minutes in last 3 months |
|
CHRN_IND |
Indicates whether a customer is a Churner or Non-churner |
Oracle Data Miner Workflow for POSTPAID_CHURN_DT_AND_SVM
Oracle Data Miner Workflow for POSTPAID_CHURN_DT_AND_SVM
Workflow Description:
-
In data miner workflow, relevant columns are selected from DWD_CUST_DNA in Data Source node
-
Data is split into source and apply datasets
-
Source dataset is further split into training and test datasets
-
Training dataset is used to train postpaid churn models using two algorithms, Support Vector Machines and Decision Tree
-
Models are PSTPD_CUST_CHRN_DT and PSTPD_CUST_CHRN_SVM
-
Decision Tree Churn model, PSTPD_CUST_CHRN_DT, details are extracted and stored in DWV_PSTPD_CHRN_DT_NODE database view
-
Support Vector Machines Churn model, PSTPD_CUST_CHRN_SVM, details are extracted and stored in DWV_PSTPD_CHRN_SVM_FACTOR database view
-
Test dataset is stored in DWV_PSTPD_CUST_CHRN_TST database view. This data is later used to extract ROC, LIFT, and Confusion Matrix from Support Vector Machines Churn model, PSTPD_CUST_CHRN_SVM
-
Apply dataset is scored using trained postpaid churn models. PSTPD_CUST_CHRN_DT model apply results are stored in DWV_PSTPD_CHRN_DT_APPLY database view and PSTPD_CUST_CHRN_SVM model apply results are stored in DWV_PSTPD_CHRN_SVM_APPLY database view
Data Model Mining Target Tables
postpaid_churn_dt_and_svm workflow is executed when pkg_ocdm_mining_odmr_util.odmr_run_wf procedure invoked. Workflows status is extracted and stored into dwc_odmr_wf_exctn table by invoking pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure.
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of workflow execution. If workflow is executed successfully without any errors, then the procedure will invoke pkg_ocdm_mining_util.updt_mnng_trgt_pstpd_chrn_dt and pkg_ocdm_mining_util.updt_mnng_trgt_pstpd_chrn_svm procedures to update mining target tables for PSTPD_CUST_CHRN_DT and PSTPD_CUST_CHRN_SVM models.
Table 11-27 Target Tables
| Model Name | Target Table Name | Database Operation | Columns |
|---|---|---|---|
|
PSTPD_CUST_CHRN_DT |
DWR_CUST_DT_NODE |
Insert |
No value |
|
PSTPD_CUST_CHRN_DT |
DWD_CUST_DNA |
Update |
PRDCT_CHURN_DT_IND PRDCT_CHURN_DT_PROB PRDCT_CHURN_DT_ND_NBR |
|
PSTPD_CUST_CHRN_SVM |
DWD_CHRN_SVM_FACTOR |
Insert |
No value |
|
PSTPD_CUST_CHRN_SVM |
DWD_PSTPD_CHRN_SVM_ROC |
Create |
No value |
|
PSTPD_CUST_CHRN_SVM |
DWD_PSTPD_CHRN_SVM_LIFT |
Create |
No value |
|
PSTPD_CUST_CHRN_SVM |
DWD_PSTPD_CHRN_SVM_CNF_MTRX |
Create |
No value |
|
PSTPD_CUST_CHRN_SVM |
DWD_CUST_DNA |
Update |
PRDCT_CHURN_SVM_IND PRDCT_CHURN_SVM_PROB |
Customer Profiling with Oracle Data Miner
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 segment.
Customer Profiling
The discovered clustering rules draw a profile of the customers along with their product subscription. Thus, the clustering rules generated for each profile group 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 group. Alternatively, there may be a group with extremely higher profit than any other group (covering high end customers).
-
K-Means Clustering
Table 11-28 Source Attributes Identified from DWD_CUST_DNA for Customer Profiling
| Attribute | Description |
|---|---|
|
CUST_CD |
Customer Identifier |
|
ACCT_TYP_CD |
Account Type Code, e.g. PSTPD - Postpaid, PRPD - Prepaid |
|
FUTRE_AGRMNT_CNT_LAST_3MO |
Number of Future Agreements in last 3 months |
|
AGRMNT_CNT_LAST_3MO |
Number of Agreements in last 3 months |
|
AGRMNT_LFT_DAYS_LAST_3MO |
Agreement Left Days in last 3 months |
|
FUTRE_AGRMNT_DAYS_LAST_3MO |
Future Agreement Days in last 3 months |
|
DEBT_VAL_LAST_3MO |
Debt Value in last 3 months |
|
TOT_PYMT_RVN_LAST_3MO |
Total Payment Revenue as of 3 months ago |
|
MO_RVN_LAST_3MO |
Monthly Revenue as of 3 months ago |
|
DEBT_AGNG_BND_CD_LAST_3MO |
Debt Ageing Band Code in last 3 months |
|
FUTRE_AGRMNT_CNT_LAST_MO |
Number of Future Agreements in last 1 month |
|
AGRMNT_CNT_LAST_MO |
Number of Agreements in last 1 month |
|
AGRMNT_LFT_DAYS_LAST_MO |
Agreement Left Days in last 1 month |
|
FUTRE_AGRMNT_DAYS_LAST_MO |
Future Agreement Days in last 1 month |
|
DEBT_VAL_LAST_MO |
Debt Value in last 1 month |
|
TOT_PYMT_RVN_LAST_MO |
Total Payment Revenue as of 1 month ago |
|
MO_RVN_LAST_MO |
Monthly Revenue as of 1 month ago |
|
DEBT_AGNG_BND_CD_LAST_MO |
Debt Ageing Band Code in last 1 month |
|
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. |
|
BNKRPT_STAT |
Bankrupt Status |
|
BLLG_ADDR_EFF_DT_NBR |
Billing 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. |
|
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. |
|
CUST_PYMT_RESPBL_IND |
Indicates whether customer is responsible for making payments |
|
CMPNY_TYP_CD |
Company Type Code |
|
SOC_JB_CD |
SOC Job Code |
|
EXTRNL_ORG_TYP_CD |
External Organization Type Code |
|
CMPNY_EMP_SZ_BND_CD |
Enterprise Customer Employee Size Band Code |
|
CMPNY_RVN_BND_CD |
Enterprise Customer Revenue Band Code |
|
NBR_EMP_SNC_CUST |
Numbers of Employees Since Customer |
|
PREF_PYMT_MTHD_TYP_CD |
Preferred Payment Method Type Code |
|
PRTY_TYP_CD |
Party Type Code, e.g. Individual, Large Enterprise, Medium Enterprise, etc. |
|
BSNS_LEGAL_STAT_CD |
Legal Status Code of Enterprise Customers, e.g. Public, Private |
|
HH_SZ |
Household Size |
|
LANG_CD |
Language 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. |
|
ESTMTD_ACQSTN_COST |
Customer Acquisition Estimated Cost |
|
BRDBND_IND |
Indicates whether Customer has Broadband connection |
|
PAY_TV_IND |
Indicates whether Customer has Pay TV connection |
|
IDD_IND |
Indicates whether Customer subscribed to International Direct Dialing |
|
HOMTEL_IND |
Indicates whether Customer has Home Telephone |
|
WRLS_IND |
Indicates whether Customer has Wireless Internet connection |
|
NEW_ACCT_IND |
Indicates whether Customer is New |
|
PORT_IN_FROM |
Service Provide from who the customer ported out from |
|
PORT_IN_CNT |
Number of times the customer ported in |
|
PORT_OUT_CNT |
Number of times the customer ported out |
|
CRDT_CTGRY_KEY |
Customer Credit Category |
|
ARPU_BND_CD |
Customer ARPU Band Code |
|
SL_CHNL_KEY |
Sales Channel |
|
SL_CHNL_RPRSTV_KEY |
Sales Channel Representative |
|
CUST_RVN_BND_CD |
Customer Revenue Band Code |
|
LIFE_SPN |
Customer Life Span |
|
CUST_TYP_CD |
Customer Type Code |
|
MULT_PRTY_ROLE_IND |
Indicates whether customer has multiple party roles, e.g. both an employee and customer of Service Provider |
|
PRMRY_STAT_CD |
Customer Primary Status Code |
|
PRMRY_STAT_RSN_CD |
Customer Primary Status Reason 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 |
|
AGE_BND_CD |
Customer Age Band 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. |
|
MAIL_ALWD_IND |
Indicates whether Customer allows Service Provider to send mail |
|
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. |
|
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. |
|
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. |
|
NO_MTHS_POSN |
Number of months an Individual Customer is in his/her current Position |
|
NO_MTHS_CMPNY |
Number of months an Individual Customer is in his/her current Company |
|
ECNMCLY_ACTV_IND |
Indicates whether a customer is economically Active |
|
ADDR_LOC_CD |
Customer Address Location Code |
|
CUST_SCR_CD |
Customer SCR Code |
|
GNDR_CD |
Individual Customer Gender Code |
|
MRTL_STAT_CD |
Individual Customer Marital Status Code |
|
NTNLTY_CD |
Customer Nationality Code |
|
ETHNIC_BCKGRND |
Customer Ethnic Background |
|
ETHNCTY |
Customer Ethnicity |
|
PLC_OF_BRTH |
Place of Birth |
|
JB_CD |
Customer Job Code |
|
JB_POSN |
Customer Job Position |
|
LEGAL_TTL_TO_HSNG |
Legal Title to Housing |
|
EDU_CD |
Education Qualification Code |
|
POSTCD_CD |
Postcard Code |
|
CITY |
City |
|
STATE |
State |
|
CNTRY |
Country |
|
NAME_PRFX |
Name Prefix |
|
FORM_OF_EMPMNT |
Form of Employment |
|
CUST_BRANCH_CD |
Customer Branch Code |
|
NAME_OF_WKPLC |
Name of Workplace |
|
JB_AGRMNT_TYP |
Job Agreement Type |
|
BARNG_RSN_CD |
Customer Barring Reason Code |
|
SRC_OF_INCM |
Source of Income |
|
DWLNG_OWNER |
Dwelling Owner |
|
DWLNG_STAT |
Dwelling Status |
|
DWLNG_TYP |
Dwelling Type |
|
DWLNG_TENR |
Dwelling Tenure |
|
DWLNG_SZ |
Dwelling Size |
|
CAR_DRVR_LICNS_IND |
Indicates whether customer has Car Driving License |
|
OTH_DRVR_LICNS_IND |
Indicates whether customer has other vehicle License |
|
SPRT_IND |
Indicates whether customer plays Sports |
|
SPRT_TYP_CD |
Sport Type Code |
|
NBR_OF_CHLDRN |
Total Number of Children |
|
NBR_OF_DPNDNT |
Total Number of Dependents |
|
TOT_AGE_CHLDRN |
Total Age of Children |
|
NBR_CHLDRN_AT_SCHL |
Total Number of Children at School |
|
SCHL_ZIP_CD |
School ZIP Code |
|
NBR_CHLDRN_AT_COLL |
Total Number of Children at College |
|
COLL_ZIP_CD |
College ZIP Code |
|
NBR_CHLDRN_AT_UNIV |
Total Number of Children at University |
|
UNIV_ZIP_CD |
University ZIP Code |
|
CAR_TYP_CD |
Car Type Code |
|
CNTCT_ALLWD_IND |
Indicates whether customer allows to contact |
|
PREF_CNTCT_MDM |
Preferred Contact Medium |
|
ACCPT_NWSLTR_IND |
Indicates whether customer accepts News Letter |
|
PREF_GVN_IND |
Indicates whether customer given any preference |
|
PREF_SPRT_IND |
Indicates whether customer given any sport preference |
|
PREF_ENTMNT_IND |
Indicates whether customer given any Entitlement preference |
|
PREF_TRVL_IND |
Indicates whether customer given any Travel preference |
|
PREF_MSC_IND |
Indicates whether customer given any Music preference |
|
SCL_NTWK_USR_IND |
Indicates whether customer uses any Social Network site |
|
LIKE_SCP_IN_FCBK_IND |
Indicates whether customer likes Service Provide page in Facebook |
|
TOT_CALL_CNT_LFTM |
Total number of calls to Call center made by customer in his/her life span |
|
CMPLNT_CNT_LFTM |
Number of complaints made by customer in his/her life span |
|
DRPD_CALLS_CNT_LFTM |
Number of dropped calls in customer life span |
|
HNGUP_CALLS_CNT_LFTM |
Number of hangup calls in customer life span |
|
RLSD_CALLS_CNT_LFTM |
Number of released calls in customer life span |
|
TOT_QUE_DRTN_LFTM |
Total queue duration in customer life span in seconds |
|
TOT_HLD_DRTN_LFTM |
Total hold duration in customer life span in seconds |
|
TOT_TALK_DRTN_LFTM |
Total talk duration in customer life span in seconds |
|
RMRK_CNT_LFTM |
Numbers of remarks made against customer in his/her life span |
|
PRTY_AGE |
Customer Age expressed as number of years |
|
TOT_CALL_CNT_LAST_3MO |
Total number of calls to Call center made by customer in last 3 months |
|
CMPLNT_CNT_LAST_3MO |
Number of complaints made by customer in last 3 months |
|
DRPD_CALLS_CNT_LAST_3MO |
Number of dropped calls in last 3 months |
|
HNGUP_CALLS_CNT_LAST_3MO |
Number of hangup calls in last 3 months |
|
RLSD_CALLS_CNT_LAST_3MO |
Number of released calls in last 3 months |
|
TOT_QUE_DRTN_LAST_3MO |
Total queue duration in last 3 months in seconds |
|
TOT_HLD_DRTN_LAST_3MO |
Total hold duration in last 3 months in seconds |
|
TOT_TALK_DRTN_LAST_3MO |
Total talk duration in last 3 months in seconds |
|
RMRK_CNT_LAST_3MO |
Numbers of remarks made against customer in last 3 months |
|
TOT_CALL_CNT_LAST_MO |
Total number of calls to Call center made by customer in this month |
|
CMPLNT_CNT_LAST_MO |
Number of complaints made by customer in this month |
|
DRPD_CALLS_CNT_LAST_MO |
Number of dropped calls this month |
|
HNGUP_CALLS_CNT_LAST_MO |
Number of hangup calls this month |
|
RLSD_CALLS_CNT_LAST_MO |
Number of released calls this month |
|
TOT_QUE_DRTN_LAST_MO |
Total queue duration this month in seconds |
|
TOT_HLD_DRTN_LAST_MO |
Total hold duration this month in seconds |
|
TOT_TALK_DRTN_LAST_MO |
Total talk duration this month in seconds |
|
RMRK_CNT_LAST_MO |
Numbers of remarks made against customer in this month |
|
TOT_RCHRG_AMT_LFTM |
Total recharge amount in customers life time |
|
TOT_RCHRG_AMT_LAST_MO |
Total recharge amount in last 3 months |
|
TOT_RCHRG_AMT_LAST_3MO |
Total recharge amount this month |
|
LAST_RCHRG_AMT |
Last recharge amount |
|
MIN_RCHRG_AMT |
Minimum recharge amount |
|
MAX_RCHRG_AMT |
Maximum recharge amount |
|
TOT_RCHRG_CNT_LFTM |
Number of recharges made in customer life time |
|
TOT_RCHRG_CNT_LAST_MO |
Number of recharges made this month |
|
TOT_RCHRG_CNT_LAST_3MO |
Number of recharges made in last 3 months |
|
TOT_EXP_AMT_EQVLT_VAL |
Total amount expired in customers life time |
|
TOT_UTILZD_EQVLT_VAL |
Total utilized monetary value in customers life time |
|
TOT_CNTRBTN_LFTM |
Sum of total payments and total recharges made in customers life time |
|
TOT_CNTRBTN_LAST_MO |
Sum of total payments and total recharges made in the last one month |
|
TOT_DRTN_OF_USG |
Total durations of usage in customer life time |
|
DAYS_BFR_FIRST_RCHRG |
Days between first payment and first recharge |
|
DAYS_BFR_FIRST_USE |
Days between payment and first use |
|
AVG_DRTN_BTWN_RCHRG |
Average duration between two recharges - in days |
|
TOT_DAYS_ON_USE |
Total days on use |
|
TOT_DAYS_ACTVTY |
Total days of activity |
|
TOT_DAYS_OUTGOING_ACTVTY |
Total days of outgoing activity |
|
LAST_OUTGOING_USG_DT_NBR |
When was last outgoing call occurred |
|
LAST_INCOMING_USG_DT_NBR |
When was last incoming call occurred |
|
VOI_NAT_AIRTIME_LFTM |
National voice air time in minutes over customer's life time |
|
VOI_NAT_AIRTIME_LAST_MO |
National voice air time in minutes in last one month |
|
VOI_NAT_AIRTIME_LAST_3MO |
National voice air time in minutes in last three month |
|
VOI_INTERNAT_AIRTIME_LFTM |
International voice air time in minutes including roaming over customer's life time |
|
VOI_INTERNAT_AIRTIME_LAST_MO |
International voice air time in minutes including roaming in last one month |
|
VOI_INTERNAT_AIRTIME_LAST_3MO |
International voice air time including roaming in minutes in last three month |
|
SMS_CNT_LFTM |
SMSs sent over customer's lifetime |
|
SMS_CNT_LAST_MO |
SMSs sent in last 1 month |
|
SMS_CNT_LAST_3MO |
SMSs sent in last 3 months |
|
MMS_CNT_LFTM |
MMSs sent over customer's lifetime |
|
MMS_CNT_LAST_MO |
MMSs sent in last 1 month |
|
MMS_CNT_LAST_3MO |
MMSs sent in last 3 months |
|
DWNLD_DATA_LFTM |
Data downloaded in KBs in lifetime |
|
DWNLD_DATA_LAST_MO |
Data downloaded in KBs in last 1 month |
|
DWNLD_DATA_LAST_3MO |
Data downloaded in KBs in last 3 months |
|
UPLD_DATA_LFTM |
Data uploaded in KBs in lifetime |
|
UPLD_DATA_LAST_MO |
Data uploaded in KBs in last 1 month |
|
UPLD_DATA_LAST_3MO |
Data uploaded in KBs in last 3 months |
|
OFFNET_CALLS_LFTM |
Number of offnet calls in lifetime |
|
OFFNET_CALLS_LAST_MO |
Number of offnet calls in last 1 month |
|
OFFNET_CALLS_LAST_3MO |
Number of offnet calls in last 3 months |
|
ONNET_CALLS_LFTM |
Number of onnet calls in lifetime |
|
ONNET_CALLS_LAST_MO |
Number of onnet calls in last 1 month |
|
ONNET_CALLS_LAST_3MO |
Number of onnet calls in last 3 months |
|
OFFNET_AIRTIME_LFTM |
Total offnet airtime in minutes in lifetime |
|
OFFNET_AIRTIME_LAST_MO |
Total offnet airtime in minutes in last 1 month |
|
OFFNET_AIRTIME_LAST_3MO |
Total offnet airtime in minutes in last 3 months |
|
ONNET_AIRTIME_LFTM |
Total onnet airtime in minutes in lifetime |
|
ONNET_AIRTIME_LAST_MO |
Total onnet airtime in minutes in last 1 month |
|
ONNET_AIRTIME_LAST_3MO |
Total onnet airtime in minutes in last 3 months |
Oracle Data Miner Workflow for Customer Segmentation KMEANS
Workflow Description:
-
In data miner workflow, relevant columns are selected from DWD_CUST_DNA in Data Source node
-
Data is split into source and training datasets
-
Training dataset is used to train postpaid customer profiling model using K-Means algorithm
-
Model is CUST_SGMNT_KMEANS
-
CUST_SGMNT_KMEANS model segment details are extracted and stored in DWV_SGMNT_DTLS database view
-
Segments centroid details are extracted and stored in DWV_SGMNT_CENTROID_DTLS database view
-
Apply dataset is scored using trained customer profiling model. Apply results are stored in DWV_CUST_SGMNT_APPLY database view
Data Model Mining Target Tables
customer_segmentation_kmeans workflow is executed when pkg_ocdm_mining_odmr_util.odmr_run_wf procedure invoked. Workflows status is extracted and stored into dwc_odmr_wf_exctn table by invoking pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure.
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of workflow execution. If workflow is executed successfully without any errors, then the procedure will invoke pkg_ocdm_mining_util.updt_mnng_trgt_cust_sgmnt procedure to update mining target tables for CUST_SGMNT_KMEAN model.
Following table gives the list of target tables (columns) that get updated/loaded/created:
Table 11-29 Data Mining Model Target Tables
| Target Table Name | Database Operation | Columns |
|---|---|---|
|
DWR_CUST_SGMNT |
Insert |
No value |
|
DWR_CUST_SGMNT_DTL |
Insert |
No value |
|
DWD_CUST_DNA |
Update |
CLSTR_SGMNT_CD |
|
DWR_CUST |
Update |
CUST_SGMNT_KEY |
Customer Lifetime Value
Customer Lifetime Value
When a Service Provider wants know how valuable a customer is to the Service Provider for the next n years into future. Customer predicted Life Time Value can be combined with customer predicted churn behavior to make important business decisions 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 3 or 5 years.-
Generalized Linear Model Regression
Table 11-30 Source Attributes Identified from DWD_CUST_DNA table for Customer Lifetime Value
| Attribute | Description |
|---|---|
|
CUST_CD |
Customer Identifier |
|
ACCT_TYP_CD |
Account Type Code, e.g. PSTPD - Postpaid, PRPD - Prepaid |
|
FUTRE_AGRMNT_CNT_LAST_3MO |
Number of Future Agreements in last 3 months |
|
AGRMNT_CNT_LAST_3MO |
Number of Agreements in last 3 months |
|
AGRMNT_LFT_DAYS_LAST_3MO |
Agreement Left Days in last 3 months |
|
FUTRE_AGRMNT_DAYS_LAST_3MO |
Future Agreement Days in last 3 months |
|
DEBT_VAL_LAST_3MO |
Debt Value in last 3 months |
|
TOT_PYMT_RVN_LAST_3MO |
Total Payment Revenue as of 3 months ago |
|
MO_RVN_LAST_3MO |
Monthly Revenue as of 3 months ago |
|
DEBT_AGNG_BND_CD_LAST_3MO |
Debt Ageing Band Code in last 3 months |
|
FUTRE_AGRMNT_CNT_LAST_MO |
Number of Future Agreements in last 1 month |
|
AGRMNT_CNT_LAST_MO |
Number of Agreements in last 1 month |
|
AGRMNT_LFT_DAYS_LAST_MO |
Agreement Left Days in last 1 month |
|
FUTRE_AGRMNT_DAYS_LAST_MO |
Future Agreement Days in last 1 month |
|
DEBT_VAL_LAST_MO |
Debt Value in last 1 month |
|
TOT_PYMT_RVN_LAST_MO |
Total Payment Revenue as of 1 month ago |
|
MO_RVN_LAST_MO |
Monthly Revenue as of 1 month ago |
|
DEBT_AGNG_BND_CD_LAST_MO |
Debt Ageing Band Code in last 1 month |
|
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. |
|
BNKRPT_STAT |
Bankrupt Status |
|
BLLG_ADDR_EFF_DT_NBR |
Billing 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. |
|
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. |
|
CUST_PYMT_RESPBL_IND |
Indicates whether customer is responsible for making payments |
|
CMPNY_TYP_CD |
Company Type Code |
|
SOC_JB_CD |
SOC Job Code |
|
EXTRNL_ORG_TYP_CD |
External Organization Type Code |
|
CMPNY_EMP_SZ_BND_CD |
Enterprise Customer Employee Size Band Code |
|
CMPNY_RVN_BND_CD |
Enterprise Customer Revenue Band Code |
|
NBR_EMP_SNC_CUST |
Numbers of Employees Since Customer |
|
PREF_PYMT_MTHD_TYP_CD |
Preferred Payment Method Type Code |
|
PRTY_TYP_CD |
Party Type Code, e.g. Individual, Large Enterprise, Medium Enterprise, etc. |
|
BSNS_LEGAL_STAT_CD |
Legal Status Code of Enterprise Customers, e.g. Public, Private |
|
HH_SZ |
Household Size |
|
LANG_CD |
Language 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. |
|
ESTMTD_ACQSTN_COST |
Customer Acquisition Estimated Cost |
|
BRDBND_IND |
Indicates whether Customer has Broadband connection |
|
PAY_TV_IND |
Indicates whether Customer has Pay TV connection |
|
IDD_IND |
Indicates whether Customer subscribed to International Direct Dialing |
|
HOMTEL_IND |
Indicates whether Customer has Home Telephone |
|
WRLS_IND |
Indicates whether Customer has Wireless Internet connection |
|
NEW_ACCT_IND |
Indicates whether Customer is New |
|
PORT_IN_FROM |
Service Provide from who the customer ported out from |
|
PORT_IN_CNT |
Number of times the customer ported in |
|
PORT_OUT_CNT |
Number of times the customer ported out |
|
CRDT_CTGRY_KEY |
Customer Credit Category |
|
ARPU_BND_CD |
Customer ARPU Band Code |
|
SL_CHNL_KEY |
Sales Channel |
|
SL_CHNL_RPRSTV_KEY |
Sales Channel Representative |
|
CUST_RVN_BND_CD |
Customer Revenue Band Code |
|
LIFE_SPN |
Customer Life Span |
|
CUST_TYP_CD |
Customer Type Code |
|
MULT_PRTY_ROLE_IND |
Indicates whether customer has multiple party roles, e.g. both an employee and customer of Service Provider |
|
PRMRY_STAT_CD |
Customer Primary Status Code |
|
PRMRY_STAT_RSN_CD |
Customer Primary Status Reason 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 |
|
AGE_BND_CD |
Customer Age Band 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. |
|
MAIL_ALWD_IND |
Indicates whether Customer allows Service Provider to send mail |
|
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. |
|
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. |
|
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. |
|
NO_MTHS_POSN |
Number of months an Individual Customer is in his/her current Position |
|
NO_MTHS_CMPNY |
Number of months an Individual Customer is in his/her current Company |
|
ECNMCLY_ACTV_IND |
Indicates whether a customer is economically Active |
|
ADDR_LOC_CD |
Customer Address Location Code |
|
CUST_SCR_CD |
Customer SCR Code |
|
GNDR_CD |
Individual Customer Gender Code |
|
MRTL_STAT_CD |
Individual Customer Marital Status Code |
|
NTNLTY_CD |
Customer Nationality Code |
|
ETHNIC_BCKGRND |
Customer Ethnic Background |
|
ETHNCTY |
Customer Ethnicity |
|
PLC_OF_BRTH |
Place of Birth |
|
JB_CD |
Customer Job Code |
|
JB_POSN |
Customer Job Position |
|
LEGAL_TTL_TO_HSNG |
Legal Title to Housing |
|
EDU_CD |
Education Qualification Code |
|
POSTCD_CD |
Postcard Code |
|
CITY |
City |
|
STATE |
State |
|
CNTRY |
Country |
|
NAME_PRFX |
Name Prefix |
|
FORM_OF_EMPMNT |
Form of Employment |
|
CUST_BRANCH_CD |
Customer Branch Code |
|
NAME_OF_WKPLC |
Name of Workplace |
|
JB_AGRMNT_TYP |
Job Agreement Type |
|
BARNG_RSN_CD |
Customer Barring Reason Code |
|
SRC_OF_INCM |
Source of Income |
|
DWLNG_OWNER |
Dwelling Owner |
|
DWLNG_STAT |
Dwelling Status |
|
DWLNG_TYP |
Dwelling Type |
|
DWLNG_TENR |
Dwelling Tenure |
|
DWLNG_SZ |
Dwelling Size |
|
CAR_DRVR_LICNS_IND |
Indicates whether customer has Car Driving License |
|
OTH_DRVR_LICNS_IND |
Indicates whether customer has other vehicle License |
|
SPRT_IND |
Indicates whether customer plays Sports |
|
SPRT_TYP_CD |
Sport Type Code |
|
NBR_OF_CHLDRN |
Total Number of Children |
|
NBR_OF_DPNDNT |
Total Number of Dependents |
|
TOT_AGE_CHLDRN |
Total Age of Children |
|
NBR_CHLDRN_AT_SCHL |
Total Number of Children at School |
|
SCHL_ZIP_CD |
School ZIP Code |
|
NBR_CHLDRN_AT_COLL |
Total Number of Children at College |
|
COLL_ZIP_CD |
College ZIP Code |
|
NBR_CHLDRN_AT_UNIV |
Total Number of Children at University |
|
UNIV_ZIP_CD |
University ZIP Code |
|
CAR_TYP_CD |
Car Type Code |
|
CNTCT_ALLWD_IND |
Indicates whether customer allows to contact |
|
PREF_CNTCT_MDM |
Preferred Contact Medium |
|
ACCPT_NWSLTR_IND |
Indicates whether customer accepts News Letter |
|
PREF_GVN_IND |
Indicates whether customer given any preference |
|
PREF_SPRT_IND |
Indicates whether customer given any sport preference |
|
PREF_ENTMNT_IND |
Indicates whether customer given any Entitlement preference |
|
PREF_TRVL_IND |
Indicates whether customer given any Travel preference |
|
PREF_MSC_IND |
Indicates whether customer given any Music preference |
|
SCL_NTWK_USR_IND |
Indicates whether customer uses any Social Network site |
|
LIKE_SCP_IN_FCBK_IND |
Indicates whether customer likes Service Provide page in Facebook |
|
TOT_CALL_CNT_LFTM |
Total number of calls to Call center made by customer in his/her life span |
|
CMPLNT_CNT_LFTM |
Number of complaints made by customer in his/her life span |
|
DRPD_CALLS_CNT_LFTM |
Number of dropped calls in customer life span |
|
HNGUP_CALLS_CNT_LFTM |
Number of hangup calls in customer life span |
|
RLSD_CALLS_CNT_LFTM |
Number of released calls in customer life span |
|
TOT_QUE_DRTN_LFTM |
Total queue duration in customer life span in seconds |
|
TOT_HLD_DRTN_LFTM |
Total hold duration in customer life span in seconds |
|
TOT_TALK_DRTN_LFTM |
Total talk duration in customer life span in seconds |
|
RMRK_CNT_LFTM |
Numbers of remarks made against customer in his/her life span |
|
PRTY_AGE |
Customer Age expressed as number of years |
|
TOT_CALL_CNT_LAST_3MO |
Total number of calls to Call center made by customer in last 3 months |
|
CMPLNT_CNT_LAST_3MO |
Number of complaints made by customer in last 3 months |
|
DRPD_CALLS_CNT_LAST_3MO |
Number of dropped calls in last 3 months |
|
HNGUP_CALLS_CNT_LAST_3MO |
Number of hang-up calls in last 3 months |
|
RLSD_CALLS_CNT_LAST_3MO |
Number of released calls in last 3 months |
|
TOT_QUE_DRTN_LAST_3MO |
Total queue duration in in last 3 months in seconds |
|
TOT_HLD_DRTN_LAST_3MO |
Total hold duration in in last 3 months in seconds |
|
TOT_TALK_DRTN_LAST_3MO |
Total talk duration in in last 3 months in seconds |
|
RMRK_CNT_LAST_3MO |
Numbers of remarks made against customer in last 3 months |
|
TOT_CALL_CNT_LAST_MO |
Total number of calls to Call center made by customer in this month |
|
CMPLNT_CNT_LAST_MO |
Number of complaints made by customer in this month |
|
DRPD_CALLS_CNT_LAST_MO |
Number of dropped calls this month |
|
HNGUP_CALLS_CNT_LAST_MO |
Number of hang-up calls this month |
|
RLSD_CALLS_CNT_LAST_MO |
Number of released calls this month |
|
TOT_QUE_DRTN_LAST_MO |
Total queue duration this month in seconds |
|
TOT_HLD_DRTN_LAST_MO |
Total hold duration this month in seconds |
|
TOT_TALK_DRTN_LAST_MO |
Total talk duration this month in seconds |
|
RMRK_CNT_LAST_MO |
Numbers of remarks made against customer in this month |
|
TOT_RCHRG_AMT_LFTM |
Total recharge amount in customers life time |
|
TOT_RCHRG_AMT_LAST_MO |
Total recharge amount in last 3 months |
|
TOT_RCHRG_AMT_LAST_3MO |
Total recharge amount this month |
|
LAST_RCHRG_AMT |
Last recharge amount |
|
MIN_RCHRG_AMT |
Minimum recharge amount |
|
MAX_RCHRG_AMT |
Maximum recharge amount |
|
TOT_RCHRG_CNT_LFTM |
Number of recharges made in customer life time |
|
TOT_RCHRG_CNT_LAST_MO |
Number of recharges made this month |
|
TOT_RCHRG_CNT_LAST_3MO |
Number of recharges made in last 3 months |
|
TOT_EXP_AMT_EQVLT_VAL |
Total amount expired in customers life time |
|
TOT_UTILZD_EQVLT_VAL |
Total utilized monetary value in customers life time |
|
TOT_CNTRBTN_LFTM |
Sum of total payments and total recharges made in customers life time |
|
TOT_CNTRBTN_LAST_MO |
Sum of total payments and total recharges made in the last one month |
|
TOT_DRTN_OF_USG |
Total durations of usage in customer life time |
|
DAYS_BFR_FIRST_RCHRG |
Days between first payment and first recharge |
|
DAYS_BFR_FIRST_USE |
Days between payment and first use |
|
AVG_DRTN_BTWN_RCHRG |
Average duration between two recharges - in days |
|
TOT_DAYS_ON_USE |
Total days on use |
|
TOT_DAYS_ACTVTY |
Total days of activity |
|
TOT_DAYS_OUTGOING_ACTVTY |
Total days of outgoing activity |
|
LAST_OUTGOING_USG_DT_NBR |
When was last outgoing call occurred |
|
LAST_INCOMING_USG_DT_NBR |
When was last incoming call occurred |
|
VOI_NAT_AIRTIME_LFTM |
National voice air time in minutes over customer's life time |
|
VOI_NAT_AIRTIME_LAST_MO |
National voice air time in minutes in last one month |
|
VOI_NAT_AIRTIME_LAST_3MO |
National voice air time in minutes in last three month |
|
VOI_INTERNAT_AIRTIME_LFTM |
International voice air time in minutes including roaming over customer's life time |
|
VOI_INTERNAT_AIRTIME_LAST_MO |
International voice air time in minutes including roaming in last one month |
|
VOI_INTERNAT_AIRTIME_LAST_3MO |
International voice air time including roaming in minutes in last three month |
|
SMS_CNT_LFTM |
SMSs sent over customer's lifetime |
|
SMS_CNT_LAST_MO |
SMSs sent in last 1 month |
|
SMS_CNT_LAST_3MO |
SMSs sent in last 3 months |
|
MMS_CNT_LFTM |
MMSs sent over customer's lifetime |
|
MMS_CNT_LAST_MO |
MMSs sent in last 1 month |
|
MMS_CNT_LAST_3MO |
MMSs sent in last 3 months |
|
DWNLD_DATA_LFTM |
Data downloaded in KBs in lifetime |
|
DWNLD_DATA_LAST_MO |
Data downloaded in KBs in last 1 month |
|
DWNLD_DATA_LAST_3MO |
Data downloaded in KBs in last 3 months |
|
UPLD_DATA_LFTM |
Data uploaded in KBs in lifetime |
|
UPLD_DATA_LAST_MO |
Data uploaded in KBs in last 1 month |
|
UPLD_DATA_LAST_3MO |
Data uploaded in KBs in last 3 months |
|
OFFNET_CALLS_LFTM |
Number of offnet calls in lifetime |
|
OFFNET_CALLS_LAST_MO |
Number of offnet calls in last 1 month |
|
OFFNET_CALLS_LAST_3MO |
Number of offnet calls in last 3 months |
|
ONNET_CALLS_LFTM |
Number of onnet calls in lifetime |
|
ONNET_CALLS_LAST_MO |
Number of onnet calls in last 1 month |
|
ONNET_CALLS_LAST_3MO |
Number of onnet calls in last 3 months |
|
OFFNET_AIRTIME_LFTM |
Total offnet airtime in minutes in lifetime |
|
OFFNET_AIRTIME_LAST_MO |
Total offnet airtime in minutes in last 1 month |
|
OFFNET_AIRTIME_LAST_3MO |
Total offnet airtime in minutes in last 3 months |
|
ONNET_AIRTIME_LFTM |
Total onnet airtime in minutes in lifetime |
|
ONNET_AIRTIME_LAST_MO |
Total onnet airtime in minutes in last 1 month |
|
ONNET_AIRTIME_LAST_3MO |
Total onnet airtime in minutes in last 3 months |
Data Model Mining Target Attribute
TOT_PYMT_RVN_LAST_MO
Customer Lifetime Survival Value
Customer Lifetime Survival Value
Service Provider wants to know how long a customer uses the services offered, rather than leaving. 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 3 or 5 years.-
Generalized Linear Model Regression
Table 11-31 Attributes Identified from DWD_CUST_DNA table for Customer Lifetime Survival Value
| Attribute | Description |
|---|---|
|
CUST_CD |
Customer Identifier |
|
ACCT_TYP_CD |
Account Type Code, e.g. PSTPD - Postpaid, PRPD - Prepaid |
|
FUTRE_AGRMNT_CNT_LAST_3MO |
Number of Future Agreements in last 3 months |
|
AGRMNT_CNT_LAST_3MO |
Number of Agreements in last 3 months |
|
AGRMNT_LFT_DAYS_LAST_3MO |
Agreement Left Days in last 3 months |
|
FUTRE_AGRMNT_DAYS_LAST_3MO |
Future Agreement Days in last 3 months |
|
DEBT_VAL_LAST_3MO |
Debt Value in last 3 months |
|
TOT_PYMT_RVN_LAST_3MO |
Total Payment Revenue as of 3 months ago |
|
MO_RVN_LAST_3MO |
Monthly Revenue as of 3 months ago |
|
DEBT_AGNG_BND_CD_LAST_3MO |
Debt Ageing Band Code in last 3 months |
|
FUTRE_AGRMNT_CNT_LAST_MO |
Number of Future Agreements in last 1 month |
|
AGRMNT_CNT_LAST_MO |
Number of Agreements in last 1 month |
|
AGRMNT_LFT_DAYS_LAST_MO |
Agreement Left Days in last 1 month |
|
FUTRE_AGRMNT_DAYS_LAST_MO |
Future Agreement Days in last 1 month |
|
DEBT_VAL_LAST_MO |
Debt Value in last 1 month |
|
TOT_PYMT_RVN_LAST_MO |
Total Payment Revenue as of 1 month ago |
|
MO_RVN_LAST_MO |
Monthly Revenue as of 1 month ago |
|
DEBT_AGNG_BND_CD_LAST_MO |
Debt Ageing Band Code in last 1 month |
|
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. |
|
BNKRPT_STAT |
Bankrupt Status |
|
BLLG_ADDR_EFF_DT_NBR |
Billing 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. |
|
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. |
|
CUST_PYMT_RESPBL_IND |
Indicates whether customer is responsible for making payments |
|
CMPNY_TYP_CD |
Company Type Code |
|
SOC_JB_CD |
SOC Job Code |
|
EXTRNL_ORG_TYP_CD |
External Organization Type Code |
|
CMPNY_EMP_SZ_BND_CD |
Enterprise Customer Employee Size Band Code |
|
CMPNY_RVN_BND_CD |
Enterprise Customer Revenue Band Code |
|
NBR_EMP_SNC_CUST |
Numbers of Employees Since Customer |
|
PREF_PYMT_MTHD_TYP_CD |
Preferred Payment Method Type Code |
|
PRTY_TYP_CD |
Party Type Code, e.g. Individual, Large Enterprise, Medium Enterprise, etc. |
|
BSNS_LEGAL_STAT_CD |
Legal Status Code of Enterprise Customers, e.g. Public, Private |
|
HH_SZ |
Household Size |
|
LANG_CD |
Language 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. |
|
ESTMTD_ACQSTN_COST |
Customer Acquisition Estimated Cost |
|
BRDBND_IND |
Indicates whether Customer has Broadband connection |
|
PAY_TV_IND |
Indicates whether Customer has Pay TV connection |
|
IDD_IND |
Indicates whether Customer subscribed to International Direct Dialing |
|
HOMTEL_IND |
Indicates whether Customer has Home Telephone |
|
WRLS_IND |
Indicates whether Customer has Wireless Internet connection |
|
NEW_ACCT_IND |
Indicates whether Customer is New |
|
PORT_IN_FROM |
Service Provide from who the customer ported out from |
|
PORT_IN_CNT |
Number of times the customer ported in |
|
PORT_OUT_CNT |
Number of times the customer ported out |
|
CRDT_CTGRY_KEY |
Customer Credit Category |
|
ARPU_BND_CD |
Customer ARPU Band Code |
|
SL_CHNL_KEY |
Sales Channel |
|
SL_CHNL_RPRSTV_KEY |
Sales Channel Representative |
|
CUST_RVN_BND_CD |
Customer Revenue Band Code |
|
LIFE_SPN |
Customer Life Span |
|
CUST_TYP_CD |
Customer Type Code |
|
MULT_PRTY_ROLE_IND |
Indicates whether customer has multiple party roles, e.g. both an employee and customer of Service Provider |
|
PRMRY_STAT_CD |
Customer Primary Status Code |
|
PRMRY_STAT_RSN_CD |
Customer Primary Status Reason 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 |
|
AGE_BND_CD |
Customer Age Band 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. |
|
MAIL_ALWD_IND |
Indicates whether Customer allows Service Provider to send mail |
|
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. |
|
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. |
|
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. |
|
NO_MTHS_POSN |
Number of months an Individual Customer is in his/her current Position |
|
NO_MTHS_CMPNY |
Number of months an Individual Customer is in his/her current Company |
|
ECNMCLY_ACTV_IND |
Indicates whether a customer is economically Active |
|
ADDR_LOC_CD |
Customer Address Location Code |
|
CUST_SCR_CD |
Customer SCR Code |
|
GNDR_CD |
Individual Customer Gender Code |
|
MRTL_STAT_CD |
Individual Customer Marital Status Code |
|
NTNLTY_CD |
Customer Nationality Code |
|
ETHNIC_BCKGRND |
Customer Ethnic Background |
|
ETHNCTY |
Customer Ethnicity |
|
PLC_OF_BRTH |
Place of Birth |
|
JB_CD |
Customer Job Code |
|
JB_POSN |
Customer Job Position |
|
LEGAL_TTL_TO_HSNG |
Legal Title to Housing |
|
EDU_CD |
Education Qualification Code |
|
POSTCD_CD |
Postcard Code |
|
CITY |
City |
|
STATE |
State |
|
CNTRY |
Country |
|
NAME_PRFX |
Name Prefix |
|
FORM_OF_EMPMNT |
Form of Employment |
|
CUST_BRANCH_CD |
Customer Branch Code |
|
NAME_OF_WKPLC |
Name of Workplace |
|
JB_AGRMNT_TYP |
Job Agreement Type |
|
BARNG_RSN_CD |
Customer Barring Reason Code |
|
SRC_OF_INCM |
Source of Income |
|
DWLNG_OWNER |
Dwelling Owner |
|
DWLNG_STAT |
Dwelling Status |
|
DWLNG_TYP |
Dwelling Type |
|
DWLNG_TENR |
Dwelling Tenure |
|
DWLNG_SZ |
Dwelling Size |
|
CAR_DRVR_LICNS_IND |
Indicates whether customer has Car Driving License |
|
OTH_DRVR_LICNS_IND |
Indicates whether customer has other vehicle License |
|
SPRT_IND |
Indicates whether customer plays Sports |
|
SPRT_TYP_CD |
Sport Type Code |
|
NBR_OF_CHLDRN |
Total Number of Children |
|
NBR_OF_DPNDNT |
Total Number of Dependents |
|
TOT_AGE_CHLDRN |
Total Age of Children |
|
NBR_CHLDRN_AT_SCHL |
Total Number of Children at School |
|
SCHL_ZIP_CD |
School ZIP Code |
|
NBR_CHLDRN_AT_COLL |
Total Number of Children at College |
|
COLL_ZIP_CD |
College ZIP Code |
|
NBR_CHLDRN_AT_UNIV |
Total Number of Children at University |
|
UNIV_ZIP_CD |
University ZIP Code |
|
CAR_TYP_CD |
Car Type Code |
|
CNTCT_ALLWD_IND |
Indicates whether customer allows to contact |
|
PREF_CNTCT_MDM |
Preferred Contact Medium |
|
ACCPT_NWSLTR_IND |
Indicates whether customer accepts News Letter |
|
PREF_GVN_IND |
Indicates whether customer given any preference |
|
PREF_SPRT_IND |
Indicates whether customer given any sport preference |
|
PREF_ENTMNT_IND |
Indicates whether customer given any Entitlement preference |
|
PREF_TRVL_IND |
Indicates whether customer given any Travel preference |
|
PREF_MSC_IND |
Indicates whether customer given any Music preference |
|
SCL_NTWK_USR_IND |
Indicates whether customer uses any Social Network site |
|
LIKE_SCP_IN_FCBK_IND |
Indicates whether customer likes Service Provide page in Facebook |
|
TOT_CALL_CNT_LFTM |
Total number of calls to Call center made by customer in his/her life span |
|
CMPLNT_CNT_LFTM |
Number of complaints made by customer in his/her life span |
|
DRPD_CALLS_CNT_LFTM |
Number of dropped calls in customer life span |
|
HNGUP_CALLS_CNT_LFTM |
Number of hangup calls in customer life span |
|
RLSD_CALLS_CNT_LFTM |
Number of released calls in customer life span |
|
TOT_QUE_DRTN_LFTM |
Total queue duration in customer life span in seconds |
|
TOT_HLD_DRTN_LFTM |
Total hold duration in customer life span in seconds |
|
TOT_TALK_DRTN_LFTM |
Total talk duration in customer life span in seconds |
|
RMRK_CNT_LFTM |
Numbers of remarks made against customer in his/her life span |
|
PRTY_AGE |
Customer Age expressed as number of years |
|
TOT_CALL_CNT_LAST_3MO |
Total number of calls to Call center made by customer in last 3 months |
|
CMPLNT_CNT_LAST_3MO |
Number of complaints made by customer in last 3 months |
|
DRPD_CALLS_CNT_LAST_3MO |
Number of dropped calls in last 3 months |
|
HNGUP_CALLS_CNT_LAST_3MO |
Number of hangup calls in last 3 months |
|
RLSD_CALLS_CNT_LAST_3MO |
Number of released calls in last 3 months |
|
TOT_QUE_DRTN_LAST_3MO |
Total queue duration in in last 3 months in seconds |
|
TOT_HLD_DRTN_LAST_3MO |
Total hold duration in in last 3 months in seconds |
|
TOT_TALK_DRTN_LAST_3MO |
Total talk duration in in last 3 months in seconds |
|
RMRK_CNT_LAST_3MO |
Numbers of remarks made against customer in last 3 months |
|
TOT_CALL_CNT_LAST_MO |
Total number of calls to Call center made by customer in this month |
|
CMPLNT_CNT_LAST_MO |
Number of complaints made by customer in this month |
|
DRPD_CALLS_CNT_LAST_MO |
Number of dropped calls this month |
|
HNGUP_CALLS_CNT_LAST_MO |
Number of hangup calls this month |
|
RLSD_CALLS_CNT_LAST_MO |
Number of released calls this month |
|
TOT_QUE_DRTN_LAST_MO |
Total queue duration this month in seconds |
|
TOT_HLD_DRTN_LAST_MO |
Total hold duration this month in seconds |
|
TOT_TALK_DRTN_LAST_MO |
Total talk duration this month in seconds |
|
RMRK_CNT_LAST_MO |
Numbers of remarks made against customer in this month |
|
TOT_RCHRG_AMT_LFTM |
Total recharge amount in customers life time |
|
TOT_RCHRG_AMT_LAST_MO |
Total recharge amount in last 3 months |
|
TOT_RCHRG_AMT_LAST_3MO |
Total recharge amount this month |
|
LAST_RCHRG_AMT |
Last recharge amount |
|
MIN_RCHRG_AMT |
Minimum recharge amount |
|
MAX_RCHRG_AMT |
Maximum recharge amount |
|
TOT_RCHRG_CNT_LFTM |
Number of recharges made in customer life time |
|
TOT_RCHRG_CNT_LAST_MO |
Number of recharges made this month |
|
TOT_RCHRG_CNT_LAST_3MO |
Number of recharges made in last 3 months |
|
TOT_EXP_AMT_EQVLT_VAL |
Total amount expired in customers life time |
|
TOT_UTILZD_EQVLT_VAL |
Total utilized monetary value in customers life time |
|
TOT_CNTRBTN_LFTM |
Sum of total payments and total recharges made in customers life time |
|
TOT_CNTRBTN_LAST_MO |
Sum of total payments and total recharges made in the last one month |
|
TOT_DRTN_OF_USG |
Total durations of usage in customer life time |
|
DAYS_BFR_FIRST_RCHRG |
Days between first payment and first recharge |
|
DAYS_BFR_FIRST_USE |
Days between payment and first use |
|
AVG_DRTN_BTWN_RCHRG |
Average duration between two recharges - in days |
|
TOT_DAYS_ON_USE |
Total days on use |
|
TOT_DAYS_ACTVTY |
Total days of activity |
|
TOT_DAYS_OUTGOING_ACTVTY |
Total days of outgoing activity |
|
LAST_OUTGOING_USG_DT_NBR |
When was last outgoing call occurred |
|
LAST_INCOMING_USG_DT_NBR |
When was last incoming call occurred |
|
VOI_NAT_AIRTIME_LFTM |
National voice air time in minutes over customer's life time |
|
VOI_NAT_AIRTIME_LAST_MO |
National voice air time in minutes in last one month |
|
VOI_NAT_AIRTIME_LAST_3MO |
National voice air time in minutes in last three month |
|
VOI_INTERNAT_AIRTIME_LFTM |
International voice air time in minutes including roaming over customer's life time |
|
VOI_INTERNAT_AIRTIME_LAST_MO |
International voice air time in minutes including roaming in last one month |
|
VOI_INTERNAT_AIRTIME_LAST_3MO |
International voice air time including roaming in minutes in last three month |
|
SMS_CNT_LFTM |
SMSs sent over customer's lifetime |
|
SMS_CNT_LAST_MO |
SMSs sent in last 1 month |
|
SMS_CNT_LAST_3MO |
SMSs sent in last 3 months |
|
MMS_CNT_LFTM |
MMSs sent over customer's lifetime |
|
MMS_CNT_LAST_MO |
MMSs sent in last 1 month |
|
MMS_CNT_LAST_3MO |
MMSs sent in last 3 months |
|
DWNLD_DATA_LFTM |
Data downloaded in KBs in lifetime |
|
DWNLD_DATA_LAST_MO |
Data downloaded in KBs in last 1 month |
|
DWNLD_DATA_LAST_3MO |
Data downloaded in KBs in last 3 months |
|
UPLD_DATA_LFTM |
Data uploaded in KBs in lifetime |
|
UPLD_DATA_LAST_MO |
Data uploaded in KBs in last 1 month |
|
UPLD_DATA_LAST_3MO |
Data uploaded in KBs in last 3 months |
|
OFFNET_CALLS_LFTM |
Number of offnet calls in lifetime |
|
OFFNET_CALLS_LAST_MO |
Number of offnet calls in last 1 month |
|
OFFNET_CALLS_LAST_3MO |
Number of offnet calls in last 3 months |
|
ONNET_CALLS_LFTM |
Number of onnet calls in lifetime |
|
ONNET_CALLS_LAST_MO |
Number of onnet calls in last 1 month |
|
ONNET_CALLS_LAST_3MO |
Number of onnet calls in last 3 months |
|
OFFNET_AIRTIME_LFTM |
Total offnet airtime in minutes in lifetime |
|
OFFNET_AIRTIME_LAST_MO |
Total offnet airtime in minutes in last 1 month |
|
OFFNET_AIRTIME_LAST_3MO |
Total offnet airtime in minutes in last 3 months |
|
ONNET_AIRTIME_LFTM |
Total onnet airtime in minutes in lifetime |
|
ONNET_AIRTIME_LAST_MO |
Total onnet airtime in minutes in last 1 month |
|
ONNET_AIRTIME_LAST_3MO |
Total onnet airtime in minutes in last 3 months |
Data Mining Target Attribute
AGE_ON_NET_NBR
Oracle Data Miner Workflow - CUSTOMER_LTV_SRVL_VAL_GLMR
Workflow Description
-
In data miner workflow, relevant columns are selected from DWD_CUST_DNA in Data Source node
-
Data is split into source and apply datasets
-
Source dataset is further split into training and test datasets
-
Training dataset is used to train customer lifetime value and customer lifetime survival models using Generalized Linear Model Regression algorithm
-
Models are CUST_LTV_RGRSN_GLMR and CUST_SRVL_RGRSN_GLMR
-
CUST_LTV_RGRSN_GLMR model details are extracted and stored in DWV_LTV_RGRSN_MDL_DTLS database view
-
CUST_SRVL_RGRSN_GLMR model details are extracted and stored in DWV_SV_RGRSN_MDL_DTLS database view
-
Apply dataset is scored using trained customer lifetime value model. CUST_LTV_RGRSN_GLMR model apply results are stored in DWV_LTV_RGRSN_MDL_APPLY database view and CUST_SRVL_RGRSN_GLMR model apply results are stored in DWV_SV_RGRSN_MDL_APPLY.
Data Mining Target Tables
customer_ltv_srvl_val_glmr workflow is executed when pkg_ocdm_mining_odmr_util.odmr_run_wf procedure invoked. Workflows status is extracted and stored into dwc_odmr_wf_exctn table by invoking pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure.
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of workflow execution. If workflow is executed successfully without any errors, then the procedure will invoke pkg_ocdm_mining_util.updt_mnng_trgt_ltv and pkg_ocdm_mining_util.updt_mnng_trgt_srvl_val procedures to update mining target tables for CUST_LTV_RGRSN_GLMR and CUST_SRVL_RGRSN_GLMR models.
Following table gives the list of target tables (columns) that get updated/loaded/created:
Table 11-32 Data Mining Target Tables Customer Lifetime Survival Value
| Model Name | Target Table Name | Database Operation | Columns |
|---|---|---|---|
|
CUST_LTV_RGRSN_GLMR |
DWD_CUST_DNA |
Update |
PRDCT_LTV_VALUE |
|
DWL_MNNG_LTV_BAND |
Insert |
No value |
|
|
CUST_LTV_RGRSN_GLMR |
DWD_CUST_DNA |
Update |
PRDCT_LT_SRVVL_VAL |
| CUST_LTV_RGRSN_GLMR |
DWL_MNNG_LT_SRVVL_BAND |
Insert |
No value |
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.
Customer Sentiment
This model leverages Text mining capability provided by Oracle database. For more information, see Oracle Data Mining Concepts
-
Oracle Text
-
Support Vector Machines
Source Tables and Attributes
There are following two steps in preparing data for Customer Sentiment Analysis:
-
Prepare customers comment data: pkg_ocdm_mining_util PL/SQL package has prepare_cust_cmmnt_data to prepare the data. This procedure takes l_apply_day parameter as an input and inserts concatenated customer comments (three months prior to l_apply_day) into DM_CUST_CMMNT table.
-
Score customers’ comments using dictionary: pkg_ocdm_mining_util PL/SQL package has cust_sntmnt_manual_score to score customers’ comments using dictionary. This procedure takes p_training_pct (by default 50%) as an input and updates MANUAL_SCORE column of DM_CUST_CMMNT table for p_training_pct (by default 50%) of customers.
Customer comments data preparation (prepare_cust_cmmnt_data) and customers’ comments scoring (cust_sntmnt_manual_score) procedures are invoked by pkg_ocdm_mining_odmr_util.odmr_run_wf procedure.
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.
| Attribute | Data Type | Description |
|---|---|---|
|
DAY_KEY |
NUMBER |
Day key |
|
CUST_CD |
VARCHAR2(30) |
Customer code |
|
MANUAL_SCORE |
VARCHAR2(10) |
Manual scores or manually adjusted after reading |
|
SENTIMENT |
VARCHAR2(10) |
Sentiment scored by Mining Model |
|
PLUSPROB |
NUMBER |
The probability of customer belonging to happy group |
|
MINUSPROB |
NUMBER |
The probability of customer belonging to un-happy group |
|
CUST_CMMNT |
VARCHAR2(4000) |
The text messages all together from the customer. |
Oracle Data Miner Workflow - CUSTOMER_SENTIMENT_SVM
Workflow Description
-
Manually scored customer comments data is selected as source dataset and customer comments data with no manual score selected as apply dataset
-
Source dataset is further split into training and test datasets
-
Training dataset is tokenized using Oracle Text algorithm
-
Tokenized Training dataset is used to train customer sentiment model using Support Vector Machines algorithm
-
Model is CUST_SNTMNT_SVM
-
Apply dataset is scored using trained customer sentiment model. CUST_SNTMNT_SVM model apply results are stored in DWV_CUST_SNTMNT_APPLY database view
Mining Target Tables
customer_sentiment_svm workflow is executed when pkg_ocdm_mining_odmr_util.odmr_run_wf procedure invoked. Workflows status is extracted and stored into dwc_odmr_wf_exctn table by invoking pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure.
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of workflow execution. If workflow is executed successfully without any errors, then the procedure will invoke pkg_ocdm_mining_util.updt_mnng_trgt_cust_sntmnt procedure to update mining target table for CUST_SNTMNT_SVM model.
Following table gives the list of target tables (columns) that get updated/loaded/created:
| Target Table Name | Database Operation | Columns |
|---|---|---|
|
DWD_CUST_DNA |
Update |
SNTMNT_PROB |
|
DWD_CUST_DNA |
Update |
SNTMNT_CTGRY_CD |
|
DWD_CUST_DNA |
Update |
MANUAL_SNTMNT_CTGRY |
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.
Targeted Promotion
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.
-
Support Vector Machines
Table 11-33 Attributes Identified from DWD_VAS_SBRP_QCK_SUMM, DWR_CUST, and DWD_CUST_DNA table for Target Promotion
| Attribute | Description |
|---|---|
|
CUST_CD |
Customer Identifier |
|
PRTY_TYP_CD |
Party Type Code, e.g. Individual, Large Enterprise, Medium Enterprise, etc. |
|
BSNS_LEGAL_STAT_CD |
Legal Status Code of Enterprise Customers, e.g. Public, Private |
|
MRTL_STAT_CD |
Individual Customer Marital Status Code |
|
HH_SZ |
Household Size |
|
JB_CD |
Customer Job Code |
|
NTNLTY_CD |
Customer Nationality Code |
|
EDU_CD |
Education Qualification Code |
|
GNDR_CD |
Individual Customer Gender Code |
|
JB_AGRMNT_TYP |
Job Agreement Type |
|
BARNG_RSN_CD |
Customer Barring Reason Code |
|
POSTCD_CD |
Postcard Code |
|
STATE |
State |
|
FUTRE_AGRMNT_CNT_LAST_MO |
Number of Future Agreements in last 1 month |
|
SBRP_CNT_LAST_MO |
Subscription Count in last three months |
|
SSPNSN_CNT_LAST_MO |
Suspension Count in last three months |
|
AGRMNT_CNT_LAST_MO |
Number of Agreements in last 1 month |
|
CMPLNT_CNT_LFTM |
Number of complaints made by customer in his/her life span |
|
CMPLNT_CNT_LAST_MO |
Number of complaints made by customer in this month |
|
LIFE_SPN |
Customer Life Span |
|
AGRMNT_LFT_DAYS_LAST_MO |
Agreement Left Days in last 1 month |
|
ACCT_LFT_VAL_LAST_MO |
Account Left Value in last 1 month |
|
REMNG_AGRMNT_AMT_LAST_MO |
Remaining Agreement Amount in last 1 month |
|
DEBT_VAL_LAST_MO |
Debt Value in last 1 month |
|
LYLTY_PROG_BAL_LAST_MO |
Loyalty Program Balance in last 1 month |
|
TOT_PYMT_RVN_LAST_MO |
Total Payment Revenue as of 1 month ago |
|
MO_RVN_LAST_MO |
Monthly Revenue as of 1 month ago |
|
LFTM_ARPU_LAST_MO |
Lifetime ARPU as of 1 month ago |
|
AGRMNT_ARPU_LAST_MO |
Agreement ARPU as of 1 month ago |
|
PORT_IN_FROM |
Service Provide from who the customer ported out from |
|
PORT_IN_CNT |
Number of times the customer ported in |
|
PORT_OUT_CNT |
Number of times the customer ported out |
|
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 |
|
CALL_BCK |
Indicates whether Customer subscribed to Call Back |
|
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 |
|
CW |
Indicates whether Customer subscribed to Call Waiting |
|
FAX |
Indicates whether Customer subscribed to Fax |
|
GPRS |
Indicates whether Customer subscribed to GPRS |
|
INTRNTL_CALL |
Indicates whether Customer subscribed to International Call |
|
MMS |
Indicates whether Customer subscribed to MMS |
|
SMS |
Indicates whether Customer subscribed to SMS |
|
WAP |
Indicates whether Customer subscribed to WAP |
Oracle Data Miner Workflow - TARGET_PROMOTION_SVM
Workflow Description
-
VAS subscription data is selected from DWD_VAS_SBRP_QCK_SUMM table
-
VAS subscription data is split into Source for Training and Source for Apply datasets
-
Source for Training dataset is combined with DWD_CUST_DNA table to generate source dataset
-
Source for Apply dataset is combined with DWD_CUST_DNA table to generate apply dataset
-
Source dataset is further split into training and test datasets
-
Test dataset is stored in DWV_TARGET_PROMO_TST database view. This data is later used to extract ROC, LIFT, and Confusion Matrix from Support Vector Machines model, TARGET_PROMO_SVM
-
Training dataset is used to train target promotion model for selected product (in Target Promotion SVM node) using Support Vector Machines algorithm
-
Model is TARGET_PROMO_SVM
-
Apply dataset is scored using trained target promotion model. TARGET_PROMO_SVM model apply results are stored in DWV_TARGET_PROMO_SVM_APPLY database view
Update Mining Target Tables
target_promotion_svm workflow is executed when pkg_ocdm_mining_odmr_util.odmr_run_wf procedure invoked. Workflows status is extracted and stored into dwc_odmr_wf_exctn table by invoking pkg_ocdm_mining_odmr_util.odmr_wf_ltst_exctn_log procedure.
Invoke pkg_ocdm_mining_odmr_util.odmr_wf_stat_chk_upd_trgt_tab procedure to check the status of workflow execution. If workflow is executed successfully without any errors, then the procedure will invoke pkg_ocdm_mining_util.updt_mnng_trgt_prod_prmtn procedure to update mining target table for CUST_SNTMNT_SVM model.
Following table gives the list of target tables (columns) that get updated/loaded/created:
| Target Table Name | Database Operation | Columns |
|---|---|---|
|
DWD_PROMO_SVM_FACTOR |
Insert |
No value |
|
DWD_CUST_PROD_AFFLTN |
Insert |
No value |
|
DWD_CUST_PROD_AFFLTN |
Update |
AFFLTN_PROB |
|
DWD_CUST_PROD_AFFLTN |
Update |
BUY_IND |
|
DWD_TARGET_PROMO_SVM_ROC |
Create |
No value |
|
DWD_TARGET_PROMO_SVM_LIFT |
Create |
No value |