Adding Forecasting to your Looker Reports and Dashboards

A new feature introduced in Looker recently is Forecasting, giving you the ability now to forecast forward one or more measures in your look or dashboard based on historical data from your application or data warehouse.

Forecasting is a Looker Labs feature that has to be enabled by your Looker administrator before you can use it, and like all Labs features it could change or even not make it through to a future Looker release.

That said, let’s take a look at how it works by using it to forecast future revenue for our company sales dashboard, that as a starting point shows revenue for the current year from January through to October, the month that we have complete numbers for.

To create my first forecast of revenue for the next three months I start by pressing the Forecast button in the visualization’s menu bar, then select the measure I wish to forecast and the number of months I wish to forecast that measure over.

Forecasts are typically expressed as a value with upper and lower bounds together with a confidence level in the prediction being within those bounds; Looker uses the AutoRegressive Integrated Moving Average (ARIMA) algorithm with a Prediction Interval setting that sets the confidence level of the prediction, giving you the ability to trade-off preciseness and confidence in those precise numbers being predicted by the algorithm.

For now I’ll leave this and other settings at their default value and press Run to view the results of the forecast. As you can see from the screenshot below, based on the ten months of revenue number for the year to-date, Looker is forecasting consistent numbers for what’s left of this year and the start of next.

Taking the Confidence Interval up to its maximum 99% and then down to the minimum 80% increases and decreases the gap between upper and lower forecast bounds, but the actual revenue numbers forecasted stay the same.

However, I know that year-end and start of the New Year are often the slowest months for us and I’d expect revenue to drop, rather than stay constant, over the next three months of November, December and January.

I know I’ve got three years of revenue data in my data warehouse, so I extend the time window for the look to now bring-in a further two years of data, and enable the Seasonality setting so that the forecast algorithm accounts for known cycles or repetitive data trends when producing my forecast.

Now my forecast reflects the historic trends and seasonal cycles of increased and decreased demand that we’ve seen since starting the business three years ago. Looker’s forecasting algorithm now predicts a drop in revenue over the next three months, in-line with our team’s expectations.

To make it easier to focus on just this year’s numbers, I use the Limit Displayed Rows option in the visualization Settings menu to display just the ten actual months and three forecasted months in the look’s chart.

Or I can switch the visualization type from line to bar chart, or one of the other visualization types that supports the Forecasting feature, if this would be a better way of communicating the range of values that the forecasting model has predicted for us.

Finally, as our Looker model contains target as well as actuals and forecast revenue numbers, I use the merged results feature to create and then overlay target data onto the visualization so that I can compare our predicted numbers against the targets we’d set for the business.

If you’re trying this with your own target data, make sure you choose the targets query as the primary source query for the merge as otherwise your target data will also stop in October, the limit of your actuals data, rather than extend over the additional three month forecast period.

Interested? Find out More

Rittman Analytics is an official Looker Consulting Partner (we’re hiring!) with a team of certified LookML developers who can get you started with Looker, centralise your data sources and enable your end-users and data team with best practices and a modern analytics workflow.

If the idea of adding revenue and other forecasted data into your Looker dashboards sounds interesting to you, or you’re looking for some help and assistance building-out your new customer data analytics platform on a modern, flexible and modular data stack, contact us now to organize a 100%-free, no-obligation call — we’d love to hear from you!