Product Analyst Interview Questions: A Worked Activation Case

Product analyst interviews often combine a metric question, a query, and a recommendation. This worked case shows why a correct aggregate can still support an incorrect conclusion. All data is fictional, and the questions are original practice material.

1. How would you define activation?

Suppose activation means completing a first project within seven days of signup. Define the eligible signup population, the event that counts as completion, deduplication rules, the reporting timezone, and the full seven-day observation window. Exclude immature signup cohorts from a completed-window comparison or label their results as incomplete.

Use activated eligible users divided by eligible users, with one observation per user. Do not divide activation events by sessions and call the result a user activation rate. Check instrumentation and eligibility before interpreting a change.

2. Did activation improve?

The following fully observed cohorts are summarized by acquisition channel. Each user belongs to exactly one channel and one period.

Overall activation rises from 220/1000 = 22% to 380/1000 = 38%, a 16-percentage-point increase. Neither channel’s rate changes. The shift toward channel A explains the aggregate increase in this fixture. Averaging the two channel percentages without weights would return 30% in both periods and would not describe either overall cohort.

3. Write and verify the query

This PostgreSQL query uses only an inline dataset. Sum compatible counts before dividing and protect the denominator. The numeric cast prevents integer division.

WITH cohorts(period, channel, eligible, activated) AS (
  VALUES ('before', 'A', 100, 40), ('before', 'B', 900, 180),
         ('after', 'A', 900, 360), ('after', 'B', 100, 20)
)
SELECT period, SUM(eligible) AS eligible, SUM(activated) AS activated,
       ROUND(SUM(activated)::numeric / NULLIF(SUM(eligible), 0), 4) AS rate
FROM cohorts
GROUP BY period
ORDER BY CASE period WHEN 'before' THEN 1 ELSE 2 END;

The expected rates are 0.2200 and 0.3800. Validate that counts are nonnegative, activated does not exceed eligible, and the groups do not overlap. NULLIF prevents division by zero; it does not repair invalid counts. Add channel to the grouping when diagnosing the composition change.

4. Does the result prove a new onboarding flow worked?

No. This is a before/after comparison with a changing acquisition mix. Other differences could include seasonality, user intent, event collection, or concurrent releases. A valid aggregate trend is not automatically a treatment effect.

Report the aggregate and channel rates together. To summarize a composition-adjusted descriptive comparison, apply the same prespecified channel weights to both periods. With equal weights, both standardized rates are 30%; that is a different estimand from the observed population rate. Adjustment for channel alone does not remove all possible confounding.

5. What experiment would you propose?

Randomize eligible users to the existing and revised onboarding flows using a stable assignment. Predefine the primary seven-day activation metric, eligibility, observation window, practical effect of interest, sample-size analysis, duration, and decision rules. Track completion errors, support demand, and downstream retention as appropriate guardrails.

Check assignment balance and sample ratio mismatch, missing events, and treatment leakage before interpreting effects. Do not stop at the first favorable p-value. Analyze according to the planned assignment and metric definition, and account for clustered assignment or interference if the product requires it.

6. What would you tell a product manager?

A concise answer is: “Observed activation increased from 22% to 38%. In this dataset, both channel rates stayed constant while the acquisition mix shifted. We cannot attribute the increase to onboarding. I would verify cohort and event definitions, then evaluate the flow using a planned controlled test.”

For practice, change one channel’s rate, introduce a zero-eligible group, and predict what the query should return. Explain which findings remain descriptive and what evidence would support a causal recommendation. Use the worked A/B test for the statistical follow-up.