Skip Headers
Oracle® Communications Data Model Reference
Release 11.3.2

E28440-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Oracle Communications Data Model Data Mining Models

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

This chapter includes the following sections:

About Data Mining in Oracle Communications Data Model

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

Note:

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

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

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

Model Algorithms Used by Data Mining Model

Model 1: Prepaid Churn Prediction

Decision Tree(DT), Support Vector Machine(SVM)

Model 2: Postpaid Churn Prediction

Decision Tree(DT), Support Vector Machine(SVM)

Model 3: Customer Profiling

K-Means (KM)

Model 4: Targeted Promotion

Support Vector Machine (SVM)

Model 5: Customer Life Time Value

Generalized Linear Models(GLM)

Model 6: Customer Life Time Survival Value

Generalized Linear Models (GLM)

Model 7: Customer Sentiment

Support Vector Machine (SVM)


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

Understanding the Mining Model Architecture

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

  • Mining Model Source Views: Views defined source derived tables, DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM. These views are used to train mining models. Source data is selected as of training day parameter provided in DWC_ETL_PARAMETER table for "BUILD-MINING-MODELS" process.

  • Mining Model Apply Views: Views defined source derived tables, DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM. These views are used to apply trained mining models. Apply data is selected as of apply day parameter provided in DWC_ETL_PARAMETER table for "BUILD-MINING-MODELS" process.

  • Mining Model Support Tables: Mining algorithm settings for different algorithms used in Oracle Communications Data Model are stored in the support tables. These support tables start with "DM". Building a mining model creates few tables and views which start with "DM$".

Note:

Do not delete tables and views that start with "DM" and "DM$". Deleting "DM$" tables and views would also delete the trained mining model.
  • Mining Model Target Tables: Mining model target tables used for storing mining model rules and prediction results. Mining model rules are generated from the trained model and predictions results are produced when a trained model is applied on apply data.

  • Mining Model Core Package: This is the core package for Oracle Communications Data Model data mining. Each mining model has separate procedure in this package. Each procedure builds, tests, and applied mining model. It uses source views as training data, and applies trained model on apply views.

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

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

Refreshing the Oracle Communications Data Model Mining Model

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

  1. Make sure the source derived tables DWD_CUST_DNA and DWD_VAS_SBRP_QCK_SUMM are loaded with latest data.

  2. Update "from_date_etl" and "end_date_etl" columns for "BUILD-MINING-MODELS" process in DWC_ETL_PARAMETER table.

    • "from_date_etl" - Training data is selected as of this date

    • "to_date_etl" - Apply data is selected as of this date

  3. Refresh mining source views and apply views to get training data and apply data for the given training day and apply day:

    exec pkg_mining_etl.refresh_mining_views(l_trnng_day_key,l_apply_day_key);
    
  4. Build, test, and apply mining models. Make sure an intra-etl process is running, if not, start a process:

    exec pkg_ocdm_mining.REFRESH_MODEL(l_apply_day_key,NULL);
    
  5. For each mining procedure, an activity is created and the status of all mining activities are saved into DWC_INTRA_ETL_ACTIVITY table.

Oracle Communications Data Model Mining Result Tables

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

Name Data Type Description

CLSTR_SGMNT_CD

VARCHAR2(8)

The k-Means algorithm divides the set of all customers into segments. This value identifies the segment that the customer belongs to.

CMPTD_LTV_VALUE

NUMBER

 

LT_SRVVL_CD

VARCHAR2(120)

The band code of Customer Survival period (Life Expectancy), predicted by Life_Exp (GLMR) Model.

LTV_BAND_CD

VARCHAR2(120)

The band code of customer lifetime value, predicted by LTV Generalized Linear Models Regression. For more information, see Oracle Data Mining Concepts.

MANUAL_SNTMNT_CTGRY

VARCHAR2(120)

The manual score applied by end user. The end user generates this model. For example, an employee from the operator might generate this model. Usually this is the call center agent. For example, when the message is recorded, there could be a manual tag associated with the message indicating that the customer is happy or upset.

PRDCT_CHURN_DT_IND

CHAR(1)

Boolean value whether customer will churn in next three months according to DT model.

PRDCT_CHURN_DT_ND_NBR

VARCHAR2(30)

The ID of the node in the decision tree where the customer is assigned.

PRDCT_CHURN_DT_PROB

NUMBER

The probability value of how likely customer will churn in next 3 months. This is the probability that the DT prediction is correct.

PRDCT_CHURN_SVM_IND

CHAR(1)

Boolean value whether customer will churn in next three months according to SVM model.

PRDCT_CHURN_SVM_PROB

NUMBER

The probability value of how likely customer will churn in next 3 months. This is the probability that the SVM prediction is correct.

PRDCT_LT_SRVVL_VAL

NUMBER(22,7)

The value of Customer Survival period (Life Expectancy), predicted by Life_Exp (GLMR) Mode.

PRDCT_LTV_VALUE

NUMBER

The real value of Customer Lifetime value, predicted by LTV (GLMR) Mode.

SNTMNT_CTGRY_CD

VARCHAR2(120)

The customer sentiment category detected by Customer sentiment model (SVM + Text). This is an SVM model on transformed TEXTs (transformed into a words matrix).

SNTMNT_PROB

NUMBER

The probability of which customer is in possible model (Happy). This is the probability that customer is happy with their service. For example, a value of 60% means there is 60% chance that customer is happy with the service and a 40% chance that customer is not happy.


Table 10-3 DWD_CUST_PROD_AFFLTN Data Mining Result Table

Name Data Type Description

AFFLTN_PROB

NUMBER(20,18)

The likelihood, predicted by the SVM model, that the customer will purchase the product.

BUY_IND

CHAR(1)

Boolean value to indicate whether customer may purchase the product. This indicates that a value 1 is BUY and a value of 0 is "NOT to BUY".

CUST_CD

VARCHAR2(120)

Customer natural key to identify the customer.

MO_KEY

NUMBER(30)

Month key for which the target promotion model was trained.

PROD_CD

VARCHAR2(120)

The product code which was predicted against. This is target product for promotion.


Table 10-4 DWD_CHRN_SVM_FACTOR Data Mining Result Table

Name Data Type Description

ACCT_TYP_CD

VARCHAR2(120)

Account Type Code. For example: Prepaid, Postpaid

ATTRIBUTE_NAME

VARCHAR2(4000)

Name of the factor.

ATTRIBUTE_SUBNAME

VARCHAR2(4000)

Subname of the factor if there is any. For example, if the ATTRIBUTE_NAME has the value, "Payment_Method", then the ATTRIBUTE_SUBNAME could be and of the following:

  • Debit_Card

  • Cash

Each ATTRIBUTE_SUBNAME has a different weight, coefficient, in the model.

ATTRIBUTE_VALUE

VARCHAR2(4000)

Value of the factor, if there is any. For example, for payment method, value of "cash" and "direct debit" might have different influence and ranking.

COEFFICIENT

NUMBER

Importance of the factor. The factors are ranked according to this value.

MODEL_NAME

VARCHAR2(120)

Churn model name


Table 10-5 DWD_PROMO_SVM_FACTOR Data Mining Result Table

Name Data Type Description

ATTRIBUTE_NAME

VARCHAR2(4000)

Name of the factor.

ATTRIBUTE_SUBNAME

VARCHAR2(4000)

Subname of the factor if there is any. For example, if the ATTRIBUTE_NAME has the value, "Payment_Method", then the ATTRIBUTE_SUBNAME could be and of the following:

  • Debit_Card

  • Cash

Each ATTRIBUTE_SUBNAME has a different weight, coefficient, in the model.

ATTRIBUTE_VALUE

VARCHAR2(4000)

Value of the factor, if there is any. For example, for payment method, value of "cash" and "direct debit" might have different influence and ranking.

COEFFICIENT

NUMBER

Importance of the factor. The factors are ranked according to this value.

MO_KEY

NUMBER(30)

Month key for which the target promotion model was trained

PROD_CD

VARCHAR2(50)

The product code which was predicted against. This is target product for promotion.


Table 10-6 DWR_CUST_DT_NODE Data Mining Result Table

Name Data Type Description

ACCT_TYP_CD

VARCHAR2(120)

Account Type Code. For example: Prepaid, Postpaid

CONFIDENCE

NUMBER

Ratios of prediction_count to record_count

IS_LEAF

VARCHAR2(10)

Indicates whether the node is a leaf indicator. The prediction of lead node is the final prediction

MODEL_NAME

VARCHAR2(120)

Churn model name

NODE_ID

VARCHAR2(50)

Decision tree node number

PREDICTION

NUMBER

Prediction for the current node. If number of customers predicted to churn is higher than number of customers to retain, then this would be 1, otherwise it would be 0

PREDICTION_COUNT

NUMBER

Number of customers predicted to churn under the current node

RECORD_COUNT

NUMBER

Number of customer under the current node

SUPPORT

NUMBER

Ration of record_count to total number of customers


Table 10-7 DWD_CHRN_SVM_ROC Data Mining Result Table

Name Data Type Description

ACCT_TYP_CD

VARCHAR2(120)

Account Type Code. For example, Prepaid, Postpaid

BASE_BENCHMARK_PERCENT

NUMBER

Base Benchmark Percentage

COST_THRESHOLD

NUMBER

Cost Threshold

GAIN_CUMULATIVE

NUMBER

Cumulative Gain

LIFT_CUMULATIVE

NUMBER

Cumulative Lift

LIFT_QUANTILE

NUMBER

Quantile Lift

MAX_SVM_TO_CHRN_PROB

NUMBER

Maximum Churn Probability

MIN_SVM_TO_CHRN_PROB

NUMBER

Minimum Churn Probability

MODEL_NAME

VARCHAR2(120)

Churn Model Name

NON_TARGETS_CUMULATIVE

NUMBER

Cumulative Non-targets

PERCENTAGE_RECORDS_CUMULATIVE

NUMBER

Cumulative Percentage Records

PRED_QUANTILE_TARGET_COUNT

NUMBER

Predicted Target Count of the Quantile

QUANTILE_NUMBER

NUMBER

Quantile number

QUANTILE_TARGET_COUNT

NUMBER

Target Count of the current Quantile

QUANTILE_TOTAL_COUNT

NUMBER

Total Count in the current Quantile

TARGET_DENSITY

NUMBER

Target Density

TARGET_DENSITY_CUMULATIVE

NUMBER

Cumulative Target Density

TARGETS_CUMULATIVE

NUMBER

Cumulative Targets

TEST_DATE

DATE

Date when mining model trained and applied


Table 10-8 DWD_PROMO_SVM_ROC Data Mining Result Table

Name Data Type Description

ACCT_TYP_CD

VARCHAR2(120)

Account Type Code. For example, Prepaid, Postpaid

BASE_BENCHMARK_PERCENT

NUMBER

Base Benchmark Percentage

COST_THRESHOLD

NUMBER

Cost Threshold

GAIN_CUMULATIVE

NUMBER

Cumulative Gain

LIFT_CUMULATIVE

NUMBER

Cumulative Lift

LIFT_QUANTILE

NUMBER

Quantile Lift

MAX_SVM_TO_BUY_PROB

NUMBER

Maximum Buy Probability

MIN_SVM_TO_BUY_PROB

NUMBER

Minimum Buy Probability

MO_KEY

NUMBER(30)

Month Key

MODEL_NAME

VARCHAR2(120)

Churn Model Name

NON_TARGETS_CUMULATIVE

NUMBER

Cumulative Non-targets

PERCENTAGE_RECORDS_CUMULATIVE

NUMBER

Cumulative Percentage Records

PRED_QUANTILE_TARGET_COUNT

NUMBER

Predicted Target Count of the Quantile

PROD_CD

VARCHAR2(50)

Product code

QUANTILE_NUMBER

NUMBER

Quantile number

QUANTILE_TARGET_COUNT

