Sales forecasting in Google Sheets

How deep learning can solve real business problems directly from your spreadsheets

This tutorial will show you how to use the full power of the Peltarion Platform in Google Sheets, one of the most frequently used business tools in the world. You will use a tabular dataset and our AI for Sheets add-on to add intelligence to your spreadsheet.

12 - Target audience: Beginners
12 - Estimated time: Setup - 10 min | Training - 10 min | Deployment - 10 min


The problem

Accurate sales forecasting is a key challenge for any business and is crucial for making informed decisions regarding sourcing, resource allocation, and planning.

Train a model to predict sales and use the Peltarion AI for Sheets add-on to use it inside Google Sheets, without having to write a single line of code!


You will learn to

  • Deploy your model and use it in Google Sheets with the AI for Sheets add-on

  • Solve a regression problem using tabulated data


The data

There are few companies that openly share revenue data. So for this tutorial, we’ve generated a synthetic dataset that looks like the real thing but is free to use.

The dataset contains daily values for many different shops over a couple of years, such as the date, national holidays, type of shop, ongoing advertisement and promotion campaigns.
The shop’s daily revenue is also given, which is what the model will learn to predict.


Preprocess the data

Open this Google spreadsheet containing the data, and make a copy that you can edit freely yourself: click on File, then Make a copy, and save it in a place that is convenient to you.

The document contains two sheets (tabs).
The Dataset sheet contains the data we’ll use to train a model. We’ll use the Test predictions sheet later to test the add-on.

You should get familiar with the data, and get a feel for how different features affect the daily revenue.
For example, a shop has 0 daily revenue when it isn’t open, but not all shops close on weekends or on a holiday.

The data in the spreadsheet is already organized in a way that can be used for training an AI model:

  • The first row contains the name of the different features, one feature per column.

  • The following rows contain the values for each feature, one row per example.

Export the data as a CSV file

Download the sheet as a CSV file by clicking on FileDownloadComma-separated values.

You have now completed the data preprocessing and can start building the model.


Create a project

Let’s begin!
Log in to the Peltarion Platform and click New project.


Import the data

In the Datasets view, click Choose files and import the CSV file you downloaded from Google Sheets, then click Done.

The appropriate feature encoding is selected automatically:

  • Categorical applies to features that can take only one of several possible values, like the store name or type.

  • Binary is similar to categorical, but applies when there are only two possible values, like whether the shop is open or closed.

Click Save version. The data is now ready to be used in an experiment.


Build the model

To begin an experiment click Use in new experiment. The Experiment wizard dialog will open.

The platform creates subsets automatically from the imported data.

  • The Training subset is used by the model to improve its predictions.

  • The Validation subset isn’t shown to the model while training. You can use it to evaluate how well the model performs on data it has never seen before.

Click Next to set up the Input(s) / target.

In the Inputs column, select everything except the Date, Year, and Revenue.

We won’t use specific time information, like Date and Year, to train the model because we want to make predictions for any future (or long past) date.
Features like the week number provide enough information about the time period, i.e., if an example is from winter or summer, while being general enough to work for any future year.

Select Revenue as the target feature, and click Next. The target is what the model will learn to predict.

Given the inputs and target selected, the wizard recommends automatically to select Tabular regression as Problem type, and selects Tabular as the Recommended snippet.

Click on Create, and the wizard will create a model that fits your tabular data.

Finally, click the Run button to start training your model. As the model trains, you can follow its performance in the Evaluation view.


Evaluate your model

The Evaluation view shows you how the model performance improves as the training progresses. The loss and metrics plot gives an overall idea of the training process. You can also check the scatter plot in the Predictions inspection tab to see predictions of specific examples.

Training will stop automatically when the model stops improving thanks to early stopping. The experiment status will change from Running to Early stopped, and you can move on to the next section to deploy your model.

In this tutorial we’ll use the base model to go forward and try the AI for Sheets add-on.
However, you can easily Duplicate your experiment to try different model configurations. The tutorial How to improve a model that uses tabular data goes into more details about how to proceed.


Deploy your model

Navigate to the Deployment view of the platform, and click on New deployment.

  • Select the Experiment that you want to deploy.
    An experiment corresponds to a particular model trained with specific settings. There should only be one available, unless you have experimented with different models in your project.

  • Select the Checkpoint you want the deployed model to use.
    Checkpoints are made throughout training. Select the Best checkpoint, since this is when the model had the best performance.

Click Enable to let your deployment go live! You can now send requests to your model from anywhere, anytime.


Integrate your model with Google Sheets

Now it’s time to deploy the model and integrate it to Google Sheets using the AI for Sheets add-on.

Fill your spreadsheet with test data

Go back to Google Sheets and open the Test predictions sheet (tab).

This sheet contains the same kind of input information that the model was trained on, but for dates in the future. This means that the model has never seen these exact combinations of promotion, holidays, advertisement, etc. while it was training, but it will make predictions based on what it has learned from the past data.

Install the Peltarion add-on

To install the Peltarion add-on to Google Sheets, click on Get add-ons under the Add-ons tab. Search for Peltarion - AI For Sheets and install the add-on.

Request predictions in Google Sheets

In your Google Sheets spreadsheet, click on the Add-ons menu, then select Peltarion - AI for Sheets, and Show sidebar.
A sidebar will appear on the right side to help you request predictions from your model.

Using the Peltarion AI for Sheets toolbar

  1. Enter credentials
    Click on Enter credentials to fill in the URL and Token from the deployment’s API information.

  2. Method
    Select Regression.
    The method is used to display the model predictions correctly in Google Sheets. Sales forecasting predicts a numerical value, which is regression problem.

  3. Enter the header range
    Fill in the range of cells in the spreadsheet that contains the name of the input features expected by the model. In our spreadsheet, the Header range is C1:K1.

  4. Enter the data range
    Fill in the range of cells in the spreadsheet that contains the value of the input features. In our spreadsheet, the Data range is C2:K100.

  5. Enter the output range
    Fill in the range of cells where the predictions from the model should go. You can use the Output range L2:L100.

  6. Predict
    Click on Predict to send the specified data to your model and fill the spreadsheet with the calculated predictions.

You can fill in a cell range easily by first selecting it on your spreadsheet, then clicking the Use selection button in the AI for Sheets toolbar.


Recap

Congratulations, you have now completed the sales forecasting tutorial! You’ve built a simple model which analyzes a large amount of tabulated data to solve a regression problem. You also used Google Sheets for an end-to-end no-code experience.

This dataset is very simple and much more can be done with the use of deep learning. Deep learning for sales forecasting could for example use combinations of complex data types such as images, text analysis and much more. To see how to solve problems using other types of data, check out our other tutorials.

Was this page helpful?
YesNo