1

Salesforce + Zendesk – popular tags by stage

Question

What types of types of tickets are your prospects sending to your success team?

Sources

Salesforce (full schema here)

Zendesk (full schema here)

Results

Query

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