Funnel conversion daily

1-- core.mixpanel_source.funnel_conversion_daily
2-- Your saved Mixpanel funnels, step by step per entry day: how many users reached each
3-- step, the conversion from the previous step and from the start, and the time it took.
4--
5-- Grain: one row per funnel x day (users' entry day) x step.
6-- Depends on: staging.mixpanel_source.funnel_step
7--
8-- These are Mixpanel's own funnel numbers, computed with each funnel's saved conversion
9-- window and identity rules, so they match the Funnels report for the same days.
10-- `drop_off` is users who reached the previous step and not this one.
11--
12-- To report a funnel over a week or a month, sum `users` per step and divide step by
13-- step one; averaging the daily conversion rates weights a quiet day like a busy one.
14-- Recent days keep changing until the conversion window has passed.
15
16SELECT
17    funnel_id,
18    funnel_name,
19    date,
20    step_number,
21    step_label,
22    step_event,
23    users,
24    FIRST_VALUE(users) OVER w                               AS users_entered,
25    LAG(users) OVER w - users                               AS drop_off,
26    step_conversion,
27    overall_conversion,
28    avg_seconds_from_previous_step,
29    avg_seconds_from_start
30FROM {{staging.mixpanel_source.funnel_step}}
31WINDOW w AS (PARTITION BY funnel_id, date ORDER BY step_number)