User Retention & Engagement Questions

Direct answer

Build two sets in a single pass over the input: MAU is the set of distinct user IDs with an activity date inside the trailing 30-day window (end_date - 29 through end_date, inclusive), and DAU is the subset of that same pass whose activity date equals end_date exactly. The ratio is len(DAU) / len(MAU), guarded against a zero-user window.

Structured elaboration

Windowing. "A 30-day window ending on a given date" means 30 calendar days total, so the start of the window is end_date - timedelta(days=29), not -30 (off-by-one is the most common bug in this kind of function: end_date - 30 through end_date is 31 days).

Single-pass O(n) construction. Iterate the input list once; for every (user_id, activity_date) pair, parse the date, and if it falls in [window_start, end_date] add the user to mau_users; if it equals end_date exactly, also add it to dau_users. Both are Python sets, so re-adding the same user on a duplicate row is a no-op, membership tests are O(1) amortized, and the whole pass is O(n) in the number of input rows with O(distinct users in window) auxiliary space.

Why not two separate passes or two dict-based counters? A naive implementation might build a dict[date, set[user_id]] first and then union the last 30 days' sets; that is also O(n) but does strictly more allocation than necessary for this specific ratio (you only ever need the day-30 window membership and the single end-date membership, not per-day breakdowns). The single-pass version below is both simpler and, in practice, faster for exactly this ask.

Edge cases named in the question:

• Missing dates: a user with zero rows anywhere in the input contributes to neither set; a user active only outside the window contributes to neither set. Both are handled automatically by the membership check, no special-casing needed.
• Timezone normalization: the function assumes every activity_date string is already normalized to one canonical calendar day in the reporting timezone before it reaches this function. If the raw event stream carries UTC timestamps for a global user base, "activity_date" must be derived by converting to the product's single reporting timezone (or the user's own local timezone, a separate and harder product decision) BEFORE this function runs; doing the conversion inside this function, given only a pre-collapsed date string with no time-of-day or offset information, is not possible with the input shape as specified.
• Bot accounts: this function counts every distinct user_id in the input equally, so a bot or script hitting the API on a fixed schedule inflates both DAU and MAU (and, since a bot is often MORE consistently active than a human, it can push the ratio artificially high or artificially low depending on whether it is active on end_date). Filtering known bot IDs is a responsibility of the caller, upstream of this function, typically via a is_bot flag joined in before the tuples are constructed.

Worked example

from datetime import date, timedelta

def dau_mau_ratio(activity, end_date_str):
    end_date = date.fromisoformat(end_date_str)
    window_start = end_date - timedelta(days=29)  # 30-day window inclusive of end_date
    dau_users, mau_users = set(), set()
    for user_id, activity_date_str in activity:
        d = date.fromisoformat(activity_date_str)
        if window_start <= d <= end_date:
            mau_users.add(user_id)
            if d == end_date:
                dau_users.add(user_id)
    if not mau_users:
        return 0.0
    return len(dau_users) / len(mau_users)

activity = [
    (1, '2026-01-30'), (1, '2026-01-15'),
    (2, '2026-01-30'),
    (3, '2026-01-05'),                     # in window, not on end date -> MAU only
    (4, '2026-01-30'), (4, '2026-01-30'),  # duplicate row, must not double count
    (5, '2025-12-01'),                     # outside the 30-day window -> excluded
    (6, '2026-01-30'),
]
print(dau_mau_ratio(activity, '2026-01-30'))
print(dau_mau_ratio([], '2026-01-30'))

Output (actually executed):

0.8
0.0

