Prioritize seasonal sales using AI

In the past, I wrote about the UEFA Euro soccer championship and used the AI Builder prediction model to predict how well the Dutch team would perform (URL to post). That made me wonder if I could find a use case to apply a custom prediction model to one of the business applications within Dynamics 365. I came up with an idea to prioritize seasonal sales using AI. In the past, I have written about things that I like, for example, soccer or padel (Padel Vamos), and to apply the seasonal component to it it was time to create a new company that operates in the BBQ business: Big Lex’s Smokehouse; Best BBQ stuff in town.

Big Lex’s Smokehouse; Best BBQ stuff in Town

These countries do not all have the same month’s Summers and Winters, which is a good start when it comes to seasonal influence. I do understand that if you are a fanatic BBQ lover you could BBQ 24/7, 365 days per year🍖. Below you will see each of the countries where Big Lex’s Smokehouse is active and the months when it would be “BBQ season”.

Prioritize seasonal sales using AI

Now the aim is to prioritize seasonal sales using AI, in this case, it will be opportunities. As an indicator of priority, I came up with the idea that the more BBQ flames🔥 you see, the higher the priority or as I would call it: the BBQ Score. The first thing that is needed is an AI Prediction Model. This AI Prediction model must be able to prioritize opportunities and should take into consideration:

• Time of the year (measured in Month)
• Weather forecast for tomorrow (based on client location)
• Type of request (Inquiretype in our scenario, either Food, BBQ, Workshop, or Accessories)

For the AI Prediction model, I will be using an AI Builder Custom prediction model. This model allows you to analyze patterns in historical data. These models will associate the pattern with outcomes. That information is being used to predict future outcomes based on defined criteria. A prediction model can be used to answer questions that are:

• Binary, so from two options
• Multiple outcomes, so from a few predefined options (in our case, Won or Lost of the Opportunity)
• Numerical prediction, the outcome is a number

The second thing that is needed, is a scheduled Power Automate Flow that updates the BBQ Score for all Open Opportunities since the Time of the Year (current month) can change as well as the Weather forecast for the day after, which potentially impacts the BBQ Score for that day.

Up until now, I have mentioned the Weather a couple of times. Since I wanted to make more impact with the weather component in my model and because of the amount of data that I have, I have transformed this into ranges that are called Weather Category which you can see below. The temperatures in this post are all in Celcius.

Preparation of the data

One of the lessons I learned when working on the other AI prediction models is that you need data, lots of data, in order to have better predictions. I have used the website Mockaroo, Thank you Megan for the tip, in order to create:

• Around 300 contact records where the Country would be randomly set
• Around 1250 Opportunities for the year 2024 where Contact and Inquiry would randomly set.

The last thing that I needed to do was to make sure that the Temperature Category and Status would match a bit (80/20 rule) for the Actual Close Date for that opportunity and the related Country. This would also mean that more opportunities would be lost for a certain country when it is non-BBQ season and vice versa. Below you will see the Excel file I was using to import the opportunities:

Below is an image of the list of opportunities for 2024 when imported into the system.

AI Builder Prediction Model

The data is sorted, and we are one step closer to being able to prioritize seasonal sales using AI. The next step is to create the AI Builder production model and this is how I have done it:

• Sign in to https://make.powerapps.com/
• Navigate to: AI Models > AI Models
• Select Prediction – Predict future outcomes from historical data
• Select Create Custom Model

The first and perhaps most important step is to determine, what you want to predict. In my scenario, I would like to predict how likely it would be to Won or Lost the Opportunity. Therefore I have selected Opportunity as the table and Status as the column and the two possible outcomes are Won or Lost.

• In the second step, you need to decide what data columns will be used to train your model. By default, a lot more columns were selected but you unselect the columns you believe are not contributing to the accuracy of the model. In my case I have selected:
  • Opportunity – Inquiry type
  • Opportunity – Month Closed
  • Opportunity – Weather Category
  • Related Contact – Country

• During the third step, you can decide if you want to filter on a subset of your data, for example, country. Since in our case I want to prioritize based on all data we select Skip this step.

• The last step is just a summary of your model, you need to provide a Name for your model at the top and Train the model

When training and publishing of your model is finished your model gets a Performance grade. This can help to evaluate your model and improve where needed. You can also see what the Most influential data of your model is based on the fields you have selected in your model.

Microsoft is giving the following guidelines on how to interpret the grade. The entire documentation about the grades and accuracy or a model, you can find on Microsoft Learn.

