Zendesk Chat Events

zendesk_chat_events.yaml

# preliminaries #

- connection: your_connection
- scoping: true
- case_sensitive: false


- explore: zendesk_chat_events
- view: zendesk_chat_events
  derived_table: 
    sql: |
      select id as ticket_id
        , num::int as message_sequence
        , ((nullif(regexp_replace(start_at, '[0-9:]{2}\\s(AM|PM)\\sUTC', ''), '')::date || ' ' || regexp_substr(split_part(message, substring(md5(id), 1, 8), num::int), '([0-9:]{2}){3}[0-9]{2}\\s(AM|PM)'))::timestamp as message_at
        , replace(regexp_substr(split_part(message, substring(md5(id), 1, 8), num::int), '[A-Z]{1}[a-z]+\\s[A-Za-z-]+\\s[A-Z]{1}[a-z]+:|[A-Z]{1}[a-z]+\\s[A-Za-z]+:|[A-Z]{1}[a-z]+:|[A-Z]{2}:'), ':', '') as chatter
        , regexp_replace(split_part(message, substring(md5(id), 1, 8), num::int), '(\\([0-9:\\s]+(AM|PM)\\))|[A-Z]{1}[a-z]+\\s[A-Za-z-]+\\s[A-Z]{1}[a-z]+:|[A-Z]{1}[a-z]+\\s[A-Za-z]+:|[A-Z]{1}[a-z]+:|[A-Z]{2}:', '') as message_body
      from (select id
              , regexp_substr(description, '[0-9\\s:-]+(AM|PM)\\sUTC') as start_at
              , regexp_replace(description, '(\\([0-9:\\s]+(AM|PM)\\))', ('\\1' || substring(md5(id), 1, 8))) as message
              , description
            from tickets
            where via_channel = 'chat') as chat
      join public.numbers
      on numbers.num <= regexp_count(description, '(\\([0-9:\\s]+(AM|PM)\\))') + 1
  fields:
  
# dimensions #

  - dimension: ticket_id
    type: number
    sql: ${TABLE}.ticket_id
    
  - dimension: message_sequence
    type: number
    sql: ${TABLE}.message_sequence
    
  - dimension_group: message
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.message_at
    
  - dimension: chatter
    type: string
    sql: ${TABLE}.chatter
    
  - dimension: message_body
    type: string
    sql: ${TABLE}.message_body

# measures #

  - measure: count
    type: count