Looker Studio Formula: A Comprehensive Guide

Looker Studio Formula is a powerful tool that allows you to create custom calculations and formulas to analyze and visualize your data.

The formula bar in Looker Studio is where you'll spend most of your time working with formulas. It's located at the top of the page and has a simple, intuitive interface that makes it easy to build and edit formulas.

To get started with Looker Studio Formula, you'll need to understand the basics of calculation fields and how they work. Calculation fields are special types of fields that allow you to perform calculations on your data. They can be used to create new fields, such as totals, averages, and percentages.

A calculation field is created by using the "CALCULATE" function, which allows you to specify the calculation you want to perform. For example, the formula "CALCULATE(SUM(value))" will sum up all the values in a field.

To create a new calculated field in Looker Studio, you can use basic arithmetic operators, predefined functions, and branching logic to derive new fields.

You can find the saved calculated metric in the data source. To edit a calculated field in the data source, click Edit against the data source to edit its calculated field.

To edit a chart-specific calculated field, open the respective chart, hover the cursor over the respective field, and click fx on the left of the field to edit it.

To set up a chart, you need to select a specific chart in the report. Click on the chart you want to work with.

The next step is to go to the Setup tab, where you can create a new calculated field. This involves clicking on the Metric or Dimension field and then clicking Create New Field.

You can use this new field to create a chart-specific calculated field. Remember, this field can only be used within this specific chart and cannot be used elsewhere.

To save the metric, click Apply at the bottom-right corner.

When loading data to Looker Studio, it's essential to consider where to create calculated fields for efficient use. Creating calculated fields in data sources before loading to Looker Studio can be a good option because you can reuse them across charts and reports, and they do not cause performance issues.

You can create calculated fields in data sources using the data source calculated field feature. This type of calculated field can be used in all reports where that data source is used and can be filtered and used to create another calculated field.

To create a calculated field in a data source, you need edit rights to the data source. You can find the saved calculated metric in the data source.

Here are some benefits of creating calculated fields in data sources:

There are two main types of Looker Studio calculated fields.

Each has its own scope of usage and purpose.

One type of calculated field is created in the Fields pane, where you can use them to create new fields that combine data from multiple fields.

These fields are limited to the specific view they're created in and can't be used in other views.

Another type of calculated field is created in the Explore section, where you can use them to create reusable formulas that can be used across multiple views.

These fields have a wider scope of usage and can be used in multiple views, making them more versatile.

You can use basic arithmetic operators, predefined functions, and branching logic to derive new fields in Looker Studio calculated field formulas.

To create a calculated field in Looker Studio, you can use the "Add Field" option and enter a formula in the Formula box.

Calculated fields can be used to create custom metrics, derive dimensions, segment data, and cleanse data.

You can add a change metric to a table by clicking "Add Metric" and then "Add Field", and entering a formula such as "Current Sessions-Previous Sessions" in the Formula box.

Here are some common uses of calculated fields:

A chart-specific calculated field is a powerful tool in Looker Studio. It allows you to perform math, functions, or return results based on logical branching, making it a valuable asset for specific charts.

You can create a chart-specific calculated field to be a metric or dimension, and it can be used in a specific chart, but not in any other chart or report. This means it's perfect for unique calculations that only apply to one chart.

To create a chart-specific calculated field, you need to be the report editor, and you can do it by following the steps mentioned in Looker Studio. Click Apply at the bottom-right corner to save the metric, and remember, this is a chart-specific metric, and it can't be used anywhere else.

Chart-specific calculated fields can't be used to create other calculated fields. If you want to reuse a calculated field in multiple reports or charts, create it in the data source.

Here's a quick summary of the key points:

To edit a chart-specific calculated field, you must be an editor of the chart. You can do it by hovering the cursor over the respective field, clicking fx on the left of the field to edit it, modifying the calculated field name, data type, and formula as per your needs, and clicking Apply to save the changes.

To use formulas in Looker Studio, you can create custom metrics, derive dimensions, segment data, and cleanse data. You can use basic arithmetic operators, predefined functions, and branching logic to derive new fields.

Calculated fields can be used to visualize data in charts, just like standard fields. You can create new metrics from existing fields, such as getting the conversion rate from the number of conversions and total visitors.

To edit a calculated field, you'll need to have the required access. For chart-specific fields, you must be an editor of the chart, while for data-source fields, you must have edit rights for the data-source.

Here are some common uses of calculated fields:

To create a new calculated column, click the Formula button on the top of the data preview. You can use various logical operators, functions, and existing fields to create the formula.

