1

Zendesk + Segment – trending ticket tags

Question

 What are the most common tickets in a free trial, and how are those tags trending over time?

Sources

Zendesk (see full visual schema here)

Analytics.js (docs here)

Results

Query

with

-- first task is to split the zendesk.tickets.tags column (csv of tags) into separate rows
-- generate a numbers table to cover a maximum of 100 comma separated values
numbers as (
select
 (row_number() over (order by true))::integer as n
from zendesk.tickets
limit 100
),

-- cross join numbers with the users table to split values into separate rows
tickets_split_tags as (
select
 id,
 requester_id,
 submitter_id,
 created_at,
 split_part(tags,',',n) as split_tags
from
 zendesk.tickets
cross join
 numbers
where
 split_part(tags,',',n) is not null
 and split_part(tags,',',n) != ''
),

-- join the split tags table with the zendesk users
tickets_with_id as (
select
tickets_split_tags.requester_id,
tickets_split_tags.submitter_id,
tickets_split_tags.created_at,
users.external_id ,
btrim(split_tags) as tags
from tickets_split_tags
join zendesk.users users
 on tickets_split_tags.requester_id = users.id
 where split_tags is not null
),

-- join the tickets with the 'Signup' event that we track
-- and aggregate the ticket counts for a particular tag
tickets_aggregated as (
select
date_trunc('month', tickets_with_id.created_at) date,
tags,
count(*) tickets
from segment.signup signups
join tickets_with_id
 on signups.user_id = tickets_with_id.external_id
-- keep tickets that were created in the first two weeks of signup
-- corresponding to our free trial
where datediff('week', signups.received_at, tickets_with_id.created_at) < 3
and tags ilike 'setup'
group by date_trunc('month', tickets_with_id.created_at), tags
order by date desc, tickets desc
)


-- add the total tickets for that month to see relative change in volume over time
select
tickets_aggregated.*,
tickets.total_tickets,
tickets_aggregated.tickets /(total_tickets::float)*100 pct_of_total
from tickets_aggregated
join (
 select date_trunc('month', zendesk.tickets.created_at) date, count(*) total_tickets
 from zendesk.tickets
 group by date_trunc('month', zendesk.tickets.created_at)
 ) tickets
 on tickets_aggregated.date = tickets.date
order by date desc