Funnel Explorer

-- models/funnel_explorer.sql
{{ config(materialized='view') }}

SELECT sessions.unique_session_id as session_id
  , sessions.session_start AS session_start
  , MIN(
      CASE WHEN
        {% condition action_1 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_1_time
  , MIN(
      CASE WHEN
        {% condition action_2 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_2_time
  , MIN(
      CASE WHEN
        {% condition action_3 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_3_time
  , MIN(
      CASE WHEN
        {% condition action_4 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_4_time
FROM public.ts_user_base AS events
LEFT JOIN {{ ref('sessions') }} sessions
  ON events.uid = sessions.user_id
  AND timestamp 'epoch' + events.ts * interval '1 second' >= sessions.session_start
  AND timestamp 'epoch' + events.ts * interval '1 second' < sessions.next_session_start
  AND {% condition event_time %} sessions.session_start {% endcondition %}
WHERE {% condition event_time %} timestamp 'epoch' + events.ts * interval '1 second' {% endcondition %}
GROUP BY 1,2

# models/schema.yml
version: 2

models:
  - name: funnel_explorer
    columns:
      - name: session_id
        tests:
          - unique
          - not_null
      - name: user_id
      - name: action_1
      - name: action_2
      - name: action_3
      - name: action_4
      - name: event1_before_event2
      - name: event2_before_event3
      - name: event3_before_event4

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT sessions.unique_session_id as session_id
  , sessions.session_start AS session_start
  , MIN(
      CASE WHEN
        {% condition action_1 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_1_time
  , MIN(
      CASE WHEN
        {% condition action_2 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_2_time
  , MIN(
      CASE WHEN
        {% condition action_3 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_3_time
  , MIN(
      CASE WHEN
        {% condition action_4 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_4_time
FROM public.ts_user_base AS events
LEFT JOIN ${sessions.SQL_TABLE_NAME} sessions
  ON events.uid = sessions.user_id
  AND timestamp 'epoch' + events.ts * interval '1 second' >= sessions.session_start
  AND timestamp 'epoch' + events.ts * interval '1 second' < sessions.next_session_start
  AND {% condition event_time %} sessions.session_start {% endcondition %}
WHERE {% condition event_time %} timestamp 'epoch' + events.ts * interval '1 second' {% endcondition %}
GROUP BY 1,2

view: funnel_explorer {
  derived_table: {
    sql: SELECT sessions.unique_session_id as session_id
  , sessions.session_start AS session_start
  , MIN(
      CASE WHEN
        {% condition action_1 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_1_time
  , MIN(
      CASE WHEN
        {% condition action_2 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_2_time
  , MIN(
      CASE WHEN
        {% condition action_3 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_3_time
  , MIN(
      CASE WHEN
        {% condition action_4 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_4_time
FROM public.ts_user_base AS events
LEFT JOIN ${sessions.SQL_TABLE_NAME} sessions
  ON events.uid = sessions.user_id
  AND timestamp 'epoch' + events.ts * interval '1 second' >= sessions.session_start
  AND timestamp 'epoch' + events.ts * interval '1 second' < sessions.next_session_start
  AND {% condition event_time %} sessions.session_start {% endcondition %}
WHERE {% condition event_time %} timestamp 'epoch' + events.ts * interval '1 second' {% endcondition %}
GROUP BY 1,2 ;;
  }

  dimension: session_id {
    type: string
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.session_id ;;
  }

  dimension: user_id {
    type: int
    hidden: yes
    sql: ${TABLE}.user_id ;;
  }

  dimension: action_1 {
    type: time
    hidden: yes
    sql: ${TABLE}.action_1_time ;;
  }

  dimension: action_2 {
    type: time
    hidden: yes
    sql: ${TABLE}.action_2_time ;;
  }

  dimension: action_3 {
    type: time
    hidden: yes
    sql: ${TABLE}.action_3_time ;;
  }

  dimension: action_4 {
    type: time
    hidden: yes
    sql: ${TABLE}.action_4_time ;;
  }

  dimension: event1_before_event2 {
    type: yesno
    hidden: yes
    sql: ${TABLE}.action_1_time < ${TABLE}.action_2_time ;;
  }

  dimension: event2_before_event3 {
    type: yesno
    hidden: yes
    sql: ${TABLE}.action_2_time < ${TABLE}.action_3_time ;;
  }

  dimension: event3_before_event4 {
    type: yesno
    hidden: yes
    sql: ${TABLE}.action_3_time < ${TABLE}.action_4_time ;;
  }

  dimension_group: session_start {
    type: time
    timeframes: [time, date, raw]
    sql: ${TABLE}.session_start ;;
  }

  measure: count_sessions {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${session_id} ;;
  }

  measure: count_sessions_event1 {
    label: "Action 1"
    type: count_distinct
    filters: [action_1_time: "NOT NULL"]
    drill_fields: [detail*]
    sql: ${session_id} ;;
  }

  measure: count_sessions_event12 {
    label: "Action 2"
    description: "Only includes sessions which also completed event 1"
    type: count_distinct
    filters: [action_1_time: "NOT NULL"]
    filters: [action_2_time: "NOT NULL"]
    filters: [event1_before_event2: "True"]
    drill_fields: [detail*]
    sql: ${session_id} ;;
  }

  measure: count_sessions_event123 {
    label: "Action 3"
    description: "Only includes sessions which also completed actions 1 and 2"
    type: count_distinct
    filters: [action_1_time: "NOT NULL"]
    filters: [action_2_time: "NOT NULL"]
    filters: [action_3_time: "NOT NULL"]
    filters: [event1_before_event2: "True"]
    filters: [event2_before_event3: "True"]
    drill_fields: [detail*]
    sql: ${session_id} ;;
  }

  measure: count_sessions_event1234 {
    label: "Action 4"
    description: "Only includes sessions which also completed actions 1, 2 and 3"
    type: count_distinct
    filters: [action_1_time: "NOT NULL"]
    filters: [action_2_time: "NOT NULL"]
    filters: [action_3_time: "NOT NULL"]
    filters: [action_4_time: "NOT NULL"]
    filters: [event1_before_event2: "True"]
    filters: [event2_before_event3: "True"]
    filters: [event3_before_event4: "True"]
    drill_fields: [detail*]
    sql: ${session_id} ;;
  }
}

- view: funnel_explorer
  derived_table:
    sql: SELECT sessions.unique_session_id as session_id
  , sessions.session_start AS session_start
  , MIN(
      CASE WHEN
        {% condition action_1 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_1_time
  , MIN(
      CASE WHEN
        {% condition action_2 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_2_time
  , MIN(
      CASE WHEN
        {% condition action_3 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_3_time
  , MIN(
      CASE WHEN
        {% condition action_4 %} events.action {% endcondition %}
        THEN events.ts
        ELSE NULL END
      ) as action_4_time
FROM public.ts_user_base AS events
LEFT JOIN ${sessions.SQL_TABLE_NAME} sessions
  ON events.uid = sessions.user_id
  AND timestamp 'epoch' + events.ts * interval '1 second' >= sessions.session_start
  AND timestamp 'epoch' + events.ts * interval '1 second' < sessions.next_session_start
  AND {% condition event_time %} sessions.session_start {% endcondition %}
WHERE {% condition event_time %} timestamp 'epoch' + events.ts * interval '1 second' {% endcondition %}
GROUP BY 1,2
  fields:
  - dimension: session_id
    type: string
    primary_key: true
    hidden: true
    sql: ${TABLE}.session_id
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: action_1
    type: time
    hidden: true
    sql: ${TABLE}.action_1_time
  - dimension: action_2
    type: time
    hidden: true
    sql: ${TABLE}.action_2_time
  - dimension: action_3
    type: time
    hidden: true
    sql: ${TABLE}.action_3_time
  - dimension: action_4
    type: time
    hidden: true
    sql: ${TABLE}.action_4_time
  - dimension: event1_before_event2
    type: yesno
    hidden: true
    sql: ${TABLE}.action_1_time < ${TABLE}.action_2_time
  - dimension: event2_before_event3
    type: yesno
    hidden: true
    sql: ${TABLE}.action_2_time < ${TABLE}.action_3_time
  - dimension: event3_before_event4
    type: yesno
    hidden: true
    sql: ${TABLE}.action_3_time < ${TABLE}.action_4_time
  - dimension_group: session_start
    type: time
    timeframes:
    - time
    - date
    - raw
    sql: ${TABLE}.session_start
  - measure: count_sessions
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${session_id}
  - measure: count_sessions_event1
    type: count_distinct
    label: Action 1
    drill_fields:
    - detail*
    filters:
    - action_1_time: NOT NULL
    sql: ${session_id}
  - measure: count_sessions_event12
    type: count_distinct
    label: Action 2
    description: Only includes sessions which also completed event 1
    drill_fields:
    - detail*
    filters:
    - action_1_time: NOT NULL
    - action_2_time: NOT NULL
    - event1_before_event2: True
    sql: ${session_id}
  - measure: count_sessions_event123
    type: count_distinct
    label: Action 3
    description: Only includes sessions which also completed actions 1 and 2
    drill_fields:
    - detail*
    filters:
    - action_1_time: NOT NULL
    - action_2_time: NOT NULL
    - action_3_time: NOT NULL
    - event1_before_event2: True
    - event2_before_event3: True
    sql: ${session_id}
  - measure: count_sessions_event1234
    type: count_distinct
    label: Action 4
    description: Only includes sessions which also completed actions 1, 2 and 3
    drill_fields:
    - detail*
    filters:
    - action_1_time: NOT NULL
    - action_2_time: NOT NULL
    - action_3_time: NOT NULL
    - action_4_time: NOT NULL
    - event1_before_event2: True
    - event2_before_event3: True
    - event3_before_event4: True
    sql: ${session_id}