Analyzing Your Olark History With SQL

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.

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.

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.

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 (
  select
    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
  from
    olark_shared
  where
    chat_start_time > now() - interval '30 days'
)

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

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

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.

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

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

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.

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 (
  select 
    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 (
  select 
    date_trunc('week', (users.created_at))::date as date 
    , sites.status
    , count(1) as count
  from 
    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.

select 
  customer_count.date 
  , customer_count.status
  , (chat_count.count::float/customer_count.count)
from 
  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!

Tags: data team