You can group a large dataset into categories using formulas, just like in the example of classifying pages into products, categories, and blog posts. This is done by creating a new calculated field and using the CASE function.

The CASE function evaluates different conditions and assigns a value according to the page URL.

You can use the REGEX_MATCH function to check if the URL field contains specific terms, like in the example where the page URL is https://example.com/product/ and it's classified as "Product Pages".

Each case is evaluated one after one until the value is found true, and if none of the cases is found true, a default value is assigned to the dimension, such as "Other Pages".

You can modify the formula to implement your own logic and use it for complex classifications.

If you have null values in your table, they can cause problems with your metrics, making them show up as null. This can happen if you have landing pages with sessions in only one time period.

The IFNULL function is what you want to use to replace null values with a useful number. You can use it like this: IFNULL(Previous Sessions,0), which turns any null value into 0.

You might not want to always use 0, especially if there's a chance you'll end up dividing by 0. If you do, you'll get a result like (80-0)/0, which isn't helpful.

To avoid this, you can use the NARY_MAX function to set the value of the Previous Sessions metric to be equal to the Current Sessions metric. This will give you a more accurate result, like (80-0)/80, which shows a percentage change of 100%.

The NARY_MAX function returns the requested metric if the original metric is null, and the value of the original metric if it's not null. This makes it a useful tool for working with blended data sources.

To set time periods in a blended data source, you need to rename your data sources first. This allows you to compare different time periods.

The next step is to set the time periods for each data source. For this, you'll use the Advanced data options in Looker Studio.

Select the Date Range of "Custom" in the table for your previous period. Then, click on the date selector to open it up, and choose the "Advanced" option from the dropdown.

You can set the Start Date to be today minus 8 weeks (starts Monday) and the End Date to be today minus 5 weeks (starts Monday). This will give you a time period of 3 weeks.

Click "Apply", and then select a Date Range of "Custom" in the current period table. Set the Start Date to be today minus 4 weeks (starts Monday) and the End Date to be today minus 1 week (starts Monday).

Google Functions are incredibly powerful and versatile, allowing you to perform a wide range of calculations, data manipulations, and text operations.

Aggregation Functions, such as AVG(X), enable you to calculate the average of all values in a field. This can be a huge time-saver, especially when working with large datasets.

Arithmetic Functions, like POWER(X, Y), allow you to perform mathematical operations like raising a value to a power. You can use this to create complex calculations with ease.

Conditional Functions enable you to apply conditional logic to your data, making it easy to create formulas that return different values based on specific conditions. For example, you can use a formula to return "High" for values above 100 and "Low" for other values.

Date Functions, such as DATE_DIFF(X, Y), allow you to work with dates and calculate differences between them. This is especially useful when analyzing data that spans multiple dates.

Geo Functions can convert geographic codes into continent, subcontinent, country, region, and city names, making it easy to work with location-based data.

Google Functions are incredibly powerful tools that can help you process and analyze data in Looker Studio. They allow you to perform calculations or process existing fields.

You can use Aggregation Functions to aggregate data from fields, such as calculating the average of all values with the AVG(X) function. This is useful when you need to summarize data.

Arithmetic Functions enable you to perform mathematic operations like addition and trigonometric functions. For example, the POWER(X, Y) function returns the value of raising X to the power Y.

Date Functions allow you to work with date fields, enabling you to change the format, compare dates, or manipulate the data. The DATE_DIFF(X, Y) function returns the difference between two dates, X and Y, in days.

Geo Functions can convert codes into continent, subcontinent, country, region, and city names. The TOCITY(X [Input Format]) function returns the name of the city by country code X.

Text Functions enable you to perform various operations on text strings, such as concatenation, formatting, and comparison. The CONCAT(x, y) function combines two strings, X and Y.

You can also use Regular Expressions (regex) in formulas, but there are alternative functions as well.

In Google Looker Studio, literals are used to specify constant values of built-in datatypes. They can be of four types: string literals, date literals, numeric literals, and boolean literals.

String literals specify a constant value of strings using single or double quotation marks, such as "John Doe" and 'John Doe'. This is useful when working with text data.

Date literals specify the date and time values, preceded by appropriate markers, like DATE '2023-9-3' and DATETIME '2023-9-3 18:30:00'. These markers help identify the type of value being specified.

Numeric literals are unquoted floating point values, such as 1, 2, and 3.33. This type of literal is useful for calculations involving numbers.

Boolean literals are used when working with boolean expressions, and they can be either true or false. For example, IF(Boolean field = true, "yes"",no") uses the boolean literal true.

