Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Do Regression Analysis in Looker

Cody Schneider••8 min read

Building dashboards that show what happened is useful, but what if you could use your business data to predict what will happen? That's the power of regression analysis, a statistical technique that helps you understand and quantify the relationships between different variables. This article will show you exactly how to perform regression analysis directly within Looker, unlocking predictive insights without ever having to export a CSV file.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

What is Regression Analysis? A Quick Refresher

At its core, regression analysis helps you understand how a dependent variable (the thing you want to predict) changes when you change one or more independent variables (the factors you think have an influence).

Imagine you run an e-commerce store. You might want to ask questions like:

  • "If I increase my Facebook Ads budget by $1,000, how much will my weekly sales increase?"
  • "How many new website sessions do we get for every new blog post we publish?"
  • "What is the relationship between a customer's time on site and their average order value?"

Regression analysis gives you a model to answer these kinds of questions. The variable you’re trying to predict (e.g., weekly sales) is the dependent variable. The variables you’re using to make the prediction (e.g., Facebook Ads budget, number of blog posts) are the independent variables.

There are different types, but the most common in a business setting is linear regression:

  • Simple Linear Regression: Uses one independent variable to explain one dependent variable (e.g., Ad Spend -> Sales).
  • Multiple Linear Regression: Uses two or more independent variables to explain one dependent variable (e.g., Ad Spend + Website Sessions + Email Subscribers -> Sales).

By fitting a "line of best fit" to your historical data, you can see the strength of these relationships and use the model to forecast future outcomes.

Why Do Regression Analysis in Looker?

You could do this analysis by exporting data to a Python notebook, RStudio, or even a specialized spreadsheet. But performing it directly within Looker has some major advantages:

  • Your Data is Already There: No more exporting stale data. Looker connects directly to your live database, so your analysis is always based on the most current information.
  • Centralized & Collaborative: The analysis lives within your company's single source of truth. Dashboards and visualizations using the regression model can be easily shared with team members and stakeholders.
  • Leverage the Power of Your Database: Modern cloud data warehouses like BigQuery, Redshift, and Snowflake have powerful machine learning capabilities built-in. Looker acts as the perfect interface to command these functions using a language you already know - SQL.
  • Real-Time Predictions: Once the model is built, you can have a field that shows "Predicted Sales" alongside "Actual Sales," updating automatically as new data comes in.

Setting Up Your Regression Model in LookML

The real work of enabling regression in Looker happens in the LookML model. This is where you'll tell Looker how to communicate with your database's machine learning functions. We'll use Google BigQuery ML as our example, as it has excellent, easy-to-use linear regression capabilities, but the principles apply to other SQL dialects with similar functions.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 1: Check Your SQL Dialect's Capabilities

Before you start, confirm that your database supports linear regression functions. BigQuery has CREATE MODEL, Redshift has CREATE MODEL for its ML capabilities, and Snowflake has libraries you can leverage. This guide will focus on the BigQuery approach, which is one of the most straightforward for Looker developers.

Step 2: Create a Persistent Derived Table (PDT) to Train the Model

You'll write a SQL query that trains a machine learning model and save the results in a Persistent Derived Table (PDT). A PDT is just a table that Looker builds and keeps up-to-date on a schedule you define, making it perfect for holding the results of a training model.

Let's say we want to predict a product's total_revenue based on its ad_spend and the number of page_views it received. In a LookML view file, you would define a PDT like this:

view: product_revenue_prediction {
  derived_table: {
    sql_trigger_value: SELECT CURRENT_DATE() ,
    datagroup_trigger: your_datagroup_name
    distribution_style: all
    sql:
      CREATE OR REPLACE MODEL `your_project.your_dataset.product_revenue_model`
      OPTIONS(
        model_type='LINEAR_REG',
        input_label_cols=['total_revenue']
      ) AS
      SELECT
        -- These are your independent variables (features)
        ad_spend,
        page_views,
        -- This is your dependent variable (the label you want to predict)
        total_revenue
      FROM
        your_schema.product_performance_table
      -- It's common practice to train on data that isn't too old
      WHERE created_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY),,
  }
}

What's happening in this code?

  • CREATE OR REPLACE MODEL: This is the BigQuery ML command to begin training a new model.
  • OPTIONS(...): Here we specify what kind of model we're making. model_type='LINEAR_REG' tells BigQuery we're doing linear regression, and input_label_cols=['total_revenue'] defines our dependent variable.
  • AS SELECT ...: The query inside defines our training data. BigQuery will use every other column selected (ad_spend, page_views) as the independent variables used to predict total_revenue.

