Write a Precise, Unambiguous Metric Definition

Half of all 'the dashboards disagree' fire drills trace back to a metric nobody defined precisely. Is an active user someone who logged in, or who took an action? Does the time window reset at midnight UTC or in the user's zone? Do trial accounts count? This prompt forces every one of those choices into the open and writes them down, turning a fuzzy noun into something two teams can implement identically. The structure, plain definition, exact formula, grain, inclusion and exclusion rules, slice-able dimensions, and resolved edge cases, mirrors how strong analytics teams document metrics so the number means the same thing in finance, product, and the board deck. The SQL-pseudocode sketch at the end bridges definition to implementation without pretending to be production code. Use it whenever a metric is about to appear in more than one place, when two reports already disagree, or when onboarding someone who keeps asking 'wait, how is that calculated?'

What you can expect back

Definition: MAU is the count of distinct accounts that performed at least one qualifying action in a calendar month.

Formula: COUNT(DISTINCT user_id) where the event is a qualifying action within the month. No denominator; it is a count.

Grain and window: one number per calendar month, in UTC.

Inclusion/exclusion: include only accounts.type = 'customer'; exclude internal/test accounts and bot user_ids; a passive login with no further action does NOT qualify; drop events with NULL user_id.

Slice by: plan, country, signup cohort, platform.

Edge cases: (1) a user active on the last day of one month and first of the next counts in both months, by design, since the window is per calendar month. (2) A user who only opens an email is excluded because that is not a qualifying in-product action.

Sketch: SELECT date_trunc('month', ts) AS m, COUNT(DISTINCT user_id) FROM events JOIN accounts ... WHERE event_name IN (<qualifying list>) AND account.type='customer' GROUP BY 1;

Still to decide: which exact event_names count as 'qualifying', and whether to use UTC or account-local time.

Illustrative example — your results will vary by tool and inputs.

Get sharper results

• 01Force a yes/no on every exclusion, test accounts, bots, refunds, NULLs, vagueness here is where two dashboards diverge.
• 02Name the time zone explicitly; UTC versus local quietly shifts daily and monthly counts.
• 03List the 'qualifying action' events by name, 'active' is meaningless until you enumerate what counts.
• 04Keep the edge-case section, it is the documentation future-you will thank present-you for.

Adapt it for your case