Buying Committee Engagement and Sales Velocity

WITH DealOutcomes AS (
    -- Get deal outcomes and cycle lengths for all customers who requested a demo
    SELECT
        customer,
        MIN(CASE WHEN activity = 'requested_demo' THEN ts END) as demo_ts,
        MIN(CASE WHEN activity = 'created_subscription' THEN ts END) as win_ts
    FROM customer_stream
    WHERE customer IS NOT NULL
    GROUP BY 1
    HAVING demo_ts IS NOT NULL
),
AccountMetrics AS (
    -- Calculate metrics for each account: win status, sales cycle, and number of pre-demo stakeholders
    SELECT
        do.customer,
        do.win_ts IS NOT NULL AS is_win,
        DATEDIFF('day', do.demo_ts, do.win_ts) AS sales_cycle_days,
        COALESCE(COUNT(DISTINCT CASE WHEN cs.ts < do.demo_ts THEN cs.anonymous_customer_id END), 0) AS num_stakeholders
    FROM DealOutcomes do
    LEFT JOIN customer_stream cs ON do.customer = cs.customer
        AND cs.activity = 'visited_page' 
        AND cs.features:CHANNEL IN ('paid_social', 'paid_search', 'organic', 'email', 'attended_webinar')
    GROUP BY 1, 2, 3
),
BinnedAccounts AS (
    -- Bucket accounts by the number of stakeholders
    SELECT
        *,
        CASE
            WHEN num_stakeholders = 1 THEN '1'
            WHEN num_stakeholders BETWEEN 2 AND 3 THEN '2-3'
            WHEN num_stakeholders BETWEEN 4 AND 5 THEN '4-5'
            WHEN num_stakeholders >= 6 THEN '6+'
            ELSE '0'
        END AS stakeholder_bucket
    FROM AccountMetrics
)
SELECT
    stakeholder_bucket,
    AVG(sales_cycle_days) AS avg_sales_cycle_days,
    (COUNT(CASE WHEN is_win THEN 1 END) * 1.0 / COUNT(*)) AS win_rate
FROM BinnedAccounts
GROUP BY 1
ORDER BY 1;