data.ml
dbt Fitness gaming

Subscription Funnel

Subscription Funnel model for fitness gaming.

inferred

Blocks

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

SELECT distinct_id
  , MIN(
      CASE WHEN
        {% condition event1 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event3_time
  , MIN(
      CASE WHEN
        {% condition event4 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event4_time
FROM {{ ref('subscription_events_table') }} as tracks
WHERE {% condition event_date_date %} to_timestamp(tracks.event_time) {% endcondition %}
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: subscription_funnel
    columns:
      - name: distinct_id
        tests:
          - unique
          - not_null
      - name: event1
      - name: event2
      - name: event3
      - name: event4
      - 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.
subscription_funnel.sql · 28 lines
source-preserved validation: not checked 
SELECT distinct_id
  , MIN(
      CASE WHEN
        {% condition event1 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event3_time
  , MIN(
      CASE WHEN
        {% condition event4 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event4_time
FROM ${subscription_events_table.SQL_TABLE_NAME} as tracks
WHERE {% condition event_date_date %} to_timestamp(tracks.event_time) {% endcondition %}
GROUP BY 1
subscription_funnel.view.lkml · 112 lines
inferred 
view: subscription_funnel {
  derived_table: {
    sql: SELECT distinct_id
  , MIN(
      CASE WHEN
        {% condition event1 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event3_time
  , MIN(
      CASE WHEN
        {% condition event4 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event4_time
FROM ${subscription_events_table.SQL_TABLE_NAME} as tracks
WHERE {% condition event_date_date %} to_timestamp(tracks.event_time) {% endcondition %}
GROUP BY 1 ;;
  }

  dimension: distinct_id {
    primary_key: yes
    sql: ${TABLE}.distinct_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: event4 {
    type: time
    sql: ${TABLE}.event4_time ;;
  }

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

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

  dimension: event3_before_event4 {
    type: yesno
    sql: ${event3_time} < ${event4_time} ;;
  }

  measure: count_sessions {
    type: count_distinct
    sql: ${distinct_id} ;;
  }

  measure: count_sessions_event1 {
    type: count_distinct
    filters: [event1_time: "NOT NULL"]
    sql: ${distinct_id} ;;
  }

  measure: count_sessions_event12 {
    type: count_distinct
    filters: [event1_time: "NOT NULL"]
    filters: [event2_time: "NOT NULL"]
    filters: [event1_before_event2: "True"]
    sql: ${distinct_id} ;;
  }

  measure: count_sessions_event123 {
    type: count_distinct
    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"]
    sql: ${distinct_id} ;;
  }

  measure: count_sessions_event1234 {
    type: count_distinct
    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"]
    filters: [event3_before_event4: "True"]
    sql: ${distinct_id} ;;
  }
}
subscription_funnel.view.lookml · 89 lines
source-preserved 
- view: subscription_funnel
  derived_table:
    sql: SELECT distinct_id
  , MIN(
      CASE WHEN
        {% condition event1 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event1_time
  , MIN(
      CASE WHEN
        {% condition event2 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event2_time
  , MIN(
      CASE WHEN
        {% condition event3 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event3_time
  , MIN(
      CASE WHEN
        {% condition event4 %} tracks.event_type {% endcondition %}
        THEN tracks.event_time
        ELSE NULL END
      ) as event4_time
FROM ${subscription_events_table.SQL_TABLE_NAME} as tracks
WHERE {% condition event_date_date %} to_timestamp(tracks.event_time) {% endcondition %}
GROUP BY 1
  fields:
  - dimension: distinct_id
    primary_key: true
    sql: ${TABLE}.distinct_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: event4
    type: time
    sql: ${TABLE}.event4_time
  - dimension: event1_before_event2
    type: yesno
    sql: ${event1_time} < ${event2_time}
  - dimension: event2_before_event3
    type: yesno
    sql: ${event2_time} < ${event3_time}
  - dimension: event3_before_event4
    type: yesno
    sql: ${event3_time} < ${event4_time}
  - measure: count_sessions
    type: count_distinct
    sql: ${distinct_id}
  - measure: count_sessions_event1
    type: count_distinct
    filters:
    - event1_time: NOT NULL
    sql: ${distinct_id}
  - measure: count_sessions_event12
    type: count_distinct
    filters:
    - event1_time: NOT NULL
    - event2_time: NOT NULL
    - event1_before_event2: True
    sql: ${distinct_id}
  - measure: count_sessions_event123
    type: count_distinct
    filters:
    - event1_time: NOT NULL
    - event2_time: NOT NULL
    - event3_time: NOT NULL
    - event1_before_event2: True
    - event2_before_event3: True
    sql: ${distinct_id}
  - measure: count_sessions_event1234
    type: count_distinct
    filters:
    - event1_time: NOT NULL
    - event2_time: NOT NULL
    - event3_time: NOT NULL
    - event1_before_event2: True
    - event2_before_event3: True
    - event3_before_event4: True
    sql: ${distinct_id}

Contents

  • subscription_funnel.sql
  • subscription_funnel.sql
  • subscription_funnel.view.lkml
  • subscription_funnel.view.lookml