1

Hubspot + Stripe – revenue per campaign

Question

Analyze the paid conversion rate and revenue from an email campaign

Sources

Hubspot (full schema here)

Stripe (full schema here)

Results

Query

with

email_events as (
select
type event,
recipient email,
created
from hubspot.email_events
),

conversions_from_email as (
select
'conversion' event,
customers.email email,
charges.created
from stripe.charges charges
join stripe.customers customers
 on charges.customer_id = customers.id
join hubspot.email_events activity
 on activity.recipient = customers.email
-- allow for 3 week conversion window
where datediff('week', activity.created, charges.created) > 0
and datediff('week', activity.created, charges.created) < 3
-- and only successful positive charges
and charges.amount > 0 and charges.captured is true
),

--select * from signups_from_email

email_and_conversions as (
select *
from email_events
union
select *
from conversions_from_email
)

select event, count(distinct email)
from email_and_conversions
group by event