2

Stripe + Zendesk – ticket load by plan type

Question

What is the support load by subscription plan?

Sources

Zendesk (full schema here)

Stripe (full schema here)

Results

Query

select
stripe_plans.name stripe_plan,
count(distinct stripe_customers.id) customers,
count(zendesk_tickets.requester_id) tickets,
count(zendesk_tickets.requester_id)/ count(distinct stripe_customers.id)::float "Average tickets per customer"

from zendesk.tickets zendesk_tickets
join zendesk.users zendesk_users
 on zendesk_tickets.requester_id = zendesk_users.id
join segment.users segment
 on zendesk_users.external_id = segment.id
join stripe.customers stripe_customers
 on segment.email = stripe_customers.email
join stripe.subscriptions stripe_subscriptions
 on stripe_customers.id = stripe_subscriptions.customer_id
join stripe.plans stripe_plans
 on stripe_subscriptions.plan_id = stripe_plans.id
group by stripe_plans.name