Grade	Guidance
A	It might still be possible to improve the model, but this is the best grade you can get.
B	The model is correct in a lot of the cases. Can it be improved? That depends on your unique circumstances, data, and requirements.
C	The model is doing slightly better than a random guess. It might be acceptable for some applications, but in most cases, this is a model that you’d continue to tweak and improve.
D	Something’s wrong. Your model is either performing worse than we’d expect a random guess to perform (underfit model). Or, it’s performing so well (at or near 100%) that you’ve probably got a data column that is directly correlated to the result (overfit model)

Power Automate Flow – Update BBQ for Open Opportunities

The next thing I needed was a Power Automate Flow that would run every night to iterate over all Open Opportunities and update the BBQ Score. Below you will see the entire Flow and below that I will highlight some of the important steps.

Get the forecast for tomorrow

For getting the weather for the Contact that is linked to the Opportunity, I am using the MSN Weather connector (link). One of the operations that you can do is based on a city, region, or country for example to get the weather forecast for tomorrow. The response will be a whole weather forecast object with all kinds of information in it. The temperature is defined as a range returning tempHi and tempLo. In this scenario, I am using the TempHi as the temperature to eventually get to a Weather Category

Compose – Round Up forecast to whole number

Since the Output of the forecast is in decimals I am using the compose statement to make it a whole number. As mentioned in the previous step, I am using the TempHi component of the response for this.

int(formatNumber(outputs('Get_the_forecast_for_tomorrow')?['body/responses/daily/tempHi'],'#'))

Condition

This would be the step that I am the least proud of in this entire configuration since I was not sure how to do this nicely. So if you do have recommendations or experience already on how to do this nicer and cleaner then please drop me a message. But I am using the Condition to check where the output of the forecast would fit in the ranges of the Weather Category. For example, the forecast for tomorrow would be 32 degrees Celcius, I would start by checking at the bottom of the Weather Category table.

is the 28 degrees Celcius Equal or Greater than 51? No then go to Else
is the 28 degrees Celcius Equal or Greater than 41? No then so to Else
is the 28 degrees Celcius Equal or Greater than 31? Yes, then I would set the Category Variable.

Predict

The AI Builder Predict connector allows you to call the AI Model that you have created. In my scenario AI Prediction – BBQ V3. For the input, I have used the following variables:

• The month of today’s date
• Country of the Contact record
• Inquiry Type of the Opportunity record
• Weather Category ID based on the forecast of tomorrow for the country of the contact

Update Opportunity with prediction

The key components of the response of the prediction need to be stored on the opportunity record and I have created two custom fields for that. The response will tell us:

• Prediction is either 1 = Won or 2 = Lost
• Likelihood = A decimal number below 1 of how certain the prediction is

Now you may wonder, Jeroen you did not set the BBQ Score with the nice flames as you showed in the beginning of the article which is the key field to prioritize😎. This is a formula field where I am using FX Formula that will calculate the number of flames based on the Prediction and likelihood. Below you see the table on how this is calculated and the FX Formula to accomplish this.

Switch(ThisRecord.'Prediction Result Today', "1", If(ThisRecord.'Prediction Score' < 1 && ThisRecord.'Prediction Score' >= 0.9, "🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥", If(ThisRecord.'Prediction Score' < 0.9 && ThisRecord.'Prediction Score' >= 0.8, "🔥🔥🔥🔥🔥🔥🔥🔥🔥", If(ThisRecord.'Prediction Score' < 0.8 && ThisRecord.'Prediction Score' >= 0.7, "🔥🔥🔥🔥🔥🔥🔥🔥", If(ThisRecord.'Prediction Score' < 0.7 && ThisRecord.'Prediction Score' >= 0.6, "🔥🔥🔥🔥🔥🔥🔥", If(ThisRecord.'Prediction Score' < 0.6 && ThisRecord.'Prediction Score' >= 0.5, "🔥"))))), "2", If(ThisRecord.'Prediction Score' < 1 && ThisRecord.'Prediction Score' >= 0.9, "🔥", If(ThisRecord.'Prediction Score' < 0.9 && ThisRecord.'Prediction Score' >= 0.8, "🔥🔥", If(ThisRecord.'Prediction Score' < 0.8 && ThisRecord.'Prediction Score' >= 0.7, "🔥🔥🔥", If(ThisRecord.'Prediction Score' < 0.7 && ThisRecord.'Prediction Score' >= 0.6, "🔥🔥🔥🔥", If(ThisRecord.'Prediction Score' < 0.6 && ThisRecord.'Prediction Score' >= 0.5, "🔥🔥🔥🔥🔥"))))))