How to Forecast Revenue in Looker

Building a reliable revenue forecast can feel like trying to predict the weather - you’re working with historical data, but unexpected factors can change everything. Creating that forecast in a complex BI tool like Looker adds another layer of challenge. This article will guide you through the practical steps to build accurate, dynamic revenue forecasts directly within Looker, moving you from manual spreadsheet work to automated, real-time insights.

Why Forecast Revenue in Looker?

You’re likely already familiar with the forecasting ritual: export data from your CRM or sales platform, drop it into Excel or Google Sheets, and start wrangling formulas. While this works, it’s a manual process plagued by stale data and version control issues. By the time you present the report, the numbers are already outdated.

Forecasting in Looker shifts this process from a static task into a dynamic, integrated part of your analytics workflow. Here’s why it’s better:

Centralized & Live Data: Your forecast is built on the same live, trusted data your entire organization uses. When a new sale closes in Salesforce or a transaction clears in Stripe, your model can reflect it immediately.
Automation & Scalability: Once built, your Looker forecast updates automatically. No more downloading CSVs every Monday morning. You build it once and let it run, freeing up your time for analysis instead of data entry.
Collaboration: Rather than emailing spreadsheets back and forth, you can share a single, interactive dashboard. Stakeholders can slice and dice the data, drill down into specific segments, and get answers to their own follow-up questions in real-time.

Prerequisites: Preparing Your Data for Forecasting

A forecast is only as reliable as the data it’s built on. Before you can predict the future, you need a clean, consistent record of the past. For revenue forecasting, this means having well-structured time-series data.

In your LookML model, you'll want to ensure you have a primary view - often called something like orders, transactions, or deals - with at least two key fields:

A timestamp dimension (e.g., created_at, order_date, close_date).
A numeric measure for revenue (e.g., sale_price, order_total, deal_amount).

Your data engineers or LookML developers should ensure this data is clean and consistent. Common preparation steps include:

Consistent Time Granularity: Ensure your timestamp field can be cleanly bucketed into days, weeks, or months using Looker's timeframes.
Handling Nulls: Decide on a strategy for orders or canceled deals. Should they be excluded entirely? Or should their revenue be set to zero? Consistency is crucial.
Joining Relevant Data: To build more sophisticated forecasts, you might join your sales data with other sources. For example, joining Google Ads data could help you forecast revenue based on marketing spend.

Once you have a clean, time-based dataset ready in a Looker Explore, you can start building the forecast itself.

Method 1: A Simple Forecast with Linear Regression

The simplest way to forecast is to project a trend from your historical data. Linear regression is a statistical method that finds the straight line that best fits your past performance and extends it into the future. It’s perfect for businesses with relatively steady growth.

In Looker, you can accomplish this without complex SQL by using Table Calculations. Let’s build a 12-month revenue forecast step-by-step.

Step-by-Step Guide

1. Set Up Your Explore

Start in the Explore that contains your historical order data. Select two fields:

Your revenue measure (e.g., Orders Total Gross Margin).
Your time dimension, filtered to the desired granularity (e.g., Orders Created Month).

Filter your data to a relevant historical period, like the last 24 or 36 months, to give the model enough data to establish a trend. Run the query.

2. Create Table Calculations for the Regression

Now, we'll use a series of table calculations to generate the forecast. Click Add calculation in the Data bar.

A. Create a Numeric Index for Time (Month Number)

Linear regression works with numbers, not dates. First, create a calculation to convert each month into a sequential number (1, 2, 3, etc.).

Name this calculation Month Number.

B. Calculate the Forecast (Forecasted Revenue)

This is the core of the forecast. We use Looker Functions slope() and intercept() to find the line of best fit for your historical revenue and project it forward.

This formula checks if a row has historical revenue or is right next to a row with revenue. If so, it calculates the forecasted value for that point in time. Future rows will now be populated with your forecasted revenue.

3. Visualize the Forecast