NUMBER

Target Count of the current Quantile

QUANTILE_TOTAL_COUNT

NUMBER

Total Count in the current Quantile

TARGET_DENSITY

NUMBER

Target Density

TARGET_DENSITY_CUMULATIVE

NUMBER

Cumulative Target Density

TARGETS_CUMULATIVE

NUMBER

Cumulative Targets

TEST_DATE

DATE

Date when mining model trained and applied


Model 1: Prepaid Churn Prediction

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

Prepaid Churner Definition

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

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

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

Prepaid Churn Source

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

Table 10-9 DWV_PRPD_CUST_CHRN_SRC

Attribute Description

ACCPT_NWSLTR_IND

Indicates whether customer accepts News Letter

ADDR_LOC_CD

Customer Address Location Code

AGE_BND_CD

Customer Age Band Code

AGE_ON_NET_BND_CD

Customer Age on Net Band Code

AGE_ON_NET_NBR

Customer Age on Net expressed in number of months

ARPU_BND_CD

Customer ARPU Band Code

AVG_DRTN_BTWN_RCHRG

Average duration between two recharges - in days

BARNG_RSN_CD

Customer Barring Reason Code

BRDBND_IND

Indicates whether Customer has Broadband connection

BSNS_LEGAL_STAT_CD

Legal Status Code of Enterprise Customers, for example, Public, Private

CAR_DRVR_LICNS_IND

Indicates whether customer has driver's license

CAR_TYP_CD

Car Type Code

CHRN_IND

Indicates whether a customer is a Churner or Non-churner

CITY

City

CMPLNT_CNT_LAST_3MO

Number of complaints made by customer in last 3 months

CMPLNT_CNT_LAST_MO

Number of complaints made by customer in this month

CMPLNT_CNT_LFTM

Number of complaints made by customer in his/her life span

CNCT_ADDR_EFF_DT_NBR

Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

CNTCT_ALLWD_IND

Indicates whether customer allows to contact

CNTRY

Country

COLL_ZIP_CD

College ZIP Code

CRDT_CTGRY_KEY

Customer Credit Category

CUST_BRANCH_CD

Customer Branch Code

CUST_CD

Customer Identifier

CUST_RVN_BND_CD

Customer Revenue Band Code

CUST_SCR_CD

Customer SCR Code

CUST_TYP_CD

Customer Type Code

DAYS_BFR_FIRST_RCHRG

Days between first payment and first recharge

DAYS_BFR_FIRST_USE

Days between payment and first use

DRPD_CALLS_CNT_LAST_3MO

Number of dropped calls in last 3 months

DRPD_CALLS_CNT_LAST_MO

Number of dropped calls this month

DRPD_CALLS_CNT_LFTM

Number of dropped calls in customer life span

DWLNG_OWNER

Dwelling Owner

DWLNG_STAT

Dwelling Status

DWLNG_SZ

Dwelling Size

DWLNG_TENR

Dwelling Tenure

DWLNG_TYP

Dwelling Type

DWNLD_DATA_LAST_3MO

Data downloaded in KBs in last 3 months

DWNLD_DATA_LAST_MO

Data downloaded in KBs in last 1 month

DWNLD_DATA_LFTM

Data downloaded in KBs in lifetime

ECNMCLY_ACTV_IND

Indicates whether a customer is economically Active

EDU_CD

Education Qualification Code

EFF_FROM_DT_NBR

Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

END_OF_JB_AGRMNT_NBR

End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

ESTMTD_ACQSTN_COST

Customer Acquisition Estimated Cost

ETHNCTY

Customer Ethnicity

ETHNIC_BCKGRND

Customer Ethnic Background

FORM_OF_EMPMNT

Form of Employment

GNDR_CD

Individual Customer Gender Code

HH_SZ

Household Size

HNGUP_CALLS_CNT_LAST_3MO

Number of hangup calls in last 3 months

HNGUP_CALLS_CNT_LAST_MO

Number of hangup calls this month

HNGUP_CALLS_CNT_LFTM

Number of hangup calls in customer life span

HOMTEL_IND

Indicates whether Customer has Home Telephone

IDD_IND

Indicates whether Customer subscribed to International Direct Dialing

JB_AGRMNT_TYP

Job Agreement Type

JB_CD

Customer Job Code

JB_POSN

Customer Job Position

LANG_CD

Language Code

LAST_INCOMING_USG_DT_NBR

When was last incoming call occurred

LAST_OUTGOING_USG_DT_NBR

When was last outgoing call occurred

LAST_RCHRG_AMT

Last recharge amount

LEGAL_TTL_TO_HSNG

Legal Title to Housing

LIFE_SPN

Customer Life Span

LIKE_SCP_IN_FCBK_IND

Indicates whether customer likes Service Provide page in Facebook

LVNG_AT_CURR_ADDR_SINCE_NBR

Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

MAIL_ALWD_IND

Indicates whether Customer allows Service Provider to send mail

MAX_RCHRG_AMT

Maximum recharge amount

MIN_RCHRG_AMT

Minimum recharge amount

MMS_CNT_LAST_3MO

MMSs sent in last 3 months

MMS_CNT_LAST_MO

MMSs sent in last 1 month

MMS_CNT_LFTM

MMSs sent over customer's lifetime

MRTL_STAT_CD

Individual Customer Marital Status Code

MULT_PRTY_ROLE_IND

Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider

NAME_OF_WKPLC

Name of Workplace

NAME_PRFX

Name Prefix

NBR_CHLDRN_AT_COLL

Total Number of Children at College

NBR_CHLDRN_AT_SCHL

Total Number of Children at School

NBR_CHLDRN_AT_UNIV

Total Number of Children at University

NBR_OF_CHLDRN

Total Number of Children

NBR_OF_DPNDNT

Total Number of Dependents

NEW_ACCT_IND

Indicates whether Customer is New

NO_MTHS_CMPNY

Number of months an Individual Customer is in his/her current Company

NO_MTHS_POSN

Number of months an Individual Customer is in his/her current Position

NTNLTY_CD

Customer Nationality Code

OFFNET_AIRTIME_LAST_3MO

Total offnet airtime in minutes in last 3 months

OFFNET_AIRTIME_LAST_MO

Total offnet airtime in minutes in last 1 month

OFFNET_AIRTIME_LFTM

Total offnet airtime in minutes in lifetime

OFFNET_CALLS_LAST_3MO

Number of offnet calls in last 3 months

OFFNET_CALLS_LAST_MO

Number of offnet calls in last 1 month

OFFNET_CALLS_LFTM

Number of offnet calls in lifetime

ONNET_AIRTIME_LAST_3MO

Total onnet airtime in minutes in last 3 months

ONNET_AIRTIME_LAST_MO

Total onnet airtime in minutes in last 1 month

ONNET_AIRTIME_LFTM

Total onnet airtime in minutes in lifetime

ONNET_CALLS_LAST_3MO

Number of onnet calls in last 3 months

ONNET_CALLS_LAST_MO

Number of onnet calls in last 1 month

ONNET_CALLS_LFTM

Number of onnet calls in lifetime

OTH_DRVR_LICNS_IND

Indicates whether customer has other vehicle License

PAY_TV_IND

Indicates whether Customer has Pay TV connection

PLC_OF_BRTH

Place of Birth

PORT_IN_CNT

Number of times the customer ported in

PORT_IN_FROM

Service Provide from who the customer ported out from

PORT_OUT_CNT

Number of times the customer ported out

POSTCD_CD

Postcard Code

PREF_CNTCT_MDM

Preferred Contact Method

PREF_ENTMNT_IND

Indicates whether customer given any Entitlement preference

PREF_GVN_IND

Indicates whether customer given any preference

PREF_MSC_IND

Indicates whether customer given any Music preference

PREF_SPRT_IND

Indicates whether customer given any sport preference

PREF_TRVL_IND

Indicates whether customer given any Travel preference

PRMRY_STAT_CD

Customer Primary Status Code

PRMRY_STAT_RSN_CD

Customer Primary Status Reason Code

PRTY_AGE

Customer Age expressed as number of years

PRTY_TYP_CD

Party Type Code, for example, Individual, Large Enterprise, Medium Enterprise, and so on.

RLSD_CALLS_CNT_LAST_3MO

Number of released calls in last 3 months

RLSD_CALLS_CNT_LAST_MO

Number of released calls this month

RLSD_CALLS_CNT_LFTM

Number of released calls in customer life span

RMRK_CNT_LAST_3MO

Numbers of remarks made against customer in last 3 months

RMRK_CNT_LAST_MO

Numbers of remarks made against customer in this month

RMRK_CNT_LFTM

Numbers of remarks made against customer in his/her life span

SCHL_ZIP_CD

School ZIP Code

SCL_NTWK_USR_IND

Indicates whether customer uses any Social Network site

SL_CHNL_KEY

Sales Channel

SL_CHNL_RPRSTV_KEY

Sales Channel Representative

SMS_CNT_LAST_3MO

SMSs sent in last 3 months

SMS_CNT_LAST_MO

SMSs sent in last 1 month

SMS_CNT_LFTM

SMSs sent over customer's lifetime

SPRT_IND

Indicates whether customer plays Sports

SPRT_TYP_CD

Sport Type Code

SRC_OF_INCM

Source of Income

STATE

State

STRT_OF_EMPMNT_NBR

Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

TOT_AGE_CHLDRN

Total Age of Children

TOT_CALL_CNT_LAST_3MO

Total number of calls to Call center made by customer in last 3 months

TOT_CALL_CNT_LAST_MO

Total number of calls to Call center made by customer in this month

TOT_CALL_CNT_LFTM

Total number of calls to Call center made by customer in his/her life span

TOT_CNTRBTN_LAST_MO

Sum of total payments and total recharges made in the last one month

TOT_CNTRBTN_LFTM

Sum of total payments and total recharges made in customers life time

TOT_DAYS_ACTVTY

Total days of activity

TOT_DAYS_ON_USE

Total days on use

TOT_DAYS_OUTGOING_ACTVTY

Total days of outgoing activity

TOT_DRTN_OF_USG

Total durations of usage in customer life time

TOT_EXP_AMT_EQVLT_VAL

Total amount expired in customers life time

TOT_HLD_DRTN_LAST_3MO

Total hold duration in last 3 months in seconds

TOT_HLD_DRTN_LAST_MO

Total hold duration this month in seconds

TOT_HLD_DRTN_LFTM

Total hold duration in customer life span in seconds

TOT_QUE_DRTN_LAST_3MO

Total queue duration in the last 3 months in seconds

TOT_QUE_DRTN_LAST_MO

Total queue duration this month in seconds

TOT_QUE_DRTN_LFTM

Total queue duration in customer life span in seconds

TOT_RCHRG_AMT_LAST_3MO

Total recharge amount this month

TOT_RCHRG_AMT_LAST_MO

Total recharge amount in last 3 months

TOT_RCHRG_AMT_LFTM

Total recharge amount in customers life time

TOT_RCHRG_CNT_LAST_3MO

Number of recharges made in last 3 months

TOT_RCHRG_CNT_LAST_MO

Number of recharges made this month

TOT_RCHRG_CNT_LFTM

Number of recharges made in customer life time

TOT_TALK_DRTN_LAST_3MO

Total talk duration in the last 3 months in seconds

TOT_TALK_DRTN_LAST_MO

Total talk duration this month in seconds

TOT_TALK_DRTN_LFTM

Total talk duration in customer life span in seconds

TOT_UTILZD_EQVLT_VAL

Total utilized monetary value in customers life time

UNIV_ZIP_CD

University ZIP Code

UPLD_DATA_LAST_3MO

Data uploaded in KBs in last 3 months

UPLD_DATA_LAST_MO

Data uploaded in KBs in last 1 month

UPLD_DATA_LFTM

Data uploaded in KBs in lifetime

