Define active users before calculating DAU and MAU

Define the qualifying behavior, identity, time window and exclusions before counting active users. A page load, heartbeat and completed report represent different levels of product use; treating them as interchangeable can inflate activity without showing delivered value.

DAU and MAU are meaningful when the audience can reconstruct who counted and why. The acronym alone is not a metric contract.

State a concrete activity definition

The product analytics lab models a fictional reporting product. An active user is an external, nonbot account with at least one valid report_created, report_published, report_viewed or report_shared event in the window.

Page views and heartbeats do not qualify. The reporting timezone is UTC, and interval starts are inclusive while ends are exclusive. User identity is user_id after identical event-ID replay handling.

These are authored choices for the example, not universal definitions used by every analytics platform.

Calculate the numerator and denominator

SELECT
 COUNT(DISTINCT CASE WHEN e.event_time >= '2026-01-15T00:00:00Z'
                     THEN e.user_id END) AS dau_jan15,
 COUNT(DISTINCT e.user_id) AS rolling_30day_active
FROM events e
JOIN users u USING(user_id)
WHERE u.user_kind = 'external' AND u.is_bot = 0 AND e.valid = 1
  AND e.event_name IN ('report_created','report_published','report_viewed','report_shared')
  AND e.event_time >= '2025-12-17T00:00:00Z'
  AND e.event_time < '2026-01-16T00:00:00Z';

Expected output is four daily active users and seven rolling-30-day active users. All timestamps share a canonical UTC format in this fixture, so the text comparisons preserve chronological order.

from build_and_verify import database, active_ids, CUTOFF

db = database()
daily = active_ids(db, '2026-01-15T00:00:00Z', CUTOFF)
rolling = active_ids(db, '2025-12-17T00:00:00Z', CUTOFF)
assert daily == ['U1', 'U2', 'U3', 'U5']
assert rolling == ['U1', 'U2', 'U3', 'U4', 'U5', 'U6', 'U9']
assert set(daily).issubset(rolling)
db.close()
print({'DAU': len(daily), 'rolling30_active': len(rolling), 'ratio': len(daily)/len(rolling)})

The daily-to-rolling ratio is 4/7, about 57.1%. It describes one day's share of the recent active population under this definition. It is not a retention probability or a universal product-quality score.

Distinguish rolling and calendar periods

The denominator covers December 17 through January 15, exactly thirty UTC calendar days. A calendar-month measure would use January 1 through February 1. A January-to-date measure at this cutoff would stop on January 16.

The fixture happens to contain no qualifying December events, but the definitions remain different. Do not infer equivalence from one dataset where their counts coincide.

Platforms also distinguish unique users, event totals and other aggregations. The Mixpanel Insights documentation describes event analysis and measurement choices. Reconcile platform settings with your contract before comparing its headline with SQL.

Inspect who was excluded

U7 is an internal account and U8 a bot. U4, U6 and U9 have only page-view activity on January 15, so they do not enter that day's qualified DAU even though they appear in the rolling population through earlier meaningful events.

E32 occurs exactly at January 16 midnight and is outside the reporting interval. An identical E02 replay does not create an additional user or accepted event.

Keep completeness and identity visible

A missing event feed can reduce DAU without a real product decline. An identity migration can increase distinct IDs without adding people. Retain ingestion coverage, identity rules and relevant schema changes alongside trend interpretation.

The lab assumes complete coverage for its synthetic window. A production event table cannot prove that assumption by itself.

Exercise: add a second qualifying January 15 event for U1 and verify unchanged DAU. Then add a qualifying event for U4 and confirm DAU increases by one while rolling active users remain seven.

NeuraPath's Data Analytics with Generative AI course connects SQL metrics with product definitions. A useful active-user report explains the behavior and population behind the count.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Continue with Build a product funnel with an explicit event contract.
• Then apply it in Activation metrics: connect first value to observable behaviour.