It’s important to note: this PDT doesn't actually contain data itself. It's a "ghost" table whose purpose is to run a command that creates or updates the ML model in your BigQuery dataset.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Step 3: Create a Second Derived Table for Predictions

Now that you have a trained model, you need a way to use it to make predictions on new or existing data. You’ll create another derived table that uses the ML.PREDICT function. This table will be the one you actually join into your Explore.

view: product_predictions {
  derived_table: {
    explore_source: product_performance {} # You can base this PDT on an existing Explore
    sql:
      SELECT
        product_id,
        (SELECT predicted_total_revenue  -- The prediction output from the model
         FROM UNNEST(predicted_label)) AS predicted_revenue
      FROM ML.PREDICT(MODEL `your_project.your_dataset.product_revenue_model`,
        (TABLE ${product_performance.SQL_TABLE_NAME})) ,,
  }

  dimension: product_id {
    primary_key: yes
    hidden: yes # Usually you'd join this on the base product ID
    sql: ${TABLE}.product_id ,,
  }

  dimension: predicted_revenue {
    type: number
    value_format_name: usd
    sql: ${TABLE}.predicted_revenue ,,
  }
}

Step 4: Join the Prediction View in Your Model

Finally, open your LookML model file (.model.lkml) and join your new prediction view to the Explore where you analyze product performance. You’ll use a LEFT JOIN on a common key, like product_id.

explore: product_performance {
  join: product_predictions {
    type: left_outer
    relationship: one_to_one
    sql_on: ${product_performance.product_id} = ${product_predictions.product_id} ,,
  }
}

Once you save these changes, your "Predicted Revenue" dimension is now available in the Product Performance Explore, ready for business users to analyze alongside actuals!

Visualizing Your Regression Analysis in Looker

With the LookML setup complete, the fun part begins - visualizing and interpreting the results. A non-technical user can now do this without ever writing a line of code.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Building a Scatter Plot with a Trend Line

A scatter plot is a classic way to view the relationship between a single independent variable and the dependent variable.

  1. Navigate to your "Product Performance" Explore.
  2. From the Dimensions, pick one independent variable (e.g., "Ad Spend").
  3. From the Measures, pick your dependent variable (e.g., "Total Revenue").
  4. Run the query.
  5. Under the "Visualization" tab, select the Scatter option.
  6. Click "Edit" on the visualization, go to the "Series" tab, and select "Linear" from the "Trend Line" dropdown.

You'll now see a chart visualizing the relationship between ad spend and revenue, with your regression "line of best fit" drawn through the data points.

Comparing Actuals vs. Predictions

To see how well your model is performing, you can create a time-series line chart or a table that compares actual revenue to predicted revenue.

  1. In the same Explore, select a time dimension (e.g., "Created Date").
  2. Select your actual revenue measure ("Total Revenue").
  3. Select your new prediction dimension ("Predicted Revenue").
  4. Run the query and choose a Line chart visualization.

This allows you to instantly spot periods where your model was more or less accurate and can reveal fascinating trends.

Interpreting the Results

Creating the analysis is one thing, using it is another. Here’s what to look for:

  • R-Squared: When adding a linear trend line in a Looker scatter plot, it often displays a "R²" value. This number, ranging from 0 to 1, tells you what percentage of the change in the dependent variable can be explained by the change in the independent variable. An R² of 0.82 means 82% of your revenue fluctuation can be explained by your ad spend - a very strong relationship.
  • The Slope of the Line: A steep upward slope on your trend line indicates a strong positive relationship (as ad spend goes up, revenue goes up significantly). A flat line suggests a weak or non-existent relationship.
  • Forecasting: The model now allows you to conduct "what-if" scenarios. If the business wants to know the likely revenue impact of a $50,000 ad campaign, your model provides a data-backed prediction.
  • Important Caveat: Remember, correlation does not equal causation! Regression shows a statistical relationship. It doesn't prove that more ad spend causes more revenue, only that they tend to move together based on your data.

Final Thoughts

Running regression analysis in Looker elevates your reporting from being purely historical to becoming a forward-looking, predictive tool. By embedding ML models directly into your data environment, you bring powerful advanced analytics into the daily workflow of your team, helping them move from just asking "what happened?" to confidently asking "what will happen next?"

While Looker is a powerful tool, setting up LookML and writing complex SQL for machine learning can be a significant hurdle for teams without dedicated data engineers. At Graphed, we remove this friction by empowering anyone to perform this kind of analysis with natural language. You can simply ask, "show me the relationship between ad spend and sales with a regression line," and our AI will instantly build the analysis, pulling directly from your connected data sources. This turns hours of development work into a 30-second conversation, giving your entire team access to predictive insights that were once locked away behind technical barriers.

Related Articles