VOI_INTERNAT_AIRTIME_LAST_3MO

International voice air time including roaming in minutes in last three months

VOI_INTERNAT_AIRTIME_LAST_MO

International voice air time in minutes including roaming in last one month

VOI_INTERNAT_AIRTIME_LFTM

International voice air time in minutes including roaming over customer's life time

VOI_NAT_AIRTIME_LAST_3MO

National voice air time in minutes in last three months

VOI_NAT_AIRTIME_LAST_MO

National voice air time in minutes in last one month

VOI_NAT_AIRTIME_LFTM

National voice air time in minutes over customer's life time

WRLS_IND

Indicates whether Customer has Wireless Internet connection


Prepaid Churn Output Target Attribute: CHRN_IND

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

  • DWD_CUST_DNA.PRDCT_CHURN_SVM_IND

  • DWD_CUST_DNA.PRDCT_CHURN_SVM_PROB

  • DWD_CUST_DNA.PRDCT_CHURN_DT_IND

  • DWD_CUST_DNA.PRDCT_CHURN_DT_ND_NBR

Prepaid Churn Algorithm

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

  • Support Vector Machines (SVM)

  • Decision Tree

Prepaid Churn Algorithm Setting Tables

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

  • DM_STNG_CHURN_SVM - For Support Vector Machines algorithm

  • DM_STNG_CHURN_DT - For Decision Tree algorithm

Model 2: Postpaid Churn Prediction

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

Postpaid Churner Definition

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, the model may receive the data only three months after the actual Churn. In this case the time window should be adjusted.

Postpaid Churner Source

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

Table 10-10 DWV_PSTPD_CUST_CHRN_SRC

Attribute Description

ACCPT_NWSLTR_IND

Indicates whether customer accepts News Letter

ACCT_LFT_VAL_LAST_3MO

Account Left Value in last three months

ACCT_LFT_VAL_LAST_MO

Account Left Value in last month

ADDR_LOC_CD

Customer Address Location Code

AGE_BND_CD

Customer Age Band Code

AGE_ON_NET_BND_CD

Customer Age on Net Band Code

AGE_ON_NET_NBR

Customer Age on Net expressed in number of months

AGRMNT_CNT_LAST_3MO

Number of Agreements in last 3 months

AGRMNT_CNT_LAST_MO

Number of Agreements in last 1 month

AGRMNT_LFT_DAYS_LAST_3MO

Agreement Left Days in last 3 months

AGRMNT_LFT_DAYS_LAST_MO

Agreement Left Days in last 1 month

ARPU_BND_CD

Customer ARPU Band Code

BARNG_RSN_CD

Customer Barring Reason Code

BLLG_ADDR_EFF_DT_NBR

Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BNKRPT_STAT

Bankrupt Status

BNKRPT_STRT_DT_NBR

Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BRDBND_IND

Indicates whether Customer has Broadband connection

BSNS_LEGAL_STAT_CD

Legal Status Code of Enterprise Customers. For example, Public, Private

CAR_DRVR_LICNS_IND

Indicates whether customer has Car Driving License

CAR_TYP_CD

Car Type Code

CHRN_IND

Indicates whether a customer is a Churner or Non-churner

CITY

City

CMPLNT_CNT_LAST_3MO

Number of complaints made by customer in last 3 months

CMPLNT_CNT_LAST_MO

Number of complaints made by customer in this month

CMPLNT_CNT_LFTM

Number of complaints made by customer in his/her life span

CMPNY_EMP_SZ_BND_CD

Enterprise Customer Employee Size Band Code

CMPNY_RVN_BND_CD

Enterprise Customer Revenue Band Code

CMPNY_TYP_CD

Company Type Code

CNCT_ADDR_EFF_DT_NBR

Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

CNTCT_ALLWD_IND

Indicates whether customer allows to contact

CNTRY

Country

COLL_ZIP_CD

College ZIP Code

CRDT_CTGRY_KEY

Customer Credit Category

CUST_BRANCH_CD

Customer Branch Code

CUST_CD

Customer Identifier

CUST_PYMT_RESPBL_IND

Indicates whether customer is responsible for making payments

CUST_RVN_BND_CD

Customer Revenue Band Code

CUST_SCR_CD

Customer SCR Code

CUST_TYP_CD

Customer Type Code

DEBT_AGNG_BND_CD_LAST_3MO

Debt Ageing Band Code in last 3 months

DEBT_AGNG_BND_CD_LAST_MO

Debt Ageing Band Code in last 1 month

DEBT_VAL_LAST_3MO

Debt Value in last 3 months

DEBT_VAL_LAST_MO

Debt Value in last 1 month

DRPD_CALLS_CNT_LAST_3MO

Number of dropped calls in last 3 months

DRPD_CALLS_CNT_LAST_MO

Number of dropped calls this month

DRPD_CALLS_CNT_LFTM

Number of dropped calls in customer life span

DWLNG_OWNER

Dwelling Owner

DWLNG_STAT

Dwelling Status

DWLNG_SZ

Dwelling Size

DWLNG_TENR

Dwelling Tenure

DWLNG_TYP

Dwelling Type

DWNLD_DATA_LAST_3MO

Data downloaded in KBs in last 3 months

DWNLD_DATA_LAST_MO

Data downloaded in KBs in last 1 month

DWNLD_DATA_LFTM

Data downloaded in KBs in lifetime

ECNMCLY_ACTV_IND

Indicates whether a customer is economically Active

EDU_CD

Education Qualification Code

EFF_FROM_DT_NBR

Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

END_OF_JB_AGRMNT_NBR

End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

ESTMTD_ACQSTN_COST

Customer Acquisition Estimated Cost

ETHNCTY

Customer Ethnicity

ETHNIC_BCKGRND

Customer Ethnic Background

EXTRNL_ORG_TYP_CD

External Organization Type Code

FORM_OF_EMPMNT

Form of Employment

FUTRE_AGRMNT_CNT_LAST_3MO

Number of Future Agreements in last 3 months

FUTRE_AGRMNT_CNT_LAST_MO

Number of Future Agreements in last 1 month

FUTRE_AGRMNT_DAYS_LAST_3MO

Future Agreement Days in last 3 months

FUTRE_AGRMNT_DAYS_LAST_MO

Future Agreement Days in last 1 month

GNDR_CD

Individual Customer Gender Code

HH_SZ

Household Size

HNGUP_CALLS_CNT_LAST_3MO

Number of hangup calls in last 3 months

HNGUP_CALLS_CNT_LAST_MO

Number of hangup calls this month

HNGUP_CALLS_CNT_LFTM

Number of hangup calls in customer life span

HOMTEL_IND

Indicates whether Customer has Home Telephone

IDD_IND

Indicates whether Customer subscribed to International Direct Dialing

JB_AGRMNT_TYP

Job Agreement Type

JB_CD

Customer Job Code

JB_POSN

Customer Job Position

LANG_CD

Language Code

LEGAL_TTL_TO_HSNG

Legal Title to Housing

LIFE_SPN

Customer Life Span

LIKE_SCP_IN_FCBK_IND

Indicates whether customer likes Service Provide page in Facebook

LVNG_AT_CURR_ADDR_SINCE_NBR

Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

LYLTY_PROG_BAL_LAST_3MO

Loyalty Program Balance in last three months

LYLTY_PROG_BAL_LAST_MO

Loyalty Program Balance in last month

MAIL_ALWD_IND

Indicates whether Customer allows Service Provider to send mail

MMS_CNT_LAST_3MO

MMSs sent in last 3 months

MMS_CNT_LAST_MO

MMSs sent in last 1 month

MMS_CNT_LFTM

MMSs sent over customer's lifetime

MO_RVN_LAST_3MO

Monthly Revenue as of 3 months ago

MO_RVN_LAST_MO

Monthly Revenue as of 1 month ago

MRTL_STAT_CD

Individual Customer Marital Status Code

MULT_PRTY_ROLE_IND

Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider

NAME_OF_WKPLC

Name of Workplace

NAME_PRFX

Name Prefix

NBR_CHLDRN_AT_COLL

Total Number of Children at College

NBR_CHLDRN_AT_SCHL

Total Number of Children at School

NBR_CHLDRN_AT_UNIV

Total Number of Children at University

NBR_EMP_SNC_CUST

Numbers of Employees Since Customer

NBR_OF_CHLDRN

Total Number of Children

NBR_OF_DPNDNT

Total Number of Dependents

NEW_ACCT_IND

Indicates whether Customer is New

NO_MTHS_CMPNY

Number of months an Individual Customer is in his/her current Company

NO_MTHS_POSN

Number of months an Individual Customer is in his/her current Position

NTNLTY_CD

Customer Nationality Code

OFFNET_AIRTIME_LAST_3MO

Total offnet airtime in minutes in last 3 months

OFFNET_AIRTIME_LAST_MO

Total offnet airtime in minutes in last 1 month

OFFNET_AIRTIME_LFTM

Total offnet airtime in minutes in lifetime

OFFNET_CALLS_LAST_3MO

Number of offnet calls in last 3 months

OFFNET_CALLS_LAST_MO

Number of offnet calls in last 1 month

OFFNET_CALLS_LFTM

Number of offnet calls in lifetime

ONNET_AIRTIME_LAST_3MO

Total onnet airtime in minutes in last 3 months

ONNET_AIRTIME_LAST_MO

Total onnet airtime in minutes in last 1 month

ONNET_AIRTIME_LFTM

Total onnet airtime in minutes in lifetime

ONNET_CALLS_LAST_3MO

Number of onnet calls in last 3 months

ONNET_CALLS_LAST_MO

Number of onnet calls in last 1 month

ONNET_CALLS_LFTM

Number of onnet calls in lifetime

OTH_DRVR_LICNS_IND

Indicates whether customer has other vehicle License

PAY_TV_IND

Indicates whether Customer has Pay TV connection

PLC_OF_BRTH

Place of Birth

PORT_IN_CNT

Number of times the customer ported in

PORT_IN_FROM

Service Provide from who the customer ported out from

PORT_OUT_CNT

Number of times the customer ported out

POSTCD_CD

Postcard Code

PREF_CNTCT_MDM

Preferred Contact Method

PREF_ENTMNT_IND

Indicates whether customer given any Entitlement preference

PREF_GVN_IND

Indicates whether customer given any preference

PREF_MSC_IND

Indicates whether customer given any Music preference

PREF_PYMT_MTHD_TYP_CD

Preferred Payment Method Type Code

PREF_SPRT_IND

Indicates whether customer given any sport preference

PREF_TRVL_IND

Indicates whether customer given any Travel preference

PRMRY_STAT_CD

Customer Primary Status Code

PRMRY_STAT_RSN_CD

Customer Primary Status Reason Code

PRTY_AGE

Customer Age expressed as number of years

PRTY_TYP_CD

Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.

PYMT_ACCT_OPEN_DT_NBR

Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

RLSD_CALLS_CNT_LAST_3MO

Number of released calls in last 3 months

RLSD_CALLS_CNT_LAST_MO

Number of released calls this month

RLSD_CALLS_CNT_LFTM

Number of released calls in customer life span

RMRK_CNT_LAST_3MO

Numbers of remarks made against customer in last 3 months

RMRK_CNT_LAST_MO

Numbers of remarks made against customer in this month

RMRK_CNT_LFTM

Numbers of remarks made against customer in his/her life span

SBRP_CNT_LAST_3MO

Subscription Count in last three months

SBRP_CNT_LAST_MO

Subscription Count in last month

SCHL_ZIP_CD

School ZIP Code

SCL_NTWK_USR_IND

Indicates whether customer uses any Social Network site

SL_CHNL_KEY

Sales Channel

SL_CHNL_RPRSTV_KEY

Sales Channel Representative

SMS_CNT_LAST_3MO

SMSs sent in last 3 months

SMS_CNT_LAST_MO

SMSs sent in last 1 month

