Intercom + Stripe – revenue impact of support

Question

Do customers that you talk to pay you more, on average?

Sources

Intercom (see full visual schema here)

Results

Query

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