data.ml
dbt Saas cloud infrastructure

Funnel Explorer

Funnel Explorer model for saas cloud infrastructure.

inferred

Blocks

funnel_explorer.sql · 48 lines
inferred 
-- models/funnel_explorer.sql
{{ config(materialized='view') }}

SELECT sessions.session_id as session_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
FROM dcos.tracks as tracks
LEFT JOIN dcos.dcos_cli as dcos_cli
  ON coalesce(tracks.anonymous_id, tracks.event_id, tracks.user_id) = coalesce(dcos_cli.anonymous_id, dcos_cli.event_id, dcos_cli.user_id)
LEFT JOIN {{ ref('aliases_mapping') }} as aliases_mapping
  ON aliases_mapping.alias = coalesce(tracks.user_id, tracks.anonymous_id)
LEFT JOIN {{ ref('track_facts') }} as tracks_sessions_map
  ON tracks.event_id = tracks_sessions_map.event_id
LEFT JOIN {{ ref('sessions_trk') }} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: funnel_explorer
    columns:
      - name: session_id
        tests:
          - unique
          - not_null
      - name: event1
      - name: event2
      - name: event3
      - name: event1_before_event2
      - name: event2_before_event3

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.
funnel_explorer.sql · 29 lines
source-preserved validation: not checked 
SELECT sessions.session_id as session_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
FROM dcos.tracks as tracks
LEFT JOIN dcos.dcos_cli as dcos_cli
  ON coalesce(tracks.anonymous_id, tracks.event_id, tracks.user_id) = coalesce(dcos_cli.anonymous_id, dcos_cli.event_id, dcos_cli.user_id)
LEFT JOIN ${aliases_mapping.SQL_TABLE_NAME} as aliases_mapping
  ON aliases_mapping.alias = coalesce(tracks.user_id, tracks.anonymous_id)
LEFT JOIN ${track_facts.SQL_TABLE_NAME} as tracks_sessions_map
  ON tracks.event_id = tracks_sessions_map.event_id
LEFT JOIN ${sessions_trk.SQL_TABLE_NAME} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1
funnel_explorer.view.lkml · 89 lines
inferred 
view: funnel_explorer {
  derived_table: {
    sql: SELECT sessions.session_id as session_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
FROM dcos.tracks as tracks
LEFT JOIN dcos.dcos_cli as dcos_cli
  ON coalesce(tracks.anonymous_id, tracks.event_id, tracks.user_id) = coalesce(dcos_cli.anonymous_id, dcos_cli.event_id, dcos_cli.user_id)
LEFT JOIN ${aliases_mapping.SQL_TABLE_NAME} as aliases_mapping
  ON aliases_mapping.alias = coalesce(tracks.user_id, tracks.anonymous_id)
LEFT JOIN ${track_facts.SQL_TABLE_NAME} as tracks_sessions_map
  ON tracks.event_id = tracks_sessions_map.event_id
LEFT JOIN ${sessions_trk.SQL_TABLE_NAME} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1 ;;
  }

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

  dimension: event1 {
    type: time
    sql: ${TABLE}.event1_time ;;
  }

  dimension: event2 {
    type: time
    sql: ${TABLE}.event2_time ;;
  }

  dimension: event3 {
    type: time
    sql: ${TABLE}.event3_time ;;
  }

  dimension: event1_before_event2 {
    type: yesno
    sql: ${event1_time} < ${event2_time} ;;
  }

  dimension: event2_before_event3 {
    type: yesno
    sql: ${event2_time} < ${event3_time} ;;
  }

  measure: count_sessions {
    type: count
  }

  measure: count_sessions_event1 {
    type: count
    filters: [event1_time: "NOT NULL"]
  }

  measure: count_sessions_event12 {
    type: count
    filters: [event1_time: "NOT NULL"]
    filters: [event2_time: "NOT NULL"]
    filters: [event1_before_event2: "True"]
  }

  measure: count_sessions_event123 {
    type: count
    filters: [event1_time: "NOT NULL"]
    filters: [event2_time: "NOT NULL"]
    filters: [event3_time: "NOT NULL"]
    filters: [event1_before_event2: "True"]
    filters: [event2_before_event3: "True"]
  }
}
funnel_explorer.view.lookml · 71 lines
source-preserved 
- view: funnel_explorer
  derived_table:
    sql: SELECT sessions.session_id as session_id
  , MIN(
      CASE WHEN
        {% condition event1 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} SUBSTRING(cmd, POSITION('/placeholder_command ' IN cmd), 99999) {% endcondition %}
        THEN dcos_cli.received_at
        ELSE NULL END
      ) as event3_time
FROM dcos.tracks as tracks
LEFT JOIN dcos.dcos_cli as dcos_cli
  ON coalesce(tracks.anonymous_id, tracks.event_id, tracks.user_id) = coalesce(dcos_cli.anonymous_id, dcos_cli.event_id, dcos_cli.user_id)
LEFT JOIN ${aliases_mapping.SQL_TABLE_NAME} as aliases_mapping
  ON aliases_mapping.alias = coalesce(tracks.user_id, tracks.anonymous_id)
LEFT JOIN ${track_facts.SQL_TABLE_NAME} as tracks_sessions_map
  ON tracks.event_id = tracks_sessions_map.event_id
LEFT JOIN ${sessions_trk.SQL_TABLE_NAME} as sessions
  ON tracks_sessions_map.session_id = sessions.session_id
GROUP BY 1
  fields:
  - dimension: session_id
    type: string
    primary_key: true
    sql: ${TABLE}.session_id
  - dimension: event1
    type: time
    sql: ${TABLE}.event1_time
  - dimension: event2
    type: time
    sql: ${TABLE}.event2_time
  - dimension: event3
    type: time
    sql: ${TABLE}.event3_time
  - dimension: event1_before_event2
    type: yesno
    sql: ${event1_time} < ${event2_time}
  - dimension: event2_before_event3
    type: yesno
    sql: ${event2_time} < ${event3_time}
  - measure: count_sessions
    type: count
  - measure: count_sessions_event1
    type: count
    filters:
    - event1_time: NOT NULL
  - measure: count_sessions_event12
    type: count
    filters:
    - event1_time: NOT NULL
    - event2_time: NOT NULL
    - event1_before_event2: True
  - measure: count_sessions_event123
    type: count
    filters:
    - event1_time: NOT NULL
    - event2_time: NOT NULL
    - event3_time: NOT NULL
    - event1_before_event2: True
    - event2_before_event3: True

Contents

  • funnel_explorer.sql
  • funnel_explorer.sql
  • funnel_explorer.view.lkml
  • funnel_explorer.view.lookml