Funnel, Cohort and Retention Analysis

Build conversion funnels, cohort tables and retention curves in SQL and Python, and explain what they say about a product.

Est. 40 min Updated Suggest a fix
// Part 01 — Start with a decision

A Funnel Is a Sequence, Not a Chart

A product team may ask why fewer new customers complete their first purchase. A funnel turns that broad question into an ordered set of user actions, such as visit, add_to_cart, and purchase. It tells you where observed progression thins out; by itself it does not explain why or prove that a change caused the drop.

Before querying, agree on the population, time window, event definitions, ordering rule, and denominator. Is the funnel for first-time visitors in a week, or all sessions? Can a user complete the steps across multiple sessions? Does the first event start the clock? Each choice changes the answer, so write it down before you compare teams or time periods.

AnalysisUnit and starting pointQuestion it answers
FunnelUsers or sessions that begin at a defined stepWhere do eligible journeys fail to reach the next step?
CohortEntities grouped by a shared start date or eventHow do groups with a common starting point behave later?
RetentionMembers of a cohort active again in a later periodWhat share returns, and how does that change with age?
ChurnCustomers who meet an explicit exit ruleHow many stop being customers during the defined period?
💡 Note
A funnel conversion rate is conditional on reaching its denominator step. Overall conversion and step-to-step conversion are different metrics; label both rather than using the word conversion without a definition.
// Part 02 — Prepare the events

Choose a Grain and Make Events Trustworthy

Assume an append-only event table with one row per recorded event: user_id, event_name, event_time, event_id, ingested_at, and optional session_id. The query below uses PostgreSQL syntax. A stable event ID helps remove duplicate ingestion; filtering invalid users and a bounded analysis window makes the population reproducible.

eligible_events.sql
sql
WITH eligible_events AS (
    SELECT DISTINCT ON (event_id)
        user_id,
        event_name,
        event_time,
        session_id
    FROM product_events
    WHERE user_id IS NOT NULL
      AND event_time >= TIMESTAMP '2026-01-01 00:00:00'
      AND event_time <  TIMESTAMP '2026-02-01 00:00:00'
      AND event_name IN ('visit', 'add_to_cart', 'purchase')
    ORDER BY event_id, ingested_at DESC
)
SELECT user_id, event_name, event_time, session_id
FROM eligible_events;

Deduplication needs a documented tie-breaker, here the most recently ingested copy of an event ID. Do not deduplicate on user_id plus event_name: a person can legitimately view or add items more than once. Also check event-time timezone handling and late arrivals before freezing a report.

⚠️ Important
The fixed dates are illustrative analysis parameters, not a recommended reporting period. In a scheduled model, pass the window in a controlled way and record its timezone and refresh cutoff.
// Part 03 — Build the funnel

Enforce Step Order at the User Grain

The user-level query finds the first timestamp for each event type, then counts a step only if its timestamp follows all required earlier steps. This allows different sessions. If the business rule requires one session, partition by session and count qualifying sessions instead; do not silently mix grains.

funnel_by_user.sql
sql
WITH user_steps AS (
    SELECT
        user_id,
        MIN(event_time) FILTER (WHERE event_name = 'visit') AS visited_at,
        MIN(event_time) FILTER (WHERE event_name = 'add_to_cart') AS carted_at,
        MIN(event_time) FILTER (WHERE event_name = 'purchase') AS purchased_at
    FROM product_events
    WHERE user_id IS NOT NULL
      AND event_time >= TIMESTAMP '2026-01-01 00:00:00'
      AND event_time <  TIMESTAMP '2026-02-01 00:00:00'
      AND event_name IN ('visit', 'add_to_cart', 'purchase')
    GROUP BY user_id
), qualified AS (
    SELECT
        user_id,
        visited_at,
        CASE WHEN carted_at >= visited_at THEN carted_at END AS carted_after_visit,
        CASE WHEN purchased_at >= visited_at
                  AND carted_at >= visited_at
                  AND purchased_at >= carted_at THEN purchased_at END AS purchased_after_cart
    FROM user_steps
    WHERE visited_at IS NOT NULL
)
SELECT
    COUNT(*) AS visitors,
    COUNT(carted_after_visit) AS visitors_who_carted,
    COUNT(purchased_after_cart) AS visitors_who_purchased,
    COUNT(carted_after_visit)::numeric / NULLIF(COUNT(*), 0) AS visit_to_cart_rate,
    COUNT(purchased_after_cart)::numeric / NULLIF(COUNT(carted_after_visit), 0) AS cart_to_purchase_rate
