Zendesk + Segment – trending ticket tags
What are the most common tickets in a free trial, and how are those tags trending over time?
Zendesk (see full visual schema here)
Analytics.js (docs here)
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