More on How to Find the Most Important Variables for a Predictive Model

Problem

To make a predictive model, we need independent variables as inputs and a single variable as the target. Typically, both independent and target variables are stored in a single table. Often there are many independent variables, say 50 or 200 of them, such as age, sex, annual income, credit limits, and transaction variables etc. How do we select a small number of variables that are most predicative of the target variable and use them to build a model that is robust?

Solution

In the early post Find the Most Important Variables In Predictive Models, we described that there is a drawback to justify the importance of variable individually. Ideally we should take a set of variables as a whole into consideration. One of the good approaches is Oracle’s Attribute Importance model.

I built a credit card transaction data that contains column is_fraud as the target, id as the unique record identifier and other independent variables that I want to analyze. The data set is v_training_set. I write the following PL/SQL script to build an attribute importance model.

begin
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'VAR_IMPORTANCE',
mining_function => DBMS_DATA_MINING.ATTRIBUTE_IMPORTANCE,
data_table_name => 'v_training_set',
case_id_column_name => 'id',
target_column_name => 'is_fraud');
END;

When the attribute importance model,VAR_IMPORTANCE, is done, all independent, i.e., all variables except target and record identifiers, are assigned an importance value. The higher the value of a variable, the more important it is in predicting the target. We can review our result using the following SQL. (I deliberately masked the independent variable names because I think fraud detection is an sensitive matter and we do not want to give away too much information to fraudsters who might be reading this blog post.)

SQL> select attribute_name, IMPORTANCE_VALUE, rank from TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('VAR_IMPORTANCE')) order by rank;

ATTRIBUTE_NAME	IMPORTANCE_VALUE	RANK
VAR__EMV___	.024118198	1
VAR__TER___	.014412195	2
VAR__RET___	.013569008	3
VAR__PT____	.008679484	4
VAR__TRA___	.008009003	5
VAR__TER___	.007207152	6
VAR__CHK___	.006322795	7
VAR__NRT___	.00591138	8
VAR__NRT___	.005564262	9
VAR__MSG___	.005332518	10
VAR__TRA___	.004457798	11
VAR__NRT___	.00409855	12
VAR__PIN___	.003852347	13
VAR__PRO___	.001177829	14
VAR__RES___	.000911889	15
VAR__TER___	.000767663	16
VAR__FIL___	.000448031	17
VAR__ACC___	.000172331	18
VAR__PRM___	.00009502	19
VAR__AUT___	.00009502	19
VAR__FRW___	.00009502	19
VAR__AUT___	.000092784	20
VAR__ACC___	.000012229	21
VAR__PT____	.000003078	22
VAR__ACC___	-6.946E-06	23
VAR__TRA___	-.02799773	24
VAR__TER___	-.16968261	25
VAR__TRA___	-.39228472	26
VAR__TIE___	-.57372309	27

Conclusion

Oracle’s Attribute Importance function ranks variables based on their importance in predicting the target. It is a great tool for selecting a small number of input variables out of many before we build a predictive model.

My Check Fraud Detection Model Reduces Fraud by 60%

A project manager from a bank just told me that the model developed by me has resulted in 60% reduction in fraud loss. He said "everyone was surprised at how effective it was." It was a model that I developed two years ago for a top 15 bank.(Of course, I was not surprised at all since the model was tested on holdout data sets and showed similar performance).

