Customer and User Obsession Questions

Goal: in two weeks deliver high-confidence root causes and prioritized fixes that recover usage. Prioritize quick, low-effort signals first, then targeted instrumentation and user contact.

Day 0–2: Triage with coarse telemetry (fast, high-confidence aggregates)

• Query daily/weekly MAU, DAU, new users, retention by region and platform (SQL). Break down by cohort, channel, OS, app version.
• Compare pre/post-release windows (7–14d baseline) and compute statistical significance (A/B-like t-test or bootstrapped CIs).
• Check server-side KPIs: request volumes, error rates (5xx, 4xx), latency P95/P99, API call mix. Correlate drop timing to deploy timestamp.
  Why first: immediate systemic failures show here. Trade-off: coarse signals won’t reveal client UX bugs.

Day 3–5: Narrow suspects with slices + session-level proxies

• Segment by user journey steps (login, onboarding, key feature events) to find where funnel drops occur.
• Use session replays / UX recordings for a sample of affected-region sessions (focus on new app versions). Look for crashes, UI regressions, blocked flows.
  Why: session replays give concrete examples fast. Trade-off: sampling bias and privacy constraints.

Day 6–9: Lightweight client-side telemetry (fast instrument)

• Add a few concise client events: feature-toggle exposure, button clicks, error handlers, version, platform. Implement with feature-flagged rollout and short TTL.
• Deploy verbose logging for only the affected region or small percent (rate-limit).
  Why: targeted metrics validate hypotheses. Trade-off: partial coverage and potential performance cost.

Day 10–12: User outreach + qualitative

• Rapid surveys and targeted outreach to users who churned (in-app, email). Ask focused questions: what they tried, error messages, device.
• Conduct 5–10 user interviews guided by session replay findings.
  Why: confirms intent and pain points. Trade-off: slower and less scalable.

Day 13–14: Synthesis and recommendations

• Combine quantitative (coarse + new telemetry) and qualitative evidence to rank root causes (e.g., backend regression, client bug, misconfiguration, rollout flag).
• Recommend fixes: rollback vs patch, telemetry to keep (persist events), monitoring alerts, remediation plan and A/B test to validate recovery.
  Trade-offs summary:
• Coarse telemetry: fast, high-level, low cost, low resolution.
• Session replay: concrete, quick UX insight, sample-limited and privacy-sensitive.
• Lightweight client telemetry: medium speed, targeted, gives causality but partial rollout risk.
• User outreach: high signal quality on intent but slower and smaller N.

Deliverables: one-page executive summary with top hypothesis + confidence, prioritized fixes, SQL notebook of analyses, and proposed minimal instrumentation (schema + dashboard) to detect recurrence.

Approach: treat this as an evidence-driven ROI problem—estimate uplift, run lightweight pilots/A-B tests, monitor early signals, then prioritize by expected impact vs. cost & risk.

1. Estimate expected impact

• Use historical cohorts and analogous features: identify user segments, baseline WAU, and prior feature lift for similar launches.
• Build a simple model: Expected WAU change = baseline_WAU * adoption_rate * engagement_multiplier.
  • Estimate adoption_rate from product surveys, beta uptake, or comparable feature adoption.
  • Estimate engagement_multiplier from prior feature lifts or set conservative/likely/optimistic scenarios.
• Produce ranges (P10/P50/P90) and revenue/ops impact.

2. Metrics & short-term signals

• Primary metric: Weekly Active Users (unique users/week).
• Leading signals (early): DAU/WAU ratio, new user activation rate, feature adoption (users who used feature at least once), retention at 1/7 days, session frequency, engagement depth.
• Guardrail metrics: churn rate, error rates, load/perf, NPS for affected flows.

3. A/B tests / pilots

• Design: randomize eligible users into control/treatment; track WAU over multiple weeks (at least 2–4 weeks depending on expected effect size).
• Power calculation: compute sample size for minimum detectable effect (e.g., 5% uplift) using baseline WAU variance; include allocation and expected adoption dilution.
• Analysis: pre-specify metric definitions, significance level (adjust for multiple tests), run both short-term leading signals for early stopping rules and full WAU analysis after required exposure.
• If rollout risk is high or adoption concentrated in specific segments, run phased pilot (beta group, geo split).

4. Prioritization

