Counting Monthly Active Users (MAU)

The Executive Dashboard

A SaaS company lives and dies by its Monthly Active Users (MAU). Investors ask about it. The board tracks it. Today, the CEO asked me to build this metric from scratch.

The Quest: Defining "Active"

Before writing any SQL, we had to agree on the definition:

A "User" is a unique `user_id`.

"Active" means they performed any event (login, click, etc.) in the `events` table.

"Monthly" means within a specific calendar month.

The Implementation: The MAU Query

-- MAU for January 2026

SELECT

COUNT(DISTINCT user_id) AS mau

FROM events

WHERE event_date BETWEEN '2026-01-01' AND '2026-01-31';

Trend: MAU Over Time

To see every month:

SELECT

DATE_TRUNC('month', event_date) AS activity_month,

COUNT(DISTINCT user_id) AS mau

FROM events

GROUP BY DATE_TRUNC('month', event_date)

ORDER BY activity_month;

The "Oops" Moment

I once forgot to use `DISTINCT`. My query returned the number of *events* (100,000), not the number of *users* (5,000). The CEO thought we went viral.

**Pro Tip**: When counting people, always use `COUNT(DISTINCT identifier)`. Otherwise, a power user who logs in 100 times will count as 100 people.

The Victory

The dashboard now shows a clear MAU trend line, going up and to the right. It's the single number that the entire leadership team trusts. Building MAU is one of the most valuable skills in the modern data economy.

Your Task for Today

Calculate your own "Monthly Active Users" from a table of logins or events. See how the count changes when you use `DISTINCT` vs. without.

*Day 25: Calculating Revenue Per Customer.*