Here are the four types of literals in Google Looker Studio:

Logical and Conditional Functions are a powerful tool in Looker Studio calculated fields. They allow you to assign values based on conditional logic.

You can use the IF function along with a comparison operator to classify data. For example, you can use the IF function to classify pages based on conversions.

The IF function returns a value if the inputted condition is true, just like the CASE function. In fact, the IF function is similar to the example in Example 3, where we used it to classify pages based on conversions.

You can also use the IF function to reward students based on their performance, as shown in Example 5. For instance, if a student's name starts with "Ph", you can give them an extra 5 points.

These functions are incredibly useful for manipulating data and getting the desired results. By using them, you can create dynamic and informative visualizations in Looker Studio.

Google Operators are the backbone of Looker Studio calculated fields, allowing you to manipulate data inputs and return results. They can be used with other functions to define logic and get the desired result.

Arithmetic operators are used for basic math operations, such as addition and subtraction. The + operator is used for addition, and the - operator is used for subtraction.

Arithmetic operators also include multiplication and division. The * operator is used for multiplication, and the / operator is used for division.

Comparison operators are used to compare values. The = operator is used to check if two values are equal, and the != operator is used to check if two values are not equal.

Comparison operators also include greater than, greater than or equal to, less than, and less than or equal to. The > operator is used to check if a value is greater than another value, and the >= operator is used to check if a value is greater than or equal to another value.

Logical operators, on the other hand, return true, false, or null values. The AND operator is used to combine two conditions, and the OR operator is used to combine two conditions with a logical OR.

Here is a list of some of the logical operators supported by Looker Studio:

These operators can be used in combination to create complex logic and get the desired result.

The IF function is a powerful tool in Looker Studio, allowing you to create dimensions based on conditions. It can be used with the ">"" comparison operator to classify data.

You can use the IF function along with other operators to manipulate data and get the desired results, as seen in the example of classifying pages based on conversions.

The IF function is similar to the CASE function, which returns a value if the inputted condition is true. It's a type of conditional function.

To use the IF function, you need to specify a condition, a value to return if the condition is true, and a value to return if the condition is false. This is shown in the example of rewarding Philip for his extra-academic activities.

In the example, the function looks for the name "Ph" in the field "NAMES" and returns an extra 5 points if it exists, otherwise it returns the default value in the field "EXAM 1".

String functions in Looker Studio are incredibly useful for manipulating and combining text strings. You can use them to concatenate fields, extract specific parts of text, or even replace characters with spaces.

The CONCAT() function is a great example of this, allowing you to combine two or more fields into a single field with a hyphen between them. For instance, if you have fields for city and country names, you can use CONCAT() to create a new field with the combined values, like "New York - USA".

You can also use regular expression functions to manipulate text strings, such as REGEXP_REPLACE, which helps replace underscores with spaces. This can be particularly useful when working with data that has inconsistent formatting.

Here are some common string functions you can use in Looker Studio:

These string functions can be incredibly powerful when used correctly, allowing you to manipulate and analyze your data in new and interesting ways.

Regex functions are a powerful tool in Looker Studio, allowing you to manipulate text and find patterns in your data. They can be used to group marketing channels by type, such as Free, Paid, and Owned.

You can use the REGEX_MATCH function to return a boolean value (true or false) when looking for a match. For example, if you want to find the name "Ava" in your data, REGEX_MATCH will return true once it finds the name "Ava" and false for the rest of names on the list.

The REGEX_REPLACE function replaces a value in your field with the one you input. This is useful if you want to replace a specific value with another one. In the example, we replaced the name "Ava" with "Sara".

The REGEX_CONTAINS function returns true if it finds a value containing what you searched for. For instance, if you want to find all the names that contain the letter "h", REGEX_CONTAINS will return true for names like "Hannah" and false for names like "Ella".

You can use the REGEX_EXTRACT function to extract a value from your field and put it in a new field. For example, we can extract the letter "A" from a list of names.

The REGEX_END function starts from the end of a string and can be useful if you have a list of names and you want to capture the names that end with the letter "a".

Replacing underscores with spaces is a common task in data manipulation, and it's surprisingly easy to do.

The REGEXP_REPLACE function is a game-changer here, allowing you to replace underscores with spaces in a single step.

This function is a must-know for anyone working with Google Sheets or Looker Studio.

The formula REGEXP_REPLACE(Event name,”_”, “ “) does all the heavy lifting, making it a very easy one to master.

It's worth noting that this function is a regular expression, which can be intimidating at first but is actually quite powerful.