• Score each feature by Expected Impact (P50 uplift in WAU), Confidence (data-backed estimates, technical risk), and Cost/Time (engineering weeks). Use RICE or ICE:
  • RICE = Reach * Impact * Confidence / Effort.
• Recommend: prioritize high RICE: features with moderate-to-high expected WAU uplift, high confidence from data, and low engineering effort. For borderline cases run small pilot to raise confidence before full investment.

Deliverables: dashboard with projected vs actual WAU, adoption funnels, power/sample calculations, clear recommendation with sensitivity analysis and next steps for pilot and full rollout.

Approach: compute session length in seconds using COALESCE(ended_at, current_timestamp) for ongoing sessions, filter extreme outliers by removing sessions above the global 99th percentile (or winsorize), compute average session length per user, then pick users whose average is in the top 5% (>= 95th percentile of per-user averages).

SQL (Postgres):

-- 1) compute per-session length (seconds), exclude negative / zero lengths
WITH sessions_clean AS (
  SELECT
    session_id,
    user_id,
    EXTRACT(EPOCH FROM (COALESCE(ended_at, current_timestamp) - started_at)) AS length_s
  FROM sessions
  WHERE started_at IS NOT NULL
    AND (ended_at IS NULL OR ended_at >= started_at)  -- basic data sanity
),
-- 2) remove global extreme outliers (e.g., top 1%)
length_cutoff AS (
  SELECT percentile_disc(0.99) WITHIN GROUP (ORDER BY length_s) AS p99
  FROM sessions_clean
),
sessions_filtered AS (
  SELECT s.*
  FROM sessions_clean s
  CROSS JOIN length_cutoff lc
  WHERE s.length_s <= lc.p99   -- drop extreme long sessions
),
-- 3) per-user average (in minutes)
per_user_avg AS (
  SELECT
    user_id,
    AVG(length_s)/60.0 AS avg_session_min,
    COUNT(*) AS session_count
  FROM sessions_filtered
  GROUP BY user_id
  HAVING COUNT(*) >= 1   -- optionally require min sessions, e.g., >=3 for stability
),
-- 4) find 95th percentile threshold across users
threshold AS (
  SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY avg_session_min) AS p95
  FROM per_user_avg
)
SELECT p.user_id, p.avg_session_min, p.session_count
FROM per_user_avg p
CROSS JOIN threshold t
WHERE p.avg_session_min >= t.p95
ORDER BY p.avg_session_min DESC;

Key decisions and reasoning:

• NULL ended_at: treated as ongoing — replaced with current_timestamp so length reflects time-to-now. This is appropriate for real-time reports; for retrospective analyses you may exclude ongoing sessions instead.
• Outliers: extreme long sessions (e.g., idle browser tabs) skew averages. Removing top 1% of session lengths globally is a simple robust step; alternative: winsorize lengths or compute median session length per user. Also consider requiring a minimum session_count (HAVING COUNT(*) >= N) to avoid noisy averages from single long sessions.
• Top 5%: computed using percentile_disc(0.95) on per-user averages which returns users at or above the 95th percentile.

Performance notes for large datasets:

• Indexes: ensure index on (user_id), and on started_at / ended_at if filtering by time windows.
• Avoid cross joins over full tables; percentile functions scan needed columns but are relatively efficient if run on filtered/partitioned data.
• For recurring reports: pre-aggregate into a materialized view (refresh schedule) or maintain daily aggregates to avoid full-table scans.
• Consider partitioning sessions by date (range partitioning on started_at) to limit scanned partitions for recent-window queries.
• If using current_timestamp for ongoing sessions, be aware that results are non-deterministic over time—cache results if reproducibility required.

Framework overview: build a numeric triage score per bug (0–100) combining Impact, Frequency, Customer Value, Revenue-at-Risk, and Effort to fix (to compute ROI). Rank by net priority = PriorityScore / EffortEstimate.

Criteria & scoring (weights sum to 100):

• Impact (30): severity on functionality/security/regulatory (Critical=30, High=20, Medium=10, Low=0).
• Frequency (25): occurrences over 30/90 days normalized to 0–25 (e.g., percentile or scaled count).
• Customer Value (20): proportion of affected active users or strategic accounts (map % affected to 0–20).
• Revenue-at-Risk (15): estimated monthly revenue exposed if bug persists (bucketed to 0–15).
• Time-to-Fix (Effort) (used as denominator): estimated dev-hours (small=1, medium=2, large=4) or continuous hours.