SMS_CNT_LFTM

SMSs sent over customer's lifetime

SOC_JB_CD

SOC Job Code

SPRT_IND

Indicates whether customer plays Sports

SPRT_TYP_CD

Sport Type Code

SRC_OF_INCM

Source of Income

SSPNSN_CNT_LAST_3MO

Suspension Count in last three months

SSPNSN_CNT_LAST_MO

Suspension Count in last month

STATE

State

STRT_OF_EMPMNT_NBR

Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

TOT_AGE_CHLDRN

Total Age of Children

TOT_CALL_CNT_LAST_3MO

Total number of calls to Call center made by customer in last 3 months

TOT_CALL_CNT_LAST_MO

Total number of calls to Call center made by customer in this month

TOT_CALL_CNT_LFTM

Total number of calls to Call center made by customer in his/her life span

TOT_HLD_DRTN_LAST_3MO

Total hold duration in the last 3 months in seconds

TOT_HLD_DRTN_LAST_MO

Total hold duration this month in seconds

TOT_HLD_DRTN_LFTM

Total hold duration in customer life span in seconds

TOT_PYMT_RVN_LAST_3MO

Total Payment Revenue as of 3 months ago

TOT_PYMT_RVN_LAST_MO

Total Payment Revenue as of 1 month ago

TOT_QUE_DRTN_LAST_3MO

Total queue duration in the last 3 months in seconds

TOT_QUE_DRTN_LAST_MO

Total queue duration this month in seconds

TOT_QUE_DRTN_LFTM

Total queue duration in customer life span in seconds

TOT_TALK_DRTN_LAST_3MO

Total talk duration in the last 3 months in seconds

TOT_TALK_DRTN_LAST_MO

Total talk duration this month in seconds

TOT_TALK_DRTN_LFTM

Total talk duration in customer life span in seconds

UNIV_ZIP_CD

University ZIP Code

UPLD_DATA_LAST_3MO

Data uploaded in KBs in last 3 months

UPLD_DATA_LAST_MO

Data uploaded in KBs in last 1 month

UPLD_DATA_LFTM

Data uploaded in KBs in lifetime

VOI_INTERNAT_AIRTIME_LAST_3MO

International voice air time including roaming in minutes in last three month

VOI_INTERNAT_AIRTIME_LAST_MO

International voice air time in minutes including roaming in last one month

VOI_INTERNAT_AIRTIME_LFTM

International voice air time in minutes including roaming over customer's life time

VOI_NAT_AIRTIME_LAST_3MO

National voice air time in minutes in last three month

VOI_NAT_AIRTIME_LAST_MO

National voice air time in minutes in last one month

VOI_NAT_AIRTIME_LFTM

National voice air time in minutes over customer's life time

WRLS_IND

Indicates whether Customer has Wireless Internet connection


Postpaid Churn Output Target Attribute: CHRN_IND

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

  • DWD_CUST_DNA.PRDCT_CHURN_SVM_IND

  • DWD_CUST_DNA.PRDCT_CHURN_SVM_PROB

  • DWD_CUST_DNA.PRDCT_CHURN_DT_IND

  • DWD_CUST_DNA.PRDCT_CHURN_DT_ND_NBR

Postpaid Churner Algorithm

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

  • Support Vector Machines (SVM)

  • Decision Tree

Postpaid Churner Algorithm Setting Tables

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

  • DM_STNG_CHURN_SVM - For Support Vector Machines algorithm

  • DM_STNG_CHURN_DT - For Decision Tree algorithm

Model 3: Customer Profiling

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

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

Customer Profiling Source

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

Table 10-11 DWV_CUST_PROFILE_SRC

Attribute Description

ACCPT_NWSLTR_IND

Indicates whether customer accepts News Letter

ACCT_TYP_CD

Account Type Code. For example, PSTPD - Postpaid, PRPD - Prepaid

ADDR_LOC_CD

Customer Address Location Code

AGE_BND_CD

Customer Age Band Code

AGE_ON_NET_BND_CD

Customer Age on Net Band Code

AGE_ON_NET_NBR

Customer Age on Net expressed in number of months

AGRMNT_CNT_LAST_3MO

Number of Agreements in last 3 months

AGRMNT_CNT_LAST_MO

Number of Agreements in last 1 month

AGRMNT_LFT_DAYS_LAST_3MO

Agreement Left Days in last 3 months

AGRMNT_LFT_DAYS_LAST_MO

Agreement Left Days in last 1 month

ARPU_BND_CD

Customer ARPU Band Code

AVG_DRTN_BTWN_RCHRG

Average duration between two recharges - in days

BARNG_RSN_CD

Customer Barring Reason Code

BLLG_ADDR_EFF_DT_NBR

Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BNKRPT_STAT

Bankrupt Status

BNKRPT_STRT_DT_NBR

Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BRDBND_IND

Indicates whether Customer has Broadband connection

BSNS_LEGAL_STAT_CD

Legal Status Code of Enterprise Customers. For example, Public, Private

CAR_DRVR_LICNS_IND

Indicates whether customer has Car Driving License

CAR_TYP_CD

Car Type Code

CITY

City

CMPLNT_CNT_LAST_3MO

Number of complaints made by customer in last 3 months

CMPLNT_CNT_LAST_MO

Number of complaints made by customer in this month

CMPLNT_CNT_LFTM

Number of complaints made by customer in his/her life span

CMPNY_EMP_SZ_BND_CD

Enterprise Customer Employee Size Band Code

CMPNY_RVN_BND_CD

Enterprise Customer Revenue Band Code

CMPNY_TYP_CD

Company Type Code

CNCT_ADDR_EFF_DT_NBR

Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

CNTCT_ALLWD_IND

Indicates whether customer allows to contact

CNTRY

Country

COLL_ZIP_CD

College ZIP Code

CRDT_CTGRY_KEY

Customer Credit Category

CUST_BRANCH_CD

Customer Branch Code

CUST_CD

Customer Identifier

CUST_PYMT_RESPBL_IND

Indicates whether customer is responsible for making payments

CUST_RVN_BND_CD

Customer Revenue Band Code

CUST_SCR_CD

Customer SCR Code

CUST_TYP_CD

Customer Type Code

DAYS_BFR_FIRST_RCHRG

Days between first payment and first recharge

DAYS_BFR_FIRST_USE

Days between payment and first use

DEBT_AGNG_BND_CD_LAST_3MO

Debt Ageing Band Code in last 3 months

DEBT_AGNG_BND_CD_LAST_MO

Debt Ageing Band Code in last 1 month

DEBT_VAL_LAST_3MO

Debt Value in last 3 months

DEBT_VAL_LAST_MO

Debt Value in last 1 month

DRPD_CALLS_CNT_LAST_3MO

Number of dropped calls in last 3 months

DRPD_CALLS_CNT_LAST_MO

Number of dropped calls this month

DRPD_CALLS_CNT_LFTM

Number of dropped calls in customer life span

DWLNG_OWNER

Dwelling Owner

DWLNG_STAT

Dwelling Status

DWLNG_SZ

Dwelling Size

DWLNG_TENR

Dwelling Tenure

DWLNG_TYP

Dwelling Type

DWNLD_DATA_LAST_3MO

Data downloaded in KBs in last 3 months

DWNLD_DATA_LAST_MO

Data downloaded in KBs in last 1 month

DWNLD_DATA_LFTM

Data downloaded in KBs in lifetime

ECNMCLY_ACTV_IND

Indicates whether a customer is economically Active

EDU_CD

Education Qualification Code

EFF_FROM_DT_NBR

Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

END_OF_JB_AGRMNT_NBR

End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

ESTMTD_ACQSTN_COST

Customer Acquisition Estimated Cost

ETHNCTY

Customer Ethnicity

ETHNIC_BCKGRND

Customer Ethnic Background

EXTRNL_ORG_TYP_CD

External Organization Type Code

FORM_OF_EMPMNT

Form of Employment

FUTRE_AGRMNT_CNT_LAST_3MO

Number of Future Agreements in last 3 months

FUTRE_AGRMNT_CNT_LAST_MO

Number of Future Agreements in last 1 month

FUTRE_AGRMNT_DAYS_LAST_3MO

Future Agreement Days in last 3 months

FUTRE_AGRMNT_DAYS_LAST_MO

Future Agreement Days in last 1 month

GNDR_CD

Individual Customer Gender Code

HH_SZ

Household Size

HNGUP_CALLS_CNT_LAST_3MO

Number of hangup calls in last 3 months

HNGUP_CALLS_CNT_LAST_MO

Number of hangup calls this month

HNGUP_CALLS_CNT_LFTM

Number of hangup calls in customer life span

HOMTEL_IND

Indicates whether Customer has Home Telephone

IDD_IND

Indicates whether Customer subscribed to International Direct Dialing

JB_AGRMNT_TYP

Job Agreement Type

JB_CD

Customer Job Code

JB_POSN

Customer Job Position

LANG_CD

Language Code

LAST_INCOMING_USG_DT_NBR

When was last incoming call occurred

LAST_OUTGOING_USG_DT_NBR

When was last outgoing call occurred

LAST_RCHRG_AMT

Last recharge amount

LEGAL_TTL_TO_HSNG

Legal Title to Housing

LIFE_SPN

Customer Life Span

LIKE_SCP_IN_FCBK_IND

Indicates whether customer likes Service Provide page in Facebook

LVNG_AT_CURR_ADDR_SINCE_NBR

Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

MAIL_ALWD_IND

Indicates whether Customer allows Service Provider to send mail

MAX_RCHRG_AMT

Maximum recharge amount

MIN_RCHRG_AMT

Minimum recharge amount

MMS_CNT_LAST_3MO

MMSs sent in last 3 months

MMS_CNT_LAST_MO

MMSs sent in last 1 month

MMS_CNT_LFTM

MMSs sent over customer's lifetime

MO_RVN_LAST_3MO

Monthly Revenue as of 3 months ago

MO_RVN_LAST_MO

Monthly Revenue as of 1 month ago

MRTL_STAT_CD

Individual Customer Marital Status Code

MULT_PRTY_ROLE_IND

Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider

NAME_OF_WKPLC

Name of Workplace

NAME_PRFX

Name Prefix

NBR_CHLDRN_AT_COLL

Total Number of Children at College

NBR_CHLDRN_AT_SCHL

Total Number of Children at School

NBR_CHLDRN_AT_UNIV

Total Number of Children at University

NBR_EMP_SNC_CUST

Numbers of Employees Since Customer

NBR_OF_CHLDRN

Total Number of Children

NBR_OF_DPNDNT

Total Number of Dependents

NEW_ACCT_IND

Indicates whether Customer is New

NO_MTHS_CMPNY

Number of months an Individual Customer is in his/her current Company

NO_MTHS_POSN

Number of months an Individual Customer is in his/her current Position

NTNLTY_CD

Customer Nationality Code

OFFNET_AIRTIME_LAST_3MO

Total offnet airtime in minutes in last 3 months

OFFNET_AIRTIME_LAST_MO

Total offnet airtime in minutes in last 1 month

OFFNET_AIRTIME_LFTM

Total offnet airtime in minutes in lifetime

OFFNET_CALLS_LAST_3MO

Number of offnet calls in last 3 months

OFFNET_CALLS_LAST_MO

Number of offnet calls in last 1 month

OFFNET_CALLS_LFTM

Number of offnet calls in lifetime

ONNET_AIRTIME_LAST_3MO

Total onnet airtime in minutes in last 3 months

ONNET_AIRTIME_LAST_MO

Total onnet airtime in minutes in last 1 month

ONNET_AIRTIME_LFTM

Total onnet airtime in minutes in lifetime

ONNET_CALLS_LAST_3MO

Number of onnet calls in last 3 months

ONNET_CALLS_LAST_MO

Number of onnet calls in last 1 month

ONNET_CALLS_LFTM

Number of onnet calls in lifetime

