February 13, 2015

Sales Forecasting using Azure Machine Learning

Post by: Adam Widi

Adam Widi is a Senior Solution Architect in Data Analytics with over 15 years of experience in Business Intelligence and Data Analytics. In his spare time, he enjoys dabbling in machine learning and data science tools and online courses.

I was recently asked to get familiar with Microsoft’s 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 shows 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.


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.

See our Data & Analytics solutions

Relevant Insights

New Site Showcases Our Growth Into a Full-Service IT Consultancy

Over the past year, Core BTS has evolved. Simply put, we’ve amassed greater scale and expertise that, in combination with...

5 Steps to Reduce Your Ransomware Risk

As the recent ransomware attack on the U.S.’s second-largest meat producer, JBS, made clear, cyberattacks on critical infrastructure can cause...

How to Unlock the Organizational Value of Digital Transformation

As organizations look to stay competitive in today's dynamic and unpredictable marketplace, a trend has re-emerged that is ushering us...