Product Analytics with SQL: Tracking What Actually Matters

-- Who signed up and when
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       TEXT,
  plan        TEXT,        -- 'free', 'pro', 'enterprise'
  created_at  TIMESTAMPTZ
);

-- Every meaningful action a user takes
CREATE TABLE events (
  id          SERIAL PRIMARY KEY,
  user_id     INT REFERENCES users(id),
  event_name  TEXT,        -- 'signed_up', 'invited_team', 'created_dashboard', etc.
  properties  JSONB,
  occurred_at TIMESTAMPTZ
);

-- Revenue events
CREATE TABLE subscriptions (
  id            SERIAL PRIMARY KEY,
  user_id       INT REFERENCES users(id),
  plan          TEXT,
  started_at    TIMESTAMPTZ,
  cancelled_at  TIMESTAMPTZ
);

-- Daily Active Users for the last 30 days
SELECT
  DATE_TRUNC('day', occurred_at)::DATE AS day,
  COUNT(DISTINCT user_id)              AS dau
FROM events
WHERE occurred_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

-- Monthly Active Users, last 6 months
SELECT
  DATE_TRUNC('month', occurred_at)::DATE AS month,
  COUNT(DISTINCT user_id)                AS mau
FROM events
WHERE occurred_at >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

-- DAU/MAU ratio for current month
WITH dau AS (
  SELECT
    DATE_TRUNC('day', occurred_at)::DATE AS day,
    COUNT(DISTINCT user_id) AS daily_users
  FROM events
  WHERE occurred_at >= DATE_TRUNC('month', NOW())
  GROUP BY 1
),
mau AS (
  SELECT COUNT(DISTINCT user_id) AS monthly_users
  FROM events
  WHERE occurred_at >= DATE_TRUNC('month', NOW())
)
SELECT
  AVG(daily_users)::NUMERIC(5,2)                    AS avg_dau,
  mau.monthly_users,
  (AVG(daily_users) / mau.monthly_users)::NUMERIC(4,3) AS dau_mau_ratio
FROM dau, mau
GROUP BY mau.monthly_users;

-- Signup → first dashboard created → invited a team member
WITH step1 AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'signed_up'
    AND occurred_at >= NOW() - INTERVAL '90 days'
),
step2 AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN step1 s ON e.user_id = s.user_id
  WHERE e.event_name = 'created_dashboard'
    AND e.occurred_at > (
      SELECT MIN(occurred_at) FROM events
      WHERE user_id = e.user_id AND event_name = 'signed_up'
    )
),
step3 AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN step2 s ON e.user_id = s.user_id
  WHERE e.event_name = 'invited_team_member'
)
SELECT
  COUNT(DISTINCT step1.user_id)                              AS signed_up,
  COUNT(DISTINCT step2.user_id)                              AS created_dashboard,
  COUNT(DISTINCT step3.user_id)                              AS invited_team,
  ROUND(COUNT(DISTINCT step2.user_id)::NUMERIC /
        NULLIF(COUNT(DISTINCT step1.user_id), 0) * 100, 1)  AS pct_to_step2,
  ROUND(COUNT(DISTINCT step3.user_id)::NUMERIC /
        NULLIF(COUNT(DISTINCT step2.user_id), 0) * 100, 1)  AS pct_to_step3
FROM step1
LEFT JOIN step2 ON TRUE
LEFT JOIN step3 ON TRUE;

WITH cohorts AS (
  -- Month each user signed up
  SELECT
    user_id,
    DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
),
activity AS (
  -- Every month each user was active
  SELECT
    user_id,
    DATE_TRUNC('month', occurred_at) AS active_month
  FROM events
  GROUP BY 1, 2
),
retention AS (
  SELECT
    c.cohort_month,
    EXTRACT(EPOCH FROM (a.active_month - c.cohort_month)) / 2592000 AS months_since_signup,
    COUNT(DISTINCT c.user_id) AS retained_users
  FROM cohorts c
  JOIN activity a ON c.user_id = a.user_id
  GROUP BY 1, 2
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY 1
)
SELECT
  r.cohort_month,
  r.months_since_signup::INT AS month_n,
  r.retained_users,
  cs.cohort_size,
  ROUND(r.retained_users::NUMERIC / cs.cohort_size * 100, 1) AS retention_pct
FROM retention r
JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
WHERE r.months_since_signup >= 0
ORDER BY 1, 2;

-- Feature usage rate among users still active after 60 days
WITH retained_users AS (
  SELECT DISTINCT u.id AS user_id
  FROM users u
  JOIN events e ON u.id = e.user_id
  WHERE u.created_at <= NOW() - INTERVAL '60 days'
    AND e.occurred_at >= u.created_at + INTERVAL '60 days'
),
all_new_users AS (
  SELECT id AS user_id
  FROM users
  WHERE created_at <= NOW() - INTERVAL '60 days'
)
SELECT
  e.event_name,
  COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN e.user_id END) AS retained_users_used,
  COUNT(DISTINCT a.user_id)                                          AS total_cohort,
  COUNT(DISTINCT e.user_id)                                          AS total_users_used,
  ROUND(
    COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN e.user_id END)::NUMERIC /
    NULLIF(COUNT(DISTINCT e.user_id), 0) * 100, 1
  ) AS adoption_among_retained_pct
FROM events e
JOIN all_new_users a ON e.user_id = a.user_id
LEFT JOIN retained_users r ON e.user_id = r.user_id
WHERE e.event_name NOT IN ('signed_up', 'logged_in', 'page_viewed')
GROUP BY 1
ORDER BY adoption_among_retained_pct DESC
LIMIT 20;

-- Average and median time from signup to first key action
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (first_action.occurred_at - u.created_at)) / 3600
  ) AS median_hours_to_activation,
  AVG(
    EXTRACT(EPOCH FROM (first_action.occurred_at - u.created_at)) / 3600
  )::NUMERIC(8,1)         AS avg_hours_to_activation,
  COUNT(*)                AS activated_users
FROM users u
JOIN LATERAL (
  SELECT MIN(occurred_at) AS occurred_at
  FROM events
  WHERE user_id = u.id
    AND event_name = 'created_dashboard'
) first_action ON first_action.occurred_at IS NOT NULL
WHERE u.created_at >= NOW() - INTERVAL '90 days';