OTH_DRVR_LICNS_IND

Indicates whether customer has other vehicle License

PAY_TV_IND

Indicates whether Customer has Pay TV connection

PLC_OF_BRTH

Place of Birth

PORT_IN_CNT

Number of times the customer ported in

PORT_IN_FROM

Service Provide from who the customer ported out from

PORT_OUT_CNT

Number of times the customer ported out

POSTCD_CD

Postcard Code

PREF_CNTCT_MDM

Preferred Contact Method

PREF_ENTMNT_IND

Indicates whether customer given any Entitlement preference

PREF_GVN_IND

Indicates whether customer given any preference

PREF_MSC_IND

Indicates whether customer given any Music preference

PREF_PYMT_MTHD_TYP_CD

Preferred Payment Method Type Code

PREF_SPRT_IND

Indicates whether customer given any sport preference

PREF_TRVL_IND

Indicates whether customer given any Travel preference

PRMRY_STAT_CD

Customer Primary Status Code

PRMRY_STAT_RSN_CD

Customer Primary Status Reason Code

PRTY_AGE

Customer Age expressed as number of years

PRTY_TYP_CD

Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.

PYMT_ACCT_OPEN_DT_NBR

Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

RLSD_CALLS_CNT_LAST_3MO

Number of released calls in last 3 months

RLSD_CALLS_CNT_LAST_MO

Number of released calls this month

RLSD_CALLS_CNT_LFTM

Number of released calls in customer life span

RMRK_CNT_LAST_3MO

Numbers of remarks made against customer in last 3 months

RMRK_CNT_LAST_MO

Numbers of remarks made against customer in this month

RMRK_CNT_LFTM

Numbers of remarks made against customer in his/her life span

SCHL_ZIP_CD

School ZIP Code

SCL_NTWK_USR_IND

Indicates whether customer uses any Social Network site

SL_CHNL_KEY

Sales Channel

SL_CHNL_RPRSTV_KEY

Sales Channel Representative

SMS_CNT_LAST_3MO

SMSs sent in last 3 months

SMS_CNT_LAST_MO

SMSs sent in last 1 month

SMS_CNT_LFTM

SMSs sent over customer's lifetime

SOC_JB_CD

SOC Job Code

SPRT_IND

Indicates whether customer plays Sports

SPRT_TYP_CD

Sport Type Code

SRC_OF_INCM

Source of Income

STATE

State

STRT_OF_EMPMNT_NBR

Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

TOT_AGE_CHLDRN

Total Age of Children

TOT_CALL_CNT_LAST_3MO

Total number of calls to Call center made by customer in last 3 months

TOT_CALL_CNT_LAST_MO

Total number of calls to Call center made by customer in this month

TOT_CALL_CNT_LFTM

Total number of calls to Call center made by customer in his/her life span

TOT_CNTRBTN_LAST_MO

Sum of total payments and total recharges made in the last one month

TOT_CNTRBTN_LFTM

Sum of total payments and total recharges made in customers life time

TOT_DAYS_ACTVTY

Total days of activity

TOT_DAYS_ON_USE

Total days on use

TOT_DAYS_OUTGOING_ACTVTY

Total days of outgoing activity

TOT_DRTN_OF_USG

Total durations of usage in customer life time

TOT_EXP_AMT_EQVLT_VAL

Total amount expired in customers life time

TOT_HLD_DRTN_LAST_3MO

Total hold duration in the last 3 months in seconds

TOT_HLD_DRTN_LAST_MO

Total hold duration this month in seconds

TOT_HLD_DRTN_LFTM

Total hold duration in customer life span in seconds

TOT_PYMT_RVN_LAST_3MO

Total Payment Revenue as of 3 months ago

TOT_PYMT_RVN_LAST_MO

Total Payment Revenue as of 1 month ago

TOT_QUE_DRTN_LAST_3MO

Total queue duration in the last 3 months in seconds

TOT_QUE_DRTN_LAST_MO

Total queue duration this month in seconds

TOT_QUE_DRTN_LFTM

Total queue duration in customer life span in seconds

TOT_RCHRG_AMT_LAST_3MO

Total recharge amount this month

TOT_RCHRG_AMT_LAST_MO

Total recharge amount in last 3 months

TOT_RCHRG_AMT_LFTM

Total recharge amount in customers life time

TOT_RCHRG_CNT_LAST_3MO

Number of recharges made in last 3 months

TOT_RCHRG_CNT_LAST_MO

Number of recharges made this month

TOT_RCHRG_CNT_LFTM

Number of recharges made in customer life time

TOT_TALK_DRTN_LAST_3MO

Total talk duration in the last 3 months in seconds

TOT_TALK_DRTN_LAST_MO

Total talk duration this month in seconds

TOT_TALK_DRTN_LFTM

Total talk duration in customer life span in seconds

TOT_UTILZD_EQVLT_VAL

Total utilized monetary value in customers life time

UNIV_ZIP_CD

University ZIP Code

UPLD_DATA_LAST_3MO

Data uploaded in KBs in last 3 months

UPLD_DATA_LAST_MO

Data uploaded in KBs in last 1 month

UPLD_DATA_LFTM

Data uploaded in KBs in lifetime

VOI_INTERNAT_AIRTIME_LAST_3MO

International voice air time including roaming in minutes in last three month

VOI_INTERNAT_AIRTIME_LAST_MO

International voice air time in minutes including roaming in last one month

VOI_INTERNAT_AIRTIME_LFTM

International voice air time in minutes including roaming over customer's life time

VOI_NAT_AIRTIME_LAST_3MO

National voice air time in minutes in last three month

VOI_NAT_AIRTIME_LAST_MO

National voice air time in minutes in last one month

VOI_NAT_AIRTIME_LFTM

National voice air time in minutes over customer's life time

WRLS_IND

Indicates whether Customer has Wireless Internet connection


Customer Profile Output

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

  • DWD_CUST_DNA. CLSTR_SGMNT_CD

Customer Profile Algorithm

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

  • K-Means Clustering

Customer Profile Algorithm Setting Tables

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

  • DM_STNG_PROFILE_KMEANS - For K-Means Clustering

Model 4: Targeted Promotion

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

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

Targeted Promotion Source

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

Table 10-12 DWV_PROD_MIX_SRC

Attribute Description

ACCT_LFT_VAL_LAST_MO

Account Left Value in last 1 month

AGRMNT_ARPU_LAST_MO

Agreement ARPU as of 1 month ago

AGRMNT_CNT_LAST_MO

Number of Agreements in last 1 month

AGRMNT_LFT_DAYS_LAST_MO

Agreement Left Days in last 1 month

BARNG_RSN_CD

Customer Barring Reason Code

BSNS_LEGAL_STAT_CD

Legal Status Code of Enterprise Customers. For example Public, Private

CALL_BCK

Indicates whether Customer subscribed to Call Back

CALL_CNFRN

Indicates whether Customer subscribed to Call Conference

CALL_TRNSFR

Indicates whether Customer subscribed to Call Transfer

CB

Indicates whether Customer subscribed to CB

CF

Indicates whether Customer subscribed to Call Forward

CF_WHEN_BUSY

Indicates whether Customer subscribed to Call Forward When Busy

CF_WHEN_NO_RPLY

Indicates whether Customer subscribed to Call Forward When No Reply

CF_WHEN_NOT_RCHBLE

Indicates whether Customer subscribed to Call Forward When Not Reachable

CLI

Indicates whether Customer subscribed to Calling Line Identity

CLIR

Indicates whether Customer subscribed to Calling Line Identification Restriction

CMPLNT_CNT_LAST_MO

Number of complaints made by customer in this month

CMPLNT_CNT_LFTM

Number of complaints made by customer in his/her life span

CUST_CD

Customer Identifier

CW

Indicates whether Customer subscribed to Call Waiting

DEBT_VAL_LAST_MO

Debt Value in last 1 month

EDU_CD

Education Qualification Code

FAX

Indicates whether Customer subscribed to Fax

FUTRE_AGRMNT_CNT_LAST_MO

Number of Future Agreements in last 1 month

GNDR_CD

Individual Customer Gender Code

GPRS

Indicates whether Customer subscribed to GPRS

HH_SZ

Household Size

INTRNTL_CALL

Indicates whether Customer subscribed to International Call

JB_AGRMNT_TYP

Job Agreement Type

JB_CD

Customer Job Code

LFTM_ARPU_LAST_MO

Lifetime ARPU as of 1 month ago

LIFE_SPN

Customer Life Span

LYLTY_PROG_BAL_LAST_MO

Loyalty Program Balance in last 1 month

MMS

Indicates whether Customer subscribed to MMS

MO_RVN_LAST_MO

Monthly Revenue as of 1 month ago

MRTL_STAT_CD

Individual Customer Marital Status Code

NTNLTY_CD

Customer Nationality Code

PORT_IN_CNT

Number of times the customer ported in

PORT_IN_FROM

Service Provide from who the customer ported out from

PORT_OUT_CNT

Number of times the customer ported out

POSTCD_CD

Postcard Code

PRTY_TYP_CD

Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.

REMNG_AGRMNT_AMT_LAST_MO

Remaining Agreement Amount in last 1 month

SBRP_CNT_LAST_MO

Subscription Count in last three months

SMS

Indicates whether Customer subscribed to SMS

SSPNSN_CNT_LAST_MO

Suspension Count in last three months

STATE

State

TOT_PYMT_RVN_LAST_MO

Total Payment Revenue as of 1 month ago

WAP

Indicates whether Customer subscribed to WAP


Targeted Promotion Output

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

  • DWD_CUST_PROD_AFFLTN.AFFLTN_PROB

  • DWD_CUST_PROD_AFFLTN.BUY_IND

Targeted Promotion Algorithm

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

  • Support Vector Machines (SVM)

Targeted Promotion Algorithm Setting Tables

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

  • DM_STNG_CHURN_SVM - For Support Vector Machines

Model 5: Customer Life Time Value

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

Customer Life Time Value Source

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

Table 10-13 DWV_CUST_LTV_SRC

Attribute Description

ACCPT_NWSLTR_IND

Indicates whether customer accepts News Letter

ACCT_TYP_CD

Account Type Code. For example, PSTPD - Postpaid, PRPD - Prepaid

ADDR_LOC_CD

Customer Address Location Code

AGE_BND_CD

Customer Age Band Code

AGE_ON_NET_BND_CD

Customer Age on Net Band Code

AGE_ON_NET_NBR

Customer Age on Net expressed in number of months

AGRMNT_CNT_LAST_3MO

Number of Agreements in last 3 months

AGRMNT_CNT_LAST_MO

Number of Agreements in last 1 month

AGRMNT_LFT_DAYS_LAST_3MO

Agreement Left Days in last 3 months

AGRMNT_LFT_DAYS_LAST_MO

Agreement Left Days in last 1 month

ARPU_BND_CD

Customer ARPU Band Code

AVG_DRTN_BTWN_RCHRG

Average duration between two recharges - in days

BARNG_RSN_CD

Customer Barring Reason Code

BLLG_ADDR_EFF_DT_NBR

Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BNKRPT_STAT

Bankrupt Status

BNKRPT_STRT_DT_NBR

Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BRDBND_IND

Indicates whether Customer has Broadband connection

BSNS_LEGAL_STAT_CD

Legal Status Code of Enterprise Customers. For example, Public, Private

CAR_DRVR_LICNS_IND

Indicates whether customer has Car Driving License

CAR_TYP_CD

Car Type Code

CITY

City

CMPLNT_CNT_LAST_3MO

Number of complaints made by customer in last 3 months

CMPLNT_CNT_LAST_MO

Number of complaints made by customer in this month

CMPLNT_CNT_LFTM

Number of complaints made by customer in his/her life span

CMPNY_EMP_SZ_BND_CD

Enterprise Customer Employee Size Band Code

CMPNY_RVN_BND_CD

