The "what is an active user" problem

A Tuesday standup at Swiggy. Riya from growth shows a dashboard: 4.6 crore weekly active users, up 3% week-over-week. Karan from finance opens his deck for the same week: 4.2 crore. Aditi, the central analytics lead, runs her own SQL on the spot: 3.9 crore. Same warehouse, same fct_user_events table, same time window, three answers separated by 18%. Nobody has bugged code. Each query is internally correct. The disagreement is older than any of the three queries — it is about what the word "active" means, and the meeting is about to spend forty-five minutes discovering that nobody had ever written it down.

"Active user" is the canonical metrics-layer puzzle because it has at least a dozen valid SQL definitions, each producing a different number, and every definition is internally consistent. The metrics layer's job is not to discover the "right" definition — it is to force the org to commit to one, version it, and make every dashboard cite which version it used. Every team that skipped this step rediscovers it the hard way.

Why one phrase produces twelve numbers

"Active user" hides at least four orthogonal choices, and each choice has multiple legitimate values:

  1. What counts as an "action"? Opening the app, logging in, completing a transaction, scrolling for ≥30 seconds, sending a message — each event type lives in its own table or has its own event_type filter, and product teams across an org will pick different ones.
  2. What identifier counts as a "user"? user_id (logged in), device_id (anyone with the app installed), cookie_id (web visitors), phone_hash (matched across logged-in and logged-out states), or all of the above with a graph-based identity resolver.
  3. What time window counts as the "period"? Trailing 7 days from the query moment, calendar week (Mon–Sun), ISO week, fiscal week, week-ending-Sunday — the bucketing function alone produces 4–5 numbers.
  4. What user attributes apply — as-of when? A user in Pune who moved to Bengaluru in March: do you count them in "active users in Maharashtra for week of 2026-04-01" using their March address or their April address? Both answers are legal; only one matches what the marketing slide deck assumes.

Multiply: 3 plausible action types × 2 identity choices × 2 time-bucketing conventions × 2 as-of choices = 24 possible numbers. In practice teams converge on around 12 because some combinations don't make sense, but the explosion is real and visible at every company that crosses 50 dashboards.

Twelve definitions of "active user" from one phraseA four-level decision tree. Level 1: action type — branches into "session_start", "any transactional event", "scroll > 30s". Level 2: identity — branches into user_id, device_id. Level 3: time grain — branches into trailing-7d, ISO-week. Level 4: as-of attributes — branches into "as-of-today", "as-of-period". Twelve leaf nodes total, each producing a different number for the same English phrase. "Weekly active user" decomposes into four orthogonal choices "WAU" action: session_start action: any txn event action: scroll > 30s user_id device_id user_id device_id user_id device_id trailing-7d | ISO-week trailing-7d | ISO-week trailing-7d | ISO-week attrs: as-of-today vs as-of-period attrs: as-of-today vs as-of-period attrs: as-of-today vs as-of-period 3 × 2 × 2 × 2 = 24 valid combinations; ~12 are commonly chosen
The phrase "weekly active user" hides four orthogonal choices. Each choice has multiple legitimate values, and the combinations explode. Most teams pick one path through this tree without realising they had a choice — until another team shows up with a different number.

A side-by-side worked example

Take a single week of Swiggy data (synthetic but realistic shape) and compute four definitions on the same rows. The numbers diverge by 22% across the four — and these are not bugs.

# wau_definitions.py — same data, four legitimate "WAU" answers.
import duckdb

con = duckdb.connect()
con.execute("""
CREATE TABLE fct_user_events AS
SELECT * FROM (VALUES
    -- (user_id, device_id, event_ts, event_type, country)
    (101, 'd1', TIMESTAMP '2026-04-13 09:12:00', 'session_start', 'IN'),
    (101, 'd1', TIMESTAMP '2026-04-15 18:30:00', 'order_placed',  'IN'),
    (102, 'd2', TIMESTAMP '2026-04-13 11:00:00', 'session_start', 'IN'),
    (102, 'd2', TIMESTAMP '2026-04-13 11:01:00', 'scroll_30s',    'IN'),
    (103, 'd3', TIMESTAMP '2026-04-14 14:20:00', 'session_start', 'IN'),
    (103, 'd9', TIMESTAMP '2026-04-16 09:00:00', 'session_start', 'IN'),  -- new device
    (NULL,'d4', TIMESTAMP '2026-04-15 22:00:00', 'session_start', 'IN'),  -- logged-out
    (NULL,'d5', TIMESTAMP '2026-04-15 22:30:00', 'scroll_30s',    'IN'),
    (104, 'd6', TIMESTAMP '2026-04-12 23:59:00', 'session_start', 'IN'),  -- prior week
    (105, 'd7', TIMESTAMP '2026-04-19 23:30:00', 'session_start', 'IN')   -- next week boundary
) AS t(user_id, device_id, event_ts, event_type, country);
""")

