How to Create a Formula in Looker Studio

Creating custom formulas in Looker Studio is one of the best ways to transform your standard reports into a powerhouse of custom insights. Instead of being limited by default metrics, you can create the exact key performance indicators (KPIs) your business actually tracks. This article will walk you through exactly how to create formulas - known as calculated fields - step by step, with practical examples you can start using today.

What Are Calculated Fields in Looker Studio?

Calculated fields are custom dimensions or metrics you create directly within Looker Studio using formulas. Think of them as new columns in your spreadsheet that don't exist in your original data source. You're creating them on the fly by performing calculations or manipulating your existing data.

Why is this so useful? Your raw data from tools like Google Analytics, Google Ads, or a simple spreadsheet is often just the starting point. The real insights come when you combine and transform that data to answer specific business questions.

You can use calculated fields to:

Create unique KPIs, like Profit Margin, Cost per Acquisition (CPA), or Average Revenue per User (ARPU).
Group data into custom categories, such as bucketing countries into sales regions or campaign names into broader channels.
Calculate conversion rates for specific goals that aren't natively tracked.
Clean up or reformat data, like joining text strings or extracting a piece of information from a URL.

The Building Blocks: Functions and Operators

Before jumping into creating formulas, it helps to know what tools you have at your disposal. Looker Studio’s formulas are built using a combination of existing fields, functions, and mathematical operators.

You don't need to be a programmer or a math expert, but understanding the basic categories will make the process much smoother.

Arithmetic Operators: These are the basics you learned in school: + (add), - (subtract), * (multiply), and / (divide).
Aggregation Functions: These functions summarize data. Common ones include SUM(), AVG(), COUNT(), COUNT_DISTINCT(), MIN(), and MAX(). They are the backbone of most custom metrics.
CASE Statements: This is a powerful logical function. It allows you to create "if/then else" logic to segment or categorize your data based on certain conditions. It's perfect for creating custom groupings.
Date Functions: These help you manipulate and calculate based on date fields. You can use functions like TODAY(), YEAR(), or DATE_DIFF() to calculate things like the number of days between two events.
Text Functions: Useful for working with text-based dimensions. You can use CONCAT() to combine text, LOWER() to convert text to lowercase, or REGEXP_EXTRACT() to pull specific pieces of text out of a string.

The key isn't to memorize every function, but to know they exist so you can find them in Looker Studio's function list when you need them.

How to Create a Calculated Field: A Step-by-Step Guide

You can create a calculated field in two different places in Looker Studio, each with its own advantages. Let's break down where, why, and how.

Location #1: At the Data Source Level

Creating a formula at the data source level is generally the best-practice approach. When you add a calculated field here, it becomes a permanent part of that data source within your Looker Studio environment. It will be available for you to use in any chart, table, or report that uses that same connected data source.

Use this method when: The metric or dimension you're creating is a core business KPI that you'll want to reuse frequently.

Steps:

Navigate to the top menu and click Resource > Manage added data sources.
Find the data source you want to modify in the list and click Edit.
In the top right corner of your data source's field list, click the blue Add a Field button.
This will open the formula editor. Here's where you'll build your new field. You'll give it a Field Name, type your formula into the Formula box, select its Data Type (e.g., Number, Text, Date), and save it. Looker's editor is helpful, offering auto-suggestions for fields and functions as you type.

Location #2: At the Chart Level

You can also create a calculated field that is "chart-specific." This new field will only exist for the single chart or table you are currently working on. It will not be saved to your data source and cannot be reused on other charts without recreating it.

Use this method when: You're doing a quick, one-off analysis or testing a formula before you add it permanently to your data source.

Steps:

Select a chart or table on your report canvas.
In the Setup panel on the right, find the section for either Dimension or Metric.
Click Add a dimension or Add a metric.
At the bottom of the field list that appears, click Create Field.
This will open the same formula editor as the data source method. The process is identical: give it a name, enter your formula, and click Apply.

Practical Examples of Looker Studio Formulas

