Metric Definition and Implementation Questions

Direct answer: Maintain an explicit internal/QA allowlist (by user_id or email domain) for known accounts, combine it with a behavioral bot-detection signal (e.g., an is_bot flag set upstream or derived from request patterns) for unknown bots, and default missing/ungraded accounts to INCLUDED rather than silently excluded, since silent exclusion hides real users and is much harder to notice than a slightly inflated count.

Structured elaboration:

• Internal/QA identification: a maintained allowlist (email domain match, or an explicit is_internal flag set at account creation) is exact and low-risk for internal accounts, since the company controls how those accounts are created and can tag them at the source.
• Bot identification: bots are adversarial and evolving, so no static list is complete; combine a known-bad-user-agent list with behavioral signals (implausibly high event frequency, requests with no normal human timing variance) and treat it as a probabilistic filter that needs periodic review, not a one-time static rule.
• Missing flags: a newly-created test account not yet added to the allowlist should default to being TREATED AS a real user in the metric (safer default) while a background reconciliation process (checking against the account-provisioning system) catches and backfills the correct flag, rather than silently excluding an unflagged account and potentially hiding a real user whose flag is simply missing due to a lag.
• Gradual onboarding: during a transition period where a filtering rule is being rolled out, run the OLD and NEW filtered counts side by side for a few weeks before cutting over, so a sudden change in the reported metric can be attributed specifically to the new filter (and validated as correct) rather than silently changing a live number with no visibility into why.

Worked example (executed): given events_raw(event_id, user_id, order_id, event_type, occurred_at, device_id, is_bot), computing each user's unique purchases needs to combine dedup-by-order_id, coalescing user/device identifiers when user_id is null, and excluding is_bot = true rows, all at once:

SELECT COALESCE(user_id, device_id) AS identity, COUNT(DISTINCT order_id) AS purchases
FROM events_raw
WHERE event_type = 'purchase' AND is_bot = false
GROUP BY COALESCE(user_id, device_id);

Run against a small synthetic table with three rows: a purchase from u1 on device d1 (is_bot=false), a second, bot-flagged purchase row with a NULL user_id on the SAME device d1 (is_bot=true), and a duplicate retry row for u1's same order_id (a retried write), this query returns exactly one row, (identity='u1', purchases=1). The bot-flagged row's contribution is excluded entirely, the retried duplicate is collapsed by COUNT(DISTINCT order_id), and the user's one legitimate purchase is still counted, confirming the bot filter and the dedup logic don't interfere with each other.

Trade-offs & pitfalls: The single most consequential design decision here is defaulting missing flags to INCLUDE rather than EXCLUDE: an over-aggressive default-exclude rule silently and invisibly undercounts real users whenever the allowlist/flag pipeline lags reality, which is a much harder bug to notice (nobody complains about a metric being too LOW due to missing real users) than a slightly inflated metric from a genuine bot slipping through temporarily.

Direct answer: Tumbling windows are fixed, non-overlapping calendar periods (a calendar day, a calendar month); sliding windows roll continuously (a trailing 7-day average recomputed every day); session windows are defined by activity gaps rather than a fixed clock. Choose a rolling 7-day average over month-to-date when you want a stable, noise-smoothed trend that updates daily; choose month-to-date when the business genuinely cares about a fixed accounting period (billing, reporting cadence).

Structured elaboration:

• Tumbling: simplest to reason about and to partition (each period is independent, no overlap to recompute), but can produce a big visible jump at the boundary (day 1 of a new month resets to a small base) that isn't a real behavior change, just an artifact of the fixed window resetting.
• Sliding (rolling): smooths day-to-day noise and avoids the reset artifact, at the cost of being harder to partition efficiently (each day's output depends on a moving set of prior days) and being less intuitive to a stakeholder who thinks in calendar months for planning purposes.
• Session-based: appropriate when the metric is fundamentally about a unit of engaged activity rather than a fixed clock period (as in S23's conversion-rate-per-session); not a substitute for tumbling/sliding when the business question is genuinely calendar-anchored (e.g., "this month's revenue").
• Recommendation: a rolling 7-day average suits a noisy daily operational metric (e.g., daily active users, which has strong day-of-week seasonality) where the goal is trend-watching; month-to-date suits a metric tied to a business/billing cycle where the goal is a specific period's total, and where partial-period comparisons ("we're at 60% of last month's total on day 18") are themselves meaningful.
• Timezone/DST pitfalls: a tumbling daily window computed with fixed 24-hour arithmetic silently mishandles the DST transition day (23 or 25 real hours), either dropping or double-counting an hour of activity; use calendar-aware date truncation, not fixed-duration bucketing, to avoid this. A rolling 7-day window crossing a DST boundary has the same latent issue if implemented as now() - 7*24 hours instead of date subtraction of 7 calendar days.

Worked example: a DAU metric with strong weekday/weekend seasonality looks noisy and hard to eyeball as a raw daily tumbling number; a rolling 7-day average smooths that seasonality out and makes a genuine trend change visible days sooner than waiting for a full month-to-date comparison would.

Trade-offs & pitfalls: Don't silently switch between these mid-series; each produces a genuinely different-shaped line even for identical underlying data, and a switch reads as a step change in trend if not explicitly flagged in the metric's documentation and on the chart itself.

Direct answer: Establish a small set of naming rules (a consistent verb/noun pattern, a required grain suffix, a controlled vocabulary for common dimensions), assign an owner and a review gate for any NEW metric, and a documented deprecation policy so metric sprawl is prevented at creation time rather than cleaned up after the fact.

Structured elaboration:

• Naming rules: a consistent pattern like <grain>_<subject>_<measure> (e.g., daily_active_users, weekly_active_paying_users) makes the grain and intent legible from the name alone, rather than requiring a lookup; ban ambiguous shorthand ("active," "engagement," used bare with no qualifier) as a metric name on its own.
• Canonical examples: publish 5-10 flagship metrics (DAU, MAU, MRR, conversion_rate, churn_rate) with their full definitions as the reference pattern new metrics should follow, so a new metric author has a concrete template rather than an abstract style guide.
• Ownership: every metric has an owning team assigned at creation, not retrofitted later; ownership is what makes the review gate below actually enforceable.
• Versioning and deprecation: a metric's calculation logic changes only via a new version (S61's change-management workflow); a metric no longer maintained is marked deprecated with a pointer to its replacement (if any) rather than silently left stale and orphaned.
• Lightweight review process: before a NEW metric is added to the shared catalog, a brief check (does an existing metric already cover this? does the name follow convention? is an owner assigned?) run by a small rotating review group, deliberately kept lightweight (a same-day turnaround, not a weeks-long committee process) so it doesn't become a bottleneck people route around.

Worked example: a company with 6 product teams each independently creating an "engagement score" metric (MECE data-dictionary discipline would catch this) ends up with 6 subtly different, confusingly identically-named metrics; a lightweight review gate that simply asks "does something like this already exist?" before approval would catch most of this duplication at the point of creation, far more cheaply than a later company-wide audit and consolidation project.

Trade-offs & pitfalls: A review process that is too heavy gets bypassed (teams just build the metric locally without registering it, defeating the entire point); a naming convention enforced only by a style guide with no actual review gate drifts within a quarter, since good intentions alone don't hold under deadline pressure. For a multi-region company, the taxonomy also needs to explicitly address per-region variation (different instrumentation coverage, currency, and privacy constraints across markets) as a documented EXCEPTION-HANDLING pattern within the naming convention (e.g., a region-scoped metric variant is named and registered distinctly from the global one, not silently substituted for it).

Direct answer: Use the backend payments ledger as the source of truth for revenue whenever accuracy matters more than speed (it's authoritative and includes refunds), and use the frontend SDK stream only for low-latency, directional signals (e.g., a live dashboard tile) where being slightly wrong briefly is an acceptable trade for freshness; when a third source (external market data) must be joined in, reconcile it against the ledger-based number, not the frontend stream, since the ledger is the more trustworthy anchor.

Structured elaboration:

• Deciding the source of truth: ask which error mode is worse for the specific use case. For financial reporting or anything feeding an official metric, an authoritative-but-slower source (the ledger) is worth the latency, because a wrong-but-fast number that later needs restating is more costly than a right number that arrives a bit later. For a live, directional "how are we doing right now" indicator, the frontend stream's speed may be worth its known double-counting risk, PROVIDED that risk is documented and the number is never fed into anything audited.
• Reconciling when a third source joins in: for a market-share metric (internal revenue ÷ external total market volume), the internal side should be the ledger-based number (accurate, refund-adjusted), since dividing an inflated internal number (frontend-stream double-counting) by an external total would systematically overstate market share, an error that compounds rather than averages out.
• Ongoing process: run both internal sources side by side with a documented, monitored gap (the frontend stream will typically read HIGHER than the ledger, consistently, due to its known double-counting tendency); a sudden CHANGE in that gap's typical size, not the gap's mere existence, is the signal worth investigating.

Worked example: a live "revenue today" dashboard tile can reasonably use the frontend stream for immediacy, with a visible caveat ("preliminary, reconciles against ledger by end of day"); the OFFICIAL "revenue this month" number feeding a board report must use the ledger; and a market-share metric joining external market-volume data should use the ledger-based internal revenue, explicitly, with a footnote on the methodology so a reviewer understands why the "live" dashboard number and the "official" reported number can legitimately differ by a few percent on any given day.

Trade-offs & pitfalls: The common mistake is treating "source of truth" as a single global property of a data source rather than a property relative to a SPECIFIC use case's tolerance for latency versus accuracy; a source that's right for a live indicator can be wrong for an audited report, and vice versa (the ledger, while accurate, may simply be too slow to power a genuinely "live" experience). State explicitly, for every metric, which source it uses and why, rather than assuming one universal answer to "which system is right."

Direct answer: Last-touch credits the final touchpoint before conversion, first-touch credits the very first touchpoint, and simple multi-touch splits credit evenly (or by a simple rule) across all touchpoints in the path; for short-term budget decisions use last-touch (or a lightweight multi-touch), and for long-term brand-investment measurement use first-touch or a fuller multi-touch model.

Structured elaboration:

• Last-touch: simple, cheap, and directly actionable for "which channel should get more budget next week," because it answers "what closed the sale," which is what a performance-marketing budget decision usually needs. Weakness: systematically undercredits upper-funnel/awareness channels (a brand ad seen weeks before the final retargeting click gets zero credit).
• First-touch: the mirror image; good for understanding what DRIVES AWARENESS and initial discovery, weak for understanding what closes deals. Overcredits channels that are good at initial reach but not conversion.
• Simple multi-touch (e.g., linear, evenly split across the average 3 touchpoints): more balanced, but requires reliable cross-touchpoint identity stitching (the same user's touches must be joinable), which is a real data dependency last-touch and first-touch don't need at all (they only need the one relevant touch).

Worked example: a user sees a display ad (touch 1), later clicks a paid search ad (touch 2), then converts after clicking an email link (touch 3). Last-touch gives 100% credit to email; first-touch gives 100% to display; even-split multi-touch gives ~33% to each. For a short-term "which channel's spend should I cut this week" decision, last-touch is defensible and cheap: it tells you what's closing sales right now. For a long-term "is our brand-awareness spend paying off" question, first-touch or multi-touch is necessary, because last-touch would make the display ad look worthless despite it starting the journey.

Trade-offs & pitfalls: Recommending one model as universally "more accurate" is a common overreach; each model answers a DIFFERENT question, and the real skill is matching the model to the decision, then being explicit that the number would look different under another model, so nobody mistakes an attribution CHOICE for an attribution FACT. Multi-touch models also require more infrastructure (identity resolution, a defined attribution window) that last-touch/first-touch don't, which is itself a legitimate reason a team defers multi-touch even when it would be conceptually more balanced.