FROM qualified;

This simple definition takes the earliest occurrence of each event in the window. It can miss a valid later sequence when an earlier purchase was recorded before a later cart. For complex journeys, use ordered event matching or a window-function approach that explicitly finds the first valid sequence. Validate sampled users against raw events.

  1. Define who is eligible to enter and how the entry event is selected.
  2. Specify the allowed time between steps and whether sessions may differ.
  3. Compute each step only after its required predecessor; keep the entity grain consistent.
  4. Publish counts alongside rates so a percentage is never detached from its denominator.
// Part 04 — Form cohorts

Group Customers by a Meaningful Start

A cohort is a set of entities sharing a meaningful event or date. Signup month is common for retention; first purchase month may be better for a commerce reactivation question. Make the cohort anchor stable and avoid letting a later activity redefine membership.

monthly_retention.sql
sql
WITH first_activity AS (
    SELECT user_id, MIN(event_time)::date AS first_seen
    FROM product_events
    WHERE event_name = 'signup'
      AND user_id IS NOT NULL
    GROUP BY user_id
), cohort_members AS (
    SELECT user_id, DATE_TRUNC('month', first_seen)::date AS cohort_month
    FROM first_activity
), active_months AS (
    SELECT DISTINCT user_id, DATE_TRUNC('month', event_time)::date AS activity_month
    FROM product_events
    WHERE event_name IN ('session_started', 'purchase')
), cohort_activity AS (
    SELECT
        c.cohort_month,
        ((EXTRACT(YEAR FROM a.activity_month) - EXTRACT(YEAR FROM c.cohort_month)) * 12
          + EXTRACT(MONTH FROM a.activity_month) - EXTRACT(MONTH FROM c.cohort_month))::int AS month_number,
        c.user_id
    FROM cohort_members c
    JOIN active_months a ON a.user_id = c.user_id
    WHERE a.activity_month >= c.cohort_month
), cohort_sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size
    FROM cohort_members
    GROUP BY cohort_month
)
SELECT
    a.cohort_month,
    a.month_number,
    s.cohort_size,
    COUNT(DISTINCT a.user_id) AS active_users,
    COUNT(DISTINCT a.user_id)::numeric / NULLIF(s.cohort_size, 0) AS retention_rate
FROM cohort_activity a
JOIN cohort_sizes s USING (cohort_month)
GROUP BY a.cohort_month, a.month_number, s.cohort_size
ORDER BY a.cohort_month, a.month_number;

Month zero is the signup month and will commonly be 100 percent if signup itself counts as activity. State whether that month is included. The query also treats any listed activity as retained. A paid subscription business might instead use an active paid status at the end of each month; select a definition that fits the product contract.

🎯 Pro Tip
A retention curve needs a clear return event. Logging a screen view, completing a meaningful task, and paying again are not interchangeable definitions of active.
// Part 05 — Read the curve

Compare Like with Like and Respect Censoring

Plot cohort age on the horizontal axis and retention rate on the vertical axis. Newer cohorts have not had time to reach later ages, so a blank month is not zero retention. Compare cohorts only at ages with enough observation time, and show the cohort size or uncertainty when counts are small.

plot_retention.py
python
import pandas as pd
import matplotlib.pyplot as plt

# CSV exported from the SQL cohort query: cohort_month, month_number,
# cohort_size, active_users, retention_rate.
retention = pd.read_csv("monthly_retention.csv", parse_dates=["cohort_month"])
wide = retention.pivot(
    index="cohort_month",
    columns="month_number",
    values="retention_rate",
).sort_index()

ax = wide.T.plot(marker="o", figsize=(9, 5), ylim=(0, 1))
ax.set_xlabel("Months since cohort start")
ax.set_ylabel("Share active under the chosen definition")
ax.set_title("Monthly cohort retention")
ax.grid(True, alpha=0.25)
plt.tight_layout()
plt.show()

Keep missing ages as missing so the chart does not imply a measured zero. Inspect the underlying counts before interpreting an unusual slope. A small cohort can swing sharply when just a few people return.