Manual check: DAU on 2026-01-30 is users {1,2,4,6} = 4. MAU across 2026-01-01..2026-01-30 is {1,2,3,4,6} = 5 (user 5's 2025-12-01 row falls outside the window and is excluded). 4/5 = 0.8, matching the function's output exactly; user 4's duplicate row did not push MAU or DAU past their true distinct-user counts, and the empty-input call correctly returns 0.0 instead of raising a ZeroDivisionError.

Complexity

Time: $O(n)$, one pass over the n input rows, with $O(1)$ work per row (date parsing plus two set operations). Space: $O(u)$ where $u$ is the number of distinct users active in the 30-day window, bounded above by $n$.

Edge cases

• Empty activity list: guarded explicitly, returns 0.0 rather than dividing by zero.
• end_date_str earlier than every date in activity: both sets end up empty, same guarded 0.0 path.
• A user active every single day in the window: contributes exactly one membership to mau_users regardless of how many rows they generate, by construction of the set.

Trade-offs and pitfalls

• A common mistake is computing DAU as "unique users active THAT DAY across the whole input" without first restricting to the window; if activity contains rows far outside the 30-day range and one of those happens to fall exactly on end_date, the direct one-pass version above naturally still gets this right because the day-of check only fires inside the window-membership branch, but a version that computes DAU independently of the MAU window (e.g., grouping all rows by date first) must remember to apply the same restriction twice.
• Returning 0.0 for an empty window is a defensible default for this function's contract, but a caller-facing API should distinguish "definitely zero engagement" from "no data available yet," which a bare float cannot express; a production version would more likely return None or raise, and let the caller decide how to render "no data."
• DAU/MAU as computed here treats all activity as equally meaningful; it does not distinguish a user who opened the app for two seconds from one who completed a core action, which is a modeling choice this question does not ask you to resolve but a hiring manager may probe if you don't name it first.

Direct answer

Because a specific release date and a specific cohort boundary coincide exactly, the highest-priority checklist item is a clean comparison of the LAST pre-release cohort against the FIRST post-release cohort, holding acquisition channel and other factors as constant as possible, followed by a triage between three candidate explanations: the release itself, a coincidental marketing/channel-mix change, or a data-quality issue, each with its own distinguishing check.

Structured elaboration

Step 1: confirm the drop is real and cohort-specific, not a broader trend. Compare week-2 retention across the last 4-6 cohorts leading up to the release, not just the two cohorts on either side of it; a sudden step-change exactly at the release boundary (rather than a gradual trend that happens to cross the boundary) is much stronger evidence of a release-caused effect than a two-cohort comparison alone.

Step 2: isolate release as a candidate cause. Segment the post-release cohort's week-2 retention by whether the user was actually exposed to the release (a version/build flag, if available, since not all users update or receive a staged rollout simultaneously); if retention is low specifically among release-exposed users and normal among users still on the old version WITHIN THE SAME COHORT, that is close to direct evidence the release itself is the cause rather than something coincidental to the calendar date.

Step 3: isolate marketing/channel-mix as a candidate cause. Segment the same cohort's week-2 retention by acquisition channel; if the drop concentrates in one or two channels rather than being spread evenly, check whether a marketing campaign, budget change, or new channel launched around the same date, since a shift in WHO is being acquired can produce a retention drop that has nothing to do with the product release itself.

Step 4: isolate data-quality issues as a candidate cause. Cross-check the affected cohort's raw event VOLUME against a rolling baseline (a genuine tracking gap or pipeline issue coinciding with the release deploy, e.g. a broken analytics SDK shipped in the same release, is a real and easy-to-overlook possibility distinct from an actual behavioral effect) and confirm the channel/version segmentation fields themselves are populating correctly and not silently defaulting/nulling for the affected cohort.

Exact SQL queries to run, in priority order: (1) a 4-6 cohort trend query for week-2 retention, to confirm step-change vs. gradual trend; (2) the post-release cohort's week-2 retention segmented by release-exposure flag; (3) the same cohort segmented by acquisition channel, cross-referenced against a marketing-spend or campaign-launch log if available; (4) a raw event-volume-by-day check for the affected cohort's acquisition window, to rule out a tracking gap.

Instrumentation verification steps. Confirm the release itself did not change any EVENT DEFINITION or event-firing logic that the retention calculation depends on (a renamed or restructured event that a query is still expecting under its old name would show up as a fake drop, purely a labeling mismatch, not a real behavioral change).

Worked example

The pre-release cohort's week-2 retention sits at a stable 34-36% across the prior 5 cohorts; the post-release cohort drops to 26%, a real step-change rather than a gradual trend. Segmenting by release-exposure shows exposed users at 22% versus the small share of cohort members still on the old build at 35%, matching the historical baseline closely, pointing at the release itself. Segmenting further by acquisition channel shows the drop is roughly even across channels (ruling out a channel-mix explanation), and the event-volume check shows no anomaly in raw tracking volume (ruling out a pipeline gap). This converges the investigation onto "something in the release itself, not a coincidental external factor," narrowing the next step to a feature-level investigation of what specifically changed in that release's user-facing flow.

Trade-offs and pitfalls

• A two-cohort (before/after) comparison alone cannot distinguish a release-caused step-change from a pre-existing gradual trend that happens to cross the release boundary; the multi-cohort trend check in Step 1 is what makes that distinction possible, and skipping straight to "before vs. after" is the most common shortcut that produces an overconfident, possibly wrong conclusion.
• Segmenting by release-exposure only works if a reliable exposure flag exists; for a release rolled out to 100% of users simultaneously with no staged rollout or version tracking, this specific isolating check is unavailable, and the investigation has to lean more heavily on the channel-mix and data-quality checks instead.
• Concluding "it's the release" from Step 2 alone, without also running Steps 3 and 4, risks a false positive if the timing is genuinely coincidental; all three candidate explanations should be checked, even once one of them looks likely, since more than one factor can be true simultaneously (a release-caused effect stacked on top of a smaller, unrelated channel-mix shift, for instance).

Direct answer

On the numbers as given, the retention program's return dwarfs the acquisition option by roughly two orders of magnitude, but that gap is itself a signal to interrogate the inputs rather than accept at face value: a $10,000 program claiming to durably raise 6-month LTV by $5 for 500,000 already-active users is an extraordinarily cheap lever, and a careful answer says so explicitly before recommending it, alongside the real non-financial factors (feasibility, risk, and diminishing returns) that the raw math cannot capture.

Structured elaboration

The acquisition math. Profit per new user $= \text{LTV} - \text{CAC} = 45 - 30 = \$15$. Return on the CAC investment $= 15/30 = 50\%$, a healthy, ordinary-looking acquisition economics figure.

The retention math. Total value created $= \$5 \times 500{,}000 = \$2{,}500{,}000$, against a $10,000 cost, for a net gain of $2,490,000 and a return of $2{,}490{,}000/10{,}000 = 24{,}900\%$, or a 250x multiple on spend.

Why this comparison, taken at face value, should raise suspicion rather than immediate confidence. A program costing $10,000 that reliably moves a behavioral metric (LTV) for 500,000 already-existing users is claiming an almost impossibly low per-user cost of intervention, $0.02 per user ($10,000/500,000). Real retention programs (a redesigned onboarding flow, a lifecycle email system, a loyalty mechanic) typically carry both meaningful one-time engineering cost AND ongoing operating cost that scales at least somewhat with user count, neither of which a flat $10,000 figure plausibly captures at this scale. The HONEST analytical move here is not to simply report the 250x figure as the answer, but to name this implausibility directly: either the $10,000 figure is understated (missing engineering time, ongoing maintenance, or opportunity cost), or the claimed $5 LTV lift is itself an optimistic assumption not yet validated by an actual experiment, or both.

Non-financial factors to weigh even after correcting the obvious implausibility. Acquisition scales roughly linearly with spend (doubling ad budget roughly doubles new users, modulo channel saturation), giving management fine-grained, continuous control over growth rate; a retention program's effect size, once shipped, is comparatively fixed and does not scale with additional spend the same way, so the two levers have very different flexibility profiles even when their headline ROI numbers are comparable. Retention improvements also typically compound (a durably retained cohort keeps generating value in FUTURE periods too, not just the one 6-month window this calculation captures), which the CAC comparison, evaluated over a single LTV snapshot, does not fully credit either lever for. Risk profile differs too: an acquisition channel's ROI is relatively easy to measure quickly and stop if it underperforms, while a shipped product change affecting 500,000 users' experience carries real execution risk (a poorly-designed retention mechanic can backfire) that a clean point-estimate LTV lift does not capture.

Worked example

The core arithmetic, run explicitly:

cac = 30
ltv_acq = 45
acq_roi = (ltv_acq - cac) / cac

program_cost = 10000
ltv_lift_per_user = 5
active_users = 500000
total_value_created = ltv_lift_per_user * active_users
retention_roi = (total_value_created - program_cost) / program_cost
cost_per_user = program_cost / active_users

print(f"acquisition profit/user = ${ltv_acq - cac}, ROI = {acq_roi:.0%}")
print(f"retention total value = ${total_value_created:,}, net = ${total_value_created - program_cost:,}")
print(f"retention ROI = {retention_roi:.0%} ({total_value_created/program_cost:.0f}x)")
print(f"implied retention program cost per user = ${cost_per_user}")

Output (actually executed):

acquisition profit/user = $15, ROI = 50%
retention total value = $2,500,000, net = $2,490,000
retention ROI = 24900% (250x)
implied retention program cost per user = $0.02

The final line, $0.02 per user, is the number that should give any candidate pause before recommending the retention program purely on the strength of the 250x figure; flagging it is what separates a senior answer from one that mechanically reports whichever number is larger.

Trade-offs and pitfalls

• Recommending the retention program purely because 250x beats 50% is the single most common shortcut this question invites, and it is exactly the shortcut a careful candidate should resist by naming the implausibility of the underlying cost assumption before accepting the conclusion.
• Even after adjusting for a more realistic program cost, retention and acquisition are not perfectly substitutable levers, since one scales continuously with spend and the other does not; a real budget decision typically wants BOTH, allocated according to their respective marginal returns at the CURRENT margin, not an all-or-nothing choice between the two options as stated.
• A single 6-month LTV snapshot undercounts a durable retention improvement's TRUE value, since a retained cohort keeps contributing in periods beyond the one measured; this cuts in favor of the retention option being under-, not over-, valued by the given numbers, a genuine tension with the cost-implausibility concern raised above and worth naming as an open question rather than resolved in either direction.

Direct answer

A retention curve plots the fraction of a cohort still active at successive time offsets after they joined, and it is computed by anchoring every user to their own start date (cohort analysis), then measuring what fraction of that cohort shows activity at each offset. For an app experiencing viral growth, the curve has to be read alongside acquisition volume, since a flat or even rising total active-user count can hide a genuinely worsening RETENTION curve if new-user volume is growing fast enough to mask it.

Structured elaboration

Step-by-step process to compute a retention curve via cohort analysis.

1. Define the cohort anchor: the event that starts the clock for a user, usually signup or first meaningful use.
2. Define what counts as "active": a specific event or set of events (not just "any record exists"), chosen to reflect genuine engagement rather than passive presence.
3. Bucket users into cohorts by the calendar period (day, week, month) in which their anchor event occurred.
4. For each cohort, compute the fraction still active at each offset (day/week N after the anchor), using distinct-user counts so repeat activity within a period doesn't inflate the number.
5. Plot offset (x-axis) against retention fraction (y-axis), one line per cohort (or an aggregate if comparing many cohorts), which is what lets you compare cohorts of different starting sizes and different start dates on the same chart.

Minimum instrumentation required. At minimum: a user_id, an event_name (or a simple boolean "was active"), and an event_timestamp, captured reliably enough that every meaningfully engaged session produces at least one qualifying event. Two subtler requirements matter as much as the raw event stream: a STABLE user identifier across sessions and devices (without which "returning" can't be detected at all, since a user who logs in from a new device looks like a brand-new user rather than a returning one), and a clear, product-specific definition of which events count as "active" (logging in is a much weaker signal than completing a core action, and picking too weak a definition inflates the curve while picking too narrow a definition can understate genuine engagement that happens through a different path).

Interpreting the curve for an app experiencing viral growth. Viral growth means a large and rapidly changing share of the total user base is very NEW at any given moment, since new users keep arriving fast. Two distinct risks follow directly from this: (1) a single aggregate "active users over time" chart can look healthy or even excitingly upward purely because acquisition volume is growing, while the COHORT retention curve for any individual signup week is flat or declining, meaning each new wave of users is churning at the same (or a worse) rate as the last, just replaced by an even larger next wave; (2) viral acquisition often pulls in a wider, less-intent-matched audience than a more targeted channel would, so it is worth checking whether the retention curve for viral-referred cohorts specifically looks different from the curve for cohorts acquired through the app's primary intended channel, since a blended aggregate curve can hide a real difference between "core" users and "viral pass-through" users. The correct read during a viral growth period is always the cohort-level curve, week by week, not the aggregate active-user trend line, precisely because the aggregate trend line is the one place growth and retention effects get tangled together.

Worked example

An app goes viral in week 3, acquiring 50,000 new signups that week versus a steady 2,000/week baseline before and after. Total weekly active users (aggregate, all cohorts combined) climbs from roughly 8,000 in week 2 to 45,000 in week 4 and stays above 30,000 for the following month, an unambiguously "up and to the right" chart if read alone. But the week-3 cohort's OWN retention curve (the 50,000 viral signups, tracked forward relative to their own week-0 anchor) shows day-7 retention of just 8%, well below the pre-viral baseline cohorts' historical day-7 retention of roughly 22%. The aggregate chart looks great because the sheer VOLUME of the viral cohort dwarfs its poor retention rate in absolute active-user terms; the cohort curve reveals that the viral traffic, while valuable for awareness, is converting to habitual users at a much lower rate than the product's core acquisition channels, a finding invisible in the aggregate view.

Trade-offs and pitfalls

• Reading an aggregate active-user trend line as if it were a retention curve is the single most common mistake during a viral growth event, exactly because growth and retention pull the aggregate number in the same visual direction (up) even when the underlying retention story is deteriorating.
• Defining "active" too loosely (any app open, including one triggered by a push notification the user didn't consciously choose to engage with) inflates every point on the curve and can make a genuinely weak cohort look adequate.
• Comparing cohorts of very different sizes on the same chart without noting the size difference risks over-interpreting noise in a small cohort's curve as a real pattern; a cohort of 200 users' week-8 retention swinging from 20% to 25% is one or two users' worth of noise, while the same swing in a 50,000-user cohort is a real, statistically meaningful signal.

Direct answer

Compute cohort LTV as the cumulative NET revenue per acquired user (gross revenue minus refunds, on the date the refund actually posts), track it separately at multiple maturity horizons (7/30/90/365 days), and be explicit that any cohort younger than your longest reporting horizon is a PARTIAL, censored observation whose eventual final value is not yet known, not a finished number.

Structured elaboration

Refunds. Model refunds as their own negative-amount revenue events, dated on the actual refund date, not the original purchase date; this makes a refund automatically net out of whichever LTV horizon it falls within (a refund 10 days after a purchase reduces the 30-day and 90-day LTV figures but has no effect on a 7-day figure computed before the refund happened), which is the economically correct behavior: a sale that gets refunded later did not actually contribute that revenue to the business, and the LTV number should reflect that once the refund occurs, not before.

Subscription prorations. For a subscription product, a mid-cycle upgrade, downgrade, or cancellation with prorated credit needs to be recognized as revenue on the date it actually posts (not smeared evenly across the original billing period), for the same reason as refunds: LTV should reflect net cash economics as they actually occurred, and a proration credit is functionally a partial refund that should reduce the relevant window's LTV once it happens.

Discounting future revenue. Revenue received later is worth less than revenue received today (time value of money, plus the ongoing risk that a subscription might still churn before that future revenue is realized); a discounted LTV multiplies each period's expected net revenue by a discount factor $1/(1+r)^t$ where $r$ is the chosen periodic discount rate and $t$ is the number of periods out, then sums across periods. Whether to discount at all is itself a judgment call: for short horizons (a 30-day LTV) discounting rarely changes the number meaningfully and is often skipped for simplicity; for long horizons (multi-year subscription LTV) it becomes material and omitting it systematically overstates the true present value of a cohort.

Censored (immature) cohorts. A cohort acquired 20 days ago simply cannot have a valid 90-day LTV yet; the correct handling is to either omit that cell from any 90-day comparison entirely, or to explicitly extrapolate it using the observed maturation CURVE of older, fully-matured cohorts (fit a curve to how LTV accumulates over time for cohorts old enough to have reached 90 days, then project the young cohort's partial trajectory forward against that shape), clearly labeled as a projection rather than an observed value. Reporting a young cohort's PARTIAL 90-day-window revenue as if it were a completed 90-day LTV, without either omitting it or explicitly flagging it as a projection, is the single most common way cohort-LTV reporting misleads a reader.

Reporting uncertainty to stakeholders. Three distinct sources of uncertainty deserve to be named separately rather than folded into one vague error bar: (1) sampling/statistical uncertainty from a genuinely small cohort size; (2) EXTRAPOLATION uncertainty for any immature, censored cohort being projected forward, which is typically much larger than the sampling uncertainty and grows the younger the cohort is; (3) MODEL uncertainty in the maturation-curve shape itself, since different reasonable curve-fitting choices (a simple linear extrapolation vs. a decaying-growth curve) can produce meaningfully different projections for the same immature cohort. Reporting a single point estimate for an immature cohort's projected LTV without at least flagging which of these applies understates how much that number could still move.

Worked example

A cohort's OBSERVED cumulative net revenue per user (refunds already netted in) at 7/30/90 days is $8, $22, and $41, based on 90 days of full observation for an older, mature cohort. Applying a simple monthly discount rate of $1\%$ to the incremental revenue earned in EACH 30-day window (treating the 0-30, 30-60, 60-90 day windows as periods 1, 2, 3): incremental revenue is $22 (period 1), $19 (period 2, i.e. 41-22), and, if a hypothetical 4th period of $15 incremental revenue were observed further out, its discounted value would be $15 / (1.01)^4 \approx \$14.41$ rather than the full $15, since it arrives roughly 4 months out. For a cohort only 20 days old with $6 in observed revenue so far, the honest report is either "$6 observed, 90-day LTV not yet available" or an explicitly-labeled projection built from the mature cohort's own 0-20-day-to-90-day ratio ($41/observed-revenue-at-day-20-for-the-mature-cohort, applied as a multiplier), never a bare "$6" presented as if it were a completed 90-day figure.

Trade-offs and pitfalls

• Presenting an immature cohort's partial revenue alongside mature cohorts' completed LTV on the same chart, with no visual or textual distinction, is the most damaging version of this mistake: it invites a reader to conclude the newest cohort is dramatically underperforming, when the real explanation is simply that it hasn't had time to accumulate revenue yet.
• Choosing not to discount at all for long-horizon LTV is a defensible simplification for many internal reporting contexts, but it should be a stated choice, not a silent default; a decision that compares LTV against CAC to justify a multi-year payback period is exactly the case where skipping discounting can meaningfully overstate the economics.
• A refund netted against the ORIGINAL purchase date instead of the refund date silently corrupts every earlier LTV snapshot that had already been reported, retroactively changing a number stakeholders may have already acted on; dating the refund event on its own actual date avoids this by construction.