Sisense Data Team 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
1An 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,2It 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.statusSpecial thanks to Rya for her help in assembling these examples!