Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Do Sensitivity Analysis in Looker

Cody Schneider••8 min read

Ever find yourself staring at a dashboard and wondering, “What would happen to our revenue if website conversion rates dropped by half a percent?” or “How would a 15% increase in ad spend affect our customer acquisition cost?” This kind of "what-if" thinking is the core of sensitivity analysis, and you can build it directly into Looker. This guide will show you how to move beyond static reports and create dynamic 'what-if' scenarios using Looker’s powerful modeling layer.

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 Sensitivity Analysis, Really?

At its heart, sensitivity analysis is a way to understand how the uncertainty in your input variables impacts your key outcomes. Instead of looking at a single, historical number (like last month’s ROAS), you create a model that lets you see a range of potential outcomes based on changing conditions. Think of it as a financial stress test for your business metrics.

Why bother? Because it helps you:

  • Make smarter decisions: You can see the potential impact of a change before you commit to it. For example, you can model the effect of a price increase on total revenue while accounting for a potential drop in sales volume.
  • Identify key drivers: You’ll quickly discover which variables have the most significant impact on your results. Is your bottom line more sensitive to website traffic or conversion rate? Now you’ll know where to focus your efforts.
  • Manage risk: By modeling worst-case scenarios, you can better prepare for potential downturns and build more resilient strategies.

While tools like Excel and Google Sheets are common for this, building sensitivity analysis into Looker keeps it connected to your live business data, making it more accurate, trusted, and reusable for your entire team.

The Looker Building Blocks for Sensitivity Analysis

Unlike a spreadsheet where you can just change a cell's value, Looker requires a more structured approach using its modeling layer, LookML. This upfront work pays off by creating a robust and scalable analysis that anyone can use through a simple filter. The two most important components you'll need to understand are Parameters and Liquid.

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.

LookML Parameters: The "Driver" of Your Analysis

A LookML parameter is a special type of field that acts as a user input variable. It doesn't pull data from your database directly. Instead, it creates a filter-only field that allows users to input a value - like a number, text, or date - that you can then use in other calculations. This is the magic key for sensitivity analysis, it’s the virtual knob your users can turn to adjust your model.

You define it in your view file like this:

parameter: name_of_parameter {
  type: number
  # You can also add suggestions for users
  allowed_value: {
    label: "+10% Increase"
    value: "1.10"
  }
  allowed_value: {
    label: "No Change"
    value: "1.0"
  }
  allowed_value: {
    label: "-10% Decrease"
    value: "0.90"
  }
}

Liquid: The "Connector" in Your Model

Liquid is a templating language developed by Shopify that Looker uses to create more dynamic content. For our purposes, its most important job is to pull the user-selected value from a parameter and insert it into another LookML field (like a dimension or measure). The syntax is straightforward: {% parameter name_of_parameter %}.

You’ll use this tag inside your dimensions and measures to make their calculations react to a user’s selection in the Looker Explore interface.

Step-by-Step Guide: Modeling Ad Spend Projections

Let's walk through a common business scenario: A marketing team wants to project their Return on Ad Spend (ROAS) under different Cost Per Click (CPC) conditions. We want to build a tool that lets them ask, "What will our ROAS be if our CPCs increase by 20%?"

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 1: Define Your Variable with a LookML Parameter

First, we need to create the user input. We'll create a parameter in our ad performance view file (e.g., ads_performance.view.lkml) that allows a user to select a percentage change.

parameter: cpc_modifier {
  type: unquoted
  label: "CPC % Change"
  description: "Select a percentage to adjust the CPC for what-if analysis."

  allowed_value: { label: "+20%" value: "1.20" }
  allowed_value: { label: "+15%" value: "1.15" }
  allowed_value: { label: "+10%" value: "1.10" }
  allowed_value: { label: "No Change" value: "1.0" }
  allowed_value: { label: "-10%" value: "0.90" }
  allowed_value: { label: "-15%" value: "0.85" }
  allowed_value: { label: "-20%" value: "0.80" }
}

Now, when a user goes to an Explore with this view, they'll see a filter called "CPC % Change" with a dropdown of these options.

