Salesforce + Zendesk – popular tags by stage
What types of types of tickets are your prospects sending to your success team?
Salesforce (full schema here)
Zendesk (full schema here)
with tickets as ( select * from zendesk.tickets ), -- first task is to split the tags csv column into multiple 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 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 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 ), tickets_with_salesforce as ( select tickets_with_id.*, opportunities.id, opportunities.stage_name from tickets_with_id -- etl3 is a Segment table that provides nightly snapshots of accounts join etl3.groups groups on tickets_with_id.external_id = groups.user_id -- segment groups to salesforce accounts join salesforce.accounts as accounts on accounts.external_id_c = groups.group_id -- accounts to oportunities join salesforce.opportunities opportunities on accounts.id = opportunities.account_id -- keep only tickets before deal closes where opportunities.close_date > tickets_with_id.created_at ), -- aggregate the ticket counts for stage_name and tag tickets_aggregated as ( select tickets_with_salesforce.stage_name, tags, count(distinct id) tickets from tickets_with_salesforce --and tags ilike 'setup' group by stage_name, tags order by stage_name, tickets desc ) select * from tickets_aggregated
Upvote Follow 1