WEEK = "event_ts >= '2026-04-13' AND event_ts < '2026-04-20'"

defs = {
  "D1: distinct user_id, session_start only":
    f"SELECT COUNT(DISTINCT user_id) FROM fct_user_events "
    f"WHERE {WEEK} AND event_type='session_start' AND user_id IS NOT NULL",
  "D2: distinct device_id, session_start only":
    f"SELECT COUNT(DISTINCT device_id) FROM fct_user_events "
    f"WHERE {WEEK} AND event_type='session_start'",
  "D3: distinct user_id, ANY event_type":
    f"SELECT COUNT(DISTINCT user_id) FROM fct_user_events "
    f"WHERE {WEEK} AND user_id IS NOT NULL",
  "D4: distinct device_id, ANY event_type (incl. logged-out)":
    f"SELECT COUNT(DISTINCT device_id) FROM fct_user_events WHERE {WEEK}",
}
for label, q in defs.items():
    print(f"{label:55s} -> {con.execute(q).fetchone()[0]}")

# Sample run:
D1: distinct user_id, session_start only                -> 4
D2: distinct device_id, session_start only              -> 6
D3: distinct user_id, ANY event_type                    -> 4
D4: distinct device_id, ANY event_type (incl. logged-out) -> 7

Walk the load-bearing pieces:

D1 and D3 happen to match here at 4, but only because of the specific synthetic data. On real Swiggy-scale data they typically differ by 6-12% because some users perform actions (like accepting a push-notification deep-link) without ever firing a session_start.

The committee meeting that actually fixes it

The fix is not "find the right definition". The fix is to run a 90-minute meeting that produces three artefacts and then never re-run it. The artefacts:

  1. A primary definition, named and versioned. "WAU v1 = distinct user_id with at least one session_start event in the trailing ISO-week, attributes resolved as-of-today." That is the layer's default. Every dashboard that says "WAU" cites v1 unless explicitly noting otherwise.

  2. A documented set of variants, each with a use case. WAU v1 (default), wau_device (device-id based, for growth), wau_engaged (any event, for product), wau_paying (transactional events only, for finance). Four metrics, four named definitions, four explicit purposes. Anything outside this set requires a registry update.

  3. A migration plan for existing dashboards. A spreadsheet with every dashboard, the SQL it currently runs, the variant it should map to, the expected delta in the headline number, and the date it will switch. Without this, the 90-minute meeting was theatre — every "single source of truth" rollout dies because the rollout-tracking artefact was never produced.

From "active user" English phrase to four committed metricsA pipeline diagram showing the resolution process. Left: an English phrase "weekly active user". Middle: a committee meeting block listing the four orthogonal choices that must be made. Right: four registered metrics emerging — wau (default), wau_device, wau_engaged, wau_paying — each with an owner, a version, and a date. Below: a "migration tracker" box showing dashboards moving from raw SQL to the registered metrics over a six-month timeline. From English phrase to committed registry entries "Weekly active user" English phrase Definition committee 90 minutes, four choices: action type, identity, time grain, as-of policy → produces 1 default + 3 variants wau v1 (default) — central wau_device v1 — growth wau_engaged v1 — product wau_paying v1 — finance Migration tracker (6-month rollout) Mo 1: 8 dashes Mo 2: 25 dashes Mo 4: 60 dashes Mo 6: 95 dashes
The fix is procedural, not technical. A definition committee turns one ambiguous English phrase into four versioned metrics with named owners, then a six-month tracker measures dashboard adoption. Skip either step and the org reverts to fragmentation within a quarter.

