Danial Al-Mansoor

Evaluated on 45,000 enterprise user accounts using logistic regression & XGBoost.

-- Incremental session windowing with touchpoint decay
WITH raw_events AS (
    SELECT
        user_id,
        event_timestamp,
        channel,
        revenue,
        LAG(event_timestamp) OVER (
            PARTITION BY user_id ORDER BY event_timestamp
        ) AS prev_timestamp
    FROM {{ ref('stg_clickstream_events') }}
),
sessions AS (
    SELECT
        *,
        CASE 
            WHEN DATEDIFF('minute', prev_timestamp, event_timestamp) > 30 
                 OR prev_timestamp IS NULL THEN 1
            ELSE 0 
        END AS is_new_session
    FROM raw_events
)
SELECT
    user_id,
    channel,
    SUM(revenue) AS attributed_rev,
    EXP(-0.05 * DATEDIFF('day', event_timestamp, CURRENT_DATE())) AS time_decay_factor
FROM sessions
GROUP BY 1, 2, event_timestamp;