How to Do Predictive Analysis in Looker

Your Looker dashboards are fantastic at showing what happened last week, last month, or last quarter. But what if they could help you see what’s likely to happen <em>next</em> quarter? Shifting from rearview reporting to forward-looking insights is the core of predictive analysis, and it's more accessible within Looker than you might think. This article will walk you through how to use Looker’s built-in tools and more advanced integrations to start forecasting your business's future.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is Predictive Analysis, Really?

Predictive analysis isn't about having a crystal ball. It’s about using your historical data - the sales records, website traffic logs, and customer behaviors you’ve been collecting - to make educated guesses about the future. It identifies patterns and trends from the past and projects them forward to estimate future outcomes.

For a business, this translates into powerful, actionable insights. Imagine being able to:

• Forecast Sales: Predict how much revenue you'll generate next month, helping with inventory management and budget allocation.
• Identify At-Risk Customers: Pinpoint customers whose behavior patterns suggest they are likely to churn, so you can proactively re-engage them.
• Estimate Lead Conversion: Score incoming leads based on their likelihood to convert, allowing your sales team to prioritize the hottest prospects.
• Anticipate Demand: Predict which products will be most popular during an upcoming holiday season based on past performance and market trends.

Instead of just reacting to what has already occurred, you can start making proactive decisions that shape your future success. Looker offers a couple of pathways to get you there, ranging from a simple-click solution to a more powerful, integrated approach.

Option 1: The Quick and Easy Start with Looker's Built-in Forecasting

If you have a time-series dataset (think daily sales, weekly website sessions, or monthly new users), Looker has a built-in forecasting feature that lets you add a projection to your charts in just a few clicks. It's the perfect starting point for getting a feel for predictive analysis without writing any code.

This feature uses an algorithm called ARIMA (Autoregressive Integrated Moving Average), which is well-suited for spotting trends and seasonality in time-based data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Use Looker's Forecasting Feature

The forecasting function is available directly in the visualization settings of a Look or an Explore. Here’s a step-by-step guide:

1. Start with a Time-Series Look: Build a visualization that shows a metric over time. This needs a date or time dimension on the x-axis that is filled (meaning there are no gaps in your dates). A line chart showing 'Orders per Week' or a column chart of 'Revenue per Month' are perfect examples.
2. Open the Visualization Settings: Click the gear icon in the top-right corner of the visualization pane to open the settings menu.
3. Navigate to the 'Forecast' Tab: Along the top of the settings menu, you'll see tabs like 'Plot', 'Series', 'Y', etc. Click on the 'Forecast' tab (note: this tab will only appear if your data is eligible for forecasting).
4. Configure Your Forecast: You'll see several options to customize your prediction.
5. Run and Visualize: Once you've set your parameters, click 'Run'. Your visualization will now display the historical data along with a dotted line extending into the future, showing your forecast.

When to Use This Method

Looker's built-in forecasting is fast, intuitive, and great for initial exploration. Use it for straightforward trend analysis on single metrics where you need a quick, directional estimate. However, it's not designed for complex, multivariate analysis where multiple factors influence an outcome.

Option 2: Supercharge Your Predictions with BigQuery ML

When you need more power and accuracy, integrating Looker with BigQuery Machine Learning (BQML) is the way to go. This approach lets you build, train, and run sophisticated machine learning models directly within your data warehouse using basic SQL commands. You don’t need to be a data scientist or have deep knowledge of Python to use it.

Once your model makes predictions in BigQuery, you can pull that data directly into Looker - creating a seamless workflow and incredibly powerful dashboards.

Here’s the general process of creating a sales forecast in BigQuery and visualizing it in Looker.

Step 1: Create a Forecasting Model in BigQuery

First, you'll write a simple SQL query in your BigQuery console to create a model. This query tells BigQuery what historical data to learn from and what type of model to build. For time-series forecasting, ARIMA_PLUS is the go-to model type.

Imagine you have a table called daily_sales with sale_date and total_revenue columns. Your BQML model creation query would look something like this:

CREATE OR REPLACE MODEL `your_project.your_dataset.sales_forecast_model`
OPTIONS(
  model_type='ARIMA_PLUS',
  time_series_timestamp_col='sale_date',
  time_series_data_col='total_revenue',
  horizon=60 /* we want to forecast 60 days of data */
) AS
SELECT
  sale_date,
  SUM(total_revenue) AS total_revenue
FROM `your_project.your_dataset.daily_sales`
GROUP BY 1
ORDER BY 1,

This SQL tells BigQuery: "Create a time-series (ARIMA_PLUS) model named sales_forecast_model by looking at the total_revenue and sale_date from our daily_sales table." You only run this once to create and train the model.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Generate the Forecast Data

Once your model is trained, you can use the ML.FORECAST function to generate the future data points. This query asks your newly created model to predict the next 60 days of revenue.

SELECT *
FROM
  ML.FORECAST(MODEL `your_project.your_dataset.sales_forecast_model`,
              STRUCT(60 AS horizon, 0.9 AS confidence_level)),

Running this query returns a table with future dates, the forecasted revenue for each day, and the upper and lower bounds of the prediction interval. You now have a concrete table of future data!

Step 3: Connect the Forecast Data to Looker

Now, let's bring those predictions into Looker so we can visualize them. The best way to do this is by creating a 'derived table' in your LookML project. A derived table lets you treat the results of a SQL query as if it were a regular database table.

In your LookML model, you would create a new view file for your forecast data. The LookML would contain the ML.FORECAST query you just wrote:

view: sales_forecast {
  derived_table: {
    sql:
        SELECT
        forecast_timestamp as forecast_date,
        forecast_value as forecasted_revenue,
        prediction_interval_lower_bound,
        prediction_interval_upper_bound
        FROM
        ML.FORECAST(MODEL `your_project.your_dataset.sales_forecast_model`,
                      STRUCT(60 AS horizon, 0.9 AS confidence_level)),
        ,,
  }

  dimension_group: forecast_date {
    type: time
    timeframes: [raw, date, week, month]
    sql: ${TABLE}.forecast_date ,,
  }

  dimension: forecasted_revenue {
    type: number
    sql: ${TABLE}.forecasted_revenue ,,
  }

  // add dimensions for upper and lower bounds as needed
}

After creating this view, you can join it into your main sales Explore in the model file. This lets you analyze your historical sales data and your forecasted sales data together in one place.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Visualize Your Historical and Future Data Together

With the BQML predictions available as a view in your Explore, you can now build a truly insightful visualization. Create a line chart that shows your actual, historical sales revenue. Then, add the forecasted_revenue measure from your new sales_forecast view.

The result is a single chart showing a solid line for what has already happened and a connected line showing what your BQML model predicts will happen next. You’ve now gone beyond simple reporting and created a powerful planning tool right inside Looker.

Final Thoughts

By moving beyond historical analytics, you can transform your Looker dashboards from backward-looking reports into strategic tools for future growth. Whether you are using Looker's accessible built-in feature for a quick trendline or integrating with BigQuery ML for more robust predictions, you have the ability to make more proactive, data-informed decisions that give your business a competitive edge.

Creating predictive models often means pulling together data from a dozen different places like Google Analytics, your ad platforms, your CRM, and your e-commerce store. At Graphed, we handle all that complexity for you. We connect all your marketing and sales sources in one click and provide an AI data analyst that can build real-time dashboards and reports from simple, natural language prompts. This allows you to focus on the insights from your forecasts instead of the tedious work of preparing the data.