Time To Convert Measurement

Why this matters

Time to Convert (TTC) tells you how long it takes users to progress from a starting action to a target action (e.g., signup to first purchase). Product Analysts use TTC to:

• Prioritize onboarding fixes that reduce delays between key steps.
• Choose remarketing windows (e.g., send a reminder at median+10% hours).
• Compare channels or experiments by speed to value, not just conversion rate.
• Detect friction: long tails or plateaus in conversion timing indicate blockers.

Concept explained simply

Think of a stopwatch that starts at a start event (e.g., signup) and stops at an end event (e.g., first purchase). For each user, record the elapsed time. Summarize the distribution with median, percentiles (p75, p90), and visualize it to spot bottlenecks. Some users never convert within your analysis window—those are right-censored. You should keep track of them for rate curves and survival analysis.

Mental model: The Stopwatch per Cohort

• Define the cohort and when the stopwatch starts (first event time).
• Stop it at the first qualifying conversion.
• If time expires (analysis window ends) before a conversion, mark as right-censored.
• Summarize the times; compare segments to identify faster paths.

Key definitions and choices

• Start event: where the clock starts (e.g., first app open, signup, add-to-cart).
• End event: what counts as conversion (e.g., first purchase, complete onboarding).
• Scope: user-level or session-level.
• Window: maximum time to observe conversion (e.g., 7/14/30 days).
• Inclusion rules: first conversion only; deduplicate retries; exclude test users/fraud.
• Summaries: median is robust; mean is sensitive to outliers; p90/p95 surface long-tail friction.
• Censoring: right-censored users didn’t convert within the window. Include them for survival curves or time-bucketed cumulative conversion.

Data preparation: step-by-step

1. Choose start/end events and the maximum observation window.
2. Extract earliest start time per user (or per session) within your study period.
3. Find the first end event after start time and within the window.
4. Compute TTC as a duration; mark records with no end event as right-censored.
5. Filter out noise (internal/test accounts, bots).
6. Segment by channel, device, experiment arm, country, or plan.

Worked examples

Example 1 — Signup to First Purchase

Cohort: 1,000 new signups this week. Results:

• Median TTC = 18 hours
• p90 TTC = 5.2 days
• 35% convert within 24 hours, 60% within 3 days, 68% within 7 days

Interpretation: Most value comes quickly; beyond day 3, conversion slows. Consider nudges around 12–24 hours.

Example 2 — Channel Comparison

• Paid Search: median 12h, p90 3.5d
• Organic: median 26h, p90 6.0d

Even with similar conversion rates, Paid Search converts faster. If cash flow matters, allocate budget toward the faster channel while monitoring LTV.

Example 3 — Multi-step Funnel

Signup → Profile Complete → First Use → Subscription

• Signup → Profile: median 30 min
• Profile → First Use: median 2 h
• First Use → Subscription: median 3.5 d; p90 10 d

Bottleneck is post-first-use to subscription. Focus experiments there (e.g., in-product education, trials, nudges).

SQL patterns (generic)

-- Assumes events(user_id, event_name, event_ts)
WITH start AS (
  SELECT user_id, MIN(event_ts) AS start_ts
  FROM events
  WHERE event_name = 'signup'
  GROUP BY 1
), end AS (
  SELECT e.user_id, MIN(e.event_ts) AS end_ts
  FROM events e
  JOIN start s ON e.user_id = s.user_id AND e.event_ts >= s.start_ts
  WHERE e.event_name = 'purchase'
  GROUP BY 1
), joined AS (
  SELECT s.user_id, s.start_ts, e.end_ts,
         CASE WHEN e.end_ts IS NULL THEN 1 ELSE 0 END AS right_censored,
         EXTRACT(EPOCH FROM (COALESCE(e.end_ts, s.start_ts) - s.start_ts))/60.0 AS ttc_minutes -- if your SQL supports EXTRACT(EPOCH)
  FROM start s
  LEFT JOIN end e USING (user_id)
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(*) FILTER (WHERE right_censored = 0) AS converters,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ttc_minutes) FILTER (WHERE right_censored = 0) AS median_minutes,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ttc_minutes) FILTER (WHERE right_censored = 0) AS p90_minutes
FROM joined;

