A strong Customer Success team should be highly engaged with customers, which is why we use Olark to enable live chat and email support directly within the product.

Olark’s reporting dashboard offers an overview of support activity, but we like to dig deeper. For a comprehensive look at our key support metrics, we upload our chat data and maintain a weekly dashboard to track median response time, hourly chat popularity, average chat duration, and other metrics.

In this post we will describe how to download this information from Olark and how we analyze the data with SQL. ​ ​ 

Downloading Chat Metadata from Olark

To start, simply go to olark.com/reports/email and enter the time period you want to analyze. 

Enail conversation report

You will get an email containing a csv which contains information about the chat and a link to the transcript. Depending on your Olark settings, you will get back fields about the operator, visitor, chat content, and feedback. In total we get 42 fields. ​

Olark CSV

​We upload this csv into our Redshift cluster through Sisense for Cloud Data Teams and analyze the results in SQL.​ 

Analyzing The Data

A great point of pride at Sisense is maintaining an average chat response time under 10 seconds. To ensure we are close to that target, we track the daily median. 

chat median response rate

​​To build this chart, we first pull operator_first_response_delay and the date from chat_start_time over the last 30 days. We save this table as response_times. ​

with response_times as (
    date_trunc('day', (chat_start_time)::timestamp)::date as date
    , case operator_first_response_delay when 'None' then 0
      else operator_first_response_delay::numeric end as response
    chat_start_time > now() - interval '30 days'

​We then get the median for each date using the percentile_cont window function.

select distinct
  , percentile_cont(0.5) within group(order by response) 
      over(partition by date) as median
order by

An important long term metric to monitor is the ratio of emails to chats. When there are no operators available on Olark, users are prompted to send an email instead.

We keep track of the weekly percentage of offline messages and this helps inform when we need to focus on recruiting customer success.

You can see in this chart that the ratio had been creeping up over the past few months until the sharp decline at the end of September when we onboarded Andreas and Sunny as support specialists. ​

Chat count over time

Writing this chart in SQL is straightforward – we select the date and type, and then count the number of occurrences. ​

  date_trunc('week', (chat_start_time)::timestamp)::date
  , transcript_type 
  , count(1)
  chat_start_time > now() - interval '150 days'
group by 

It is also important to integrate chat metrics with the rest of our analytics. We keep track of how chat activity correlates with customer growth, our key financial metrics, and engagement with the product.

Tracking number of chats per customer helps us understand if features and bug fixes we introduce make the product easier to understand or could benefit from refined simplicity. ​

Chats per customer

Because we control our user analytics, writing the SQL to support this chart is straightforward.

First, we count both the chats and customers: ​

with chat_count as (
    date_trunc('week', (chat_start_time))::date as date 
    , status
    , count(1) as count
  from olark_shared join users 
    on olark_shared.visitor_email=users.email_address
      join sites on users.site_id=sites.id
  group by 1,2
, customer_count as (
    date_trunc('week', (users.created_at))::date as date 
    , sites.status
    , count(1) as count
    sites join users on users.site_id=sites.id
  group by 1,2

Now we join our chat_count and customer_count tables and divide the counts. ​

  , customer_count.status
  , (chat_count.count::float/customer_count.count)
  customer_count join chat_count on 
    customer_count.date = chat_count.date 
    and customer_count.status = chat_count.status

Special thanks to Rya for her help in assembling these examples!