The hardest part is not the technical encoding. The hardest part is having the political mandate to say "from 2026-05-01, the board deck cites WAU v1; if you want a different number, register wau_v2 and explain why." Razorpay's central data team got this mandate after a 2024 board meeting where the CFO and CMO presented WAU numbers off by 11% on the same slide; Cred's data team got it after a 2025 fundraise diligence call that asked which number to trust. The mandate is usually downstream of an embarrassment.

What the metrics layer actually has to encode

Once the committee has named the four metrics, the metrics-layer registry must encode:

Six fields plus a version. That is the whole encoding. Every metrics-layer tool — Looker, MetricFlow, Cube, dbt — implements these fields with different syntax. The semantic content is identical.

Common confusions

Going deeper

How LinkedIn's data team versioned their flagship metric

LinkedIn's "weekly visiting members" metric — their public-disclosed engagement number — went through three versions between 2018 and 2023 as the company refined what counted. Each version change was announced internally with a six-month transition where both old and new numbers were reported in parallel, and external disclosures specified which version was being cited. The lesson for Indian-scale companies (Flipkart, Swiggy, Zomato preparing for IPO disclosures): the metric in your S-1 filing is a contract with regulators and investors; "we changed how we counted" is not a defence three years later. Version, parallel-report, and pin.

As-of-period vs as-of-today: the SCD-2 join trap

The "as-of-period" semantic — joining a user's attribute table using the period's timestamp rather than today — requires a Slowly Changing Dimension Type 2 (SCD-2) table on the user side. Most teams ship dim_user as SCD-1 (just current values), which makes as-of-period queries impossible without a full historical rebuild. Adding SCD-2 retroactively requires either a CDC log on the user table from day one (rare) or a "reasonable approximation" rebuild from event history (lossy). The cheapest fix is to mandate SCD-2 on every dim_table from day one, even though 80% of analyses use as-of-today; the 20% that need as-of-period will not negotiate.

Backfilling a v1 metric when the source schema changed

Razorpay's payments team published WAU back to 2021 when they registered the v1 metric in 2024. The challenge: the fct_user_events table's schema had changed three times — event_type enum values were added ('session_start' was renamed from 'app_open' in 2022), and the user_id column moved from INT to BIGINT in 2023. The backfill SQL had to apply different logic per time period: pre-2022 used the old enum, 2022-2023 used a coalesce, 2023-onward used the current schema. The backfill script lives in version control, the parallel-period numbers were validated against three preserved historical dashboards, and a runbook documents the schema-shift dates. This is the kind of work no metrics-layer tool will do for you; it is engineering labour amortised over the next decade of board meetings.

When the committee can't agree (and what Cred did)

In a 2024 Cred standup, the growth team and finance team could not agree on whether "active" should require a transactional event. Growth wanted session_start (broader funnel); finance wanted transactional event (narrower, revenue-aligned). The compromise: register both as separate top-level metrics — wau (session-based, the company-default) and wau_paying (transactional, the finance default) — and require every dashboard to label which one it shows. This sounds obvious, but the political work was getting both teams to stop calling their own number "WAU" and start labelling it. The artefact that locked the change in was a slack-bot that posted a daily reminder to the analytics channel showing the two numbers side by side. After three months the labelling habit stuck.

Where this leads next

The metrics layer's encoding lives in tools like dbt + MetricFlow (/wiki/dbt-as-the-de-facto-transformation-layer) and headless engines (/wiki/the-headless-bi-movement). The cross-tool comparison (/wiki/lookml-cube-metricflow-the-landscape) walks through how each tool encodes the seven fields above, and where they fall short.

The deeper question — does the metrics layer require human governance forever, or can LLMs constrain themselves to the registry? — is in /wiki/semantic-layer-llms-the-new-interface. And the meta-narrative of why this category keeps getting reinvented is the previous chapter, /wiki/wall-the-metrics-layer-keeps-getting-re-invented.

Build 13 as a whole answers the question the warehouse cannot: when bytes have left the engine and meaning starts, who owns the meaning. The "active user" problem is the canonical case, but the same shape recurs for "revenue", "conversion rate", "retention" — every aggregate that humans argue about. The mechanism is identical; learn it once, apply it ten times.

References