Marketing Touchpoint Impact on Demo Requests

WITH Touchpoints AS (
    -- Get the first time a user had a specific touchpoint
    SELECT
        customer_id,
        ts,
        activity AS touchpoint_type
    FROM customer_stream
    WHERE activity IN ('attended_webinar', 'downloaded_asset')
    QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id, activity ORDER BY ts) = 1
),
DemoRequests AS (
    -- Get all demo requests
    SELECT
        customer_id,
        ts AS demo_ts
    FROM customer_stream
    WHERE activity = 'requested_demo'
),
TouchpointConversions AS (
    -- For each touchpoint, find if a demo was requested within 30 days.
    SELECT
        t.customer_id,
        t.touchpoint_type,
        MIN(dr.demo_ts) as first_demo_ts
    FROM Touchpoints t
    LEFT JOIN DemoRequests dr ON t.customer_id = dr.customer_id AND dr.demo_ts >= t.ts AND dr.demo_ts <= DATEADD(day, 30, t.ts)
    GROUP BY 1, 2
),
TouchpointRates AS (
    SELECT
        tc.touchpoint_type,
        (COUNT(tc.first_demo_ts) * 1.0 / COUNT(tc.customer_id)) AS conversion_rate
    FROM TouchpointConversions tc
    GROUP BY 1
),
BaselineRate AS (
    -- Calculate the baseline conversion rate: what percentage of all prospects eventually request a demo?
    SELECT
        (COUNT(DISTINCT CASE WHEN activity = 'requested_demo' THEN customer_id END) * 1.0 / COUNT(DISTINCT customer_id)) AS baseline_conversion_rate
    FROM customer_stream
)
SELECT
    tr.touchpoint_type,
    tr.conversion_rate,
    (tr.conversion_rate / br.baseline_conversion_rate) AS lift_vs_baseline
FROM TouchpointRates tr, BaselineRate br
ORDER BY 3 DESC;