/user-insights

User insights

Start from a product decision and metric definition. Do not turn convenient columns or arbitrary time thresholds into personas.

Choose a mode

• Question framing: turn a product concern into measurable questions.
• Segmentation: identify behaviorally meaningful groups from distributions or justified business rules.
• Retention/cohort: measure return behavior from event history.
• Activation/funnel: locate progression and drop-off.
• Feature adoption/pathing: understand use sequences and value realization.
• Churn/monetization: analyze decline, cancellation, expansion, and revenue behavior.
• Qualitative synthesis: connect interviews, support, or survey evidence to behavioral patterns.
• Audit: validate an existing query, dashboard, segment, or conclusion.

Workflow

1. State the product decision, population, behavior, time window, and action the analysis may trigger.
2. Inspect schemas, event taxonomy, identity model, account/user relationships, plan history, billing/refunds, timezone, retention policy, and available qualitative evidence. Match the query language to the actual system.
3. Audit data quality before analysis: event semantics, duplicate/late events, nulls, bots/internal/test accounts, identity merges, plan changes, censoring, seasonality, and instrumentation changes.
4. Define every metric with numerator, denominator, eligibility, window, and unit of analysis. Prefer event history over current user snapshots for trends and retention.
5. For segments, inspect distributions and use quantiles, clusters, or business thresholds only when interpretable and justified. Test sensitivity to reasonable boundary changes. Small groups and ties need explicit handling.
6. For retention, build cohort-period activity from events and distinguish classic, rolling, and bounded retention. Do not infer historical retention from last_active_at.
7. For decline or churn risk, compare each subject with its own prior behavior or an appropriate matched baseline; do not call low cumulative usage a decline.
8. Execute queries only when access is available. Otherwise return executable queries and expected result shapes without fabricating counts.
9. Quantify sample size, uncertainty, missingness, and alternative explanations. Treat observational associations as non-causal.
10. Connect each finding to a decision, mechanism, proposed action, and validation method. Prefer experiments or staged tests for causal recommendations.

Privacy and safety

• Minimize selected fields; avoid SELECT * and raw email unless the task requires identifiable outreach and access is authorized.
• Aggregate or pseudonymize outputs where possible.
• Respect consent, retention, deletion, and access-control boundaries.
• Do not label individuals with sensitive or stigmatizing inferred traits.

Load conditional reference

Read analysis patterns for calibrated segmentation, event-based cohort SQL, decline analysis, query review, and action design. The reference contains patterns to adapt, not fixed thresholds or a separate workflow.

Output contract

Distinguish two states:

• Executed analysis: evidence ledger, metric definitions, data-quality findings, results with denominators/uncertainty, limitations, and decisions.
• Query plan: schema mapping, executable queries, expected columns, validation queries, and interpretation rules, with no invented results.

For either state, include justified segments/cohorts only, privacy notes, alternative explanations, recommended action, and how to test it.

Verify

• Queries use real schema fields and correct grain.
• Trends and retention use event history rather than snapshots.
• Cohort denominators and maturity/censoring are correct.
• Segment thresholds are justified and sensitivity-tested.
• Counts reconcile to eligible-population totals without unintended overlap.
• PII is minimized and access assumptions are explicit.
• Observational results are not presented as causal.
• Recommendations state evidence, mechanism, uncertainty, and validation.

Product analysis patterns

Adapt these patterns to the actual event model and product decision. They intentionally avoid fixed lifecycle thresholds and snapshot-based retention.

Contents

• Metric contract
• Data-quality audit
• Activation and funnel analysis
• Behavioral segmentation
• Event-based retention
• Usage decline
• Query validation
• Action design

Metric contract

For every metric record:

name and decision supported
unit: user | account | workspace | subscription | device
eligible population and exclusions
numerator/event and denominator
time zone and window
event maturity/censoring rule
source tables and grain
known instrumentation changes

Data-quality audit

Before interpreting behavior, measure:

• event volume and distinct subjects by day/version/environment;
• duplicate event IDs and retry patterns;
• null/unknown identity and merge rate;
• internal, bot, test, and deleted-account inclusion;
• late-arriving events and ingestion outages;
• event-property availability and semantic changes;
• plan/status history, refunds, pauses, and account merges.

Reconcile eligible totals across source systems where feasible.

Activation and funnel analysis

Define activation as an observed value event or validated leading indicator, not signup or a convenient click by default. Record the eligible population, ordered or unordered steps, allowed window, unit of analysis, identity transition, and whether repeated attempts count.

Build the funnel from subject-level first qualifying timestamps so retries and duplicate events do not inflate conversion:

