Any good analysis depends on creating a customer profile with good data. For data to be good it will need to be cleansed, meaning when you look up State in your customer database table that MD is always the entry for Maryland. The collecting and cleansing of the data is the hard part or where most of the work comes into play. The information you need to collect depends on your type of business. For example, if you sell to individual consumers, you will want to know their age, gender, location, spending habits and income. If you sell to other businesses, find out what sector they are in, how big they are, how much they spend and what other suppliers they use. Once all the hard work of data collecting and cleansing is done it’s time to analyze the data.
Creating a Data Mining Project
For this post I will be using Visual Studio to create my Data Mining Project which will help me build a DMX query when providing a front end interface for my data end users. You can get some to the same results with Excel’s SQL Data Mining add-in located here: http://www.microsoft.com/en-us/download/details.aspx?id=35578
1. Open SQL Server Data Tools (SSDT).
2. On the File menu, point to New, and then select Project.
3. Verify that Business Intelligence Projects is selected in the Project types pane.
4. In the Templates pane, select Analysis Services Multidimensional and Data Mining Project.
5. In the Name box, name the new project BasicDataMining.
6. Click OK.
7. Next I create a data source and data view associated with the data source.
8. Next we create a mining structure for the targeted mailing scenario and two data mining models. Each model type is based on a different algorithm and provides different insights into the data.
· The Decision Tree model tells you about factors that influence bike buying.
· The Clustering model groups your customers by attributes that include their bike buying behavior and other selected attributes.
Once the data mining models have been built and deployed, we can use the Lift Chart function in Visual Studio with Biker Buyer set to yes to see what algorithm, Decision Tree or Clustering provides the best prediction that someone will be a bike buyer. On the chart below, the top green line represents the ideal model, 100% of your customers buying a bike and the bottom blue line represents a random guess. So the closer to the ideal model the better your model is at predicting a bike buyer. In this case it is the Decision Tree model.
Creating Predictions
Using the Decision Tree model together with our prospective buyer table we can build a marketing list for our sales team to focus their follow up calls on.
1. On the Mining Model Prediction tab of Data Mining Designer, in the Mining Model box, click Select Model.
2. In the Select Mining Model dialog box, navigate through the tree to the Targeted Mailing structure, expand the structure, select Decision Tree, and then click OK.
3. In the Select Input Table(s) box, click Select Case Table.
4. In the Select Table dialog box, in the Data Source list, select the data source view Adventure Works DW.
5. In Table/View Name, select the ProspectiveBuyer (dbo) table, and then click OK.
6. Right-click the lines connecting the Mining Model window to the Select Input Table window, and select Modify Connections. Notice that not every column is mapped.
7. From the ProspectiveBuyer table drag and drop the email, address1, city fields to the source grid.
8. From the Mining Model drag and drop the Bike Buyer field to the source grid.
9. Select the Prediction Function under Source and PredictProbability under Field and in the Criteria/Argument field drag and drop the Bike Buyer field from the Mining Model window. Complete the argument by adding the “,1” to predict the bike buyer. The results will look like the below screen shot.
Execute the Predictive query.
The query will product the below results that can be provided to your sales team.
By using Visual Studio to build my Mining Model Prediction it helped me create a DMX query that I can then use with Excel or SSRS to provide front end access for my end users. Giving the end user a way to access the data for a focused marketing campaign or targeted potential buyers list. If you would like additional information on how we built our data mining model, Microsoft GP and CRM, please contact KTL Solutions.