Enterprise Customer Revenue Band Code

CMPNY_TYP_CD

Company Type Code

CNCT_ADDR_EFF_DT_NBR

Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

CNTCT_ALLWD_IND

Indicates whether customer allows to contact

CNTRY

Country

COLL_ZIP_CD

College ZIP Code

CRDT_CTGRY_KEY

Customer Credit Category

CUST_BRANCH_CD

Customer Branch Code

CUST_CD

Customer Identifier

CUST_PYMT_RESPBL_IND

Indicates whether customer is responsible for making payments

CUST_RVN_BND_CD

Customer Revenue Band Code

CUST_SCR_CD

Customer SCR Code

CUST_TYP_CD

Customer Type Code

DAYS_BFR_FIRST_RCHRG

Days between first payment and first recharge

DAYS_BFR_FIRST_USE

Days between payment and first use

DEBT_AGNG_BND_CD_LAST_3MO

Debt Ageing Band Code in last 3 months

DEBT_AGNG_BND_CD_LAST_MO

Debt Ageing Band Code in last 1 month

DEBT_VAL_LAST_3MO

Debt Value in last 3 months

DEBT_VAL_LAST_MO

Debt Value in last 1 month

DRPD_CALLS_CNT_LAST_3MO

Number of dropped calls in last 3 months

DRPD_CALLS_CNT_LAST_MO

Number of dropped calls this month

DRPD_CALLS_CNT_LFTM

Number of dropped calls in customer life span

DWLNG_OWNER

Dwelling Owner

DWLNG_STAT

Dwelling Status

DWLNG_SZ

Dwelling Size

DWLNG_TENR

Dwelling Tenure

DWLNG_TYP

Dwelling Type

DWNLD_DATA_LAST_3MO

Data downloaded in KBs in last 3 months

DWNLD_DATA_LAST_MO

Data downloaded in KBs in last 1 month

DWNLD_DATA_LFTM

Data downloaded in KBs in lifetime

ECNMCLY_ACTV_IND

Indicates whether a customer is economically Active

EDU_CD

Education Qualification Code

EFF_FROM_DT_NBR

Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

END_OF_JB_AGRMNT_NBR

End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

ESTMTD_ACQSTN_COST

Customer Acquisition Estimated Cost

ETHNCTY

Customer Ethnicity

ETHNIC_BCKGRND

Customer Ethnic Background

EXTRNL_ORG_TYP_CD

External Organization Type Code

FORM_OF_EMPMNT

Form of Employment

FUTRE_AGRMNT_CNT_LAST_3MO

Number of Future Agreements in last 3 months

FUTRE_AGRMNT_CNT_LAST_MO

Number of Future Agreements in last 1 month

FUTRE_AGRMNT_DAYS_LAST_3MO

Future Agreement Days in last 3 months

FUTRE_AGRMNT_DAYS_LAST_MO

Future Agreement Days in last 1 month

GNDR_CD

Individual Customer Gender Code

HH_SZ

Household Size

HNGUP_CALLS_CNT_LAST_3MO

Number of hangup calls in last 3 months

HNGUP_CALLS_CNT_LAST_MO

Number of hangup calls this month

HNGUP_CALLS_CNT_LFTM

Number of hangup calls in customer life span

HOMTEL_IND

Indicates whether Customer has Home Telephone

IDD_IND

Indicates whether Customer subscribed to International Direct Dialing

JB_AGRMNT_TYP

Job Agreement Type

JB_CD

Customer Job Code

JB_POSN

Customer Job Position

LANG_CD

Language Code

LAST_INCOMING_USG_DT_NBR

When was last incoming call occurred

LAST_OUTGOING_USG_DT_NBR

When was last outgoing call occurred

LAST_RCHRG_AMT

Last recharge amount

LEGAL_TTL_TO_HSNG

Legal Title to Housing

LIFE_SPN

Customer Life Span

LIKE_SCP_IN_FCBK_IND

Indicates whether customer likes Service Provide page in Facebook

LVNG_AT_CURR_ADDR_SINCE_NBR

Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

MAIL_ALWD_IND

Indicates whether Customer allows Service Provider to send mail

MAX_RCHRG_AMT

Maximum recharge amount

MIN_RCHRG_AMT

Minimum recharge amount

MMS_CNT_LAST_3MO

MMSs sent in last 3 months

MMS_CNT_LAST_MO

MMSs sent in last 1 month

MMS_CNT_LFTM

MMSs sent over customer's lifetime

MO_RVN_LAST_3MO

Monthly Revenue as of 3 months ago

MO_RVN_LAST_MO

Monthly Revenue as of 1 month ago

MRTL_STAT_CD

Individual Customer Marital Status Code

MULT_PRTY_ROLE_IND

Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider

NAME_OF_WKPLC

Name of Workplace

NAME_PRFX

Name Prefix

NBR_CHLDRN_AT_COLL

Total Number of Children at College

NBR_CHLDRN_AT_SCHL

Total Number of Children at School

NBR_CHLDRN_AT_UNIV

Total Number of Children at University

NBR_EMP_SNC_CUST

Numbers of Employees Since Customer

NBR_OF_CHLDRN

Total Number of Children

NBR_OF_DPNDNT

Total Number of Dependents

NEW_ACCT_IND

Indicates whether Customer is New

NO_MTHS_CMPNY

Number of months an Individual Customer is in his/her current Company

NO_MTHS_POSN

Number of months an Individual Customer is in his/her current Position

NTNLTY_CD

Customer Nationality Code

OFFNET_AIRTIME_LAST_3MO

Total offnet airtime in minutes in last 3 months

OFFNET_AIRTIME_LAST_MO

Total offnet airtime in minutes in last 1 month

OFFNET_AIRTIME_LFTM

Total offnet airtime in minutes in lifetime

OFFNET_CALLS_LAST_3MO

Number of offnet calls in last 3 months

OFFNET_CALLS_LAST_MO

Number of offnet calls in last 1 month

OFFNET_CALLS_LFTM

Number of offnet calls in lifetime

ONNET_AIRTIME_LAST_3MO

Total onnet airtime in minutes in last 3 months

ONNET_AIRTIME_LAST_MO

Total onnet airtime in minutes in last 1 month

ONNET_AIRTIME_LFTM

Total onnet airtime in minutes in lifetime

ONNET_CALLS_LAST_3MO

Number of onnet calls in last 3 months

ONNET_CALLS_LAST_MO

Number of onnet calls in last 1 month

ONNET_CALLS_LFTM

Number of onnet calls in lifetime

OTH_DRVR_LICNS_IND

Indicates whether customer has other vehicle License

PAY_TV_IND

Indicates whether Customer has Pay TV connection

PLC_OF_BRTH

Place of Birth

PORT_IN_CNT

Number of times the customer ported in

PORT_IN_FROM

Service Provide from who the customer ported out from

PORT_OUT_CNT

Number of times the customer ported out

POSTCD_CD

Postcard Code

PREF_CNTCT_MDM

Preferred Contact Method

PREF_ENTMNT_IND

Indicates whether customer given any Entitlement preference

PREF_GVN_IND

Indicates whether customer given any preference

PREF_MSC_IND

Indicates whether customer given any Music preference

PREF_PYMT_MTHD_TYP_CD

Preferred Payment Method Type Code

PREF_SPRT_IND

Indicates whether customer given any sport preference

PREF_TRVL_IND

Indicates whether customer given any Travel preference

PRMRY_STAT_CD

Customer Primary Status Code

PRMRY_STAT_RSN_CD

Customer Primary Status Reason Code

PRTY_AGE

Customer Age expressed as number of years

PRTY_TYP_CD

Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.

PYMT_ACCT_OPEN_DT_NBR

Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

RLSD_CALLS_CNT_LAST_3MO

Number of released calls in last 3 months

RLSD_CALLS_CNT_LAST_MO

Number of released calls this month

RLSD_CALLS_CNT_LFTM

Number of released calls in customer life span

RMRK_CNT_LAST_3MO

Numbers of remarks made against customer in last 3 months

RMRK_CNT_LAST_MO

Numbers of remarks made against customer in this month

RMRK_CNT_LFTM

Numbers of remarks made against customer in his/her life span

SCHL_ZIP_CD

School ZIP Code

SCL_NTWK_USR_IND

Indicates whether customer uses any Social Network site

SL_CHNL_KEY

Sales Channel

SL_CHNL_RPRSTV_KEY

Sales Channel Representative

SMS_CNT_LAST_3MO

SMSs sent in last 3 months

SMS_CNT_LAST_MO

SMSs sent in last 1 month

SMS_CNT_LFTM

SMSs sent over customer's lifetime

SOC_JB_CD

SOC Job Code

SPRT_IND

Indicates whether customer plays Sports

SPRT_TYP_CD

Sport Type Code

SRC_OF_INCM

Source of Income

STATE

State

STRT_OF_EMPMNT_NBR

Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

TOT_AGE_CHLDRN

Total Age of Children

TOT_CALL_CNT_LAST_3MO

Total number of calls to Call center made by customer in last 3 months

TOT_CALL_CNT_LAST_MO

Total number of calls to Call center made by customer in this month

TOT_CALL_CNT_LFTM

Total number of calls to Call center made by customer in his/her life span

TOT_CNTRBTN_LAST_MO

Sum of total payments and total recharges made in the last one month

TOT_CNTRBTN_LFTM

Sum of total payments and total recharges made in customers life time

TOT_DAYS_ACTVTY

Total days of activity

TOT_DAYS_ON_USE

Total days on use

TOT_DAYS_OUTGOING_ACTVTY

Total days of outgoing activity

TOT_DRTN_OF_USG

Total durations of usage in customer life time

TOT_EXP_AMT_EQVLT_VAL

Total amount expired in customers life time

TOT_HLD_DRTN_LAST_3MO

Total hold duration in the last 3 months in seconds

TOT_HLD_DRTN_LAST_MO

Total hold duration this month in seconds

TOT_HLD_DRTN_LFTM

Total hold duration in customer life span in seconds

TOT_PYMT_RVN_LAST_3MO

Total Payment Revenue as of 3 months ago

TOT_PYMT_RVN_LAST_MO

Total Payment Revenue as of 1 month ago

TOT_QUE_DRTN_LAST_3MO

Total queue duration in the last 3 months in seconds

TOT_QUE_DRTN_LAST_MO

Total queue duration this month in seconds

TOT_QUE_DRTN_LFTM

Total queue duration in customer life span in seconds

TOT_RCHRG_AMT_LAST_3MO

Total recharge amount this month

TOT_RCHRG_AMT_LAST_MO

Total recharge amount in last 3 months

TOT_RCHRG_AMT_LFTM

Total recharge amount in customers life time

TOT_RCHRG_CNT_LAST_3MO

Number of recharges made in last 3 months

TOT_RCHRG_CNT_LAST_MO

Number of recharges made this month

TOT_RCHRG_CNT_LFTM

Number of recharges made in customer life time

TOT_TALK_DRTN_LAST_3MO

Total talk duration in the last 3 months in seconds

TOT_TALK_DRTN_LAST_MO

Total talk duration this month in seconds

TOT_TALK_DRTN_LFTM

Total talk duration in customer life span in seconds

TOT_UTILZD_EQVLT_VAL

Total utilized monetary value in customers life time

UNIV_ZIP_CD

University ZIP Code

UPLD_DATA_LAST_3MO

Data uploaded in KBs in last 3 months

UPLD_DATA_LAST_MO

Data uploaded in KBs in last 1 month

UPLD_DATA_LFTM

Data uploaded in KBs in lifetime

VOI_INTERNAT_AIRTIME_LAST_3MO

International voice air time including roaming in minutes in last three month

VOI_INTERNAT_AIRTIME_LAST_MO

International voice air time in minutes including roaming in last one month

VOI_INTERNAT_AIRTIME_LFTM

