Metric Definition and Implementation Questions

Direct answer: Three dedup strategies cover most cases: order-id (or event-id) based dedup for a system with a stable idempotency key, session-window dedup for click/view-type events that lack a natural key, and cross-device probabilistic dedup when the same real action is logged from multiple identifiers (web + mobile) with no shared key at all.

Structured elaboration:

1. Order-id / event-id based dedup: keep one row per business key (e.g., order_id), typically the latest by ingestion time. Fails when: the upstream system does NOT guarantee a stable key across retries (e.g., a client-side retry generates a new event_id for what is logically the same user action), producing silent over-counting no dedup step catches, because there is nothing to key on.
2. Session-window dedup: collapse events from the same user within an inactivity gap (commonly 30 minutes) into one session, then count once per session rather than once per raw event. Fails when: a genuinely fast second visit (user leaves and comes back in 10 minutes for an unrelated reason) gets merged into the same session and undercounts distinct usage occasions; the window boundary is also timezone/DST-sensitive.
3. Cross-device probabilistic dedup: match events across web_events and mobile_events using signals like hashed email, IP + user-agent pattern, or timing proximity, when there is no shared login identifier. Fails when: two DIFFERENT users share a device fingerprint (a household on one Wi-Fi network, a shared kiosk), causing a false-positive merge that undercounts unique users, or conversely a probabilistic match set with too strict a threshold fails to merge a real duplicate and overcounts.

Worked example: for a "purchase" metric, order-id dedup is nearly always correct if the checkout system assigns a stable order_id before retries; you would NOT reach for session-window or probabilistic dedup here, because a stable key already exists, and using a fuzzier method would only introduce risk for no benefit. Session-window dedup is the right tool for a "page view" metric with no natural key. Probabilistic dedup is reserved for genuinely cross-surface identity problems (e.g., "how many distinct people visited," not "how many purchases"), because it trades exactness for coverage.

Trade-offs & pitfalls: The choice is not a checklist to apply all three; it is matched to whether a reliable key exists. Applying session-window dedup where an order-id already exists throws away precision for no reason; applying probabilistic dedup by default (instead of only when cross-device identity is genuinely required) introduces false merges into every downstream metric. State which strategy you used and why in the metric's documentation, because a reviewer cannot tell from the output number alone which failure mode is live.

