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

This article clearly explains how to use the COUNT function in Looker Studio and provides concrete examples of its application. Utilize the functions available in Looker Studio to process data and visualize it.

For processing data in GA4 (Google Analytics 4), many tasks can be accomplished with calculated fields without needing to use Big Query. Specific examples will be explained based on GA4 data accessed via connectors, so please make use of this information.

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

The COUNT function is used to count the number of records that meet specified conditions. As a result of the calculation, it produces a numerical value. The count includes duplicates since it simply totals the number of records. If you want to count the number of unique values excluding duplicates, you should use the COUNT_DISTINCT function.

Syntax

The syntax of the COUNT function is as follows:

COUNT("target to count")

In "target to count" specify the dimension, metric, or expression to be counted. The number of records for the specified target will be counted.

Conditions for Using the COUNT Function

The COUNT 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 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 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 function cannot be used on them. However, it can be used on dimensions.

(Quote)Looker Studio 

How to Use the COUNT Function

To use the COUNT 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 Number of Pages Viewed Per Date in Looker Studio

Set up the calculated field as follows:

 

 (Quote)Looker Studio 

 

1. Field Name: Enter any field name.
2. Formula: COUNT(Page Path and Screen Class)

This time, we want to count without differentiating the pages by parameters, so we specify "Page Path and Screen Class".

3. Save: Once you have completed the input, click save.

As a result, a new metric called "Page Count" has been created.

 (Quote)Looker Studio  

 

Next, since the metric we created earlier is now available for use in reports, we will add it to the metrics.

By setting it up as follows, you can visualize the number of pages viewed per date.

 (Quote)Looker Studio  

 Use case: Visualize the number of regions viewed per date in Looker Studio

Set up the calculated field as follows:

  (Quote)Looker Studio 

1. Field name: Please enter any field name.

2. Formula: COUNT(region)

The dimension of the region is initially set to prefecture level.

3. Save: Click save once you have completed the input.

As a result, a new metric called 'number of regions' has been created.

 

Next, since the metric we created earlier is now available for use in reports, we will continue to add it to the metrics.

By setting it up as follows, you can visualize the number of regions per date.

  (Quote)Looker Studio 

How to Count Numbers Without Using Calculated Fields

For relatively simple calculations such as counts, it is possible to reflect them in reports without using calculated fields.

Use case: Visualizing the Number of Landing Pages per Day in Looker Studio

First, click the mark on the left side of the metric you want to count in the report.

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

 

 (Quote)Looker Studio 

This will reflect the settings in the report and display the number of landing pages. This is how to display numbers using the COUNT function. Please give it a try.

Relevant Looker Studio Official Documentation

Looker Studio Official Help : About calculated fields

Looker Studio Official Help : Function list