Cohort Funnels to Decisions: A PM’s One‑Hour Guide
Build cohort-aware funnels and a decision dashboard that drive triage, experiments, and shipping decisions, starting from raw events.

If you own activation, retention or monetization and your charts tell you everything and nothing, this guide is for you. We will build cohort‑aware funnels and a decision dashboard that answer three things: do we triage, do we experiment, or do we ship. At the end, you will have SQL you can run, two dashboard tiles you can ship, and three hypothesis cards ready for engineers and analysts.
In brief
- For: Product managers who own activation, retention or monetization.
- Outcome: A cohort‑aware funnel, a decision dashboard, and a prioritized experiment list.
- In the hour: Define the decision, list events and identity joins, write cohort SQL, add two dashboard tiles, draft three hypotheses.
Start with the decision, not the chart
Decision‑first metrics keep you out of the vanity trap. Write the single question you must answer this week. Examples:
- Activation: Which cohort’s drop‑off should we triage now, and which can wait?
- Retention: Did last month’s change help week‑4 return for the same users we saw in week 1?
- Monetization: Do users who tried feature X convert within 28 days more than those who did not?
Instrument identity that persists across time and variants
Cohorts break when identity is weak. Use immutable keys and join paths that survive experiments, devices and platforms.
- Stable user_id (post‑signup) and anonymous_id (pre‑signup). Keep both and link when signup happens.
- Optional account_id or workspace_id for B2B. Do not recycle IDs.
- Event_time in UTC, event_name, source (web, iOS, Android, server).
- experiment_id and variant for every event while a test runs.
- Idempotency key per event to avoid duplicates.
- Schema_version so you can migrate without breaking queries.
Build cohort‑aware funnels with lifetime windows
A cohort‑aware funnel tracks the same users across steps. Add a lifetime window per step to capture delayed effects, like week‑2 activation from users who signed up in week 0.
| Traditional funnel | Cohort‑aware funnel |
|---|---|
| Counts events per step in a period. | Follows the same users from a cohort across steps. |
| Mixes new and old users. | Fixes a cohort start (e.g., signup week). |
| Misses delayed conversions. | Uses lifetime windows (e.g., convert within 28 days). |
| Good for volume trends. | Good for decisions on where to act and when. |
Example SQL to create weekly signup cohorts and compute stepwise conversion within 28 days. Keep windows consistent and users constant.
-- 1) Weekly signup cohorts
WITH signup AS (
SELECT user_id, MIN(event_time) AS signup_at,
DATE_TRUNC('week', MIN(event_time)) AS signup_week
FROM events
WHERE event_name = 'signup_completed'
GROUP BY user_id
),
-- 2) First occurrence of each step within 28 days of signup
steps AS (
SELECT s.user_id, s.signup_week,
MIN(CASE WHEN e.event_name = 'profile_completed' AND e.event_time <= s.signup_at + INTERVAL '28 days' THEN e.event_time END) AS profile_at,
MIN(CASE WHEN e.event_name = 'first_value_action' AND e.event_time <= s.signup_at + INTERVAL '28 days' THEN e.event_time END) AS fva_at,
MIN(CASE WHEN e.event_name = 'activation_metric' AND e.event_time <= s.signup_at + INTERVAL '28 days' THEN e.event_time END) AS activated_at
FROM signup s
LEFT JOIN events e ON e.user_id = s.user_id
GROUP BY s.user_id, s.signup_week
)
-- 3) Cohort conversion by step and by week since signup
SELECT signup_week,
COUNT(*) AS cohort_users,
COUNT(profile_at) * 1.0 / COUNT(*) AS to_profile,
COUNT(fva_at) * 1.0 / COUNT(*) AS to_first_value,
COUNT(activated_at) * 1.0 / COUNT(*) AS to_activation
FROM steps
GROUP BY signup_week
ORDER BY signup_week DESC;
Make a dashboard that answers: triage, experiment, or ship
Tile each decision. Show a clear status, the cohort and window, and the next step.
Triage
Purpose: flag urgent cohort drops.
- Signal: Largest negative delta vs trailing median, by cohort and step.
- Scope: Same users, fixed window.
- Action: Open an incident or start a deep dive.
Experiment
Purpose: show where to test next.
- Signal: Stable gap between cohorts or variants over multiple weeks.
- Scope: Same users, fixed window, same definition.
- Action: Add to hypothesis tracker with impact × confidence.
Ship
Purpose: confirm a durable win.
- Signal: Cohort lift holds across windows and segments.
- Scope: Post‑test monitoring, regression checks.
- Action: Roll out and set a guardrail watch.
Prioritize with expected impact × confidence
Use the cohort deltas to size ideas. Estimate the range you think is plausible, then set a confidence based on data quality and repeatability. Keep it simple and comparable.
- Expected impact: tie to the cohort step (e.g., +5 percentage points to week‑2 activation).
- Confidence: low / medium / high, based on cohort stability and past tests.
- Priority score: use a simple grid rather than fragile math. High‑impact × high‑confidence goes first.
Worked example: SaaS onboarding
Goal: improve activation. We will map events, cohort by signup week, compute stepwise conversion over four weeks, flag a week‑2 activation drop, and produce three experiment hypotheses with estimates and data needs.
1) Map events and identity joins
- Identity: anonymous_id on visit, user_id on signup; link them at signup; account_id for teams.
- Key events: signup_started, signup_completed, profile_completed, first_value_action, activation_metric.
- Context: experiment_id and variant on all events during tests.
2) Create weekly signup cohorts
CREATE TEMP VIEW signup_week AS
SELECT user_id,
DATE_TRUNC('week', MIN(event_time)) AS signup_week,
MIN(event_time) AS signup_at
FROM events
WHERE event_name = 'signup_completed'
GROUP BY user_id;
3) Compute stepwise cohort conversion over 4 weeks
WITH s AS (
SELECT * FROM signup_week
), steps AS (
SELECT s.user_id, s.signup_week,
MIN(CASE WHEN e.event_name = 'profile_completed' AND e.event_time <= s.signup_at + INTERVAL '28 days' THEN e.event_time END) AS profile_at,
MIN(CASE WHEN e.event_name = 'first_value_action' AND e.event_time <= s.signup_at + INTERVAL '28 days' THEN e.event_time END) AS fva_at,
MIN(CASE WHEN e.event_name = 'activation_metric' AND e.event_time <= s.signup_at + INTERVAL '28 days' THEN e.event_time END) AS activated_at
FROM s
LEFT JOIN events e ON e.user_id = s.user_id
GROUP BY s.user_id, s.signup_week
)
SELECT signup_week,
COUNT(*) AS cohort_users,
COUNT(profile_at)::float/COUNT(*) AS to_profile,
COUNT(fva_at)::float/COUNT(*) AS to_first_value,
COUNT(activated_at)::float/COUNT(*) AS to_activation
FROM steps
GROUP BY signup_week
ORDER BY signup_week DESC;
4) Surface a dashboard tile for triage
Triage: Activation drop
Cohort: Signup week of 2024‑W32
Window: Activation within 14–21 days (week 2)
Signal: Activation down 20% vs trailing 4‑week median for this step and cohort size band.
Next step: open incident, check release notes and experiment flags in that week, and segment by platform.
5) Produce a prioritized experiment slate
We base ideas on the week‑2 activation drop and where users stall. Estimates here are illustrative for the example.
| Hypothesis | Expected impact | Confidence | Data needed |
|---|---|---|---|
| Reduce friction in first_value_action by pre‑filling defaults for new signups. | +5–8 pp to week‑2 activation (absolute). | Medium (clear stall before activation; repeated pattern across cohorts). | Event timing from profile_completed to first_value_action; drop‑off reasons from session data. |
| Nudge at day 10: email in‑app prompt linking straight to the activation path. | +2–5 pp to week‑2 activation. | Medium (similar nudges helped earlier cohorts; delivery is reliable). | Reach and clickthrough by cohort; holdout to detect cannibalization of week‑3. |
| Clarify activation definition in‑product (progress meter at 60% with next step button). | +1–3 pp to week‑2 activation; potential bigger lift over 28 days. | Low–Medium (qual shows confusion; limited prior data). | Variant tag on events; measure spillover to week‑3/4. |
6) Add an experiment tracker tile
Experiment tracker
Target step: Week‑2 activation within 14–21 days
In flight: Nudge at day 10 — variant B running on 50% of week‑W33 cohort.
Decision gate: Lift holds across 2 consecutive signup weeks; no week‑3 regression.
Steps to do in one hour
Define one decision question
Write the question. Pick the cohort and window that answer it. Freeze the metric definition for this pass.
List events and identity joins
List user_id, anonymous_id, account_id. Map signup and activation events. Confirm experiment tags.
Write the cohort funnel SQL
Create weekly cohorts. Compute first occurrence of each step within a fixed window. Keep users constant.
Make the triage tile
Compare the latest cohort to a trailing median. Flag the largest negative delta and add the next action.
Make the experiment tracker tile
List in‑flight variants, target step, gate rules, and planned stop date. Keep it short.
Write three hypothesis cards
For each: the user problem, the change, the target step, expected impact range, confidence, and data needed.
Mistakes to avoid
- Counting events instead of users per cohort. Funnels are about the same users across steps.
- Mixing time windows. Keep the lifetime window fixed per step while comparing cohorts.
- Trusting a single A/B lift without cohort checks. Confirm it holds across signup weeks and windows.
- Dashboards that stop at vanity metrics. Every tile must say what to do next.
- Silent identity breaks. Audit joins after releases and experiment flag changes.


