1

Hubspot + Segment – conversion rate of email campaigns

Question

Build a custom funnel of email events to in-app conversions

Sources

Hubspot (see full visual schema here)

Results

Query

with

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

signups_from_email as (
select
'signup' event,
users.email email,
signup.received_at
from segment.signup signup
join segment.users users
 on signup.user_id = users.id
join hubspot.email_events activity
 on activity.recipient = users.email
-- allow for 3 week conversion window
where datediff('week', activity.created, signup.received_at) > 0
and datediff('week', activity.created, signup.received_at) < 3
),

email_and_signups as (
select *
from email_events
union
select *
from signups_from_email
)

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