International voice air time in minutes including roaming over customer's life time

VOI_NAT_AIRTIME_LAST_3MO

National voice air time in minutes in last three month

VOI_NAT_AIRTIME_LAST_MO

National voice air time in minutes in last one month

VOI_NAT_AIRTIME_LFTM

National voice air time in minutes over customer's life time

WRLS_IND

Indicates whether Customer has Wireless Internet connection


Customer Life Time Value Output Target Attribute: TOT_PYMT_RVN_LAST_MO

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

  • DWD_CUST_DNA.PRDCT_LTV_VALUE

Customer Life Time Value Algorithms

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

  • Generalized Linear Model Regression

Customer Life Time Value Algorithm Setting Tables

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

  • DM_STNG_LTV_GLMR - For Generalized Linear Model Regression

Model 6: Customer Life Time Survival Value

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

Customer Life Time Value (LTV) Survival Source

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

Table 10-14 DWV_CUST_LTV_SRC

Attribute Description

ACCPT_NWSLTR_IND

Indicates whether customer accepts News Letter

ACCT_TYP_CD

Account Type Code. For example, PSTPD - Postpaid, PRPD - Prepaid

ADDR_LOC_CD

Customer Address Location Code

AGE_BND_CD

Customer Age Band Code

AGE_ON_NET_BND_CD

Customer Age on Net Band Code

AGE_ON_NET_NBR

Customer Age on Net expressed in number of months

AGRMNT_CNT_LAST_3MO

Number of Agreements in last 3 months

AGRMNT_CNT_LAST_MO

Number of Agreements in last 1 month

AGRMNT_LFT_DAYS_LAST_3MO

Agreement Left Days in last 3 months

AGRMNT_LFT_DAYS_LAST_MO

Agreement Left Days in last 1 month

ARPU_BND_CD

Customer ARPU Band Code

AVG_DRTN_BTWN_RCHRG

Average duration between two recharges - in days

BARNG_RSN_CD

Customer Barring Reason Code

BLLG_ADDR_EFF_DT_NBR

Building Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BNKRPT_STAT

Bankrupt Status

BNKRPT_STRT_DT_NBR

Bankrupt Start Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

BRDBND_IND

Indicates whether Customer has Broadband connection

BSNS_LEGAL_STAT_CD

Legal Status Code of Enterprise Customers. For example, Public, Private

CAR_DRVR_LICNS_IND

Indicates whether customer has Car Driving License

CAR_TYP_CD

Car Type Code

CITY

City

CMPLNT_CNT_LAST_3MO

Number of complaints made by customer in last 3 months

CMPLNT_CNT_LAST_MO

Number of complaints made by customer in this month

CMPLNT_CNT_LFTM

Number of complaints made by customer in his/her life span

CMPNY_EMP_SZ_BND_CD

Enterprise Customer Employee Size Band Code

CMPNY_RVN_BND_CD

Enterprise Customer Revenue Band Code

CMPNY_TYP_CD

Company Type Code

CNCT_ADDR_EFF_DT_NBR

Customer Contact Address Effective Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

CNTCT_ALLWD_IND

Indicates whether customer allows to contact

CNTRY

Country

COLL_ZIP_CD

College ZIP Code

CRDT_CTGRY_KEY

Customer Credit Category

CUST_BRANCH_CD

Customer Branch Code

CUST_CD

Customer Identifier

CUST_PYMT_RESPBL_IND

Indicates whether customer is responsible for making payments

CUST_RVN_BND_CD

Customer Revenue Band Code

CUST_SCR_CD

Customer SCR Code

CUST_TYP_CD

Customer Type Code

DAYS_BFR_FIRST_RCHRG

Days between first payment and first recharge

DAYS_BFR_FIRST_USE

Days between payment and first use

DEBT_AGNG_BND_CD_LAST_3MO

Debt Ageing Band Code in last 3 months

DEBT_AGNG_BND_CD_LAST_MO

Debt Ageing Band Code in last 1 month

DEBT_VAL_LAST_3MO

Debt Value in last 3 months

DEBT_VAL_LAST_MO

Debt Value in last 1 month

DRPD_CALLS_CNT_LAST_3MO

Number of dropped calls in last 3 months

DRPD_CALLS_CNT_LAST_MO

Number of dropped calls this month

DRPD_CALLS_CNT_LFTM

Number of dropped calls in customer life span

DWLNG_OWNER

Dwelling Owner

DWLNG_STAT

Dwelling Status

DWLNG_SZ

Dwelling Size

DWLNG_TENR

Dwelling Tenure

DWLNG_TYP

Dwelling Type

DWNLD_DATA_LAST_3MO

Data downloaded in KBs in last 3 months

DWNLD_DATA_LAST_MO

Data downloaded in KBs in last 1 month

DWNLD_DATA_LFTM

Data downloaded in KBs in lifetime

ECNMCLY_ACTV_IND

Indicates whether a customer is economically Active

EDU_CD

Education Qualification Code

EFF_FROM_DT_NBR

Customer Effective From Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

END_OF_JB_AGRMNT_NBR

End of Job Agreement expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

ESTMTD_ACQSTN_COST

Customer Acquisition Estimated Cost

ETHNCTY

Customer Ethnicity

ETHNIC_BCKGRND

Customer Ethnic Background

EXTRNL_ORG_TYP_CD

External Organization Type Code

FORM_OF_EMPMNT

Form of Employment

FUTRE_AGRMNT_CNT_LAST_3MO

Number of Future Agreements in last 3 months

FUTRE_AGRMNT_CNT_LAST_MO

Number of Future Agreements in last 1 month

FUTRE_AGRMNT_DAYS_LAST_3MO

Future Agreement Days in last 3 months

FUTRE_AGRMNT_DAYS_LAST_MO

Future Agreement Days in last 1 month

GNDR_CD

Individual Customer Gender Code

HH_SZ

Household Size

HNGUP_CALLS_CNT_LAST_3MO

Number of hangup calls in last 3 months

HNGUP_CALLS_CNT_LAST_MO

Number of hangup calls this month

HNGUP_CALLS_CNT_LFTM

Number of hangup calls in customer life span

HOMTEL_IND

Indicates whether Customer has Home Telephone

IDD_IND

Indicates whether Customer subscribed to International Direct Dialing

JB_AGRMNT_TYP

Job Agreement Type

JB_CD

Customer Job Code

JB_POSN

Customer Job Position

LANG_CD

Language Code

LAST_INCOMING_USG_DT_NBR

When was last incoming call occurred

LAST_OUTGOING_USG_DT_NBR

When was last outgoing call occurred

LAST_RCHRG_AMT

Last recharge amount

LEGAL_TTL_TO_HSNG

Legal Title to Housing

LIFE_SPN

Customer Life Span

LIKE_SCP_IN_FCBK_IND

Indicates whether customer likes Service Provide page in Facebook

LVNG_AT_CURR_ADDR_SINCE_NBR

Since When Customer Living at Current Address expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

MAIL_ALWD_IND

Indicates whether Customer allows Service Provider to send mail

MAX_RCHRG_AMT

Maximum recharge amount

MIN_RCHRG_AMT

Minimum recharge amount

MMS_CNT_LAST_3MO

MMSs sent in last 3 months

MMS_CNT_LAST_MO

MMSs sent in last 1 month

MMS_CNT_LFTM

MMSs sent over customer's lifetime

MO_RVN_LAST_3MO

Monthly Revenue as of 3 months ago

MO_RVN_LAST_MO

Monthly Revenue as of 1 month ago

MRTL_STAT_CD

Individual Customer Marital Status Code

MULT_PRTY_ROLE_IND

Indicates whether customer has multiple party roles. For example, both an employee and customer of Service Provider

NAME_OF_WKPLC

Name of Workplace

NAME_PRFX

Name Prefix

NBR_CHLDRN_AT_COLL

Total Number of Children at College

NBR_CHLDRN_AT_SCHL

Total Number of Children at School

NBR_CHLDRN_AT_UNIV

Total Number of Children at University

NBR_EMP_SNC_CUST

Numbers of Employees Since Customer

NBR_OF_CHLDRN

Total Number of Children

NBR_OF_DPNDNT

Total Number of Dependents

NEW_ACCT_IND

Indicates whether Customer is New

NO_MTHS_CMPNY

Number of months an Individual Customer is in his/her current Company

NO_MTHS_POSN

Number of months an Individual Customer is in his/her current Position

NTNLTY_CD

Customer Nationality Code

OFFNET_AIRTIME_LAST_3MO

Total offnet airtime in minutes in last 3 months

OFFNET_AIRTIME_LAST_MO

Total offnet airtime in minutes in last 1 month

OFFNET_AIRTIME_LFTM

Total offnet airtime in minutes in lifetime

OFFNET_CALLS_LAST_3MO

Number of offnet calls in last 3 months

OFFNET_CALLS_LAST_MO

Number of offnet calls in last 1 month

OFFNET_CALLS_LFTM

Number of offnet calls in lifetime

ONNET_AIRTIME_LAST_3MO

Total onnet airtime in minutes in last 3 months

ONNET_AIRTIME_LAST_MO

Total onnet airtime in minutes in last 1 month

ONNET_AIRTIME_LFTM

Total onnet airtime in minutes in lifetime

ONNET_CALLS_LAST_3MO

Number of onnet calls in last 3 months

ONNET_CALLS_LAST_MO

Number of onnet calls in last 1 month

ONNET_CALLS_LFTM

Number of onnet calls in lifetime

OTH_DRVR_LICNS_IND

Indicates whether customer has other vehicle License

PAY_TV_IND

Indicates whether Customer has Pay TV connection

PLC_OF_BRTH

Place of Birth

PORT_IN_CNT

Number of times the customer ported in

PORT_IN_FROM

Service Provide from who the customer ported out from

PORT_OUT_CNT

Number of times the customer ported out

POSTCD_CD

Postcard Code

PREF_CNTCT_MDM

Preferred Contact Method

PREF_ENTMNT_IND

Indicates whether customer given any Entitlement preference

PREF_GVN_IND

Indicates whether customer given any preference

PREF_MSC_IND

Indicates whether customer given any Music preference

PREF_PYMT_MTHD_TYP_CD

Preferred Payment Method Type Code

PREF_SPRT_IND

Indicates whether customer given any sport preference

PREF_TRVL_IND

Indicates whether customer given any Travel preference

PRMRY_STAT_CD

Customer Primary Status Code

PRMRY_STAT_RSN_CD

Customer Primary Status Reason Code

PRTY_AGE

Customer Age expressed as number of years

PRTY_TYP_CD

Party Type Code. For example, Individual, Large Enterprise, Medium Enterprise, and so on.

PYMT_ACCT_OPEN_DT_NBR

Payment Account Open Date expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

RLSD_CALLS_CNT_LAST_3MO

Number of released calls in last 3 months

RLSD_CALLS_CNT_LAST_MO

Number of released calls this month

RLSD_CALLS_CNT_LFTM

Number of released calls in customer life span

RMRK_CNT_LAST_3MO

Numbers of remarks made against customer in last 3 months

RMRK_CNT_LAST_MO

Numbers of remarks made against customer in this month

RMRK_CNT_LFTM

Numbers of remarks made against customer in his/her life span

SCHL_ZIP_CD

School ZIP Code

SCL_NTWK_USR_IND

Indicates whether customer uses any Social Network site

SL_CHNL_KEY

Sales Channel

SL_CHNL_RPRSTV_KEY

Sales Channel Representative

SMS_CNT_LAST_3MO

SMSs sent in last 3 months

SMS_CNT_LAST_MO

SMSs sent in last 1 month

SMS_CNT_LFTM

SMSs sent over customer's lifetime

SOC_JB_CD

SOC Job Code

SPRT_IND

Indicates whether customer plays Sports

SPRT_TYP_CD

Sport Type Code

SRC_OF_INCM

Source of Income

