University > Looker Studio Course > Formulas > Mastering the COUNT Function in Looker Studio

Mastering the COUNT Function in Looker Studio

The COUNT function is an essential tool for analyzing and summarizing data in Looker Studio (formerly Google Data Studio). With the ability to quickly count rows, values, and other records in your datasets, the COUNT function unlocks powerful reporting capabilities. In this comprehensive tutorial, we’ll explore the ins and outs of using COUNT to enhance your data analysis.

A simple and practical tool for your day to day usages

Learning to leverage the COUNT function is a must for any Looker Studio user looking to go beyond basic reporting. With the right COUNT formulas, you can derive key metrics, summarize large datasets, and better understand your data at an aggregate level.

- What COUNT does and why it's useful
- How to create calculated fields with COUNT
- Applying COUNT directly in metrics
- Using COUNT vs COUNT DISTINCT
- Real-world examples and use cases

Follow along as we dive into the mechanics of COUNT and see how it can take your Looker Studio reports to the next level. Whether you're counting sessions, orders, visitors, or any other metric, this guide will help you master the nuances of this important function. Let's get counting!

What does the COUNT function do?

COUNT tallies up the number of values or rows in a field or data set. Here are the key things you have to know:

Use it to count rows, numeric values, or anything with a quantity.
It counts all values including duplicates unless you use COUNT DISTINCT.
Counts non-empty fields and ignores null values.
Can be used in calculated fields or directly in metrics.

How to create a calculated field with COUNT

Let's look at an example of an ecommerce dataset with an orders table using COUNT in a calculated field.

Follow these steps:

In Fields, click "New Calculated Field."
Name it something like "Total Orders."
In the formula enter: COUNT([Order ID])
Order ID is the name of the field we want to count.
Click "Save" to create the calculation.

Now we have a field that dynamically counts all orders as new data comes in. You don’t have to go through your whole document to be sure you are not missing some important insights. COUNT can also be used to check the exact amount of data lines have been processed.

The power of metrics

A metric is an aggregated value derived from applying a function like COUNT, SUM, or AVG to a field or set of data. Unlike dimensions which have defined values you can group by, metrics are aggregated numbers with no distinct values of their own. The aggregation function condenses a set of data points into a single summarized value.

Some examples of metrics include:

Total revenue calculated by summing a revenue column
Average order value from applying AVG to order amounts
User count generated by counting rows in a user table

The key distinction is that metrics are aggregated values rather than specific data points. You can’t break a metric down further into groups or segments. The raw data is aggregated into a summary number like a total, average, or count.

Metrics help provide high-level summaries and KPIs for analysis and reporting. Dimensions let you dig deeper into the underlying data categories and segments. Together, metrics and dimensions provide different lenses for exploring your data.

How to use COUNT in metrics

COUNT can also be used directly as a metric aggregation. For example, if you want to show the total number of users in our Users table you just have to follow these simple steps:

In Metrics, click "Add Metric."
Select "User ID" as the field.
For Aggregation select "COUNT".
Save metric.

This will display a total count of user IDs as a metric. You can understand this metric usage as a specific extension to the original utilization of the COUNT function.

Let’s tackle a real world example

Website Traffic Analysis is a common task for online marketing specialists and webmasters.

‍
Let's produce web analytics data simply using the COUNT function in Looker Studio. Our dataset has the following informations : Pages, Visitors, Sessions and surely many others.

The marketing team wants a report with:

Total number of website sessions
Total number of unique visitors
Pages per session (Total Pages / Total Sessions)

Here's how we can use COUNT to calculate these. We will also introduce the COUNT_DISTINCT variation of the basic COUNT function. The COUNT_DISTINCT function counts the number of unique items in a field. You have to use the syntax COUNT_DISTINCT(value) where value is a field or an expression that contains the items to be counted.

The report will be created with only three steps. You need to:

Count Sessions with COUNT(Sessions)
Count unique visitors with COUNT DISTINCT(Visitor ID)
Divide Total Pages by Total Sessions for Pages per Session.

Using COUNT formulas provides the ability to analyze traffic and engagement with only two basic Looker Studio statements.

Key Takeaways

The COUNT function is great and simple tool for:

Tallying total records like orders, sessions, users, etc.
Counting field values like revenue amounts, ages, and so on.
You can use its COUNT DISTINCT variant for unique counts.
Please do remember that counts are non-empty values only.
Apply COUNT to either calculated fields or directly when using metrics.

Learning to effectively use COUNT in Looker Studio opens up many possibilities for analyzing and reporting on your business data. It is one more powerful insight generator that is straightforward to use.

‍

More function to use with Looker Studio

DATETIME_ADD

Mastering Date and Time Data Manipulation: An In-Depth Guide to DATETIME_ADD Function in Looker Studio

ASIN

Taking Advantage of Google Data Studio's ASIN Function

DATE_FROM_UNIX_DATE

Decoding Date Data: Understanding and Applying the DATE_FROM_UNIX_DATE Function in Looker Studio

TRIM

Mastering Text Data Manipulation: An In-Depth Guide to the TRIM Function in Looker Studio

FORMAT_DATETIME

Understanding the FORMAT_DATETIME Function in Looker Studio for Enhanced Data Analysis and Presentation

Become a Looker Studio expert
for free

Learn all the secrets of data analysis and create beautiful and effective dashboards thanks to our 30-second video courses! Join Catchr's community on YouTube.

Get more video lessons