{"id":1868,"date":"2013-09-27T01:10:31","date_gmt":"2013-09-27T01:10:31","guid":{"rendered":"http:\/\/bicortex.com\/?p=1868"},"modified":"2013-09-27T02:42:42","modified_gmt":"2013-09-27T02:42:42","slug":"decision-trees-solving-customer-classification-marketing-problem-with-excel-and-microsoft-sql-server-data-mining-tools","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/decision-trees-solving-customer-classification-marketing-problem-with-excel-and-microsoft-sql-server-data-mining-tools\/","title":{"rendered":"Decision Trees – Solving Customer Classification Marketing Problem With Excel And Microsoft SQL Server Data Mining Tools"},"content":{"rendered":"

A few months ago I started playing around with a bunch of statistical applications and built a very simple Twitter sentiment analysis engine for feeds classification using RapidMiner \u2013 you can see the full post HERE<\/a><\/strong>. Since then I have been quite busy and haven\u2019t had a chance to play with data mining applications any further but given my recent time off work and a few extra hours on my hands I was eager to replicate a proof-of-concept solution I built a long time ago for a marketing department in response to their issue with customer classification for catalogue mail-out. For that project I put together a rudimentary SSIS, SSAS and SSRS solution which, in its core functionality utilised Microsoft data mining features. In this post I will show you how to build a similar solution (highly simplified though \u2013 no reporting or complex ETL\/data cleansing features) using Excel. I will also use the same dataset to compare my results to those generated by SAP Predictive Analysis \u2013 another application which I recently attended a demo of. SAP Predictive Analysis is a \u2018new kid on the block\u2019 of data mining software crated to allow analysts and business users, not quants with PhDs, to build simple models to gather insight from their data.<\/p>\n

Excel, being the lovechild of any commercial department can be utilised as a powerful tool for many different domains of data mining, especially in conjunction with Analysis Services engine running as part of Microsoft SQL Server deployment. Yes, it is true that Excel contains many mathematical and statistical functions, however, on its own, Excel does not provide the functionality for robust data cleansing, model testing, scripting languages such as DMX, deployment options etc. which can be achieved in conjunction with SQL Server engine. Also, in order to take advantage of the highly simplified data mining techniques mostly driven by step-by-step wizard dialogs, Excel requires a connection to Analysis Services so if you\u2019re keen to replicate this exercise in your own environment, make sure you can connect to SSAS database and have Excel data mining add-in installed.<\/p>\n

\u00a0Let\u2019s go ahead and start with a problem definition to help us determine the possible course of action and two sample datasets \u2013 one for model training and another one which will be used for scoring.<\/p>\n

Problem Definition<\/h3>\n

Let\u2019s envisage that a telecom company has struck a deal with a telephone manufacturer and is about to release a new model of a highly anticipated smart phone on a competitive plan. They know that the phone is going to be a hit among its customers but given that their marketing department is very cutting-edge and would like to maximise the odds of addressing advertising campaign budget towards the right group of clients, they want to use the customer data to pinpoint those individuals who are happy not to upgrade their existing phones. Chances are that there will almost certainly be a large group of die-hard fans who are even happy to camp outside the store just to be the first ones to get their hands on the latest gadget when it eventually gets released \u2013 these individuals do not need the power of persuasion or marketing dollars spent on. It\u2019s the ones who are on the fence, undecided or doubtful that may need just a little nudge to sway them over the line. This group will unknowingly become the focus of the campaign but in order to determine their profile, we first must determine the method we can employ to single out customers who fall into this category.<\/p>\n

The decision tree is probably the most popular data mining technique because of fast training performance, a high degree of accuracy, and easily understandable patterns. The most common data mining task for a decision tree is classification \u2014 that is, determining whether or not a set of data belongs to a specific type, or class. For example, loan applicants can be classified as high risk or low risk, and decision trees help determine the rules to perform that classification based on historical data. The principal idea of a decision tree is to split your data recursively into subsets. Each input attribute is evaluated to determine how cleanly it divides the data across the classes (or states) of your target variable (predictable attribute). The process of evaluating all inputs is then repeated on each subset. When this recursive process is completed, a decision tree is formed. For this exercise I will use Microsoft decision trees algorithm as it fits the intended classification purpose very well. There is lots of information on the internet pertaining to decision tree algorithm applications so rather than theorising, let\u2019s look at some concrete examples of data that will be used for this post.<\/p>\n

Let\u2019s assume that marketing department has provided us with a training dataset (downloadable HERE<\/b><\/a>) of customers (identified with unique IDs for privacy reasons) in conjunction with a series of additional attributes such as Gender, Age, Marital Status, Contract Length, whether the customer has kids, whether he or she is married etc. Amongst all those there is also one attribute – Adopter Class – which categorises clients based on whether they are likely to adopt the new product relatively quickly after it has been released or whether they are happy to wait. This attribute has four distinctive values \u2013 Late, Very Late, Early, Very Early \u2013 which indicate customer adoption promptness. Obviously, if a customer has been tagged with Very Early in the past based on their previous behaviour patterns, he or she is highly likely to procure a new phone the moment it becomes available. On the other side of the spectrum are those customers marked as Very Late, meaning they are not as savvy to peruse the latest and greatest and are happy to wait. All those attributes in the training dataset will be used to train our model to learn about the most deterministic factors that influence clients\u2019 behaviours.<\/p>\n

We were also given a second dataset (downloadable HERE<\/b><\/a>) which contains a different set of customers with same descriptive attributes attached to their IDs but this time without Adopter Class populated. The goal is to mine the already pre-classified customers from our first dataset and based on the findings apply the \u2018knowledge\u2019 to determine the category the new set of customers will most likely fall into.<\/p>\n

Datasets and SQL Code<\/h3>\n

As far as the individual datasets, you can download them HERE<\/b><\/a> or alternatively, you can re-create them from scratch using the SQL code I wrote to populate our training and scoring dataset files as per below. Please note that if you wish to run the code yourself, ensure that you have SampleData directory created on your C:\\ drive or alternatively change the code to output the files into your nominated directory structure. Also, in relation to datasets content, note that this data was synthesised using SQL and is not a true representation of the customer data in a sense that the values are not as random or representative of a group of individuals as they would be if the data was coming from a data warehouse or CRM sources.<\/p>\n

\r\nUSE [master]\r\nGO\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Temp_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE [Temp_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE [Temp_DB]\r\nEND\r\nGO\r\nCREATE DATABASE [Temp_DB] \r\nGO\r\nUSE  [Temp_DB] \r\n\r\nDECLARE @Gender Table\r\n(Gender char(1))\r\nDECLARE @Age Table\r\n(Age int)\r\nDECLARE @Marital_Status Table\r\n(Marital_Status varchar (20))\r\nDECLARE @Yes_No_Flag Table\r\n(Flag char (1))\r\nDECLARE @Current_Plan Table\r\n(CPlan varchar (20))\r\nDECLARE @Payment_Method Table\r\n(Payment_Method varchar (50))\r\nDECLARE @Contract_Length Table\r\n(Contract_Length varchar (30))\r\nDECLARE @Adopter_Class Table\r\n(Adopter_Class varchar (20))\r\nDECLARE @Age_Range Table\r\n(Age int)\r\n\r\nDECLARE @Years int = 18\r\n\r\nWHILE @Years < 65\r\nBEGIN\r\n\tINSERT INTO @Age_Range\r\n\tSELECT @Years\r\n\tSET @Years = @Years +1\r\nEND\r\n\r\nINSERT INTO @Gender \r\nVALUES ('M'), ('F')\r\nINSERT INTO @Age \r\nSELECT * FROM @Age_Range\r\nINSERT INTO @Marital_Status\r\nVALUES ('Married'), ('Single')\r\nINSERT INTO @Yes_No_Flag \r\nVALUES('Y'), ('N')\r\nINSERT INTO @Current_Plan\r\nVALUES ('Low'), ('Medium'), ('Heavy'), ('PrePaid')\r\nINSERT INTO @Payment_Method\r\nVALUES ('Automatic'), ('Non-Automatic')\r\nINSERT INTO @Contract_Length\r\nVALUES ('12 Months'), ('24 months'), ('36 Months'), ('No Contract')\r\nINSERT INTO @Adopter_Class\r\nVALUES ('Very Early'), ('Early'), ('Very Late'), ('Late')\r\n\r\nCREATE TABLE Temp_Results\r\n(UserID\t\t\t\t\tint\t\t\t\tNOT NULL,\r\nGender\t\t\t\t\tchar(1)\t\t\tNOT NULL,\r\nAge\t\t\t\t\t\tint\t\t\t\tNOT NULL,\r\nMarital_Status\t\t\tvarchar (20)\tNOT NULL,\r\nCurrent_Plan\t\t\tvarchar (20)\tNOT NULL,\r\nPayment_Method\t\t\tvarchar (50)\tNOT NULL, \r\nContract_Length\t\t\tvarchar (30)\tNOT NULL,\r\nHas_Kids\t\t\t\tchar (1)\t\tNOT NULL,\r\nOther_Services_Bundled\tchar(1)\t\t\tNOT NULL,\r\nAdopter_Class\t\t\tvarchar(20)\t\tNOT NULL)\r\n\r\nSET NOCOUNT ON\r\nDECLARE @Random INT;\r\nDECLARE @Upper INT;\r\nDECLARE @Lower INT\r\nSET @Lower = 10000\r\nSET @Upper = 50000\r\nDECLARE @Records_Count int = 0\r\nWHILE @Records_Count < 5000\r\nBEGIN\r\n\tINSERT INTO Temp_Results\r\n\tSELECT  ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0), \r\n\t(SELECT TOP (1) Gender\t\t\tFROM @Gender\t\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Age\t\t\t\tFROM @Age\t\t\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Marital_Status\tFROM @Marital_Status\tORDER BY NewID()),\r\n\t(SELECT TOP (1) CPlan\t\t\tFROM @Current_Plan\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Payment_Method\tFROM @Payment_Method\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Contract_Length FROM @Contract_Length\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Flag\t\t\tFROM @Yes_No_Flag\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Flag\t\t\tFROM @Yes_No_Flag\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Adopter_Class\tFROM @Adopter_Class\t\tORDER BY NewID())\r\n\tSET @Records_Count = @Records_Count + 1\r\nEND\r\n\r\nUPDATE Temp_Results\r\nSET Payment_Method\t=\tCASE\tWHEN Current_Plan = 'PrePaid'\tTHEN 'Non-Automatic' ELSE Payment_Method END\r\nUPDATE Temp_Results\r\nSET Contract_Length =\tCASE\tWHEN Current_Plan = 'PrePaid'\tTHEN 'No Contract' ELSE Contract_Length END\r\nUPDATE Temp_Results\r\nSET Adopter_Class\t=\tCASE\tWHEN\tAge BETWEEN 18 and 35 AND\r\n\t\t\t\t\t\t\t\t\t\tOther_Services_Bundled = 'Y' AND\r\n\t\t\t\t\t\t\t\t\t\tCurrent_Plan IN ('Medium', 'Heavy', 'Low') AND\r\n\t\t\t\t\t\t\t\t\t\tContract_Length IN ('12 Months', '24 Months')\r\n\t\t\t\t\t\t\t\t\t\tTHEN 'Very Early'\r\n\t\t\t\t\t\t\t\tWHEN\tAge BETWEEN 36 and 45 AND\r\n\t\t\t\t\t\t\t\t\t\tOther_Services_Bundled = 'Y' AND\r\n\t\t\t\t\t\t\t\t\t\tCurrent_Plan IN ('Medium', 'Heavy', 'Low') AND\r\n\t\t\t\t\t\t\t\t\t\tContract_Length IN ('12 Months', '24 Months', '36 Months')\r\n\t\t\t\t\t\t\t\t\t\tTHEN 'Early'\r\n\t\t\t\t\t\t\t\tWHEN\tAge BETWEEN 46 and 55 AND\r\n\t\t\t\t\t\t\t\t\t\tOther_Services_Bundled = 'N' AND\r\n\t\t\t\t\t\t\t\t\t\tCurrent_Plan IN ('Medium','Low') AND\r\n\t\t\t\t\t\t\t\t\t\tContract_Length IN ('12 Months', '24 Months', '36 Months')\r\n\t\t\t\t\t\t\t\t\t\tTHEN 'Late'\r\n\t\t\t\t\t\t\t\tWHEN\tAge > 55 AND\r\n\t\t\t\t\t\t\t\t\t\tOther_Services_Bundled ='N' AND\r\n\t\t\t\t\t\t\t\t\t\tCurrent_PLan IN ('PrePaid', 'Low') AND\r\n\t\t\t\t\t\t\t\t\t\tContract_Length IN ('No Contract', '36 Months', '24 Months')\r\n\t\t\t\t\t\t\t\t\t\tTHEN 'Very Late' ELSE Adopter_Class END\r\n\r\nDECLARE @ID varchar (200)\r\nDECLARE @COUNT int\r\nDECLARE CUR_DELETE CURSOR FOR\r\n\tSELECT UserID,COUNT([UserID]) FROM [Temp_Results] \r\n\tGROUP BY [UserID] HAVING COUNT([UserID]) > 1\r\n\tOPEN CUR_DELETE\r\n\t\tFETCH NEXT FROM CUR_DELETE INTO @ID, @COUNT\r\n\t\tWHILE @@FETCH_STATUS = 0\r\n\t\tBEGIN\r\n\t\tDELETE TOP(@COUNT -1) FROM [Temp_Results] WHERE [UserID] = @ID\r\n\t\tFETCH NEXT FROM CUR_DELETE INTO @ID, @COUNT\r\n\tEND\r\nCLOSE CUR_DELETE\r\nDEALLOCATE CUR_DELETE\r\n\r\nDECLARE\r\n@saveloc\t\tVARCHAR(2048),\r\n@query\t\t\tVARCHAR(2048),\r\n@bcpquery\t\tVARCHAR(2048),\r\n@bcpconn\t\tVARCHAR(64),\r\n@bcpdelim\t\tVARCHAR(2)\r\n\r\n\/*\r\nTo enable CMD_SHELL IF DISABLED\r\nEXEC sp_configure 'show advanced options', 1\r\nGO\r\nRECONFIGURE\r\nGO\r\nEXEC sp_configure 'xp_cmdshell', 1\r\nGO\r\nRECONFIGURE\r\nGO\r\n*\/\r\n\r\nSET @query =\t'USE Temp_DB \r\n\t\t\t\tSELECT \r\n\t\t\t\t''UserID''\t\t\t\t\tas H1,\r\n\t\t\t\t''Gender''\t\t\t\t\tas H2,\r\n\t\t\t\t''Age''\t\t\t\t\t\tas H3,\r\n\t\t\t\t''Marital_Status''\t\t\tas H4,\r\n\t\t\t\t''Current_Plan''\t\t\tas H5,\r\n\t\t\t\t''Payment_Method''\t\t\tas H6,\r\n\t\t\t\t''Contract_Length''\t\t\tas H7,\r\n\t\t\t\t''Has_Kids''\t\t\t\tas H8,\r\n\t\t\t\t''Other_Services_Bundled''\tas H9,\r\n\t\t\t\t''Adopter_Class''\t\t\tas H10\r\n\t\t\t\tUNION ALL\r\n\t\t\t\tSELECT \r\n\t\t\t\tCAST(UserID as Varchar(10)),\r\n\t\t\t\tGender,\r\n\t\t\t\tCAST(Age as Varchar (10)),\r\n\t\t\t\tMarital_Status,\r\n\t\t\t\tCurrent_Plan,\r\n\t\t\t\tPayment_Method,\r\n\t\t\t\tContract_Length,\r\n\t\t\t\tHas_Kids,\r\n\t\t\t\tOther_Services_Bundled,\r\n\t\t\t\tAdopter_Class\r\n\t\t\t\tFROM Temp_Results'\r\nSET @saveloc = 'c:\\SampleData\\Training_DataSet.csv'\r\nSET @bcpdelim   = ','\r\nSET @bcpconn    = '-T' -- Trusted\r\n--SET @bcpconn    = '-U <username> -P <password>' -- SQL authentication\r\nSET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveloc + '" -c -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername\r\nEXEC master..xp_cmdshell @bcpquery  \r\n\r\nTRUNCATE TABLE Temp_Results\r\n\r\nALTER TABLE Temp_Results\r\nDROP COLUMN Adopter_Class\r\n\r\nSET @Lower = 50001\r\nSET @Upper = 99999\r\nSET @Records_Count = 0\r\nWHILE @Records_Count < 5000\r\nBEGIN\r\n\tINSERT INTO Temp_Results\r\n\tSELECT  ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0),  \r\n\t(SELECT TOP (1) Gender\t\t\tFROM @Gender\t\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Age\t\t\t\tFROM @Age\t\t\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Marital_Status\tFROM @Marital_Status\tORDER BY NewID()),\r\n\t(SELECT TOP (1) CPlan\t\t\tFROM @Current_Plan\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Payment_Method\tFROM @Payment_Method\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Contract_Length FROM @Contract_Length\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Flag\t\t\tFROM @Yes_No_Flag\t\tORDER BY NewID()),\r\n\t(SELECT TOP (1) Flag\t\t\tFROM @Yes_No_Flag\t\tORDER BY NewID())\r\n\tSET @Records_Count = @Records_Count + 1\r\nEND\r\n\r\nSET @ID =''\r\nSET @COUNT = 0\r\nDECLARE CUR_DELETE CURSOR FOR\r\n\tSELECT UserID,COUNT([UserID]) FROM [Temp_Results] \r\n\tGROUP BY [UserID] HAVING COUNT([UserID]) > 1\r\n\tOPEN CUR_DELETE\r\n\t\tFETCH NEXT FROM CUR_DELETE INTO @ID, @COUNT\r\n\t\tWHILE @@FETCH_STATUS = 0\r\n\t\tBEGIN\r\n\t\tDELETE TOP(@COUNT -1) FROM [Temp_Results] WHERE [UserID] = @ID\r\n\t\tFETCH NEXT FROM CUR_DELETE INTO @ID, @COUNT\r\n\tEND\r\nCLOSE CUR_DELETE\r\nDEALLOCATE CUR_DELETE\r\n\r\nSET @query =\t'USE Temp_DB \r\n\t\t\t\tSELECT \r\n\t\t\t\t''UserID''\t\t\t\t\tas H1,\r\n\t\t\t\t''Gender''\t\t\t\t\tas H2,\r\n\t\t\t\t''Age''\t\t\t\t\t\tas H3,\r\n\t\t\t\t''Marital_Status''\t\t\tas H4,\r\n\t\t\t\t''Current_Plan''\t\t\tas H5,\r\n\t\t\t\t''Payment_Method''\t\t\tas H6,\r\n\t\t\t\t''Contract_Length''\t\t\tas H7,\r\n\t\t\t\t''Has_Kids''\t\t\t\tas H8,\r\n\t\t\t\t''Other_Services_Bundled''\tas H9\r\n\t\t\t\tUNION ALL\r\n\t\t\t\tSELECT \r\n\t\t\t\tCAST(UserID as Varchar(10)),\r\n\t\t\t\tGender,\r\n\t\t\t\tCAST(Age as Varchar (10)),\r\n\t\t\t\tMarital_Status,\r\n\t\t\t\tCurrent_Plan,\r\n\t\t\t\tPayment_Method,\r\n\t\t\t\tContract_Length,\r\n\t\t\t\tHas_Kids,\r\n\t\t\t\tOther_Services_Bundled\r\n\t\t\t\tFROM Temp_Results'\r\nSET @saveloc = 'c:\\SampleData\\Scoring_DataSet.csv'\r\nSET @bcpdelim   = ','\r\nSET @bcpconn    = '-T' -- Trusted\r\n--SET @bcpconn    = '-U <username> -P <password>' -- SQL authentication\r\nSET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveloc + '" -c -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername\r\nEXEC master..xp_cmdshell @bcpquery \r\n\r\nDROP TABLE Temp_Results\r\n<\/pre>\n
Decision Trees Model Deployment and Application<\/h3>\n
Once the two files have been created (or saved if you opted out of running the script and just downloaded them), we can open the one prefixed with \u2018training\u2019 and highlight all records. Having data mining plug-in installed, Classify button should be one of the options available form Data Mining tab on Excel\u2019s ribbon. Clicking it should start the wizard which will take us through the steps where we will adjust model’s properties and variables before deployment.<\/p>\n
\"ExcelTrainingData_ExcelDM_DecisionTrees\"<\/a><\/p>\n
Step one provides a brief description of its features and functionality, let\u2019s click Next and to proceed to data range selection dialog. As per image above you will notice that I have selected over 4600 records generated by the SQL query. Alternatively, you can also select external data source. Let\u2019s click Next to advance to Classification window where we have an option to choose the columns participating in the model creation as well as the column to analyse. I have intentionally removed the UserID column which has no relevance or dependencies on other columns and selected Adopter_Class column as an analysis target. Adopter_Class column contains the data which is the focus of this exercise and marketing wants to know how this attribute gets affected by other columns’ data.<\/p>\n
\"ColumnsSelection_ExcelDM_DecisionTrees\"<\/a><\/p>\n
Clicking Next we will advance to Split Data Into Training And Testing step where we can specify the percentage of our dataset to get allocated to training and testing the model. We will leave this value at default and advance to the last step where we have an option to change some of the final properties such as Model Name or Model Description. Again, we will leave everything at default and finish the wizard deploying the model as per image below.<\/p>\n
\"ModelCreation_ExcelDM_DecisionTrees\"<\/a><\/p>\n
After data reading and training has completed and our decision trees model has been deployed we can see the breakdown of individual attributes participating in how their values and affinities or dependencies relate to Adopter Class values. In the below image, the Background property has been set to Very Late, which is the group marketing campaign wants to potentially address when devising their next campaign. These people are the most resistant to adopting new technology and with the release of the new smart phone, marketing is hoping to gather insight into their profile.\u00a0 Rectangles shaded in darkest blue are representative of the target group i.e. very late adopters (click on image to enlarge).<\/p>\n
\"VeryLate_ExcelDM_DecisionTrees\"<\/a><\/p>\n
Following this tree from left to right, we can deduce the following.
\n<\/span><\/p>\n