Root Cause Analysis and Diagnostics Questions

SELECT
    event_time,
    COALESCE(event_props->>'new_property', event_props->>'old_property') AS property_val,
    ...
  FROM events
  WHERE event_type = 'purchase';

WITH first_events AS (
  -- compute first occurrence per user for each step; restrict to relevant events early
  SELECT
    user_id,
    traffic_source,
    MIN(CASE WHEN event_name = 'visit' THEN occurred_at END) AS first_visit_at,
    MIN(CASE WHEN event_name = 'signup' THEN occurred_at END) AS first_signup_at,
    MIN(CASE WHEN event_name = 'purchase' THEN occurred_at END) AS first_purchase_at
  FROM events
  WHERE event_name IN ('visit','signup','purchase')
  GROUP BY user_id, traffic_source
),
users_with_signup_week AS (
  -- only users who signed up have a signup_week; include others if you want cohorts with null week
  SELECT
    user_id,
    traffic_source,
    first_visit_at,
    first_signup_at,
    first_purchase_at,
    date_trunc('week', first_signup_at)::date AS signup_week
  FROM first_events
  WHERE first_signup_at IS NOT NULL
)
SELECT
  signup_week,
  traffic_source,
  COUNT(*) FILTER (WHERE first_visit_at IS NOT NULL)            AS users_visited,
  COUNT(*) FILTER (WHERE first_signup_at IS NOT NULL)           AS users_signed_up,  -- always >0 for this CTE
  COUNT(*) FILTER (WHERE first_purchase_at IS NOT NULL)         AS users_purchased,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE first_purchase_at IS NOT NULL) /
    NULLIF(COUNT(*) FILTER (WHERE first_visit_at IS NOT NULL), 0),
    2
  ) AS conversion_visit_to_purchase_pct
FROM users_with_signup_week
GROUP BY signup_week, traffic_source
ORDER BY signup_week, traffic_source;