<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/sales-analytics-how-to-use-machine-learning-to-predict-and-optimize-product-backorders/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html"> business-science.io - Articles</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Sales, customer service, supply chain and logistics, manufacturing… no matter which department you’re in, you more than likely care about backorders. Backorders are products that are temporarily out of stock, but a customer is permitted to place an order against future inventory. Back orders are both good and bad: Strong demand can drive back orders, but so can suboptimal planning. <strong>The problem is when a product is not immediately available, customers may not have the luxury or patience to wait. This translates into lost sales and low customer satisfaction.</strong> The good news is that machine learning (ML) can be used to identify products at risk of backorders. In this article we use the <strong>new H2O automated ML algorithm</strong> to implement Kaggle-quality predictions on the Kaggle dataset, <a rel="nofollow" href="https://www.kaggle.com/tiredgeek/predict-bo-trial" target="_blank">“Can You Predict Product Backorders?”</a>. This is an advanced tutorial, which can be difficult for learners. We have good news, see our <a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#announcements" target="_blank">announcement below</a> if you are interested in a machine learning course from Business Science. If you love this tutorial, please <a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#social" target="_blank">connect with us on social media</a> to stay up on the latest <a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#contact" target="_blank">Business Science</a> news, events and information! Good luck and happy analyzing!</p>
<h1>Challenges and Benefits</h1>
<p>This tutorial covers two challenges. One challenge with this problem is <strong>dataset imbalance</strong>, when the majority class significantly outweighs the minority class. We implement a special technique for dealing with unbalanced data sets called <a rel="nofollow" href="https://www.cs.cmu.edu/afs/cs/project/jair/pub/volume16/chawla02a-html/chawla2002.html" target="_blank">SMOTE (synthetic minority over-sampling technique)</a> that improves modeling accuracy and efficiency (win-win). The second challenge is optimizing for the business case. To do so, we explore <strong>cutoff (threshold) optimization</strong> which can be used to find the cutoff that maximizes expected profit.</p>
<p>One of the important visualizations (benefits to your understanding) is the effect of precision and recall on inventory strategy. A lever the analyst controls is the cutoff (threshold). The threshold has an effect on precision and recall. By selecting the optimal threshold we can <strong>maximize expected profit</strong>.</p>
<p><img alt="Precision and Recall Vs Cutoff" src="https://i1.wp.com/www.business-science.io/assets/sales_backorder_prediction.png?w=456" data-recalc-dims="1"></p>
<h1>Quick Navigation</h1>
<p>This is a longer analysis, and certain sections may be more interesting based on your role. Are you an Executive or a Data Scientist?</p>
<ul>
<li><strong>Executive</strong>: Read these:
<ul>
<li><a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#background" target="_blank">Background on Backorder Prediction</a></li>
<li><a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#challenges" target="_blank">The Challenges with Predicting Backorders</a></li>
</ul>
</li>
<li><strong>Data Scientist</strong>: Read these:
<ul>
<li><a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#case-study" target="_blank">Case Study: Predicting Backorder Risk and Modeling Profit</a></li>
<li><a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#ML" target="_blank">Using Machine Learning to Predict Backorders</a></li>
<li><a rel="nofollow" href="http://www.business-science.io/business/2017/10/16/sales_backorder_prediction.html#optim" target="_blank">Optimizing the Model for the Expected Profit</a></li>
</ul>
</li>
</ul>
<p>Others should just read the whole thing. <img alt="😉" src="https://s.w.org/images/core/emoji/2/72x72/1f609.png" class="wp-smiley"></p>
<h1>Background on Backorder Prediction <a></a></h1>
<h3>What is a backorder?</h3>
<p>According to <a rel="nofollow" href="http://www.investopedia.com/terms/b/back-order.asp" target="_blank">Investopedia</a>, a backorder is…</p>
<blockquote>
<p>A customer order that has not been fulfilled. A backorder generally indicates that customer demand for a product or service exceeds a company’s capacity to supply it. Total backorders, also known as backlog, may be expressed in terms of units or dollar amount.</p>
</blockquote>
<h3>Why do we care?</h3>
<p><img alt="Apple iPhone X" src="https://i2.wp.com/www.business-science.io/assets/iphone-x-waterproof.png?w=456" data-recalc-dims="1"></p>
<p>Source: <a rel="nofollow" href="http://www.trustedreviews.com/news/iphone-x-release-date-specs-features-uk-price-2951157" target="_blank">www.trustedreviews.com</a></p>
<p>A backorder can be both a good thing and a bad thing. Consider a company like <a rel="nofollow" href="https://www.apple.com/" target="_blank">Apple</a> that recently rolled out several new iPhone models including iPhone 8 and iPhone X. Because the initial supply cannot possibly keep up with the magnitude of demand, Apple is immediately in a “backorder” situation that requires customer service management.</p>
<p>On the positive side, backorders indicate healthy demand for a product. For Apple, this is a credit to strong brand recognition. Customers are likely to wait several months to get the latest model because of the new and innovative technology and incredible branding. Conversely, Apple’s competitors don’t have this luxury. If new models cannot be provided immediately, their customers cancel orders and go elsewhere.</p>
<p>Companies constantly strive for a balance in managing backorders. It’s a fine line: Too much supply increases inventory costs while too little supply increases the risk that customers may cancel orders. Why not just keep 100,000 of everything on the shelves at all time? For most retailers and manufacturers, this strategy will drive inventory costs through the roof considering they likely have a large number of SKUs (unique product IDs).</p>
<p><strong>A predictive analytics program can identify which products are most likely to experience backorders giving the organization information and time to adjust</strong>. Machine learning can identify patterns related to backorders before customers order. Production can then adjust to minimize delays while customer service can provide accurate dates to keep customers informed and happy. The predictive analytics approach enables the maximum product to get in the hands of customers at the lowest cost to the organization. <strong>The result is a win-win: organizations increase sales while customers get to enjoy the products they demand</strong>.</p>
<h1>Challenges with Predicting Backorders <a></a></h1>
<p>One of the first steps in developing a backorder strategy is to understand which products are at risk of being backordered. Sounds easy right? Wrong. A number of factors can make this a challenging business problem.</p>
<h3>Demand-Event Dependencies</h3>
<p><img alt="Home Depot Snow Blower" src="https://i0.wp.com/www.business-science.io/assets/home_depot_snow_blower.png?w=456" data-recalc-dims="1"></p>
<p>Demand for existing products are constantly changing, often dependent on non-business data (holidays, weather, and other events). Consider demand for <strong>snow blowers at <a rel="nofollow" href="http://www.homedepot.com/" target="_blank">Home Depot</a></strong>. We know that geographically-speaking in Northern USA the winter is when demand will rise and the spring is when demand will fall. However, this demand is highly dependent on the level of snowfall. If snowfall is minimal, demand will be low regardless of it being winter. Therefore, <strong>Home Depot</strong> will need to know the likelihood of snowfall to best predict demand surges and shortfalls in order to optimize inventory.</p>
<h3>Small Numbers (Anomalies)</h3>
<p>If backorders are very infrequent but highly important, it can be very difficult to predict the minority class accurately because of the imbalance between backorders to non-backorders within the data set. Accuracy for the model will look great, but the actual predictive quality may be very poor. Consider the <strong>NULL error rate</strong>, or the rate of just picking the majority class. Just picking “non-backorder” may be the same or more accurate than the model. Special strategies exist for dealing with unbalanced data, and we’ll implement SMOTE.</p>
<h3>Time Dependency</h3>
<p>Often this problem is viewed as a <a rel="nofollow" href="https://en.wikipedia.org/wiki/Cross-sectional_data" target="_blank">cross-sectional problem</a> rather than as a temporal (or time-based) problem. The problem is time can play a critical role. Just because a product sold out this time last year, will it sell out again this year? Maybe. Maybe not. Time-based demand forecasting can be necessary to augment cross-sectional binary classification.</p>
<h1>Case Study: Predicting Backorder Risk and Optimizing Profit <a></a></h1>
<h3>The Problem</h3>
<p>A hypothetical manufacturer has a data set that identifies whether or not a backorder has occurred. The challenge is to accurately predict future backorder risk using predictive analytics and machine learning and then to identify the optimal strategy for inventorying products with high backorder risk.</p>
<h3>The Data</h3>
<p>The data comes from <a rel="nofollow" href="https://www.kaggle.com/tiredgeek/predict-bo-trial" target="_blank">Kaggle’s Can You Predict Product Backorders?</a> dataset. If you have a Kaggle account, you can download the data, which includes both a training and a test set. We’ll use the training set for developing our model and the test set for determining the final accuracy of the best model.</p>
<p>The data file contains the historical data for the 8 weeks prior to the week we are trying to predict. The data were taken as weekly snapshots at the start of each week. The target (or response) is the <code>went_on_backorder</code> variable. To model and predict the target, we’ll use the other features, which include:</p>
<ul>
<li>sku – Random ID for the product</li>
<li>national_inv – Current inventory level for the part</li>
<li>lead_time – Transit time for product (if available)</li>
<li>in_transit_qty – Amount of product in transit from source</li>
<li>forecast_3_month – Forecast sales for the next 3 months</li>
<li>forecast_6_month – Forecast sales for the next 6 months</li>
<li>forecast_9_month – Forecast sales for the next 9 months</li>
<li>sales_1_month – Sales quantity for the prior 1 month time period</li>
<li>sales_3_month – Sales quantity for the prior 3 month time period</li>
<li>sales_6_month – Sales quantity for the prior 6 month time period</li>
<li>sales_9_month – Sales quantity for the prior 9 month time period</li>
<li>min_bank – Minimum recommend amount to stock</li>
<li>potential_issue – Source issue for part identified</li>
<li>pieces_past_due – Parts overdue from source</li>
<li>perf_6_month_avg – Source performance for prior 6 month period</li>
<li>perf_12_month_avg – Source performance for prior 12 month period</li>
<li>local_bo_qty – Amount of stock orders overdue</li>
<li>deck_risk – Part risk flag</li>
<li>oe_constraint – Part risk flag</li>
<li>ppap_risk – Part risk flag</li>
<li>stop_auto_buy – Part risk flag</li>
<li>rev_stop – Part risk flag</li>
<li><strong>went_on_backorder – Product actually went on backorder. This is the target value.</strong></li>
</ul>
<h1>Using Machine Learning to Predict Backorders <a></a></h1>
<h3>Libraries</h3>
<p>We’ll use the following libraries:</p>
<ul>
<li><code>tidyquant</code>: Used to quickly load the “tidyverse” (<code>dplyr</code>, <code>tidyr</code>, <code>ggplot</code>, etc) along with custom, business-report-friendly <code>ggplot</code> themes. Also great for time series analysis (not featured)</li>
<li><code>unbalanced</code>: Contains various methods for working with unbalanced data. We’ll use the <code>ubSMOTE()</code> function.</li>
<li><code>h2o</code>: Go-to package for implementing professional grade machine learning.</li>
</ul>
<p>Note on H2O: You’ll want to install the <strong>latest stable R version</strong> from <a rel="nofollow" href="https://www.h2o.ai/" target="_blank">H2O.ai</a>. If you have issues in this post, you probably did not follow these steps:</p>
<ol>
<li>Go to H2O.ai’s <a rel="nofollow" href="https://www.h2o.ai/download/" target="_blank">download page</a></li>
<li>Under H2O, select “Latest Stable Release”</li>
<li>Click on the “Install in R” tab</li>
<li>Follow instructions exactly.</li>
</ol>
<p>Load the libraries.</p>
<figure>
<pre><code data-lang="r"><span># You can install these from CRAN using install.packages()
</span><span>library</span><span>(</span><span>tidyquant</span><span>)</span><span> </span><span># Loads tidyverse and custom ggplot themes
</span><span>library</span><span>(</span><span>unbalanced</span><span>)</span><span> </span><span># Methods for dealing with unbalanced data sets
</span><span>
</span><span># Follow instructions for latest stable release, don't just install from CRAN
</span><span>library</span><span>(</span><span>h</span><span>2</span><span>o</span><span>)</span><span> </span><span>#</span><span> </span><span>Professional</span><span> </span><span>grade</span><span> </span><span>ML</span></code></pre>
</figure>
<h3>Load Data</h3>
<p>Use <code>read_csv()</code> to load the training and test data.</p>
<figure>
<pre><code data-lang="r"><span># Load training and test sets </span><span>train_raw_df</span><span> </span><span>&lt;-</span><span> </span><span>read_csv</span><span>(</span><span>&quot;data/Kaggle_Training_Dataset_v2.csv&quot;</span><span>)</span><span>
</span><span>test_raw_df</span><span> </span><span>&lt;-</span><span> </span><span>read_csv</span><span>(</span><span>&quot;data/Kaggle_Test_Dataset_v2.csv&quot;</span><span>)</span></code></pre>
</figure>
<h3>Inspect Data</h3>
<p>Inspecting the head and tail, we can get an idea of the data set. There’s some sporadic <code>NA</code> values in the “lead_time” column along with -99 values in the two supplier performance columns.</p>
<figure>
<pre><code data-lang="r"><span># Head
</span><span>train_raw_df</span><span> </span><span>%>%</span><span> </span><span>head</span><span>()</span><span> </span><span>%>%</span><span> </span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre>
</figure>
<table>
<thead>
<tr>
<th>sku</th>
<th>national_inv</th>
<th>lead_time</th>
<th>in_transit_qty</th>
<th>forecast_3_month</th>
<th>forecast_6_month</th>
<th>forecast_9_month</th>
<th>sales_1_month</th>
<th>sales_3_month</th>
<th>sales_6_month</th>
<th>sales_9_month</th>
<th>min_bank</th>
<th>potential_issue</th>
<th>pieces_past_due</th>
<th>perf_6_month_avg</th>
<th>perf_12_month_avg</th>
<th>local_bo_qty</th>
<th>deck_risk</th>
<th>oe_constraint</th>
<th>ppap_risk</th>
<th>stop_auto_buy</th>
<th>rev_stop</th>
<th>went_on_backorder</th>
</tr>
</thead>
<tbody>
<tr>
<td>1026827</td>
<td>0</td>
<td>NA</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>-99.00</td>
<td>-99.00</td>
<td>0</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1043384</td>
<td>2</td>
<td>9</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>0.99</td>
<td>0.99</td>
<td>0</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1043696</td>
<td>2</td>
<td>NA</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>-99.00</td>
<td>-99.00</td>
<td>0</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1043852</td>
<td>7</td>
<td>8</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>1</td>
<td>No</td>
<td>0</td>
<td>0.10</td>
<td>0.13</td>
<td>0</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1044048</td>
<td>8</td>
<td>NA</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>4</td>
<td>2</td>
<td>No</td>
<td>0</td>
<td>-99.00</td>
<td>-99.00</td>
<td>0</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1044198</td>
<td>13</td>
<td>8</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>0.82</td>
<td>0.87</td>
<td>0</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
</tbody>
</table>
<p>The tail shows another issue, the last row is <code>NA</code> values.</p>
<figure>
<pre><code data-lang="r"><span># Tail
</span><span>train_raw_df</span><span> </span><span>%>%</span><span> </span><span>tail</span><span>()</span><span> </span><span>%>%</span><span> </span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre>
</figure>
<table>
<thead>
<tr>
<th>sku</th>
<th>national_inv</th>
<th>lead_time</th>
<th>in_transit_qty</th>
<th>forecast_3_month</th>
<th>forecast_6_month</th>
<th>forecast_9_month</th>
<th>sales_1_month</th>
<th>sales_3_month</th>
<th>sales_6_month</th>
<th>sales_9_month</th>
<th>min_bank</th>
<th>potential_issue</th>
<th>pieces_past_due</th>
<th>perf_6_month_avg</th>
<th>perf_12_month_avg</th>
<th>local_bo_qty</th>
<th>deck_risk</th>
<th>oe_constraint</th>
<th>ppap_risk</th>
<th>stop_auto_buy</th>
<th>rev_stop</th>
<th>went_on_backorder</th>
</tr>
</thead>
<tbody>
<tr>
<td>1407754</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>10</td>
<td>10</td>
<td>10</td>
<td>0</td>
<td>5</td>
<td>7</td>
<td>7</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>0.69</td>
<td>0.69</td>
<td>5</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1373987</td>
<td>-1</td>
<td>NA</td>
<td>0</td>
<td>5</td>
<td>7</td>
<td>9</td>
<td>1</td>
<td>3</td>
<td>3</td>
<td>8</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>-99.00</td>
<td>-99.00</td>
<td>1</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1524346</td>
<td>-1</td>
<td>9</td>
<td>0</td>
<td>7</td>
<td>9</td>
<td>11</td>
<td>0</td>
<td>8</td>
<td>11</td>
<td>12</td>
<td>0</td>
<td>No</td>
<td>0</td>
<td>0.86</td>
<td>0.84</td>
<td>1</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr>
<td>1439563</td>
<td>62</td>
<td>9</td>
<td>16</td>
<td>39</td>
<td>87</td>
<td>126</td>
<td>35</td>
<td>63</td>
<td>153</td>
<td>205</td>
<td>12</td>
<td>No</td>
<td>0</td>
<td>0.86</td>
<td>0.84</td>
<td>6</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>1502009</td>
<td>19</td>
<td>4</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>2</td>
<td>7</td>
<td>12</td>
<td>20</td>
<td>1</td>
<td>No</td>
<td>0</td>
<td>0.73</td>
<td>0.78</td>
<td>1</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
</tr>
</tbody>
</table>
<p>If we take a look at the distribution of the target, <code>train_raw_df$went_on_backorder</code>, we see that the data set is <strong>severely imbalanced</strong>. We’ll need a strategy to balance the data set if we want to get maximum model performance and efficiency.</p>
<figure>
<pre><code data-lang="r"><span># Unbalanced data set
</span><span>train_raw_df</span><span>$</span><span>went_on_backorder</span><span> </span><span>%>%</span><span> </span><span>table</span><span>()</span><span> </span><span>%>%</span><span> </span><span>prop.table</span><span>()</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## .
##          No         Yes 
## 0.993309279 0.006690721</code></pre>
</figure>
<p>We can also inspect missing values. Because both the train and test sets have missing values, we’ll need to come up with a strategy to deal with them.</p>
<figure>
<pre><code data-lang="r"><span># train set: Percentage of complete cases
</span><span>train_raw_df</span><span> </span><span>%>%</span><span> </span><span>complete.cases</span><span>()</span><span> </span><span>%>%</span><span> </span><span>sum</span><span>()</span><span> </span><span>/</span><span> </span><span>nrow</span><span>(</span><span>train_raw_df</span><span>)</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## [1] 0.9402238</code></pre>
</figure>
<p>The test set has a similar distribution.</p>
<figure>
<pre><code data-lang="r"><span># test set: Percentage of complete cases
</span><span>test_raw_df</span><span> </span><span>%>%</span><span> </span><span>complete.cases</span><span>()</span><span> </span><span>%>%</span><span> </span><span>sum</span><span>()</span><span> </span><span>/</span><span> </span><span>nrow</span><span>(</span><span>test_raw_df</span><span>)</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## [1] 0.939172</code></pre>
</figure>
<p>Finally, it’s also worth taking a <code>glimpse</code> of the data. We can quickly see:</p>
<ul>
<li>We have some character columns with Yes/No values.</li>
<li>The “perf_” columns have -99 values, which reflect missing data.</li>
</ul>
<figure>
<pre><code data-lang="r"><span>glimpse</span><span>(</span><span>train_raw_df</span><span>)</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## Observations: 1,687,861
## Variables: 23
## $ sku               </code></pre></figure>