Back to List

Sales Forecasting using Azure Machine Learning

Adam Widi Adam Widi  |  
Feb 13, 2015
 
I was recently asked to get familiar with Microsoft’s new cloud based predictive analytics tool called Azure Machine Learning. Azure Machine Learning (ML) is currently in preview and Microsoft has made it Free and Easy to get started. Go to http://azure.microsoft.com/en-us/services/machine-learning/ and click “Get started now”. My goal: to see how we can leverage this technology with our clients. After reading about the topic and watching a few videos, I was ready to dive in. 

So, where do I start? Luckily I have a great client that approved a proof of concept. On a past project, I helped them build a forecasting tool. This is a web application written in C# that region managers use to enter their sales forecasts for the next 3 months. Wouldn’t it be great if we could use their historical sales information to make this prediction for them, and remove this task from their workload? 
 

Getting Started

Now that I have a general understanding of Machine Learning, and a concept to try, I’m ready to get started. ML studio comes loaded with many different samples. They include models to predict credit risk, customer churn, flight delays, and many more. I chose the hourly bike rentals sample as my starting point. This uses previous rental history, and weather data to predict future rental demand. I first reviewed the dataset in the sample, and created a similar dataset based on my customer’s sales data that includes a row for each region and month for the past 2 years. For each row, I include the actual value for that month (this is what I hope to predict), as well as columns containing the actual value for each of the prior 12 months (to help the algorithm make a prediction). I made a few minor adjustments to the sample to work with my dataset and in a short amount of time I was able to have ML make a prediction.  

Figure 1 shows the results of that first experiment:
 
Figure 1: Predictions using Boosted Decision Tree Regression
 

Expanding the Experiment

Well, that’s a start, but how do I know if the predictions are any good? We obviously want to make the best prediction possible. Azure ML allows you to include multiple prediction scenarios in the same experiment and components to easily compare the results.  It’s time to expand the initial experiment.  

Here are a few things we should do:  
 
  1. Adjust the amount of data used to make the predictions. Since I know that our data over 12 months varies greatly from month to month, maybe limiting the data to the last 3 or 6 months will actually result in better predictions.  
  2. Include the manual forecast data in the experiment so we can compare how the machine learned forecast performs vs the manual forecast.
  3. Consider adding supplemental data into the experiment. For instance, in the bike rental sample, they include weather data since the weather will likely impact the number of bike rentals on a given day. In my case, I added sales opportunity data into my experiment. It makes sense that the number of sales opportunities would likely be a key factor in sales.  
  4. Try different prediction algorithms.  
  5. Evaluate the predictions to see how close they come to the actual values.

Figure 2 show my expanded experiment. If you can’t quite make out what’s going on, don’t worry, it’s not as complicated as it looks, and I’ll split it up and explain. 
 

Figure 2: The expanded experiment
 

Step 1: Gather and Transform the data.


Figure 3: Preparing data for the experiment 

The first thing to do in an experiment is to prepare the data for analysis. Azure ML provides many tools to do this. Being new to ML and since this is a proof-of-concept project, I chose to do most of the work in excel and just upload .csv files to my workspace.
 
  1. ForecastPOC_Actuals_Trial2.csv: Contains a row for the actual sales value for each region for each month since January 2013. For each region/month combination we also include the prior 12 months of actual sales.  
  2. ForecastPOC_ManualForecast.csv: Contains a row for each manually forecasted sales value for each region and month since January 2013. This also contains the historical opportunity values.
  3. Join: Combines the 2 datasets into 1 based on region and month.
  4. Metadata Editor: Converts Year and Month to categorical columns.
 

Step 2: Train and Score an analytical model.

Figure 4: One of the seven training scenarios

Next we use ML to analyze the data and train a model to make predictions. 
 
  1. Project Columns: Include the columns that we want to use for this training scenario. In figure 4 above, we include the prior 12 months.
  2. Missing Value Scrubber: If any row contains missing values, we remove the entire row. We only want to include a complete set of data to base our predictions on.
  3. Split: Partition the data into a training set and test data set. In our case we train the model using Jan/2013-Sept/2014 actuals data, and test the model using Oct/2014 actuals.
  4. Decision Forest Regression: The algorithm used for this training scenario.
  5. Project Columns: Our dataset includes a Year, Month, and Year_Month columns. The Year_Month was used for the split, so we want to exclude it in the training since its meaning is redundant with the other 2 columns.
  6. Train Model: Creates a trained ML model to predict the actual value based on the prior 12 months of actuals using the Decision Forest Regression algorithm.
  7. Score Model: Uses the trained ML model to predict Oct/2014 values.

