Making the Profitable Choice Tool: Part II

Welcome back! In Part II of Making the Profitable Choice Tool we will take a look at the issues we came across during this project, the overall solution for the tool and what the future will hold.

The Pain

Now that we have a packaged Excel file + Python ready to be emailed to our sales associates across the United States, we’re all set, right? Not so fast. ATD’s email filters prevent random zip files with .exe being sent (which makes perfect sense). They also have a limit on what the size of the attachment can be. This file violates both. We decided another method would be to have IT push our tool directly to the laptops of all the users (of course forgetting we might actually have a shared drive where the file could be accessed; hindsight is 20/20).

It turns out that although it’s easy for IT to push out a file to 500+ laptops, there are checks and balances in place to make sure that a malicious deployment doesn’t sabotage internal systems. After a discussion with the IT security team to make sure our file and executable were safe, the next step was to see if this could be expedited, which they kindly agreed to do. Perfect!

While we waited for final approval, we had a small subset of users test out the Excel + Python magic on their work laptops. Immediately we saw a message that was seared permanently in our minds:

It was already a few days past the time we promised to have the tool delivered and now this happened. We needed to speak to the person in charge of running our Symantec operations. Unfortunately, this was the eve of Thanksgiving and the eve of a major industry change. We continued our testing while we waited to figure out Symantec problem. During our testing, we ultimately decided to ditch the Python executable attempt, as our setup didn’t work in its current state on some of the sales associates Windows 7 laptops.

Coming to a Solution

We started to quickly see that even if the Excel + Python executable approach launched to the sales team, it wasn’t a long-term (or even medium-term) solution. On a late-night call, we had surmised that it would be easier to use Visual Basic to call an API that did all the calculations for us. Since we already had Flask applications deployed, we could wrap our master_programs.py in a Flask API, and call it from Excel. The setup looks like this:

diagram_pct

Our API runs inside a Docker container and calls our master_programs.py script. Our logs — both activity and program-specific — are dumped into a Cloud SQL database on the Google Cloud Platform. The Excel file front-end calls our API via Visual Basic. Notice the Microsoft BI (Cubes), which only connects directly to Excel. This was an add-on after-the-fact, and consistent of copying a BI cube Excel file that was already in use by the sales team into the Profitable Choice Excel file. So our back-end Python script does not pull any customer data, instead it’s shuttled from MS SQL BI database -> Excel -> Flask; not exactly the shortest route, but it works and is within the familiarity of our users.

The master_programs.py discussed above is wrapped in an app.py file, which has our API calls setup. Then we deploy our Flask app through gunicorn, and call the API via Excel. Calling the API via Excel requires Visual Basic, and the code looks like:

Now we can do this for 10+ programs x 2 (for current & go-forward scenarios). Each click of the ‘Calculate’ button calls the API and takes about 2–3 seconds to complete in total.

What about the downsides?

1. Because the back-end is decoupled from the front-end, we have to redistribute a new Excel file every time a front-end change is made. Because our sales associates often save the Excel file for each scenario they run, it’s easy to get behind on versions. For back-end changes, it’s much easier — if we find a bug, or a program changes, we simply update master_programs.py file and the changes are immediate.

2. Our API is only accessible behind our corporate firewall, thus our sales associates must be on our corporate VPN or connected to the corporate network in some manner.

3. The Excel file is still married to Visual Basic — and there is windows-specific visual basic code that doesn’t run on other platforms. Read: no OS X, and no native iPad support (though we got around that with remote desktop).

4. On small screens (like an iPad), the interface can be cumbersome to navigate through.

However, these downsides are minor when compared to the immediate upsides:

1. The tool works — we now have a way for our sales team to consult with their customers about where their bonus incentive is coming from quickly and how they might rearrange volume to maximize that end of their profit.
2. Excel is a familiar interface; there’s no learning curve when it comes to how to manipulate cells.
3. Scenarios can be saved and manipulated as needed with customers.
4. Easy copying-and-pasting ability from the tool to PowerPoint, our primary presentation software.

Since the tool deployed to the field, we have had thousands of scenarios run through it. From start to finish, we had the Profitable Choice tool operational in less than two weeks. There was no fancy data science here: just simple if -then statements, Excel, Visual Basic, and an API call. Yet the tool has had an immediate positive impact on our business. Sometimes the biggest bang-for-your-buck comes from the low hanging fruit that you can hash out in tables, without any model building.

The Future

The bonus incentive calculations were simple –the deployment was the hard part. Now that Profitable Choice is a staple in the toolkit of our sales associates, the AACoE is on to the next step. How can we make this tool even more granular (going down to the SKU-level) and how can we take the burden off our sales associates to figure out the best way to move volume and maximize payout? That is, what if there was an “optimize” button that would distribute volume, subject to constraints, in a way that would maximize not only the bonus incentive payout, but profit margin as well? All while being packaged in a web interface without the downsides of Excel? Stay tuned for a future blog posts on how we implemented advanced methods to take volume allocation to the next level!