How to calculate support needs per hour/day of the week?

The problem. We need to understand how many support chats we are getting to calculate coverage needs (agents to assign).

The scheme is simple. We provided livechat and when all our reps were busy, we created a ticket out of the user request/question. And this ticket was answered by email in lower cadence moments or when we had support reps. availability. In a few words, we would need to calculate the coverage needs (how many support agents we needed per hour and day of the week) so that we use more efficiently our resources.

Queuing theory can be useful later. But first we would need to put in clear and understand the number of chats we are getting per hour/day. Also… the chat length.

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

I reused a LookML code I had for another project so I will stick to the key part:

....

 select
    sessions.session_id as chat_id,
    from_unixtime(cast(sessions.session_dt /1000 as bigint)) as start_time,
    from_unixtime(cast(sessions.session_end_dt /1000 as bigint)) as end_time,
    from_unixtime(cast(sessions.session_end_dt /1000 as bigint)-cast(sessions.session_dt /1000 as bigint)) as chat_length
 from sessions

This will give you the a primary key (chat Id) together with the 3 main infos you need from it: the time it started, when it finished and the length. You could also use a having clause to filter some results and make the result to be smaller and more manageable:

having min(from_unixtime(cast(`timestamp` /1000 as bigint))) >= '2019-12-01 00:00:00'

Special thanks go to Matt Mazur’s SQL guide

Solution. Using Looker. 1.- the view.

We need to convert SQL into LookML, always using Matt Mazur’s SQL style guide, and then create dimensions that we’ll use for the looks and dashboards.

Some of them were:

dimension_group: time_start {
    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_group: time_end {
    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}.end_time ;;
  }

I guess these were not very surprising but after that we needed to find a way to put the chat length in an useful format. This was my approach. I used hours, but I think we could just hide it from the visualization, once we have the minutes. And then I kept granularity at a minute level.

  dimension: chat_length_hour {
    type: number
    label: "Complete Hours"
    group_label: "Chat Length"
    sql: hour(${TABLE}.chat_length) ;;
  }

  dimension: chat_length_minutes {
    type: number
    label: "Total Minutes"
    group_label: "Chat Length"
    sql: hour(${TABLE}.chat_length)*60+minute(${TABLE}.chat_length) ;;
  }

After this, we needed measures. A count always goes well, but also the average_length for the chats.


  measure: count {
    type: count
    drill_fields: [session_id, chat_transcript, messages_number]
  }

  measure: average_length_minutes {
    type:  average
    sql: ${chat_length_minutes} ;;
  }

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

What’s the result? A Looker Dashboard

The first part of the looker dashboard contains macro numbers having to do with the standard support chat length. The decomposition per day of the week is useful in lots of cases. For instance, if you just offer chat for a kind of users on weekends, you can see here if this has an impact on the chat length.

After that, as the main goal was to understand the number of support agents we need for each time slot, we would need the chat length per hour/day and the number of chats. In the picture: I run the dashboard on Thursday at noon with the filter set to last 7 days. And, last 7 days means from last Friday to today (we have not data for today noon to midnight) 🙂 TIL

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

From here you can use a queueing theory approach to calculate your coverage needs. It will depend on the specificities of your service, the SLA % you have set or you’d like to set…

DataTadpole.com