[MUSIC PLAYING]
POLONG LIN: In this video, I'll show you
how to build and deploy a demand forecasting
solution with BigQuery ML.
My name is Polong Lin.
And I'm a Developer Advocate for Google Cloud.
Demand forecasting can apply to many different use cases--
for example, predicting ticket sales in the transportation
industry to media, online gaming, or telecommunications--
when you want to predict whether you
might need to allocate more resources in order to meet
demand.
And, of course, for retail businesses,
there's a Goldilocks problem when it comes to inventory.
Don't stock too little because this could mean
a loss of potential revenue.
But don't stock too much because that could be costly, too,
with a surplus of goods taking up precious space
with a limited lifespan.
So with potentially millions of products for our data science
and engineering team to create multimillions of forecasts
is one thing.
But to also procure and manage the infrastructure
to handle an end-to-end model training and forecasting
solution, this can quickly become overwhelming, especially
for large businesses.
With BigQuery ML, without needing to move your data out
of BigQuery, you can train and deploy machine
learning models directly using SQL.
That means you get your data storage, data analytics,
and machine learning all built into BigQuery.
So in this video, we'll cover how
to prepare the training data in BigQuery,
train and evaluate a time series model with BigQuery ML,
visualize the forecasts in a dashboard,
and then schedule and automate model retraining.
Let's start with the training data.
We can use this public data set available on BigQuery on liquor
sales to forecast demand.
This data set contains transactional information
on when liquor products like whiskey, rye, rum,
and other kinds of liquor are sold in the state of Iowa
in the United States and how many bottles were sold
in that particular transaction.
So we can forecast the total demand
for each of these products over time across all the stores.
The first step is to prepare the data in the right shape
so we can use this as the training data for the time
series model.
So here, I've selected three columns--
the date, the item name, and the sum
or the total of the amount of each product that
was sold for that date across all transactions.
And here, I'm selecting a year and a half's worth
of data between 2016 and 2017.
You may also notice in the data that there's actually
some dates missing.
And that's OK because BigQuery ML
will impute any values for those missing dates
for you automatically using local linear interpolation.
The links to all the SQL code used
in this video and other reference patterns
are in the video description below.
If you were to plot this, you can
see the daily historical demand over time--
for example, with these two products, Black Velvet,
which is a Canadian whiskey, and Captain Morgan Spiced Rum,
which is a blend of Caribbean rums and some spices.
Our goal here would be to try to create a time series
model to forecast future demand for the next 30 days.
Now, let's look at creating the time series model in SQL
with BigQuery ML.
To form our query to train the time series model with BigQuery
ML, we first need to select the data, which I've
put in a training data table.
Next, we want to create or replace model
followed by the data set dot model name,
which, in this case, I've decided to call this model
forecast_by_product.
Next, we need to specify the model type,
which is the ARIMA-based model, and then specify the three
columns in our data--
the timestamp column, which is our date,
the data column, which is the total amount
of the product sold, and the ID column, which is the item name.
Just as a note, this ID column allows
us to train multiple time series models
in a single query, one for every item name in our data.
So we don't have to write a separate query
to train a separate model for each of these products.
Another thing to note is you can also set the holiday region
so that the model can account for potential holiday effects--
for example, global or regions like
NA for North America or country codes
like US for United States.
You can find the full list of regions in the Create Model
documentation for BigQuery ML.
And just know that, to take advantage of holiday effects,
your data must have at least one year's worth of daily data.
So with this one Create Model Query,
as you run this, for each item, BigQuery ML
will try to find the best time series model by first creating
up to 42 candidate models.
And amongst those candidate models,
it automatically selects the best model
for you using Auto ARIMA.
And that's all within a single query.
And if you want to know more about what
happens behind the scenes when building a time series
model in BigQuery ML, stay tuned till the end
of the video when I cover all the various bells and whistles.
Next, let's look at model evaluation.
So once the model is created, you
can see it shows up in my Iowa liquor data set here,
as forecast_by_product, which is the name of my model.
And I can click on the model and navigate
to the Evaluation tab, where I can
see the metrics for the models.
So with five items, we have five rows or five models.
And for each of these items, the corresponding model
is the best model that was selected
by BigQuery ML based on Auto ARIMA, which
I mentioned earlier.
So we have the P, D, Q, and whether or not
these models have drift, the log likelihood, the AIC,
the variance, and seasonality.
And for seasonal periods, this can
be any combination of daily, weekly, monthly, quarterly,
yearly, or no seasonality.
And in these cases with these items,
only weekly seasonality was detected by BigQuery ML.
And in SQL, it's SELECT star FROM ML.EVALUATE MODEL and then
your model name.
Next is using the model to make your predictions
on future dates.
You can make your forecasts using ML.FORECAST.
And I'll show you the SQL query in a bit.
And ML.FORECAST will give you future timestamps,
the forecasted value and its standard error,
the confidence level, which I've set to 0.90 here,
and the upper and lower prediction interval bounds.
So by adding this to the historical data,
you can plot this on a graph and compare
between the forecasted values and the actual values
if you have them.
You can see, for the most part, the actual values
fell within the 90% confidence interval.
To use the model to make your forecasts in SQL,
you can use SELECT star FROM ML.FORECAST, the model name,
and then pass into important values
the horizon, which is the number of time points
to predict in the future up to a maximum of 1,000 data points.
And in this case, given daily data,
we want to use a value of 30 for one month worth of forecast
values and the confidence level, which
is the percentage of the future values that fall
in the prediction interval.
Just to make the confidence level a little easier
to understand, here are three forecasts
at three different confidence intervals for the same model.
At 0.8 or 80%, your confidence intervals
are tighter or closer to the forecasted values.
So that buffer is narrower.
At 0.99 or 99% confidence interval, they're much looser.
So you can really be confident that, whatever the actual value
will be, it will likely fall within this confidence
interval.
Note that, regardless of the confidence interval
level that you choose, the forecasted values
will remain the same.
So how you choose your confidence interval
is entirely up to you and your use case.
Do you want to be very, very certain
that the actual demand will fall within your predicted ranges?
Then maybe offer a higher confidence interval.
Or if you want to really have slightly narrower predicted
ranges to help you better optimize your products
or resources, then maybe opt for something lower, like 0.80.
Now, you may want to visualize your forecasts
in some sort of interactive dashboard
that you can share with others--
for example, with Data Studio.
Regardless of the visualization tool,
you may want to consider preparing
the data for visualization by combining your historical data
with the forecasted values and intervals into the same table
or view so it's easier to use with your dashboard.
To do so, you can use UNION ALL between your historical data
that you select and your forecasted values
and prediction intervals.
Just noting that you'll need to create
null values for forecasted data when selecting
your historical data and null values for historical values
when selecting your ML.FORECAST data, as well.
That way, you're using UNION ALL with the same schema.
Here is an example of a dashboard
that I created using Data Studio where it's interactive
and I can see the forecasted values and the intervals.
And I can also drill down into the individual time series
for each of the products.
So effectively, this dashboard can now
be used to predict future sales so
that you can prepare and optimize your stock and/or
price levels.
If you're like many businesses that
need to create fresh time series forecasts based on the most
recent data, you can use scheduled queries
to automatically rerun your SQL queries, which
can include your Create Model, ML.EVALUATE, or ML.FORECAST
queries as examples.
You can set how often you want to rerun them,
if you want to start them on a certain day
and end on a certain day and even send out
email notifications when they're done
or send a pub/sub message to trigger something
downstream, as well.
To create a scheduled query, you can simply click on Schedule
and Create New Scheduled Query.
And once you've created the scheduled query job,
it'll show up in your Scheduled Queries page.
Now, at this time, I want to shed some light
on what's happening behind the scenes
when you train a time series model in BigQuery ML.
When you train a time series model with BigQuery ML,
multiple components are used in the model creation
pipeline with ARIMA being one of the core algorithms.
But there are other components used, as well.
So here, they are roughly listed in the order that they are run.
So the first step is preprocessing.
And this means automatic cleaning
of any issues with your input time series.
So this can include missing values which are imputed.
This could include deduplication of timestamps.
And if you have any spike anomalies or any kind
of abrupt changes in your time series data,
then it's identified at this stage.
The next stage is dealing with holiday effects.
Holiday effects are disabled by default.
And when they are disabled, any kinds of spikes or dips
that may occur due to holidays become
treated as anomalies, which may not be ideal
if you want the model to treat these days as kind
of holiday behavior.
So to enable holiday effects, set the holiday region
you want in your Create Model code.
And make sure you have the prerequisite of at least
one year's worth of daily data.
Next, BigQuery ML will try to extract or detect
the seasonality and trend in the time series data.
And in the final stage, BigQuery ML
uses the ARIMA model and the Auto ARIMA algorithm
for hyper-parameter tuning.
Up to 42 candidate models are trained and evaluated
in parallel.
And you get the best model based on the lowest AIC score.
[MUSIC PLAYING]
So now you should know how to build and deploy
a demand forecasting solution with BigQuery ML.
Thanks for watching.