Lens Metrics

-- User Retention by Signup Cohort
-- For each weekly cohort, % of users still active at D1, D7, D30
-- "Active" = any event in the events table (not just login)
WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('week', created_at) AS cohort_week
    FROM users
    WHERE created_at >= NOW() - INTERVAL '90 days'
),
activity AS (
    SELECT DISTINCT
        e.user_id,
        DATE_TRUNC('day', e.created_at) AS active_day
    FROM events e
    WHERE e.created_at >= NOW() - INTERVAL '90 days'
)
SELECT
    c.cohort_week,
    COUNT(DISTINCT c.user_id)                                        AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN a.active_day BETWEEN
            (MIN(u.created_at)::date + 1) AND
            (MIN(u.created_at)::date + 1)
        THEN a.user_id END)                                          AS retained_d1,
    COUNT(DISTINCT CASE
        WHEN a.active_day BETWEEN
            (MIN(u.created_at)::date + 7) AND
            (MIN(u.created_at)::date + 7)
        THEN a.user_id END)                                          AS retained_d7,
    COUNT(DISTINCT CASE
        WHEN a.active_day BETWEEN
            (MIN(u.created_at)::date + 30) AND
            (MIN(u.created_at)::date + 30)
        THEN a.user_id END)                                          AS retained_d30,
    ROUND(COUNT(DISTINCT CASE WHEN a.active_day =
        MIN(u.created_at)::date + 1 THEN a.user_id END)
        ::numeric / NULLIF(COUNT(DISTINCT c.user_id), 0) * 100, 1)  AS d1_pct,
    ROUND(COUNT(DISTINCT CASE WHEN a.active_day =
        MIN(u.created_at)::date + 7 THEN a.user_id END)
        ::numeric / NULLIF(COUNT(DISTINCT c.user_id), 0) * 100, 1)  AS d7_pct,
    ROUND(COUNT(DISTINCT CASE WHEN a.active_day =
        MIN(u.created_at)::date + 30 THEN a.user_id END)
        ::numeric / NULLIF(COUNT(DISTINCT c.user_id), 0) * 100, 1)  AS d30_pct
FROM cohorts c
JOIN users u ON u.user_id = c.user_id
LEFT JOIN activity a ON a.user_id = c.user_id
GROUP BY 1
ORDER BY 1 DESC;

-- Activation Rate
-- Definition: % of users who reach "activated" state within 7 days of signup
-- "Activated" = completed onboarding + created at least 1 project
-- Why 7 days: users who don't activate within a week rarely return
WITH signups AS (
    SELECT user_id, created_at AS signed_up_at
    FROM users
    WHERE created_at >= NOW() - INTERVAL '30 days'
),
activations AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_type = 'project_created'
),
onboarded AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_type = 'onboarding_complete'
)
SELECT
    COUNT(DISTINCT s.user_id)                               AS signups,
    COUNT(DISTINCT a.user_id)                               AS activated,
    ROUND(
        COUNT(DISTINCT a.user_id)::numeric /
        NULLIF(COUNT(DISTINCT s.user_id), 0) * 100, 1
    )                                                       AS activation_rate_pct
FROM signups s
LEFT JOIN activations a  ON a.user_id = s.user_id
LEFT JOIN onboarded   ob ON ob.user_id = s.user_id;

-- Engagement Ratio: DAU / WAU
-- Measures stickiness — how often weekly actives return daily
-- Benchmark: consumer apps target > 20%, B2B SaaS > 15%
WITH dau AS (
    SELECT COUNT(DISTINCT user_id) AS value
    FROM events
    WHERE created_at::date = CURRENT_DATE - 1  -- yesterday
),
wau AS (
    SELECT COUNT(DISTINCT user_id) AS value
    FROM events
    WHERE created_at >= CURRENT_DATE - 7
)
SELECT
    dau.value                                           AS dau,
    wau.value                                           AS wau,
    ROUND(dau.value::numeric / NULLIF(wau.value, 0) * 100, 1) AS engagement_ratio_pct
FROM dau, wau;

Event: project_created
Trigger: user clicks "Create Project" and the project is successfully saved
Properties:
  - project_id: string (UUID)
  - project_type: enum ['blank', 'template', 'imported']
  - user_id: string (UUID)
  - org_id: string (UUID)
  - plan: enum ['free', 'pro', 'enterprise']
  - created_at: ISO 8601 timestamp
Do NOT fire: on project duplication (use project_duplicated event instead)
Owner: [team responsible for instrumentation]

-- metrics/activation_rate.sql
CREATE OR REPLACE VIEW metrics.activation_rate AS
SELECT
    DATE_TRUNC('week', u.created_at)  AS cohort_week,
    COUNT(DISTINCT u.user_id)         AS signups,
    COUNT(DISTINCT e.user_id)         AS activated,
    ROUND(
        COUNT(DISTINCT e.user_id)::numeric /
        NULLIF(COUNT(DISTINCT u.user_id), 0) * 100,
    1)                                AS activation_rate_pct
FROM users u
LEFT JOIN events e
       ON e.user_id = u.user_id
      AND e.event_type = 'project_created'
      AND e.created_at <= u.created_at + INTERVAL '7 days'
GROUP BY 1
ORDER BY 1 DESC;

┌─ Metrics Spec: [Product Area] ─────────────────────────┐
│  Stage: [early/growth/mature]   Data source: [stack]   │
└────────────────────────────────────────────────────────┘

NORTH STAR
  [Metric Name]
  [Definition in one sentence]
  Target: [value]   Alert: [threshold]

KPIS (3–5)
──────────────────────────────────────────────────────────
  Metric              Definition              Target    Owner
  ──────────────────  ──────────────────────  ────────  ─────
  [name]              [precise definition]    [value]   [who]
  [name]              [precise definition]    [value]   [who]

IMPLEMENTED
  [N] SQL views → [location]
  [N] Event specs → [tracking plan location]
  Metrics doc → [path]

MISSING DATA
  [any metric that requires instrumentation not yet in place]

RULE
  Every metric has: precise definition, SQL query, target, owner.
  Missing any one of those? It's not a metric — it's a guess.