STATE

State

STRT_OF_EMPMNT_NBR

Customer Start of Employment expressed as number against a reference date, 1st January 2000. Oracle Data Mining does not accept DATE data type attributes, hence expressed as number.

TOT_AGE_CHLDRN

Total Age of Children

TOT_CALL_CNT_LAST_3MO

Total number of calls to Call center made by customer in last 3 months

TOT_CALL_CNT_LAST_MO

Total number of calls to Call center made by customer in this month

TOT_CALL_CNT_LFTM

Total number of calls to Call center made by customer in his/her life span

TOT_CNTRBTN_LAST_MO

Sum of total payments and total recharges made in the last one month

TOT_CNTRBTN_LFTM

Sum of total payments and total recharges made in customers life time

TOT_DAYS_ACTVTY

Total days of activity

TOT_DAYS_ON_USE

Total days on use

TOT_DAYS_OUTGOING_ACTVTY

Total days of outgoing activity

TOT_DRTN_OF_USG

Total durations of usage in customer life time

TOT_EXP_AMT_EQVLT_VAL

Total amount expired in customers life time

TOT_HLD_DRTN_LAST_3MO

Total hold duration in the last 3 months in seconds

TOT_HLD_DRTN_LAST_MO

Total hold duration this month in seconds

TOT_HLD_DRTN_LFTM

Total hold duration in customer life span in seconds

TOT_PYMT_RVN_LAST_3MO

Total Payment Revenue as of 3 months ago

TOT_PYMT_RVN_LAST_MO

Total Payment Revenue as of 1 month ago

TOT_QUE_DRTN_LAST_3MO

Total queue duration in the last 3 months in seconds

TOT_QUE_DRTN_LAST_MO

Total queue duration this month in seconds

TOT_QUE_DRTN_LFTM

Total queue duration in customer life span in seconds

TOT_RCHRG_AMT_LAST_3MO

Total recharge amount this month

TOT_RCHRG_AMT_LAST_MO

Total recharge amount in last 3 months

TOT_RCHRG_AMT_LFTM

Total recharge amount in customers life time

TOT_RCHRG_CNT_LAST_3MO

Number of recharges made in last 3 months

TOT_RCHRG_CNT_LAST_MO

Number of recharges made this month

TOT_RCHRG_CNT_LFTM

Number of recharges made in customer life time

TOT_TALK_DRTN_LAST_3MO

Total talk duration in the last 3 months in seconds

TOT_TALK_DRTN_LAST_MO

Total talk duration this month in seconds

TOT_TALK_DRTN_LFTM

Total talk duration in customer life span in seconds

TOT_UTILZD_EQVLT_VAL

Total utilized monetary value in customers life time

UNIV_ZIP_CD

University ZIP Code

UPLD_DATA_LAST_3MO

Data uploaded in KBs in last 3 months

UPLD_DATA_LAST_MO

Data uploaded in KBs in last 1 month

UPLD_DATA_LFTM

Data uploaded in KBs in lifetime

VOI_INTERNAT_AIRTIME_LAST_3MO

International voice air time including roaming in minutes in last three month

VOI_INTERNAT_AIRTIME_LAST_MO

International voice air time in minutes including roaming in last one month

VOI_INTERNAT_AIRTIME_LFTM

International voice air time in minutes including roaming over customer's life time

VOI_NAT_AIRTIME_LAST_3MO

National voice air time in minutes in last three month

VOI_NAT_AIRTIME_LAST_MO

National voice air time in minutes in last one month

VOI_NAT_AIRTIME_LFTM

National voice air time in minutes over customer's life time

WRLS_IND

Indicates whether Customer has Wireless Internet connection


Customer Life Time Value Survival Output Target Attribute: AGE_ON_NET_NBR

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

  • DWD_CUST_DNA.PRDCT_LT_SRVVL_VAL

Customer Life Time Value Survival Algorithms

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

  • Generalized Linear Model Regression

Customer Life Time Value Survival Algorithm Setting Tables

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

  • DM_STNG_LTV_GLMR - For Generalized Linear Model Regression

Model 7: Customer Sentiment

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

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

Customer Sentiment Source

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

Table 10-15 shows DM_CUST_CMMNT attributes.

Table 10-15 DM_CUST_CMMNT Customer Sentiment Source Table

Attribute Data Type Description

CUST_CMMNT

VARCHAR2(4000)

The text messages all together from the customer.

CUST_KEY

NUMBER(30)

Customer Key

MANUAL_SCORE

VARCHAR2(40)

Manual scores or manually adjusted after reading

MINUSPROB

NUMBER(20,16)

The probability of customer belonging to un-happy group

PLUSPROB

NUMBER(20,16)

The probability of customer belonging to happy group

SENTIMENT

VARCHAR2(40)

Sentiment scored by Mining Model


Customer Sentiment Output

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

  • DWD_CUST_DNA.SNTMNT_CTGRY_CD

  • DWD_CUST_DNA.MANUAL_SNTMNT_CTGRY

  • DWD_CUST_DNA. SNTMNT_PROB

Customer Sentiment Algorithm

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

  • Oracle Text

  • Support Vector Machines

Customer Sentiment Algorithm Setting Tables

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

  • DM_STNG_SENTIMENT_SVM - For Generalized Linear Model Regression

Oracle Communications Data Model Mining Setting Tables

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

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

  • DM_STNG_USER_ALL

  • DM_STNG_SENTIMENT_SVM

  • DM_STNG_LTV_GLMR

  • DM_STNG_CHURN_SVM

  • DM_STNG_CHURN_DT

  • DM_STNG_PROFILE_KMEANS

  • DM_STNG_ATRIB_IMPORTANCE

Table 10-16 Data Mining Algorithm Setting Table Structure

Name Data Type Description

SETTING_NAME

VARCHAR2(500)

Setting Name

SETTING_VALUE

VARCHAR2(500)

Setting Value


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

Table 10-17 DM_STNG_CHURN_SVM_PRIORS Data Mining Algorithm Setting Table

Name Data Type Description

TARGET_VALUE

NUMBER

Target Value. For SVM Churn model, target value is either 1 or 0.

PRIOR_PROBABILITY

NUMBER

Prior probability of the target value.


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

Table 10-18 DM_STNG_CHURN_DT_COST Data Mining Algorithm Setting Table

Name Data Type Description

ACTUAL_TARGET_VALUE

NUMBER

Actual Target Value. For DT Churn model, target value is either 1 or 0.

PREDICTED_TARGET_VALUE

NUMBER

Target Value predicted by DT Churn model. Predicted target value is either 1 or 0.

COST

NUMBER

Cost of misclassification


Oracle Communications Data Model Data Mining Related Tables

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

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

Table 10-19 DWD_CUST_SNTMNT_MANUAL_SCORE Data Mining Source Table

Name Data Type Description

SENTIMENT_EXPRSN

VARCHAR2(100)

Sentiment expression that customer give in e-mail or on phone.

SENTIMENT_SCORE

VARCHAR2(30)

Sentiment score for the expression. It is "-" or "+"


Table 10-20 DWL_MNNG_CHRN_TYP Data Mining Lookup Table

Name Data Type Description

LANG_CD

VARCHAR2(30)

Language Code

CHRN_TYP_CD

VARCHAR2(120)

Churn Type Code. It is 1 or 0.

CHRN_TYP_NAME

VARCHAR2(200)

Churn Type Name. It is "Churner" or "Non-Churner"

CHRN_TYP_DSCR

VARCHAR2(400)

Churn Type Description


Table 10-21 DWL_MNNG_SNTMNT_CTGRY Data Mining Lookup Table

Name Data Type Description

LANG_CD

VARCHAR2(30)

Language Code

SNTMNT_CTGRY_CD

VARCHAR2(30)

Sentiment Category Code. For e.g. Angry, Satisfied

SNTMNT_CTGRY_NAME

VARCHAR2(50)

Sentiment Category Name

SNTMNT_CTGRY_DSCR

VARCHAR2(500)

Sentiment Category Description


Table 10-22 DWL_MNNG_LTV_BAND Data Mining Result Table

Name Data Type Description

LANG_CD

VARCHAR2(30)

Language Code

LTV_BAND_CD

VARCHAR2(30)

Life Time Value Band Code

LTV_BAND_NAME

VARCHAR2(50)

Life Time Value Band Name

LTV_BAND_DSCR

VARCHAR2(500)

Life Time Value Band Description


Table 10-23 DWL_MNNG_LT_SRVVL_BAND Data Mining Result Table

Name Data Type Description

LANG_CD

VARCHAR2(30)

Language Code

LT_SRVVL_BAND_CD

VARCHAR2(30)

Life Time Survival Value Band Code

LT_SRVVL_BAND_NAME

VARCHAR2(50)

Life Time Survival Value Band Name

LT_SRVVL_BAND_DSCR

VARCHAR2(500)

Life Time Survival Value Band Description


Table 10-24 Sequences Defined for Data Mining

Table Name Sequence Name

DWR_CUST_SGMNT

CUST_SGMNT_SEQ


Data Mining Package Customer Life Time Value Computation

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

  • PKG_OCDM_MINING.COMPUTE_PRPD_CUST_LTV

  • PKG_OCDM_MINING.COMPUTE_PSTPD_CUST_LTV

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

Prepaid Customer Life Time Value Computation

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

Table 10-25 Prepaid Customer Life Time Value Computation

Parameter Description

PREPAID_MARGIN_RATE (m)

% Profit margin of all prepaid customers

PREPAID_HISTORY_MONTHS (h)

Historical data to calculate Average Monthly Recharge Amount for each customer

ANNUAL_DISCOUNT_RATE (i)

Specifies the interest rate used in discounted cash flow analysis to determine the present value of cash flows. Usually it falls between 8% and 15%

CUST_LIFETIME_VALUE_COMPUATION_TIME_MONTHS (T)

Specifies for how long the Service Provider wants to calculate the Life Time Value


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

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

Parameter Description

RETENTION RATE (r)

1- the Predicted Churn Probability. The Predicted Churn Probability is retrieved from the DWD_CUST_DNA table.

AVERAGE MONTHLY RECHARGE AMOUNT or REVENUE (R)

Ratio of "Sum of all recharges in last PREPAID_HISTORY_MONTHS" to "PREPAID_HISTORY_MONTHS"


Prepaid Customer Life Time Value Calculation

Prepaid Customer Life Time Value =

Description of cdmrf_dm1.png follows
Description of the illustration cdmrf_dm1.png

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

  • DWD_CUST_DNA. CMPTD_LTV_VALUE

Postpaid Customer Life Time Value Computation

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

Table 10-27 Postpaid Customer Life Time Value Computation

Parameter Description

POSTPAID_MARGIN_RATE (m)

% Profit margin of all postpaid customers

POSTPAID_HISTORY_MONTHS (h)

Specifies the historical data to calculate Average Monthly Billed Amount for each customer

ANNUAL_DISCOUNT_RATE (i)

Specifies the interest rate used in discounted cash flow analysis to determine the present value of cash flows. Usually it falls between 8% and 15%

CUST_LIFETIME_VALUE_COMPUATION_TIME_MONTHS (T)

Specifies how long the Service Provider wants to calculate the Life Time Value


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

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

Parameter Description

RETENTION RATE (r)

1- the Predicted Churn Probability. The Predicted Churn Probability is retrieved from the DWD_CUST_DNA table.

AVERAGE MONTHLY BILLED AMOUNT or REVENUE (R)

Ratio of "Sum of all Billed amounts in last POSTPAID_HISTORY_MONTHS" to "POSTPAID_HISTORY_MONTHS"


Postpaid Customer Life Time Value Calculation

Postpaid Customer Life Time Value =

Description of cdmrf_dm2.png follows
Description of the illustration cdmrf_dm2.png

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

  • DWD_CUST_DNA. CMPTD_LTV_VALUE