WITH eligible AS (
  SELECT subject_id, MIN(occurred_at) AS entered_at
  FROM events
  WHERE event_name = :entry_event
    AND occurred_at >= :analysis_start
    AND occurred_at < :analysis_end
  GROUP BY subject_id
), steps AS (
  SELECT
    e.subject_id,
    e.entered_at,
    MIN(v.occurred_at) FILTER (
      WHERE v.event_name = :value_event
        AND v.occurred_at >= e.entered_at
        AND v.occurred_at < e.entered_at + :activation_window
    ) AS activated_at
  FROM eligible e
  LEFT JOIN events v ON v.subject_id = e.subject_id
  GROUP BY e.subject_id, e.entered_at
)
SELECT
  COUNT(*) AS eligible_subjects,
  COUNT(activated_at) AS activated_subjects
FROM steps;

For multi-step funnels, calculate each step from raw events and require timestamps to satisfy the intended ordering. Report subject counts and denominators at every step, time-to-step distributions, window maturity, and exclusions. Compare segments only after checking sample size, instrumentation parity, acquisition mix, and exposure opportunity. Treat the observed funnel as descriptive unless assignment or a causal design supports stronger claims.

Behavioral segmentation

Choose features tied to the product mechanism, such as recent active periods, successful value events, frequency, breadth/depth, collaboration, spend, or support friction.

Use quantiles when relative rank is meaningful:

WITH subject_metrics AS (
  SELECT
    subject_id,
    COUNT(*) FILTER (WHERE event_name = :value_event) AS value_events,
    COUNT(DISTINCT DATE_TRUNC(:period, occurred_at)) AS active_periods,
    MAX(occurred_at) AS last_value_at
  FROM events
  WHERE occurred_at >= :analysis_start
    AND occurred_at < :analysis_end
    AND environment = 'production'
  GROUP BY subject_id
), ranked AS (
  SELECT *,
    PERCENT_RANK() OVER (ORDER BY value_events) AS value_event_rank
  FROM subject_metrics
)
SELECT * FROM ranked;

Define segments after inspecting distributions. Handle ties, zero-inflation, small populations, and overlapping definitions. Run sensitivity checks at nearby boundaries and explain why a segment changes a decision.

Event-based retention

Build cohort and activity periods from event rows:

WITH cohort AS (
  SELECT
    subject_id,
    DATE_TRUNC('week', MIN(occurred_at) AT TIME ZONE :analysis_timezone)::date AS cohort_week
  FROM events
  WHERE event_name = :entry_event
  GROUP BY subject_id
), activity AS (
  SELECT DISTINCT
    subject_id,
    DATE_TRUNC('week', occurred_at AT TIME ZONE :analysis_timezone)::date AS activity_week
  FROM events
  WHERE event_name = :return_event
), matrix AS (
  SELECT
    c.subject_id,
    c.cohort_week,
    ((a.activity_week - c.cohort_week) / 7)::int AS period
  FROM cohort c
  JOIN activity a USING (subject_id)
  WHERE a.activity_week >= c.cohort_week
)
SELECT cohort_week, period, COUNT(DISTINCT subject_id) AS retained_subjects
FROM matrix
GROUP BY cohort_week, period
ORDER BY cohort_week, period;

Join to cohort sizes and exclude cohorts that have not matured for the period. Define whether retention is exact-period, rolling, or bounded. Adapt SQL dialect and event eligibility.

Usage decline

Measure a subject against its own prior comparable windows:

WITH periods AS (
  SELECT
    subject_id,
    COUNT(*) FILTER (
      WHERE occurred_at >= :current_start AND occurred_at < :current_end
    ) AS current_value_events,
    COUNT(*) FILTER (
      WHERE occurred_at >= :prior_start AND occurred_at < :prior_end
    ) AS prior_value_events
  FROM events
  WHERE event_name = :value_event
  GROUP BY subject_id
)
SELECT *,
  (current_value_events - prior_value_events)::numeric
    / NULLIF(prior_value_events, 0) AS relative_change
FROM periods;

Align weekday/season length, exclude incomplete windows, set a minimum prior activity, and distinguish product-wide seasonality from subject-specific decline. Do not label a person “at risk” without validating association with churn or a business rule.

Query validation

For every final query:

• inspect join cardinality and duplicate amplification;
• reconcile totals to the eligible population;
• test empty, small, tied, null, deleted, and multi-account cases;
• verify time boundaries and timezone;
• compare a sample of subjects with raw event timelines;
• explain overlap or enforce mutually exclusive segments;
• use parameters, least-privilege access, and minimal fields;
• review execution plan/cost on large datasets.

ORM output is optional. Produce it only when the application needs the query in that ORM; do not duplicate SQL mechanically.

Action design

For each finding state:

• evidence and denominator;
• plausible mechanism;
• alternative explanations;
• affected population and privacy risk;
• proposed action and expected change;
• guardrail and test design;
• decision owner and review window.

Founder outreach, incentives, onboarding, or feature education are hypotheses—not universal winners. Test proportionally and stop actions that create complaint, trust, or fairness harm.