The model was built and deployed on Oracle databases. The main reason for the success of the model was that I spent huge amount of effort building model variables that captures the fraud patterns. Luckily, I used Oracle analytic functions to build those variables easily. (Please see my posts "How to build predictive models that win competitions" and "Recency, Frequency, Monetary (RFM) Analysis: Part 1"

Another important advantage of using an uniform platform, i.e., Oracle databases, is that the deployment is easy. I simply deployed the model as a set of SQL query. See my posts "Build Predictive Models Using PL/SQL" and "Logistic Regression Model Implemented in SQL"

Graphical User Interface Data Mining Tools

Many data miners are aware of graphical user interface data mining tools such as Enterprise Miner from SAS and SPSS Clementine. I have found that the GUI-based data mining tool in Oracle Advanced Analytics is great. With Oracle's data mining tool, everything is stored in the database which give me a peace of mind knowing that my data and processes are secure. The following is the flow that I built last year to show how to train and deploy a cell phone customer acquisition model.

Build a Scorecard in Oracle Using Logistic Regression Model

One of the popular methods in assessing credit risk is a scorecard. To build a scorecard manually, analysts first identify a number of relevant variables, e.g. having bankruptcies in the past, number of open accounts, etc. Each variable is divided into meaningful segments based on its value. Then a point is assigned to each segment. The credit score is the sum of the points for all the variables. For example, a scorecard may look like the following:

Variable | Segment | Point
Having Bankruptcy | NO | 95
Having Bankruptcy | YES | 15
Number of Open Accounts | from 1 to 3 | 30
Number of Open Accounts | from 4 to 8 | 50
Number of Open Accounts | 9 or more | 40
...................................................

Assume a person's credit history shows past bankruptcy, 4 open accounts and other information. His credit score will be calculated as 15+50.....

To determine the points manually is subjective. A much better way is to build a logistic regression model and use the model coefficients as the "points". A scorecard created this way is accurate and yet still provides the transparency of a scorecard. One of the top cell phone service provider actually used our model-derived scorecard to screen the new customers.

In example 2 of the post Build Predictive Models Using PL/SQL, we show how to build a logistic regression model in Oracle. The extract the model coefficients, we simply use the following query:

select attribute_name, attribute_value, coefficient from table(select dbms_data_mining.get_model_details_glm('GLM_MODEL') from dual);
(Replacing 'GLM_MODEL' with real model name)

We may want to perform some transformation of the coefficients so that the score will be within the desired range.

From Oracle SQL Developer to Data Miner

As mentioned in an earlier post How easy is it for a SQL developer/analyst to become a data miner?, if we consider data mining as just more sophisticated SQL queries, data mining is simply a natural extension of SQL programmer/analyst's daily job, i.e., querying the database. For example, a SQL programmer can write a query to answer a business question: finding people living in Boston, MA with annual income at least $50K and credit score 750 or higher. One simply puts the three conditions in the "where clause" as the following:

select person_id, person_first_name, person_last_name
from tbl_data_about_people
where
home_city='Boston' and home_state='MA' and
annual_income>50000 and
credit_score >=750;

Similarly, we can put a predictive function in the "where clause" to answer a more useful question: finding people living in Boston, MA who are 25% more likely to purchase a new car with 2 months as the following:
select person_id, person_first_name, person_last_name
from tbl_data_about_people
where
home_city='Boston' and home_state='MA' and
prediction_probability(model_likely_to_buy, 1 using *)>=0.25;

We can see how easy and powerful to use predictive models in SQL. A traditional Oracle SQL developer can greatly enhance his/her value by picking up those data mining functions. There are a number of good documents and sample codes available at Oracle's site, e.g,
The Data Mining Sample Programs
Oracle® Data Mining Concepts

In-Database Data Mining

In previous post "Data Mining Components", we talk about four components or layers in a using data mining to solve business problems, e.g., fraud prevention, new customer life time value prediction, online ads click through rate, etc. Two abstract components are business problems and data mining algorithms. By abstract, we mean their existence is largely mental. They do not have physical forms and can not be purchased. Two physical components are data mining software and data management tools. Data mining software are the implementation of data mining algorithms. Data management tools could be relational databases or plain files.   It is important to realize that items within each layer are sometimes exchangeable. We can select the combination of data mining models, tools and databases that suit our needs.  In these years, we have found that performing data mining within database has its unique advantages.  In this in-database data mining scenario,  two physical components, i.e., data mining algorithm implementation and data management, are collapsed into a single component, Oracle database. For example, we have used Oracle Data Mining to build debit card and check deposit fraud  models. The in-database solution provides substantially increased security, productivity, manageability and scalability. This should not be a surprise because a database is designed to handle large data using SQL as a standard language in an enterprise environment. If we consider data mining as just more sophisticated SQL queries, nothing more, nothing less, a lot of difficulties in data mining practices will disappear naturally. 

       In-Database Data Mining