Fct Conversion

1-- Warehouse-agnostic funnel with step ordering AND a conversion window, in plain SQL — the dbt
2-- analogue of windowFunnel. Each step is the EARLIEST occurrence AFTER the previous step's timestamp
3-- (and within N days of step 1), so a valid ordered chain survives even when an out-of-order event
4-- happens to be earlier — which an independent per-step min() would wrongly pick.
5-- 14-day window shown; adjust the interval literal to your warehouse's syntax.
6{{ config(materialized='table') }}
7
8with events as (
9    select person_id, event_name, event_at from {{ ref('stg_funnel_events') }}
10),
11step1 as (
12    select person_id, min(event_at) as t_signup
13    from events
14    where event_name = 'signed_up'
15    group by person_id
16),
17step2 as (
18    -- first activation at/after signup and within the window
19    select s.person_id, s.t_signup, min(e.event_at) as t_activated
20    from step1 s
21    join events e
22        on e.person_id = s.person_id
23       and e.event_name = 'activated'
24       and e.event_at between s.t_signup and s.t_signup + interval '14 day'
25    group by s.person_id, s.t_signup
26),
27step3 as (
28    -- first purchase at/after activation and still within the window from signup
29    select a.person_id, min(e.event_at) as t_purchased
30    from step2 a
31    join events e
32        on e.person_id = a.person_id
33       and e.event_name = 'purchased'
34       and e.event_at between a.t_activated and a.t_signup + interval '14 day'
35    group by a.person_id
36)
37select
38    count(s1.person_id)                                                     as entered_step_1,
39    count(s2.person_id)                                                     as reached_activated,
40    count(s3.person_id)                                                     as reached_purchased,
41    round(count(s3.person_id)::numeric / nullif(count(s1.person_id), 0), 4) as overall_conversion
42from step1 s1
43left join step2 s2 using (person_id)
44left join step3 s3 using (person_id)