The problem. Being sure we deal with any user’s problem: what about unanswered support chats.

Our division directions included to be able to analyze all the information we have in user’s comments. We found 2 main bags of comments that weren’t analyzed, or at least not always.

1. Support feedbacks that sometimes contain direct unanswered questions.
2. unanswered chats. What happens when a user asks for something and we never answer them?

Let’s focus on the unanswered chats part. This should never happen, but

• is there any way to actually know if / how much it is happening?
• could we build a process to be sure to reach out to these users in a reasonable time to take care of their needs?

We can use #SQL for that… and later #Looker.

Solution. Interrogating the DB to get the list of unanswered chats in a timespan.

We can interrogate the DB with a bit complex query. You would basically have 2 tables, the sessions table and the messages table witha relationship 1:n. You have n messages per session, being session_id foreign key for the messages table.

You could be tempted to use the sessions table but you don’t really need it. You need the number of messages per session, this making the difference between messages sent by the user (questions) and by the Support Rep (answers), and this can be done without using the sessions table. We just need the messages table.

However you need a join to get the timestamp of the first message for each session, in order to create reports. This would be a skeleton of the query I used:

select SESSION.chat_session_id, nvl(count(MESSAGES.chat_session_id), 0) from 
(SELECT chat_session_id
FROM chat_messages
group by chat_session_id
having MIN( from_unixtime(cast( `timestamp` /1000 as bigint)) ) BETWEEN '__DATE1__' AND '__DATE2__'  ) SESSION
LEFT JOIN 
(select chat_session_id
from chat_messages 
WHERE 
is_operator=TRUE
) MESSAGES
ON 
SESSION.chat_session_id=MESSAGES.chat_session_id
Group by chat_session_id
having count(MESSAGES.chat_session_id) = 0

Where you’d probably need to add a few more things typically to the WHERE clauses. (If you have both messages and events in the messages table, for instance).

Note: nvl(count(MESSAGES.chat_session_id), 0) is something I learned here. The idea is to avoid null values and convert nulls into 0 (in this case)

With this query, we can get the list a list of chats extracted and copied into a spreadsheet, daily (it takes 10 minutes). A couple of persons could read chats_session_ids and take a look at the chat transcript to identify problems in the chat tool and also to give an answer to these unanswered users. Because behind an unanswered chat, there is a likely unhappy user.

Review the list daily and send follow-ups when needed seems a good workflow, but we can do it better.

Solution. Using Looker. 1.- the view.

We need to convert SQL into LookML, but always using Matt Mazur’s SQL style guide https://mattmazur.com/2019/06/13/mazurs-sql-style-guide/

This is the result (again, the skeleton):

with sessions as (

    select
        chat_messages.chat_session_id,
        min(from_unixtime(cast(`timestamp` /1000 as bigint)))  as session_dt
    from chat_messages
    group by chat_messages.chat_session_id
    having min(from_unixtime(cast(`timestamp` /1000 as bigint))) >= '2019-12-01 00:00:00'

    ),

messages as (

    select chat_messages.happychat_session_id
    from chat_messages
    where is_operator=true

    )

 select
    sessions.chat_session_id as chat_id,
    sessions.session_dt as start_time,
    nvl(count(messages.chat_session_id), 0) as msg_nb
 from sessions
left join messages on sessions.chat_session_id=messages.chat_session_id
group by sessions.chat_session_id, sessions.session_dt

This, together with a good list of dimensions and measures will assure that a nice dashboard can be built. Some of these are:

• A timeframes for start_time, to create reports based on time
• chat_transcript will compose the URL to the chat transcription so that we can open unanswered chats by clicking on the report. We assume the URL for a chat is https://URLWITHOUTTHECHATID + Chat Id.
• answered chat: useful for pivots. It is a yesno dimension. No means we sent 0 messages back to the user during the chat.
• A tier with buckets of 10 to create nice barcharts with chats vs. number of messages during the chat.

dimension_group: start_time {
    description: " time of the first message the user dropped in the chat"
    type: time
    timeframes: [
      time,
      date,
      hour_of_day,
      day_of_week,
      week,
      month
  ]
    sql: ${TABLE}.start_time ;;
}

...

  dimension: chat_transcript {
    description: "Chat transcript link /URL for a livechat. Composed using the session id"
    type: string
    sql: CONCAT ( "https://URL PART WITHOUT THE CHAT ID", CAST (${TABLE}.chat_id as string)) ;;
  }

... 

  dimension: answered_chat {
    description: "Yes means this chat was answered by a HE. No means we never answered"
    type:  yesno
    sql: ${TABLE}.msg_nb > 0 ;;
  }

...

  dimension: messages_number_tier {
    description: "grouping of number of messages we sent to the user while in a chat"
    label: "Number of HE messages on a chat: Buckets of 10"
    type: tier
    tiers: [10,20,30,40,50,60,70,80,90,100,110,120,130,140,150]
    style: integer
    sql:  ${messages_number} ;;
  }

What’s the result? A Looker Dashboard

The dashboard contains several looks. Among them

• % and number of unanswered chats in the last 12/24 hours
• A list of these chats with the URL to open the chat transcript from the report.
• 2 bar graphs to see the number of unanswered chats per day of the week and also per hour in the last 90 days.
• A barchart with the average messages per day of the week and another one with the the % of chats on each bucket (0 to 10 messages, 10 to 20 messages…)
• Lastly a bidimensional table with Avg Messages per hour/day of the week, using a color scale to show the hours in the week with more and less messages/chat

Next step. A Report distributed daily and a follow up with users.

The last step was straightforward. A report created and distributed daily to 3 persons that 1) take a look at chat transcripts, 2) follow up with users when needed and 3) review % of unanswered chats and number of messages trends.