First, explain that rehashing IDs changes the identifier for the same user, so the same person appears as two different IDs across the window. Then, describe how naive distinct counting would treat those as two separate users, inflating MAU, and quantify the distortion by considering the proportion of users active both before and after T.
Pro tip: Mention that the distortion is not just inflation—it can also cause undercounting if users are split across the boundary and each half falls below activity thresholds, and suggest using a stable surrogate key or crosswalk to deduplicate.
Clarify that on day T, all user IDs are replaced with new hashed values, so the same user has different IDs before and after T.
The MAU window spans dates before and after T, meaning it includes user activity recorded under both old and new IDs.
Naively counting distinct user IDs will count a user active on both sides of T as two distinct users, inflating the MAU count.
The inflation equals the number of users active both before and after T (i.e., the intersection). Estimate this by looking at users active in the days just before and after T.
Suggest using a stable identifier (e.g., account ID, email) or a mapping table to link old and new IDs, or restrict the window to avoid crossing T.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, clarify that MAU overestimate depends on how you count distinct users over a window (e.g., calendar month vs. rolling 30 days) and the underlying user activity distribution. Then, derive the maximum and minimum overestimate by considering extreme cases: all users active every day (overestimate = 0%) and users active exactly once per month on different days (overestimate can approach 100% if counting daily uniques summed, but relative to true distinct users over the window, the overestimate is bounded by the number of days in the window minus 1, expressed as a percentage).
Pro tip: Emphasize that the maximum overestimate is not unbounded—it's capped by the ratio of the window length to the minimum activity frequency, and always relate it back to the business impact of metric definitions.
Clarify what 'MAU' means: distinct users active in a calendar month vs. rolling 30-day window, and how 'true distinct user count' is defined over the same window.
Overestimation occurs when summing daily unique users (or using a shorter window) instead of counting distinct users over the full window; also consider duplicate counting due to multiple devices or logins.
For maximum overestimate, assume each user is active on exactly one day per month, all on different days, so daily uniques sum to N*days while true distinct is N, giving overestimate = (days-1)/1 *100%. For minimum, assume all users active every day, so overestimate = 0%.
Maximum overestimate = (D-1)/1 *100% where D is number of days in window (e.g., 30 days → 2900% if summing daily uniques). Minimum overestimate = 0% when all users are active every day.
Note that real-world overestimates are typically much smaller due to repeated user activity, and recommend using distinct counts over the full window to avoid inflation.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
My answer went in a few directions: maintain an ID mapping table as a first-class artifact in the warehouse, use a stable internal surrogate key that never changes even when the external ID does, and version or snapshot identity resolution at ingestion time rather than at query time.
Start by clarifying the business definition of MAU and the root cause of ID changes (e.g., account merges, device switches, re-registrations). Then propose a layered identity resolution system that maintains a canonical user ID and uses event-level stitching to deduplicate across IDs, ensuring accurate MAU counts.
Pro tip: Emphasize that any redesign must balance accuracy with cost and latency—propose a hybrid approach where real-time stitching handles recent activity and batch reconciliation corrects historical data, and always validate with backtesting against known ground truth.
Define what 'MAU' means for the business (e.g., unique users in a 30-day window) and identify all scenarios causing ID changes (e.g., login from new device, account merge, anonymous-to-registered).
Propose a canonical user ID system that maps multiple identifiers (device IDs, emails, cookies) to a single entity, using deterministic and probabilistic matching with a confidence threshold.
Store raw events with all available identifiers, then create a mapping table that links events to canonical IDs. Use graph-based or rule-based stitching to handle merges and splits.
Compute MAU by counting distinct canonical IDs active in the period, ensuring that events from different IDs belonging to the same user are deduplicated. Consider incremental updates for real-time accuracy.
Backtest the new system against historical data with known ID changes, measure accuracy and performance, and set up monitoring for identity resolution quality and MAU anomalies.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.