PatternPossible interpretationNext check
Sharp early declineActivation or expectation mismatchCheck onboarding steps and first-value time
Stable early, then gradual decayRecurring value may weaken over timeSegment by acquisition source and product use
One cohort breaks trendProduct, campaign or data changeInspect release dates, tracking changes and mix
New cohorts look unusually strongImprovement or incomplete observationCompare only equal cohort ages and validate maturity
// Part 06 — Churn carefully

Do Not Infer Churn from a Quiet Month

A customer with no event this month may be on vacation, may use the service irregularly, or may have left. For a subscription, churn can mean cancellation or a paid subscription ending; for a marketplace, inactivity needs an explicit observation window and is better described as inactivity risk. Report the operational definition and the date at which status is evaluated.

Segmenting by signup source, plan, geography, or first-week behavior can help locate patterns. Keep segments large enough to be interpretable, compare equivalent calendar periods, and separate descriptive evidence from a causal explanation. A discount cohort that retains better does not prove the discount caused retention; customer mix may differ.

📌 Real World Example
For a subscription dashboard, a defensible monthly logo churn numerator might be customers active at the start of the month whose paid subscription ended during that month. Divide by the active starting customer count and separately track reactivations. Document treatment of trials, pauses, refunds and plan changes.
// Part 07 — Production checks

Make the Metric Reproducible

Before publishing, verify that IDs are non-null, event timestamps use one timezone, events are deduplicated by a stable key, and the cohort denominator matches the declared population. Reconcile a small sample to source records, and compare totals with an independent query or product-system report.

Instrumentation often changes after launch. Version event definitions, record the date of semantic changes, and avoid comparing pre-change and post-change curves without a note. Apply access controls and aggregate small groups where learner, health, payment or other sensitive activity could be exposed.

⚠️ Important
A polished retention chart can make weak tracking look authoritative. If an event definition or tracking gap makes a conclusion unsafe, label the limitation and fix measurement before recommending a product intervention.
// Part 08 — Pitfalls

Common Mistakes

Counting event rows instead of distinct users

Why: One person may fire the same event repeatedly.

Fix: Choose a user or session grain and count that entity consistently.

Ignoring event order

Why: Independent event totals can include a cart that happened before the visit.

Fix: Require each timestamp to follow its predecessor and validate actual sequences.

Treating immature cohorts as failures

Why: Recent cohorts have not reached older observation windows.

Fix: Show age-based maturity and compare only periods observed for every cohort.

Calling inactivity churn without a rule

Why: Different products have different natural usage cycles.

Fix: Define churn from the product contract or label the metric inactivity.

// Part 09 — Practice

Analyse a Fictional Delivery-App Funnel

Practice

From event table to a retention recommendation

  1. Write an event dictionary for visit, menu_view, checkout_started and order_completed, including properties and timezone.
  2. Build a user-level funnel with ordered steps and publish both step counts and conditional conversion rates.
  3. Create signup-month cohorts and define retention as at least one completed order in a later month.
  4. Plot the cohort table; mask unobserved cohort ages and annotate the minimum cohort size you consider useful.
  5. Write a short recommendation that distinguishes an observed pattern, a plausible explanation and the next validation.

Hint: Start with the exact entity grain and eligibility rule. Then hand-check a few users whose event order is ambiguous.

🎯 Key Takeaways

  • ✓A funnel is a defined, ordered journey; its grain, eligibility, time window and denominator must be explicit.
  • ✓Cohorts group entities by a stable starting event and compare later behavior at the same cohort age.
  • ✓Retention depends on an agreed meaningful return event; inactivity is not automatically churn.
  • ✓Use event ordering, deduplication and sample checks to make SQL counts trustworthy.
  • ✓Treat unobserved retention periods as missing, not zero, and compare cohorts at equal maturity.
  • ✓Descriptive patterns identify where to investigate; they do not prove why behavior changed.
  • For SQL date and window-function details, use the documentation for your database engine; this lesson's date syntax is PostgreSQL-oriented.

Next

What comes next

You have a set of measures and a retention story. Next, design a dashboard that helps a stakeholder take the right action.

Lesson 12 · Dashboards with Power BI, Tableau and Looker Studio →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...