Switch to the Visualization tab and choose a Line Chart. You’ll see two lines: one for your actual historical revenue and another for the combined actual + forecasted revenue trendline. To make this clearer:

Hide the Month Number calculation from the visualization.
Customize the series colors and labels to clearly distinguish between “Actual Revenue” and “Forecasted Revenue.”

You now have a simple, dynamic revenue forecast that updates automatically as new data comes in.

Method 2: Incorporating Seasonality with Moving Averages

Linear regression is great, but it assumes growth is consistent. What if your business has predictable peaks and valleys? Maybe you're a retailer who gets a huge sales bump every November and December, or a B2B SaaS company that sees a dip every summer. A straight line won't capture this reality.

This is where incorporating seasonality becomes essential. We can do this in Looker by calculating a seasonal index based on a moving average. The process is a bit more involved, but it produces a much more accurate forecast for seasonal businesses.

Step-by-Step Guide

Start with the same Explore as before: your revenue measure grouped by month.

1. Calculate a Moving Average

A moving average smooths out the fluctuations in your data, helping you see the underlying trend more clearly. A 12-month moving average is common for modeling annual seasonality.

Create a table calculation named 12 Month Moving Average:

This calculates the average revenue over the preceding 12 months for each data point.

2. Determine the Seasonal Index

The seasonal index shows how a particular month's performance typically compares to the annual average. An index of 1.2 means that month performs 20% better than average, while 0.9 means it’s 10% below average.

Create a Seasonal Index table calculation:

3. Calculate the Average Index for Each Month

Next, you'll need to figure out the typical seasonal index for each calendar month (e.g., what’s the average index for all past Januarys?).

First, create a Month Name calculation for grouping:

Then, calculate the Average Monthly Index:

To make this work, pivot your Explore on the Month Name field. Now, every row for January will show the average seasonal index across all historical Januarys.

4. Forecast the Base Trend

Use the linear regression method from before, but this time, apply it to your smoothed 12 Month Moving Average instead of the raw revenue. This gives you a more stable trend line.

Create a Base Forecast calculation on your moving average data:

This projects the underlying growth trend, ignoring the seasonal ups and downs for now.

5. Apply Seasonality to the Forecast

Finally, combine the trend with the seasonality. The final Seasonal Forecast multiplies your base forecast by the appropriate seasonal index for that month.

When you visualize this, you’ll see a forecast that not only projects future growth but also mirrors the seasonal patterns you've historically seen, making it far more reliable.

Tips for Better Forecasting in Looker

No matter which method you choose, a few best practices will improve the quality of your forecasts.

Use Enough Historical Data: Your model needs data to learn from. For forecasting monthly revenue, aim for at least 24 months of clean historical data. For seasonal forecasts, 36 months is even better.
Create Scenarios: A single-line forecast is useful, but business reality is rarely so certain. Create "Optimistic," "Pessimistic," and "Realistic" scenarios by tweaking your forecasting formula. You can do this by adding a multiplier as a custom field or a static value in a table calculation (e.g., multiplying your final forecast by 1.2 for an optimistic case).
Validate and Iterate: A forecast is a living document. At the end of each month, compare your forecasted revenue with the actual results. If they’re far apart, investigate why. Did a large, unexpected deal close? Was there a marketing campaign you hadn't accounted for? Use these insights to refine your model. Looker's ability to overlay actuals on top of forecasts makes this validation process simple.

Final Thoughts

Moving your revenue forecasting from spreadsheets to Looker automates a time-consuming business task and transforms it into a dynamic, reliable strategic tool. By leveraging table calculations for linear regression or seasonal models, you can create forecasts that adapt with your business and provide a clear view of where you're headed.

Of course, while Looker is incredibly powerful, mastering its table calculations and LookML structure takes time. We built Graphed to bridge this gap. You can connect all your sales and marketing data sources in minutes, then simply ask in plain English to build your reports and dashboards - no complex functions required. Instead of hours spent writing complex formulas, you can get AI-powered insights and create a clear forecast in seconds, giving you back time to focus on what the numbers actually mean for your business.