No items found.

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 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 Periscope Data and analyze the results in SQL.​ 

Analyzing The Data

A great point of pride at Periscope Data 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 (
    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 (we’re hiring!).

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. ​

  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. ​

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
  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
  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 = 
    and customer_count.status = chat_count.status

​ Check out the latest version of these charts (and more!) on our Olark Chats Dashboard. Special thanks to Rya for her help in assembling these examples!

Want to discuss this article? Join the Periscope Data Community!

Periscope Data
The world’s fastest visualization platform for data analysts.