How to calculate Activation Rate

Formula

Activation rate = signups who completed the activation event within N days ÷ signups × 100

SQL query

This example assumes: users(id, signed_up_at), events(user_id, occurred_at, event_name). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT DATE_TRUNC('week', u.signed_up_at) AS signup_week,
       COUNT(*) AS signups,
       COUNT(a.user_id) AS activated,
       COUNT(a.user_id) * 100.0 / NULLIF(COUNT(*), 0) AS activation_pct
FROM users u
LEFT JOIN (
  SELECT DISTINCT e.user_id
  FROM events e JOIN users uu ON uu.id = e.user_id
  WHERE e.event_name = 'first_query_run'
    AND e.occurred_at <= uu.signed_up_at + INTERVAL '7 days'
) a ON a.user_id = u.id
WHERE u.signed_up_at < CURRENT_DATE - 7
GROUP BY 1
ORDER BY 1;

Common mistakes that make Activation Rate wrong

• The activation event must correlate with long-term retention; test that, do not assume it.
• Signups younger than the activation window cannot be judged and are excluded.
• Changing the activation definition resets the trend.

Ask for Activation Rate in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What share of signups ran a query within 7 days?
• Which signup sources have the best activation?