Excel at Data Mining – Creating a Profit Chart
Today, I’m going to show you how to create and read a Profit Chart in less than 5 minutes with the Microsoft Excel data mining add-in. The assumption of this post is that you have already installed the data mining add-in for Excel and configured the add-in to be pointed at an instance of SQL Server with Analysis Services to which you have access rights.
For this example, we will be using a tutorial spreadsheet that can be found on Codeplex here. You will also need to attach the AdventureworksDW2012 data file to SQL Server which can be downloaded here.
Our goal is to create a Profit Chart based on a mining structure with all of its associated models that we have created previously.
Read below if you are unable to view the video walkthrough.
Step 1: Creating the Profit Chart
We go to the DATA MINING tab, the Accuracy and Validation group, and select the Profit Chart icon. From here, select the Mining Structure that you wish to analyze and Click Next. On the Specify Profit Chart Parameters screen, change the individual cost to 8.00.
Click Next and then Finish. And that is how easy it is to actually create a Profit Chart!
Step 2: Reading the Profit Chart
Before we dive into reading the chart, let’s take a moment to think about what we are doing. We started off by creating a mining structure based upon what types of folks bought bicycles. Then we applied a couple of models to the mining structure to be able to make predictions on who would be more likely to purchase a bike. But why do we care about the likelihood someone will purchase? If they idea is to sell as many bikes as possible then shouldn’t we just contact every customer?
The answer to that is no, because the assumption of wanting to sell the most bikes is faulty. The point is maximize the profit off the bikes sold. Since contacting each customer has a cost, then we want to focus on contacting the customers most likely to buy. But we need a cutoff percentage to say, “We will contact everyone who is x% or higher likely to buy a bike.” How do we get that number?
Enter the Profit Chart. After finishing the Wizard, you should see something like the screenshot below.
Let’s zoom in a bit on the actual graph that was produced.
We notice that the y-axis represents profit and the x-axis represents the overall population. We see that there are three lines on the graph that show how profit changes as we contact more people. The blue line represents how the profit would change if we just randomly selected customers from the pool. The green and red lines show how the models allow us to prioritize the customers more likely to purchase a bike. Interestingly, we see that contacting all the customers in a pool is bad in this particular case because it minimizes profit even though it maximizes revenue. It is easy to see from the graph that the models outperform the random method and that not everyone in the pools should be contacted using either model. Both models peak in profitability around the 50% mark. We can use this information to set a cutoff point and have our sales people focus on customers that have a greater than 50% probability. We make more profit by doing less work, how great is that?
And that is how you create and read a Profit Chart in less than five minutes using the Excel Data Mining Add in.
Billy Decker is a consultant at StatSlice Systems. He graduated with a dual degree in Physics and Mathematics from the University of Texas at Austin and received his Masters Degree in Physics from the University of Texas at Arlington. He previously worked for Global Technical Services as a Senior Training Analyst and Bell Hellicopter as an Instructional Designer. His technical experience includes, but is not limited to, SQL, SAP, Business Objects, QlikView, and Sharepoint.
You can subscribe to our RSS feed.