Step 2: Create a Baseline CPC Measure (If you don't have one)

For this analysis to work, we need a base calculation for our current CPC. It's usually a simple ratio of cost to clicks. If you don't already have one, add it as a new measure in your view.

measure: average_cpc {
  type: number
  sql: SAFE_DIVIDE(SUM(${cost}), SUM(${clicks})) ,,
  value_format_name: usd
}

Step 3: Build the "Projected" Dimensions and Measures

This is where we use Liquid to connect our parameter with our calculations. We'll create new measures for projected_cpc and projected_cost that will dynamically change based on what the user selects for the "CPC % Change" filter.

First, let's calculate the projected CPC:

measure: projected_cpc {
  type: number
  label: "Projected CPC"
  sql: SAFE_DIVIDE(SUM(${cost}), SUM(${clicks})) * {% parameter cpc_modifier %} ,,
  value_format_name: usd
  description: "Calculates the CPC based on the selected '%' modifier."
}

Here, the {% parameter cpc_modifier %} syntax inserts the value (e.g., 1.20) selected by the user, modifying our base CPC calculation.

Next, we can calculate the projected total cost. Since CPC is Cost / Clicks, we can say that Projected Cost is our Projected CPC multiplied by the number of clicks.

measure: projected_total_cost {
  type: sum
  label: "Projected Total Cost"
  sql: ${projected_cpc} * SUM(${clicks}) ,,
  value_format_name: usd
}

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 4: Calculate the Final Output Metric (Projected ROAS)

Finally, we create the key metric we care about: Projected ROAS. For this, we'll assume we have an existing measure for total_conversion_value. Our projected ROAS is simply this value divided by our new projected_total_cost.

measure: projected_roas {
  type: number
  label: "Projected ROAS"
  sql: SAFE_DIVIDE(${total_conversion_value}, ${projected_total_cost}) ,,
  value_format_name: decimal_2
}

Step 5: Putting It All Together in the Explore

Now for the payoff. Head to your Looker Explore and follow these steps:

  1. Find the Parameter Filter: In the filter pane, you will see your new parameter, "CPC % Change."
  2. Select a Value: Choose one of the options from the dropdown, for example, "+20%." Nothing will happen in the viz pane yet.
  3. Add a Dimension: Add something like "Ad Campaign Name" to see the breakdown.
  4. Add Your Measures: Select the original average_cpc and the new projected_cpc. You'll see the values change side-by-side. Then, add measures like total_ad_cost, projected_total_cost, and finally, projected_roas.
  5. Run the Query: You now have a report that shows your actual campaign performance alongside a projection of what would happen if your CPCs increased by 20%. You can change the filter to "-10%" and re-run to see that scenario instantly.

Visualize this in a table or bar chart to easily compare the baseline ROAS against the potential ROAS across all of your campaigns.

Tips for Better Sensitivity Analysis

  • Start simple: Model one variable at a time. It's tempting to build a complex model with multiple moving parts, but you'll get more reliable insights by isolating the impact of each variable first.
  • Document everything: Use the label and description tags liberally in your LookML. This helps a business user understand what an input does without having to read a line of code.
  • Use good visualizations: A line chart showing how a profitability metric changes as a cost input increases is far more intuitive than a simple table of numbers. Communicate the story clearly.
  • Consider performance: Remember that every time a user changes a parameter, Looker is re-running a query against your database. For very large datasets or complex calculations, this can introduce a delay. Always build with performance in mind.

Final Thoughts

By using LookML parameters and Liquid, you can transform Looker from a descriptive reporting tool into a predictive, strategic one. It allows you to build powerful, interactive models that empower your entire team to explore different business scenarios, understand risks, and make more data-informed decisions without ever leaving their dashboards.

Building robust models in Looker is powerful, but it still requires a clear understanding of LookML and your underlying data structure. We built Graphed to remove that barrier, allowing you to ask these "what-if" questions using simple, natural language. Instead of writing LookML, you can just connect your data sources and ask, "Show me how our profit changes if ad spend on our Facebook campaigns increases by 20%," and get an instant analysis without needing a developer to build it for you.

Related Articles