使用 SQL 測量轉化率

npm install telemetry-sh

import telemetry from "telemetry-sh";

telemetry.init(process.env.TELEMETRY_API_KEY);

await telemetry.log("product_events", {
  event_name: "signup_completed",
  user_id: user.id,
  team_id: team.id,
  acquisition_channel: "docs",
  plan: "free",
});

await telemetry.log("product_events", {
  event_name: "first_event_received",
  user_id: user.id,
  team_id: team.id,
  acquisition_channel: user.acquisitionChannel,
  plan: team.plan,
});

WITH user_funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'signup_completed' THEN timestamp_utc END)
      AS signed_up_at,
    MIN(CASE WHEN event_name = 'workspace_created' THEN timestamp_utc END)
      AS workspace_created_at,
    MIN(CASE WHEN event_name = 'first_event_received' THEN timestamp_utc END)
      AS first_event_at,
    MIN(CASE WHEN event_name = 'dashboard_created' THEN timestamp_utc END)
      AS dashboard_created_at
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
    AND event_name IN (
      'signup_completed',
      'workspace_created',
      'first_event_received',
      'dashboard_created'
    )
  GROUP BY user_id
),
stage_counts AS (
  SELECT 1 AS step, 'Signed up' AS stage, COUNT(*) AS users
  FROM user_funnel
  WHERE signed_up_at IS NOT NULL

  UNION ALL

  SELECT 2, 'Created workspace', COUNT(*)
  FROM user_funnel
  WHERE workspace_created_at >= signed_up_at

  UNION ALL

  SELECT 3, 'Sent first event', COUNT(*)
  FROM user_funnel
  WHERE first_event_at >= workspace_created_at

  UNION ALL

  SELECT 4, 'Created dashboard', COUNT(*)
  FROM user_funnel
  WHERE dashboard_created_at >= first_event_at
)
SELECT
  step,
  stage,
  users,
  ROUND(
    100.0 * users /
    NULLIF(MAX(CASE WHEN step = 1 THEN users END) OVER (), 0),
    2
  ) AS conversion_from_signup_pct,
  ROUND(
    100.0 * users /
    NULLIF(LAG(users) OVER (ORDER BY step), 0),
    2
  ) AS conversion_from_previous_pct
FROM stage_counts
ORDER BY step;