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

In this article, we will clearly explain how to use the COUNT_DISTINCT function in Looker Studio and provide specific examples of its application. Utilize the functions available in Looker Studio to process data and visualize the data you want to see.

Regarding data processing for GA4 (Google Analytics 4), many tasks can be accomplished using calculated fields without the need for Big Query. We will explain specific examples based on GA4 data accessed via connectors, so please make use of it. 

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 COUNT_DISTINCT Function?

The COUNT_DISTINCT function is a function that counts the number of unique values by eliminating duplicates within the specified dimensions, metrics, or expressions. As a result, the output of the calculation is a numerical value. Similar to the COUNT function, which simply totals the number of records, the COUNT function includes duplicates. If you want to count the number of unique values, such as the number of unique users, you should use the COUNT_DISTINCT function.

Syntax

The syntax of the COUNT_DISTINCT function is as follows:

COUNT_DISTINCT("target to count distinct values")

In "target to count distinct values," specify the dimension, metric, or expression to be targeted. The distinct values of the specified target are counted.

Conditions for Using the COUNT_DISTINCT Function

The COUNT_DISTINCT function is a useful tool, however, there are conditions under which it can be used. It is important to check beforehand if the target to be counted does not fall under the following:

The metric is not pre-aggregated

If the numerical values from the data source are already aggregated through connectors, etc., the COUNT_DISTINCT function cannot be used. Whether the values are pre-aggregated can be determined by the default aggregation of the data source being set to automatic. If set to automatic, the COUNT_DISTINCT function cannot be used, so please be careful.

Metrics from data sources using Looker Studio connectors such as GA4, Search Console, and Google Ads are all pre-aggregated, hence the COUNT_DISTINCT function cannot be used on them. However, it can be used on dimensions.

(Quote)Looker Studio 

How to Use the COUNT_DISTINCT Function

To use the COUNT_DISTINCT 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.

Difference between COUNT and COUNT_DISTINCT Functions

The COUNT function calculates the number of data entries including duplicates, whereas the COUNT_DISTINCT function calculates the number of unique data values by excluding duplicates. For example, with the data A, A, B, B, C, the COUNT function results in a count of 5 (A, A, B, B, C), while the COUNT_DISTINCT function results in 3 (A, B, C).

When dealing with unprocessed raw data, it is important to be clear about what you want to count and choose the appropriate function.

On the other hand, the data sources for GA4 utilizing Looker Studio connectors use aggregated data. Therefore, the values are aggregated, eliminating duplicates, which can result in the same outcome for both the COUNT and COUNT_DISTINCT functions. Below, we will explain the use case assuming the data is already aggregated in GA4.

Use case: Visualizing the average number of active users per day in Looker Studio

Set up a calculated field as follows

 (Quote:Looker Studio)

① Field Name: Please enter any field name.

② Formula: Active Users / COUNT_DISTINCT(Date)

Divide the number of active users by the count of distinct dates.

③ Save: Once you have completed entering the information, click save.

 A new metric called "average number of active users per day" has now been created as shown below.

 (Quote:Looker Studio)

Next, since the metric we just created is now available in the report, we will start adding it to the metrics.

By setting it up as described below, you can represent the average number of active users per day.

(Quote:Looker Studio)

 Use Case: Visualizing the Number of Product Types Sold in Looker Studio

Set up a calculated field as follows:

(Quote:Looker Studio)

1. Field Name: Please enter any field name.

2. Formula: COUNT_DISTINCT(Item ID)

Select the unique "Item ID" for each product.

3. Save: Once you have entered the information, click Save.

A new metric called "sold sku" has now been created.

(Quote:Looker Studio) 

Next, since the metric you just created is available in the report, you will add it to the metrics.

By setting it up as described below, you can visualize the number of product types sold by date.

 

How to Count Numbers Without Using Calculated Fields

It is possible to reflect relatively simple calculations such as counts in reports without using calculated fields.

Use Case: Visualizing the number of types of events that occurred on each date in Looker Studio 

First, add the dimension or metric you want to count in the report as a metric. Next, click the mark to the left of the metric name to open the editing screen as shown in the image below.

Next, enter a name and check the aggregation method as "Count Distinct".

(Quote:Looker Studio)

With this, the settings are reflected in the report and the number of event types is displayed. Here, we have shown the number of all event types, but you can also use the filter function to see how much a specific group of events is occurring. For details, please refer to the filter usage guide | Common settings examples such as period selection and specific page filtering.

This concludes the method of displaying numbers using the COUNT_DISTINCT function. Please give it a try.

Relevant Looker Studio Official Documentation

Looker Studio Official Help : About calculated fields

Looker Studio Official Help : Function list