Intercom + Stripe – revenue impact of support
Do customers that you talk to pay you more, on average?
Intercom (see full visual schema here)
with intercom_stripe as ( select intercom_users.id intercom_user_id, conversations.user_id conversation_user_id, sum(stripe_charges.amount)/100::float total_rev from intercom.users intercom_users left join intercom.conversations conversations on conversations.user_id = intercom_users.id left join segment.users segment_users on segment_users.id = intercom_users.user_id left join stripe.customers stripe_customers on stripe_customers.email = segment_users.email left join stripe.charges stripe_charges on stripe_charges.customer_id = stripe_customers.id where stripe_charges.amount > 0 and stripe_charges.captured is true group by intercom_users.id, conversations.user_id ) -- -- number of customers select 'Counts' as "label", count(distinct intercom_user_id) - count(distinct conversation_user_id) "No conversations", count(distinct conversation_user_id) "Had Conversation(s)" from intercom_stripe union -- average revenue select a.label, a.no_conversations, b.had_conversation from ( select 'Average Revenue' as label, avg(total_rev) no_conversations from intercom_stripe where conversation_user_id is null ) a join ( select 'Average Revenue' as label, avg(total_rev) had_conversation from intercom_stripe where conversation_user_id is not null ) b on a.label = b.label
Upvote Follow 1