Data Mining in Excel Part 11: Goal Seek

Today, we will be talking about another one of the Table Analysis Tools, Goal Seek.

Goal Seek

Imagine that you have a table with the characteristics of each customer, as well as the price of each of your products. What if you wanted to determine what the price of the product should be in order to entice the customer to buy it?  This algorithm handles this type of problem.  However, our sample data set only has data about the customer, which we cannot change.  For example, we can't make a customer have 3 children instead of 2.  Therefore, this example is technically flawed; but, it is still a good way to demonstrate the procedure.  As usual, we will be using the Data Mining Sample data set from Microsoft.  Let's get started.

Goal Seek Window

First, we need to specify the variable that will be our "goal" and the value that we are "seeking".  In this example, our goal is to get the customer to buy a bike.  Notice that there are three options for goals.  You can tell the algorithm the exact value you are looking for, a certain percentage increase or decrease in the current value, or a range of values.  We should note that the second and third options are only applicable for numeric values.  Next, we need to choose the column that we want to manipulate.  Here we are asking the question "How many miles would this customer have to commute in order for them to want to buy a bike?"  Finally, we tell the algorithm whether we want to use a single row, or the entire table.  Let's use the entire table.

Recommended Commute Distance

We see that the algorithm returned the best possible commute distance, as well as whether or not the goal was capable of being achieved.  Sometimes, there is no commute distance that would make the customer likely to buy a bike.  For these customers, you would need to figure out a different way to entice them to buy.  Now, let's try using a numeric value as our goal instead.  Let's ask "What level of education would this person need to obtain a 20% increase in income?"

Increasing Income by 20%

Recommended Education

This wasn't so difficult.  Now, let's see if we can combine these algorithms in some cool ways.  Let's also use a clustering algorithm that doesn't consider the four new columns we just added.  Then let's create a simple analysis in Tableau examining how they relate.

Dashboard

Goal Seek is a useful tool when you want to ask questions TO your data, not ABOUT your data.  We were able to pretty quickly toss together a few questions and get some pretty interesting results from our analysis.  Keep an eye out for our next post where we'll be talking about "What-If".  Thanks for reading.  We hope you found this informative.

Brad Llewellyn
Director, Consumer Sciences
Consumer Orbit
llewellyn.wb@gmail.com

http://www.linkedin.com/in/bradllewellyn