PLG Activation: Identifying "Aha!" Moments that Drive Conversion

WITH AhaMoments AS (
    -- Get the first time each user performed a key "aha!" moment
    SELECT
        customer_id,
        activity AS aha_moment
    FROM customer_stream
    WHERE activity IN ('created_first_project', 'invited_teammate', 'setup_integration')
    QUALIFY ROW_NUMBER() OVER(PARTITION BY customer_id, activity ORDER BY ts ASC) = 1
),
Conversions AS (
    -- Identify every user who converted
    SELECT DISTINCT customer_id
    FROM customer_stream
    WHERE activity = 'created_subscription'
),
BaselineRate AS (
    -- Calculate the baseline conversion rate for all users
    SELECT
        (COUNT(DISTINCT c.customer_id) * 1.0 / COUNT(DISTINCT cs.customer_id)) AS baseline_rate
    FROM customer_stream cs
    LEFT JOIN Conversions c ON cs.customer_id = c.customer_id
),
MomentConversion AS (
    SELECT
        am.aha_moment,
        (COUNT(DISTINCT c.customer_id) * 1.0 / COUNT(DISTINCT am.customer_id)) AS conversion_rate
    FROM AhaMoments am
    LEFT JOIN Conversions c ON am.customer_id = c.customer_id
    GROUP BY 1
)
SELECT
    mc.aha_moment,
    mc.conversion_rate,
    (mc.conversion_rate / br.baseline_rate) AS lift_vs_baseline
FROM MomentConversion mc, BaselineRate br
ORDER BY 3 DESC;