This step is duplicated several times. We use different sets of columns and different algorithms so that we can compare the results and pick the best performing model. To do this I simply select the 7 components, copy/paste them, and adjust the first “Project Columns” to include the columns I want.

Some of the column sets that I used are: Prior months 1-12 actuals, Prior months 1-6 actuals, Prior months 1-3 actuals, Prior months 1, 2 & 12 actuals, Opportunities for the month, Prior month actuals & opportunities for the month, and finally the manual forecast values. I also tried Boosted Decision Tree Regression, Decision Forest Regression, and Bayesian Linear Regression algorithms.

The last scenario is a little different and is used to create a scored dataset with the manual forecast values so that we can compare the ML forecast scenarios to the manual forecast. This took a little “trickery” that I won’t go into detail here (since there is likely a better way). If you’re curious, I posted a question to the ML forum that I later answered here.
 

Step 3: Evaluate and compare the different models.

Figure 5: Evaluating the results

Finally we want to evaluate the different models and combine the results for review.
 
  1. Evaluate Model: Compares the scored model’s predicted values to the actual values to determine how well the model performs.
  2. Add Rows: Since the Evaluate Model component can only evaluate 1 or 2 models, the Add Rows component combines the results into a single dataset.
  3. Execute R Script: Adds a “prediction method” description column to the results.
 

Review the Results:

Figure 6: Visualize Results

After running the experiment, you can view the results of any component by right-clicking on the output port and choosing “Visualize”. Doing this on the “Score Model” component will show the predictions made by the model as we saw in figure 1 above. Doing this on an “Evaluate Model” component will show how well the model performed.  

Figure 7 shows how my best performing prediction methods compare to the actuals for October 2014. Let’s look at the Mean Absolute Error column. This shows the average difference between the predicted value, and the actual value. As you can see, as I adjusted the datasets I continued to improve the predictions. I also have several models that (at least for October 2014) performed better than the manual forecast

 
Figure 7: Combined Evaluate Model Results
 

Next Steps:

Now that we have a prediction model that we can use, Azure ML makes it easy to operationalize. This is done by creating a second experiment containing only the best performing model. You take this experiment and with a few clicks, can publish it as a web service to use within an application.
 

Summary:

There is a lot of excitement about this new Azure Machine Learning service. I’m definitely excited to continue my learning and start helping clients use their data to help improve their business. Hopefully I’ve removed a bit of the mystery and convinced you to give it a try.
 
AgileBusiness Intelligence

 

Love our Blogs?

Sign up to get notified of new Skyline posts.

 


Related Content


Blog Article
What is Microsoft’s Power Platform and How to Use It: The Guide
Skyline Technologies  |  
Jan 14, 2020
In this guide, Libby Fisette (Director of Skyline Modern Workplace team) and Marcus Radue (Data Analytics Engineer), dig into the functionality of the Microsoft Power Platform and how you can leverage this toolset to solve many business situations. From basics to key questions, you will find...
Blog Article
3 Agile Transformation Pain Points Managers and Executives Face
Rachael WilterdinkRachael Wilterdink  |  
Jan 07, 2020
In this blog, Rachael Wilterdink, CBAP®, PMI-PBA®, CSM®, PSM, pulls from her extensive experience to guide professionals along their agile journey. For a full overview on this topic, download her eBook: 20 Advanced Agile Transformation Pain Points.   As a Business Analyst, I...
Blog Article
Realtime and Near-Realtime Data Sources and Data Integrity
Matt PlusterMatt Pluster  |  
Dec 17, 2019
In this blog series, Matt Pluster, Director of Skyline’s Data Analytics Consulting Practice, explores data sources and processing options. For a full overview on this topic, check out the Realtime vs Near-Realtime webinar.   In previous blogs in this series, I dug into advantages...
Blog Article
Mitigating the Risks of Realtime or Near-Realtime Data Processing
Matt PlusterMatt Pluster  |  
Dec 10, 2019
In this blog series, Matt Pluster, Director of Skyline’s Data Analytics Consulting Practice, explores data sources and processing options. For a full overview on this topic, check out the Realtime vs Near-Realtime webinar.   In previous blogs in this series, I’ve talked about...
Blog Article
“The Other Realtime”: Low-Latency Data Processing via DirectQuery
Matt PlusterMatt Pluster  |  
Dec 03, 2019
In this blog series, Matt Pluster, Director of Skyline’s Data Analytics Consulting Practice, explores data sources and processing options. For a full overview on this topic, check out the Realtime vs Near-Realtime webinar.    So far in this blog series, we have talked...