SQL Saas crm life sciences

User Events Facts

User Events Facts model for saas crm life sciences.

source-preserved

Blocks

dbt SQL LKML LookML

user_events_facts.sql · 69 lines

inferred

{{ config(materialized='view') }}

SELECT
 mixpanel_id
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:localSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS local_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:ConnectSalesforce'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS connect_salesforce
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:emailSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS email_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:usernameWait'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS username_wait
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:enterPassword'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS enter_password
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:sourcesSyncing'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS source_syncing
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:day_of_weeknloadDB'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS day_of_weeknload_db
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:welcome'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS welcome
 , MIN(
     CASE
       WHEN e.event = 'getStarted:tapped'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS get_started_tapped
FROM events AS e
GROUP BY 1

Assumptions

materialized='view' is the default; no persist hint found.

user_events_facts.sql · 67 lines

source-preserved validation: parses

SELECT
 mixpanel_id
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:localSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS local_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:ConnectSalesforce'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS connect_salesforce
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:emailSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS email_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:usernameWait'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS username_wait
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:enterPassword'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS enter_password
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:sourcesSyncing'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS source_syncing
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:day_of_weeknloadDB'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS day_of_weeknload_db
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:welcome'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS welcome
 , MIN(
     CASE
       WHEN e.event = 'getStarted:tapped'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS get_started_tapped
FROM events AS e
GROUP BY 1

user_events_facts.view.lkml · 183 lines

inferred

view: user_events_facts {
  derived_table: {
    sql: SELECT
 mixpanel_id
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:localSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS local_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:ConnectSalesforce'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS connect_salesforce
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:emailSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS email_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:usernameWait'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS username_wait
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:enterPassword'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS enter_password
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:sourcesSyncing'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS source_syncing
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:day_of_weeknloadDB'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS day_of_weeknload_db
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:welcome'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS welcome
 , MIN(
     CASE
       WHEN e.event = 'getStarted:tapped'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS get_started_tapped
FROM events AS e
GROUP BY 1 ;;
  }

  dimension_group: local_setup {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.local_setup ;;
  }

  dimension_group: connect_salesforce {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.connect_salesforce ;;
  }

  dimension_group: email_setup {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.email_setup ;;
  }

  dimension_group: username_wait {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.username_wait ;;
  }

  dimension_group: enter_password {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.enter_password ;;
  }

  dimension_group: source_syncing {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.source_syncing ;;
  }

  dimension_group: day_of_weeknload_db {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.day_of_weeknload_db ;;
  }

  dimension_group: welcome {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.welcome ;;
  }

  dimension_group: get_started_tapped {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${TABLE}.get_started_tapped ;;
  }

  measure: count {
    type: count
  }

  measure: local_setup_count {
    label: "Setup Funnel: Step 1 Local Setup"
    type: count
    filters: [local_setup: "True"]
  }

  measure: connect_salesforce_count {
    label: "Setup Funnel: Step 2 Salesforce Connected"
    type: count
    filters: [connect_salesforce: "True"]
  }

  measure: email_setup_count {
    label: "Setup Funnel: Step 3 Local Setup"
    type: count
    filters: [email_setup: "True"]
  }

  measure: username_wait_count {
    label: "Setup Funnel: Step 4 Username Wait"
    type: count
    filters: [username_wait: "True"]
  }

  measure: enter_password_count {
    label: "Setup Funnel: Step 5 Entered Password"
    type: count
    filters: [enter_password: "True"]
  }

  measure: source_syncing_count {
    label: "Setup Funnel: Step 6 Source Synced"
    type: count
    filters: [source_syncing: "True"]
  }

  measure: day_of_weeknload_db_count {
    label: "Setup Funnel: Step 7 day_of_weeknloaded DB"
    type: count
    filters: [day_of_weeknload_db: "True"]
  }

  measure: welcome_count {
    label: "Welcome Funnel: Step 1 Welcome"
    type: count
    filters: [welcome: "True"]
  }

  measure: get_started_count {
    label: "Welcome Funnel: Step 2 Get Started Tapped"
    type: count
    filters: [get_started_tapped: "True"]
  }
}

user_events_facts.view.lookml · 198 lines

source-preserved

- view: user_events_facts
  derived_table:
    sql: SELECT
 mixpanel_id
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:localSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS local_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:ConnectSalesforce'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS connect_salesforce
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:emailSetup'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS email_setup
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:usernameWait'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS username_wait
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:enterPassword'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS enter_password
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:sourcesSyncing'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS source_syncing
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:onboarding:day_of_weeknloadDB'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS day_of_weeknload_db
 , MIN(
     CASE
       WHEN e.event = 'viewScreen:welcome'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS welcome
 , MIN(
     CASE
       WHEN e.event = 'getStarted:tapped'
       THEN e.occurred_at
       ELSE NULL
     END
   ) AS get_started_tapped
FROM events AS e
GROUP BY 1
  fields:
  - dimension_group: local_setup
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.local_setup
  - dimension_group: connect_salesforce
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.connect_salesforce
  - dimension_group: email_setup
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.email_setup
  - dimension_group: username_wait
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.username_wait
  - dimension_group: enter_password
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.enter_password
  - dimension_group: source_syncing
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.source_syncing
  - dimension_group: day_of_weeknload_db
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.day_of_weeknload_db
  - dimension_group: welcome
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.welcome
  - dimension_group: get_started_tapped
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${TABLE}.get_started_tapped
  - measure: count
    type: count
  - measure: local_setup_count
    type: count
    label: 'Setup Funnel: Step 1 Local Setup'
    filters:
    - local_setup: True
  - measure: connect_salesforce_count
    type: count
    label: 'Setup Funnel: Step 2 Salesforce Connected'
    filters:
    - connect_salesforce: True
  - measure: email_setup_count
    type: count
    label: 'Setup Funnel: Step 3 Local Setup'
    filters:
    - email_setup: True
  - measure: username_wait_count
    type: count
    label: 'Setup Funnel: Step 4 Username Wait'
    filters:
    - username_wait: True
  - measure: enter_password_count
    type: count
    label: 'Setup Funnel: Step 5 Entered Password'
    filters:
    - enter_password: True
  - measure: source_syncing_count
    type: count
    label: 'Setup Funnel: Step 6 Source Synced'
    filters:
    - source_syncing: True
  - measure: day_of_weeknload_db_count
    type: count
    label: 'Setup Funnel: Step 7 day_of_weeknloaded DB'
    filters:
    - day_of_weeknload_db: True
  - measure: welcome_count
    type: count
    label: 'Welcome Funnel: Step 1 Welcome'
    filters:
    - welcome: True
  - measure: get_started_count
    type: count
    label: 'Welcome Funnel: Step 2 Get Started Tapped'
    filters:
    - get_started_tapped: True

user_events_facts.sql
user_events_facts.sql
user_events_facts.view.lkml
user_events_facts.view.lookml

User Events Facts

Blocks

Assumptions

Contents