Theory is great, but let's see how this works with some real-world examples you might use for your ecommerce, marketing, or sales reporting.

Example 1: Calculating Website Conversion Rate

Let's say you're using Google Analytics 4 data and want to calculate a specific conversion rate, like transactions per session.

Field Name: Transaction Conversion Rate
Formula: SUM(Transactions) / SUM(Sessions)
Data Type: Number > Percent

This simple formula takes the total number of transactions and divides it by the total number of sessions. Setting the data type to "Percent" will automatically format the output correctly in your charts (e.g., showing 2.5% instead of 0.025).

Example 2: Calculating Ad Cost Per Click (CPC)

If you're pulling in data from Google Ads, Facebook Ads, or LinkedIn Ads, CPC is a fundamental metric. While often included by default, creating it yourself helps you understand how formulas work.

Field Name: Cost Per Click (CPC)
Formula: SUM(Cost) / SUM(Clicks)
Data Type: Number > Currency (e.g., USD)

This divides your total ad spend by the total clicks, giving you the average cost for each click you paid for. Setting the data type to your currency formats it beautifully.

Example 3: Grouping Countries into Sales Regions with a CASE Statement

Imagine your sales team is divided into North America, Europe, and Asia-Pacific. You can create a new dimension that automatically buckets countries into these regions.

Field Name: Sales Region
Formula:

Data Type: Text

The CASE statement checks the Country field for each row of data. If it matches one of the countries listed in the "IN" clause, it returns the assigned region. The ELSE "Other" part is a useful catch-all for any countries you haven’t specifically listed.

Example 4: Calculating Days Since a Customer's Last Purchase

This is a valuable formula for ecommerce businesses wanting to identify at-risk or dormant customers. You need a field for the purchase date (let's call it Last Purchase Date).

Field Name: Days Since Last Purchase
Formula: DATE_DIFF(TODAY(), Last Purchase Date)
Data Type: Number > Number

DATE_DIFF() calculates the difference between two dates. By comparing today's date with the date of the last purchase, you get the number of days that have passed.

Example 5: Cleaning Up a Page URL

Sometimes your URL data might contain query parameters (?source=email, etc.) that you want to remove for cleaner reporting.

Field Name: Clean Page Path
Formula: REGEXP_EXTRACT(Page path, '([^?]+)')
Data Type: Text

This formula uses a regular expression to extract everything in the Page path field up until the first question mark ?, effectively giving you a clean URL without any tracking parameters.

Tips for Success with Calculated Fields

As you start writing more formulas, keep these best practices in mind to make your life easier.

Give Your Fields Clear Names: Don't stick with the default "Untitled Calculated Field." Use a descriptive name like "Lead to Customer Rate" so you and your teammates know exactly what it is.
Start Simple and Build Up: If you have a complex formula to create, build and test it in smaller chunks. This makes it much easier to catch errors.
Pay Attention to Aggregation: Remember that metrics are almost always aggregated (e.g., SUM of Sales, AVG of Session Duration). Dimensions are not. Your formula needs to respect this logic. Wrapping a metric in a SUM() or AVG() is a standard practice and solves many common errors.
Use the Formula Validator: As you type, Looker Studio will check your syntax. If you see a green checkmark, your formula is structurally sound. If you see a red warning message, it will often give you a clue about what's gone wrong (like a missing parenthesis or an incorrect function name).

Final Thoughts

Mastering calculated fields unlocks the full potential of Looker Studio, allowing you to move beyond basic reporting to create customized dashboards that truly reflect your business goals. By combining a few key functions and operators, you can build powerful custom metrics and dimensions that provide deeper, more actionable insights.

Building these formulas yourself provides a lot of control, but it also takes time to learn the syntax and debug errors. This is why we created Graphed, which uses a conversational approach to data analysis. Instead of manually entering formulas and configuring charts, you just connect your data and ask in plain English for results like, "Show me a line chart of our transaction conversion rate over the last 90 days." We handle all the calculations in the background, giving you the finalized report in seconds.