[Looker Studio] How to Use the STDDEV Function and Practical Examples | Calculated Fields

June 30, 2024

[Looker Studio] How to Use the STDDEV Function and Practical Examples | Calculated Fields

In this article, we will clearly explain how to use the STDDEV function in Looker Studio and provide specific examples of its application. Let's leverage the functions available in Looker Studio to process and visualize data.

While the STDDEV function cannot be used with GA4 (Google Analytics 4), Search Console, or Google Ads connectors, it can be applied to data imported from spreadsheets.

What are Calculated Fields in Looker Studio?

Calculated Fields in Looker Studio are a handy feature that allows you to create custom fields by using operators (such as addition, subtraction, multiplication, division), functions, and regular expressions based on existing items for use in reports.

Also, for basic usage of Looker Studio, please refer to the "How to Use Looker Studio" guide. Looker Studio is a very convenient tool that is free to use and allows for the creation of easy-to-understand reports by connecting to various data sources, so let's make active use of it.

What is the STDDEV Function?

The STDDEV function is used to obtain the "standard deviation" of a specified numerical field. "Standard deviation" is a statistical measure that represents the dispersion of data. It indicates how spread out the values in a dataset are from the mean value. A larger standard deviation signifies greater variability in the data, while a smaller standard deviation indicates less variability.

For example, consider the test scores of two classes: [10, 20, 30, 40, 50] and [20, 30, 30, 30, 40]. Although both classes have the same average score of 30, the scores in the first class are more spread out compared to the second class. This difference is not apparent when only looking at the average score. The standard deviations for the two classes are approximately 14.14 and 6.32, respectively. This shows that the scores in each class are dispersed around the mean score of 30 within the range indicated by their standard deviations. By considering the standard deviation along with the average, you can understand the variability in the data that the average alone cannot reveal.

Syntax

The syntax for the STDDEV function is as follows:

STDDEV("numeric_field")

"numeric_field" specifies the numeric field for which you want to obtain the standard deviation.

Conditions for Using the STDDEV Function

There are two conditions for using the MAX function. Before using it to obtain the maximum value, ensure that the number meets the following criteria:

It Must Be a Number (Including Dates)

The MAX function is designed to find the maximum value, so it cannot be used for dimensions that are not numerical.

It Must Not Be an Aggregated Metric

If the numerical data from the data source has already been aggregated by a connector or similar tool, the MAX function cannot be used. You can determine if a number is aggregated by checking if the default aggregation of the data source is set to automatic. The MAX function cannot be used on automatically aggregated numbers, so please be cautious.

(Quote)Looker Studio

How to Use the STDDEV Function

To use the STDDEV function, you need to create a calculated field in Looker Studio. There are two types of calculated fields: data source calculated fields and chart-specific calculated fields. This article will focus on creating a data source calculated field, but for more detailed differences between the two, please see the differences between data source calculated fields and chart-specific calculated fields.

As previously mentioned, the STDDEV function cannot be used with metrics from data sources such as GA4 (Google Analytics 4), Search Console, and Google Ads that utilize connectors. Therefore, for this example, we have prepared sample data in a spreadsheet containing daily customer and sales data for each store. I will use this sample data to explain.

Use case:Visualizing the Standard Deviation of Sales in Looker Studio

Set the calculated fields as follows.

(Quote) Looker Studio

Field Name: Please enter any field name.
Formula: STDDEV(sales)
Save: Once you have entered the information, click Save.

A field named sales_stddev has now been created as shown below.

(Quote)Looker Studio

Let's add the metrics we just created to the report so they can be utilized. By doing so, you will see that the numbers are correctly reflected in the table.

(Quote)Looker Studio

Additionally, when comparing the standard deviation of daily sales for each store with the metrics created using the AVG and MAX functions introduced earlier, it becomes clear that Store C has more variability in daily sales compared to other stores. By further analyzing what kind of days have low or high sales and why, we can use this information as a basis for devising strategies to improve sales.

(Quote)Looker Studio

How to Calculate Standard Deviation Without Using Calculated Fields

Basic statistical measures, such as standard deviation, can be reflected in reports without using calculated fields. Here’s how you can do it.

Use case: Visualizing the Standard Deviation of Customer Count in Looker Studio

First, click on the mark to the left of the metric for which you want to calculate the standard deviation in your report.

(Quote)Looker Studio

Next, enter the name and check the box for the standard deviation as the aggregation method.

(Quote)Looker Studio

The settings are now reflected in the report, and the standard deviation of the number of customers is displayed. We found that Store C has the largest fluctuation in the number of customers on a daily basis. From here, we can obtain clues for further analysis, such as understanding which days have low or high customer numbers and why that might be. This is the method for displaying the standard deviation using the STDDEV function. Please give it a try.