data.ml
dbt Saas hospitality

Conversations Facts

Conversations Facts model for saas hospitality.

inferred

Blocks

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

SELECT
  conversation_id
  , COUNT(*) AS number_messages
  , SUM(case m.sender_type when 'Guest' then 1 else 0 end) AS number_guest_messages
  , SUM(case m.sender_type when 'User' then 1 else 0 end) AS number_user_messages
  , MIN(m.created_at) AS first_message_sent
  , MAX(m.created_at) AS last_message_sent
  , MIN(CASE WHEN y.message_sequence_number > 1 THEN y.elapsed_response_time ELSE NULL END) AS first_message_response_time_seconds
  , AVG(elapsed_response_time) AS average_of_messages_response_time_seconds
  , SUM(elapsed_response_time) AS total_response_time_seconds
FROM messages AS m
LEFT JOIN
  (SELECT
    *
    , EXTRACT(EPOCH FROM (x.created_at - x.previous_message_sent)) as elapsed_response_time
  FROM
    (SELECT
      m.id
      , m.created_at
      , ROW_NUMBER() OVER(PARTITION BY m.conversation_id ORDER BY m.created_at) AS message_sequence_number
      , CASE WHEN(m.sender_type != LAG(m.sender_type) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
            THEN (LAG(m.created_at) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
                ELSE NULL
                  END as previous_message_sent
    FROM messages m
    WHERE m.type = 'Messages::PlaceholderAdHoc'
    AND m.sender_type != 'PlaceholderSenderType'
    ) x) y ON y.id = m.id
WHERE m.type = 'Messages::PlaceholderAdHoc'
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: conversations_facts
    columns:
      - name: conversation_id
        tests:
          - unique
          - not_null
      - name: number_messages
      - name: number_guest_messages
      - name: number_user_messages
      - name: number_messages_tier
      - name: total_response_time_seconds
      - name: total_response_time_seconds_tier
      - name: average_of_messages_response_time_seconds
      - name: average_of_messages_response_time_minutes
      - name: average_message_response_time_minutes_tier
      - name: first_message_sent
      - name: last_message_sent
      - name: first_message_response_time_seconds
      - name: first_message_response_time_minutes
      - name: first_message_response_time_seconds_tier
      - name: first_message_response_time_minutes_tier

Assumptions

  • 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.
conversations_facts.sql · 30 lines
source-preserved validation: parses 
SELECT
  conversation_id
  , COUNT(*) AS number_messages
  , SUM(case m.sender_type when 'Guest' then 1 else 0 end) AS number_guest_messages
  , SUM(case m.sender_type when 'User' then 1 else 0 end) AS number_user_messages
  , MIN(m.created_at) AS first_message_sent
  , MAX(m.created_at) AS last_message_sent
  , MIN(CASE WHEN y.message_sequence_number > 1 THEN y.elapsed_response_time ELSE NULL END) AS first_message_response_time_seconds
  , AVG(elapsed_response_time) AS average_of_messages_response_time_seconds
  , SUM(elapsed_response_time) AS total_response_time_seconds
FROM messages AS m
LEFT JOIN
  (SELECT
    *
    , EXTRACT(EPOCH FROM (x.created_at - x.previous_message_sent)) as elapsed_response_time
  FROM
    (SELECT
      m.id
      , m.created_at
      , ROW_NUMBER() OVER(PARTITION BY m.conversation_id ORDER BY m.created_at) AS message_sequence_number
      , CASE WHEN(m.sender_type != LAG(m.sender_type) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
            THEN (LAG(m.created_at) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
                ELSE NULL
                  END as previous_message_sent
    FROM messages m
    WHERE m.type = 'Messages::PlaceholderAdHoc'
    AND m.sender_type != 'PlaceholderSenderType'
    ) x) y ON y.id = m.id
WHERE m.type = 'Messages::PlaceholderAdHoc'
GROUP BY 1
conversations_facts.view.lkml · 144 lines
inferred 
view: conversations_facts {
  derived_table: {
    sql: SELECT
  conversation_id
  , COUNT(*) AS number_messages
  , SUM(case m.sender_type when 'Guest' then 1 else 0 end) AS number_guest_messages
  , SUM(case m.sender_type when 'User' then 1 else 0 end) AS number_user_messages
  , MIN(m.created_at) AS first_message_sent
  , MAX(m.created_at) AS last_message_sent
  , MIN(CASE WHEN y.message_sequence_number > 1 THEN y.elapsed_response_time ELSE NULL END) AS first_message_response_time_seconds
  , AVG(elapsed_response_time) AS average_of_messages_response_time_seconds
  , SUM(elapsed_response_time) AS total_response_time_seconds
FROM messages AS m
LEFT JOIN
  (SELECT
    *
    , EXTRACT(EPOCH FROM (x.created_at - x.previous_message_sent)) as elapsed_response_time
  FROM
    (SELECT
      m.id
      , m.created_at
      , ROW_NUMBER() OVER(PARTITION BY m.conversation_id ORDER BY m.created_at) AS message_sequence_number
      , CASE WHEN(m.sender_type != LAG(m.sender_type) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
            THEN (LAG(m.created_at) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
                ELSE NULL
                  END as previous_message_sent
    FROM messages m
    WHERE m.type = 'Messages::PlaceholderAdHoc'
    AND m.sender_type != 'PlaceholderSenderType'
    ) x) y ON y.id = m.id
WHERE m.type = 'Messages::PlaceholderAdHoc'
GROUP BY 1 ;;
  }

  dimension: conversation_id {
    primary_key: yes
    sql: ${TABLE}.conversation_id ;;
  }

  dimension: number_messages {
    type: number
    sql: ${TABLE}.number_messages ;;
  }

  dimension: number_guest_messages {
    type: number
    sql: ${TABLE}.number_guest_messages ;;
  }

  dimension: number_user_messages {
    type: number
    sql: ${TABLE}.number_user_messages ;;
  }

  dimension: number_messages_tier {
    type: tier
    sql: ${number_messages} ;;
  }

  dimension: total_response_time_seconds {
    type: number
    sql: ${TABLE}.total_response_time_seconds ;;
  }

  dimension: total_response_time_seconds_tier {
    type: tier
    sql: ${total_response_time_seconds} ;;
  }

  dimension: average_of_messages_response_time_seconds {
    type: number
    sql: ${TABLE}.average_of_messages_response_time_seconds ;;
  }

  dimension: average_of_messages_response_time_minutes {
    type: number
    sql: ${average_of_messages_response_time_seconds}/60 ;;
  }

  dimension: average_message_response_time_minutes_tier {
    type: tier
    sql: ${average_of_messages_response_time_minutes} ;;
  }

  dimension: first_message_sent {
    type: time
    sql: ${TABLE}.first_message_sent ;;
  }

  dimension: last_message_sent {
    type: time
    sql: ${TABLE}.last_message_sent ;;
  }

  dimension: first_message_response_time_seconds {
    type: number
    sql: ${TABLE}.first_message_response_time_seconds ;;
  }

  dimension: first_message_response_time_minutes {
    type: number
    sql: ${first_message_response_time_seconds}/60 ;;
  }

  dimension: first_message_response_time_seconds_tier {
    type: tier
    sql: ${first_message_response_time_seconds} ;;
  }

  dimension: first_message_response_time_minutes_tier {
    type: tier
    sql: ${first_message_response_time_minutes} ;;
  }

  measure: avg_first_message_response_time_seconds {
    type: avg
    sql: ${first_message_response_time_seconds} ;;
  }

  measure: avg_first_message_response_time_minutes {
    type: avg
    sql: ${first_message_response_time_minutes} ;;
  }

  measure: avg_message_response_time_minutes {
    type: avg
    sql: ${average_of_messages_response_time_minutes} ;;
  }

  measure: avg_number_messages {
    type: avg
    sql: ${number_messages} ;;
  }

  measure: avg_number_guest_messages {
    type: avg
    sql: ${number_guest_messages} ;;
  }

  measure: avg_number_user_messages {
    type: avg
    sql: ${number_user_messages} ;;
  }
}
conversations_facts.view.lookml · 99 lines
source-preserved 
- view: conversations_facts
  derived_table:
    sql: SELECT
  conversation_id
  , COUNT(*) AS number_messages
  , SUM(case m.sender_type when 'Guest' then 1 else 0 end) AS number_guest_messages
  , SUM(case m.sender_type when 'User' then 1 else 0 end) AS number_user_messages
  , MIN(m.created_at) AS first_message_sent
  , MAX(m.created_at) AS last_message_sent
  , MIN(CASE WHEN y.message_sequence_number > 1 THEN y.elapsed_response_time ELSE NULL END) AS first_message_response_time_seconds
  , AVG(elapsed_response_time) AS average_of_messages_response_time_seconds
  , SUM(elapsed_response_time) AS total_response_time_seconds
FROM messages AS m
LEFT JOIN
  (SELECT
    *
    , EXTRACT(EPOCH FROM (x.created_at - x.previous_message_sent)) as elapsed_response_time
  FROM
    (SELECT
      m.id
      , m.created_at
      , ROW_NUMBER() OVER(PARTITION BY m.conversation_id ORDER BY m.created_at) AS message_sequence_number
      , CASE WHEN(m.sender_type != LAG(m.sender_type) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
            THEN (LAG(m.created_at) OVER(PARTITION BY m.conversation_id ORDER BY m.created_at))
                ELSE NULL
                  END as previous_message_sent
    FROM messages m
    WHERE m.type = 'Messages::PlaceholderAdHoc'
    AND m.sender_type != 'PlaceholderSenderType'
    ) x) y ON y.id = m.id
WHERE m.type = 'Messages::PlaceholderAdHoc'
GROUP BY 1
  fields:
  - dimension: conversation_id
    primary_key: true
    sql: ${TABLE}.conversation_id
  - dimension: number_messages
    type: number
    sql: ${TABLE}.number_messages
  - dimension: number_guest_messages
    type: number
    sql: ${TABLE}.number_guest_messages
  - dimension: number_user_messages
    type: number
    sql: ${TABLE}.number_user_messages
  - dimension: number_messages_tier
    type: tier
    sql: ${number_messages}
  - dimension: total_response_time_seconds
    type: number
    sql: ${TABLE}.total_response_time_seconds
  - dimension: total_response_time_seconds_tier
    type: tier
    sql: ${total_response_time_seconds}
  - dimension: average_of_messages_response_time_seconds
    type: number
    sql: ${TABLE}.average_of_messages_response_time_seconds
  - dimension: average_of_messages_response_time_minutes
    type: number
    sql: ${average_of_messages_response_time_seconds}/60
  - dimension: average_message_response_time_minutes_tier
    type: tier
    sql: ${average_of_messages_response_time_minutes}
  - dimension: first_message_sent
    type: time
    sql: ${TABLE}.first_message_sent
  - dimension: last_message_sent
    type: time
    sql: ${TABLE}.last_message_sent
  - dimension: first_message_response_time_seconds
    type: number
    sql: ${TABLE}.first_message_response_time_seconds
  - dimension: first_message_response_time_minutes
    type: number
    sql: ${first_message_response_time_seconds}/60
  - dimension: first_message_response_time_seconds_tier
    type: tier
    sql: ${first_message_response_time_seconds}
  - dimension: first_message_response_time_minutes_tier
    type: tier
    sql: ${first_message_response_time_minutes}
  - measure: avg_first_message_response_time_seconds
    type: avg
    sql: ${first_message_response_time_seconds}
  - measure: avg_first_message_response_time_minutes
    type: avg
    sql: ${first_message_response_time_minutes}
  - measure: avg_message_response_time_minutes
    type: avg
    sql: ${average_of_messages_response_time_minutes}
  - measure: avg_number_messages
    type: avg
    sql: ${number_messages}
  - measure: avg_number_guest_messages
    type: avg
    sql: ${number_guest_messages}
  - measure: avg_number_user_messages
    type: avg
    sql: ${number_user_messages}

Contents

  • conversations_facts.sql
  • conversations_facts.sql
  • conversations_facts.view.lkml
  • conversations_facts.view.lookml