WITH base AS (
  SELECT s.user_id, s.start_ts, e.end_ts
  FROM start s LEFT JOIN end e USING (user_id)
), days AS (
  SELECT user_id, start_ts, end_ts,
         GENERATE_SERIES(0, 30) AS day_since_start -- 31 days window
  FROM base
), status AS (
  SELECT user_id, day_since_start,
         CASE WHEN end_ts IS NOT NULL AND end_ts <= start_ts + (day_since_start || ' days')::interval THEN 1 ELSE 0 END AS converted_by_day
  FROM days
)
SELECT day_since_start,
       AVG(converted_by_day) AS cumulative_conversion -- denominator includes all users
FROM status
GROUP BY 1
ORDER BY 1;

Choosing the right summary

• Median: preferred central tendency; robust to outliers.
• p90/p95: tells you when most converters finally convert; guides remarketing cutoffs.
• Mean: use sparingly; long tails distort it.
• Segments: always compare segments (channel, device, plan, country) for actionable differences.

Visualizations that help

• Histogram of TTC (log scale if long-tailed).
• Cumulative conversion curve by day.
• Survival curve (1 − cumulative conversion).
• Box/violin plots across segments.

Common mistakes and how to self-check

• Mixing time zones: normalize to UTC or user-local consistently.
• Ignoring right-censoring: report both converter-only metrics and cumulative conversion including everyone.
• Wrong start event: ensure the clock starts at the correct first occurrence.
• Double counting conversions: use the first qualifying end event after start.
• Window mismatch: document the observation window; do not compare 7-day vs 30-day TTC directly.
• Outliers dominate: prefer median and percentiles; show distribution.

Exercises

These mirror the exercises below. Work them directly here, then check the solutions.

user_id,event,ts
u1,signup,2025-01-01 09:00
u1,purchase,2025-01-02 10:00
u2,signup,2025-01-01 12:00
u2,purchase,2025-01-01 16:00
u3,signup,2025-01-01 18:00
u3,purchase,2025-01-04 18:00
u4,signup,2025-01-02 09:00
u5,signup,2025-01-02 10:15
u5,purchase,2025-01-03 09:15
u6,signup,2025-01-02 11:00
u6,purchase,2025-01-02 11:30
u7,signup,2025-01-03 10:00
u7,purchase,2025-01-10 10:00
u8,signup,2025-01-03 11:00

• Checklist: Decide start/end events and window before querying.
• Checklist: Use median and p90, not just mean.
• Checklist: Show cumulative conversion including censored users.

Practical projects

• Build a weekly TTC dashboard for signup → first value. Include median, p90, and a 30-day cumulative curve by channel.
• Run an onboarding experiment; compare TTC distributions (signup → first success action) across variants.
• Create a churn-risk alert if TTC from first use → second use exceeds p90 for a user segment.

Who this is for

• Product Analysts and Data Analysts focused on activation, monetization, or growth.
• PMs who need timing-based insights to improve onboarding and payback speed.

Prerequisites

• Comfort with SQL joins, filtering, and basic window functions.
• Understanding of funnels and conversion events.
• Basic statistics: median, percentiles.

Learning path

• 1) Define events and windows → 2) Compute converter TTC → 3) Add cumulative conversion including censored → 4) Segment and compare → 5) Visualize and act.

Next steps

• Take the quick test to check your understanding.
• Apply the SQL pattern to your own product’s key funnel.
• Share insights with your team and propose one experiment to reduce p90 TTC.

Note: The quick test is available to everyone; only logged-in users get their progress saved.

Mini challenge

Your funnel is Add to Cart → Purchase. Median TTC is stable, but p90 increased from 3 to 5 days after a shipping change. List two hypotheses and one quick analysis to validate each.