Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
The 3rd problem of the Alteryx Grand Prix this year was to create an optimal set of sales territories for 5 existing sales offices. In order to judge the results, contestants were provided a scoring macro that returned a score based on the set of states they picked for each sales office. The competitors were asked to create a module that returned a better score.
The simplest good answer was to just use a Find Nearest tool and assign each state to its nearest sales office. This produced very good results for the compactness and the travel fairness portions of the score, but did not do so well on the fairness portion. The New York office would have over 3X the # of customers as the Boulder office. Clearly the earning potential of the Boulder sales guys is way less than the NY sales guy (although maybe he is too busy having fun in the mountains to notice.)
Here is the solution by simply using a Find Nearest.
This is one of those classic problems that have no direct solution. There are too many possible combinations to try them all and there is no definitive way to find the perfect solution. The solution I chose was to guess. Guessing isn't very good, but a computer can make a lot of guesses much faster than a person does. In particular, I chose an evolutionary algorithm.
The basic algorithm is:
- Start with the results from the find nearest. It is pretty good, and a good starting point speeds the process up.
- Randomly move a few states from 1 sales territory to another.
- Compare the score for my new result to my previous score. If it is better keep it, if not, throw it out.
- Repeat this for a fixed number of times.
This is actually a very simple solution, but as long as you can quantify your results with a scoring macro well, it produces excellent results. The best score from the grand prix was Drew Brighton at 2631.
The genetic algorithm was able to better that by quite a bit at 2184. Below is the solution from the iterative algorithm.
The beauty of the iterative algorithm is that it adapts to new inputs easily. Running it on problem 3B (a new set of locations) with 7 sales offices produced a score of of 2854 vs Drew Brighton (who once again had the best score) at 3907. The solution can be seen below.
The module and macros for this solution can be downloaded here. I limited the # of iterations to 500, but since the results are random that will not always produce the same results as above. For more consistent results, the number of iterations should be increased.