Direct answer: Buffer DAU computation behind a documented watermark (don't finalize "today's" number until enough time has passed for the vast majority of late data to arrive), explicitly label each day as provisional or finalized, and run a periodic backfill correction for the residual late data that arrives even after the watermark.

Structured elaboration:

• Event-time vs processing-time: compute DAU using each event's own event_time (when the activity actually happened), not processing_time/ingestion_time (when the pipeline saw it); using processing-time would attribute a late-arriving event to the day it ARRIVED rather than the day it actually occurred, silently misplacing activity across day boundaries.
• Watermarking: define a watermark (e.g., "we consider a day's data complete after 24 hours," calibrated from OBSERVED historical arrival-lag distributions, not a guess) after which the day's DAU is marked FINAL; before the watermark passes, the day's number is PROVISIONAL and should be labeled as such wherever displayed.
• Buffering: hold the day's computation open (recomputing as new events for that day continue arriving) until the watermark passes, rather than computing once and never revisiting.
• Backfill for residual lateness: some data will inevitably arrive even after a generous watermark (the long tail of late arrivals); run a periodic (e.g., weekly) reconciliation pass that re-checks recently-finalized days against fresh data and issues a documented, versioned correction if the residual lateness meaningfully changed the number, rather than assuming the watermark caught everything perfectly.
• Labeling for downstream reports: every DAU value exposed to a consumer carries a status field (provisional | final), so a dashboard or alert can choose to only trust final values for anything sensitive (an OKR grading, a financial figure) while still surfacing provisional values for a live, directional view.

Worked example: a watermark of 24 hours, calibrated from historical data showing 99% of events arrive within 20 hours, will occasionally still miss the remaining ~1% of genuinely late events (a delayed sync from a spotty-network mobile client, for instance); the weekly reconciliation pass catches and corrects for that residual, typically a very small adjustment, but one that should still be versioned and disclosed rather than silently absorbed into the "final" number without a trace.

Trade-offs & pitfalls: setting the watermark too short (finalizing too early) means "final" numbers still get revised often, undermining trust in the "final" label itself; setting it too long delays useful reporting unnecessarily. Calibrate it from actual observed arrival-lag data and revisit the calibration periodically, since arrival-lag characteristics can shift as client versions, network conditions, or upstream systems change.

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: Detect bot contamination via signals like implausibly high per-user event frequency, identical or suspicious user-agent/IP patterns across many "distinct" accounts, and unnatural timing regularity; filter those events out with documented rules, quantify the historical metric impact by recomputing key metrics with the filter applied retroactively, and add continuous monitoring so a future bot wave is caught quickly rather than discovered months later.

Structured elaboration:

• Detection signals: (1) event frequency far beyond plausible human behavior (hundreds of events per minute from one account); (2) identical user-agent strings or IP ranges shared across many supposedly-distinct accounts, suggesting a scripted source; (3) unnaturally regular timing (events firing at exact fixed intervals, which real human behavior essentially never produces).
• Filtering rules: combine multiple weaker signals (frequency + UA pattern + timing regularity) rather than any single one alone, since a single signal in isolation has both false positives (a legitimate power user with an unusual workflow) and false negatives (a well-disguised bot).
• Estimating historical impact: recompute the affected metrics (e.g., session counts, DAU) for the contaminated historical period WITH the new bot filter applied, and report both the before/after numbers and the percentage of the metric that was bot-driven, so stakeholders understand the SCALE of the correction, not just that a correction happened.
• Continuous monitoring: track the RATE of events matching bot-like signals as its own metric over time (not just filtering them silently), so a new wave of bot traffic shows up as a rising trend in that monitoring metric before it meaningfully distorts the primary business metrics.

Worked example: if session counts were inflated 8% by a bot wave discovered retroactively, recomputing the trailing 6 months with the new filter and publishing both series (as-reported and bot-corrected) lets stakeholders see exactly which weeks were most affected, rather than a single blanket disclaimer; if the bot traffic was concentrated in specific weeks (a specific attack/scraping campaign), the correction should show that concentration, not a flat percentage smeared evenly across the whole period, which is a common shortcut that misrepresents when the actual damage occurred.

Trade-offs & pitfalls: Retroactively correcting historical numbers changes previously reported figures, which needs explicit communication (a footnote, a documented restatement) rather than silently updating a dashboard's historical trend line, since stakeholders may have already made decisions based on the uncorrected numbers and need to know what changed and why. Also watch for over-correction: an overly aggressive bot filter applied retroactively can start excluding legitimate power users who happen to match one weak signal, understating real engagement in the corrected series.

Direct answer: Compute the weighted conversion rate as the weight-adjusted sum of conversions divided by the weight-adjusted sum of totals, and approximate its standard error using a weighted-variance formula (a design-effect-adjusted version of the standard binomial proportion standard error), since unequal weights inflate variance beyond what an unweighted formula would suggest.

Structured elaboration and SQL/pseudocode:

WITH weighted AS (
  SELECT
    user_id, converted, weight,
    weight * converted AS w_conv,
    weight * weight AS w_sq
  FROM users
),
agg AS (
  SELECT
    SUM(w_conv) / SUM(weight) AS weighted_conversion_rate,
    SUM(weight) AS sum_w,
    SUM(weight*weight) AS sum_w_sq,
    COUNT(*) AS n
  FROM weighted
)
SELECT weighted_conversion_rate,
  -- approximate standard error via an effective-sample-size adjustment (design effect)
  SQRT( weighted_conversion_rate * (1 - weighted_conversion_rate)
        / (sum_w * sum_w / sum_w_sq) ) AS approx_std_error
FROM agg;

The term sum_w * sum_w / sum_w_sq is the EFFECTIVE sample size under unequal weighting (Kish's effective sample size), which is always less than or equal to the raw count n when weights vary; using the raw n in a standard proportion standard-error formula instead of this effective sample size UNDERSTATES the true standard error whenever weights are unequal, since a few high-weight observations effectively carry more influence (and thus more sampling variability) than an equal-weighted average of the same count would.

Worked example: with weights ranging from 1 to 5 (some users representing 5x the population weight of others) and a raw sample size of 1,000, the effective sample size after this adjustment might be closer to 700-800 depending on the weight distribution's spread, meaning the true standard error is LARGER than a naive unweighted calculation on 1,000 observations would suggest; ignoring this adjustment would produce an artificially narrow, overconfident confidence interval.

Trade-offs & pitfalls: this design-effect approximation is itself an approximation (a more rigorous approach for a genuinely complex sampling design would use full survey-statistics methods, e.g., a Taylor-series linearization or replication-based variance estimator), but it is a substantial improvement over ignoring the weighting altogether, which is the far more common and more damaging mistake in practice; always confirm the weight column's actual distribution (a few extreme weights can dominate the effective-sample-size calculation) before trusting the resulting interval at face value.