SaaS product analytics, in plain SQL

-- Signup -> workspace -> upgrade funnel. A user counts at a step only if they
-- reached the previous step first, so the numbers can only go down.
WITH step AS (
    SELECT user_id, event_name, MIN(event_ts) AS ts
    FROM events
    WHERE event_name IN ('signed_up', 'created_workspace', 'upgraded')
    GROUP BY user_id, event_name
),
journey AS (
    SELECT s1.ts AS t1, s2.ts AS t2, s3.ts AS t3
    FROM step s1
    LEFT JOIN step s2 ON s2.user_id = s1.user_id
                     AND s2.event_name = 'created_workspace' AND s2.ts >= s1.ts
    LEFT JOIN step s3 ON s3.user_id = s1.user_id
                     AND s3.event_name = 'upgraded' AND s2.ts IS NOT NULL AND s3.ts >= s2.ts
    WHERE s1.event_name = 'signed_up'
),
counts AS (
    SELECT 1 AS step_no, 'Signed up' AS step, COUNT(t1) AS users FROM journey
    UNION ALL SELECT 2, 'Created a workspace', COUNT(t2) FROM journey
    UNION ALL SELECT 3, 'Upgraded to paid', COUNT(t3) FROM journey
)
SELECT step_no,
       step,
       users,
       ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS pct_of_signups,
       ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1)         AS pct_of_previous_step
FROM counts
ORDER BY step_no;

-- Which acquisition channel brings users who actually convert?
-- Ranked by upgrade rate (upgraded users / all signups from that channel).
WITH per_user AS (
    SELECT u.user_id,
           u.channel,
           MAX(e.event_name = 'created_workspace') AS activated,
           MAX(e.event_name = 'upgraded')          AS upgraded
    FROM users u
    LEFT JOIN events e ON e.user_id = u.user_id
    GROUP BY u.user_id, u.channel
),
by_channel AS (
    SELECT channel,
           COUNT(*)       AS users,
           SUM(activated) AS activated,
           SUM(upgraded)  AS upgraded
    FROM per_user
    GROUP BY channel
)
SELECT channel,
       users,
       activated,
       upgraded,
       ROUND(100.0 * activated / users, 1) AS activation_rate,
       ROUND(100.0 * upgraded / users, 1)  AS upgrade_rate,
       RANK() OVER (ORDER BY 1.0 * upgraded / users DESC) AS rank_by_upgrade
FROM by_channel
ORDER BY rank_by_upgrade, channel;

-- Among users who created a workspace: do those who invited a teammate
-- upgrade more often? (An association, not proof that inviting causes upgrades.)
WITH activated AS (
    SELECT DISTINCT user_id FROM events WHERE event_name = 'created_workspace'
),
flags AS (
    SELECT a.user_id,
           EXISTS (SELECT 1 FROM events e
                   WHERE e.user_id = a.user_id AND e.event_name = 'invited_teammate') AS invited,
           EXISTS (SELECT 1 FROM events e
                   WHERE e.user_id = a.user_id AND e.event_name = 'upgraded')         AS upgraded
    FROM activated a
)
SELECT CASE invited WHEN 1 THEN 'Invited a teammate' ELSE 'Did not invite' END AS segment,
       COUNT(*)      AS users,
       SUM(upgraded) AS upgraded,
       ROUND(100.0 * SUM(upgraded) / COUNT(*), 1) AS upgrade_rate
FROM flags
GROUP BY invited
ORDER BY invited DESC;

-- Weekly active users (anyone with a session) and week-over-week change.
WITH weekly AS (
    SELECT date(event_ts, 'weekday 0', '-6 days') AS week_start,
           COUNT(DISTINCT user_id)                AS wau
    FROM events
    WHERE event_name = 'session'
    GROUP BY week_start
)
SELECT week_start,
       wau,
       ROUND(100.0 * (wau - LAG(wau) OVER (ORDER BY week_start))
                   / LAG(wau) OVER (ORDER BY week_start), 1) AS wow_pct
FROM weekly
ORDER BY week_start;

-- Weekly cohort retention. A cohort is everyone who signed up in the same
-- Monday-to-Sunday week; "retained in week N" means at least one session
-- N weeks after the cohort week started. Weeks that have not happened yet
-- (relative to the last event in the data) are left out, not shown as zero.
WITH cohort AS (
    SELECT user_id, date(event_ts, 'weekday 0', '-6 days') AS cohort_week
    FROM events
    WHERE event_name = 'signed_up'
),
data_end AS (
    SELECT MAX(date(event_ts)) AS last_day FROM events
),
sizes AS (
    SELECT cohort_week, COUNT(*) AS cohort_size
    FROM cohort
    GROUP BY cohort_week
),
weeks(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM weeks WHERE n < 30
),
grid AS (
    SELECT s.cohort_week, s.cohort_size, w.n AS week_no
    FROM sizes s
    CROSS JOIN weeks w
    CROSS JOIN data_end d
    WHERE date(s.cohort_week, '+' || (w.n * 7) || ' days') <= d.last_day
),
active AS (
    SELECT c.cohort_week,
           CAST((julianday(date(e.event_ts)) - julianday(c.cohort_week)) / 7 AS INTEGER) AS week_no,
           COUNT(DISTINCT e.user_id) AS retained
    FROM events e
    JOIN cohort c ON c.user_id = e.user_id
    WHERE e.event_name = 'session'
    GROUP BY c.cohort_week, week_no
)
SELECT g.cohort_week,
       g.cohort_size,
       g.week_no,
       COALESCE(a.retained, 0) AS retained,
       ROUND(100.0 * COALESCE(a.retained, 0) / g.cohort_size, 1) AS retention_pct
FROM grid g
LEFT JOIN active a ON a.cohort_week = g.cohort_week AND a.week_no = g.week_no
ORDER BY g.cohort_week, g.week_no;