Scoring methodology:

1. Collect raw metrics, normalize each criterion to its weight.
2. PriorityScore = Impact + Frequency + CustomerValue + RevenueAtRisk (0–90). FinalRankMetric = PriorityScore / EffortFactor.
3. Optionally apply business rules: always schedule security/regulatory-critical bugs regardless of score.

Required data sources:

• Issue tracker (bug severity, created date, labels, reporter)
• Logging/telemetry (error rates, user impact, frequency)
• Product analytics (MAU, feature usage to compute % affected)
• CRM/ARR data (identify strategic accounts, revenue mapped to users)
• Engineering estimates (time-to-fix)
• Support tickets/CSAT (qualitative severity)

Incorporating stakeholder feedback:

• Run a weekly prioritization review with reps from Support, Sales (top accounts), Product, and Engineering. Present top-N ranked list and supporting metrics in a dashboard.
• Allow stakeholders to flag up to N exceptions per cycle with mandatory rationale and data (e.g., legal risk or high-value customer escalation). Log overrides and require a short business impact justification.
• Iterate weights quarterly based on outcomes (reduced repeat tickets, revenue retention) — A/B test weighting if needed.

Deliverables: automated dashboard (SQL -> BI tool) showing scores, drilldowns to raw metrics, and an override log. Track KPIs: mean time to resolution for high-score bugs, ticket volume change, churn/ARR impact.

High-level taxonomy

• North-star: single metric that captures core product value (strategic goal driven).
• Health: business-level KPIs that track ongoing performance.
• Diagnostic: slice-and-dice metrics used to investigate causes and opportunities.
• Guardrail: safety/quality metrics to detect negative side effects.

Ownership model

• Metric Owner (Product PM): defines intent & business logic, prioritizes downstream use.
• Metric Steward (Data Analyst): implements SQL/model, documents lineage, writes tests.
• Platform Owner (Data Engineering): ensures pipeline reliability, infra, access controls.
• Consumers (growth, CS, execs): feedback loop; request changes via RFC process.
• Change governance: any change requires owner sign-off + test run; tracked in metrics registry.

Standards for definitions & SLAs

• Canonical name: <category>.<metric_name> e.g., northstar.net_revenue_retention
• Single-source SQL notebook in repo + versioned DAG; include description, owner, calculation window, dimensions, and business caveats.
• Metadata: frequency (daily/weekly), refresh SLA (e.g., daily metrics available by 06:00 UTC), freshness SLA (<1% late runs), accuracy tests (row counts, reconciliation to GL).
• SLAs: data pipeline uptime 99.9% monthly; failing tests auto-open incident.

Automated alerting

• Two layers: deterministic thresholds + anomaly detection.
• Implement data tests (Great Expectations / dbt tests) that run on schedule; failures -> Slack channel + PagerDuty if critical.
• Metric-level alerts: threshold (e.g., churn > 3% absolute) + statistical anomaly detector (seasonal ETS or rolling z-score); alert routing depends on category (north-star -> PM + exec Slack + email; guardrail critical -> PagerDuty + eng).
• Include runbook link in alert with expected triage steps and rollback actions.

Three example metrics (subscription product)

1. North-star: Net Revenue Retention (NRR) — definition: (MRR at end of period from existing customers) / (MRR at start of period from same cohort) *100. Frequency: monthly. Owner: Head of Product. SLA: available by day 3 of month; alert if monthly drop >5% (anomaly + threshold).
2. Health: Monthly Recurring Revenue (MRR) — definition: sum of recurring subscription revenue recognized in month. Frequency: daily rollup, monthly canonical. Owner: Rev Ops. SLA: daily refresh by 06:00 UTC; alert if daily MRR delta >2x expected variance.
3. Guardrail: Payment Failure Rate — definition: failed payment attempts / total attempts (30-day rolling). Frequency: realtime/ hourly. Owner: Finance + PM. SLA: <1% rolling; critical alert if >1.5% -> PagerDuty to payments eng and finance.

This model ensures clear accountability, reproducible definitions, automated quality checks, and tiered alerting so stakeholders act on reliable signals.