1

SendGrid + Segment – conversion of email campaign to signup

Question

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

Sources

Sendgrid (see full visual schema here)

Analytics.js (docs here)

Results

Query

with

email_events as (
select
event,
email,
received_at
from sendgrid.activity 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 sendgrid.activity activity
 on activity.email = users.email
-- allow for 3 week conversion window
where datediff('week', activity.received_at, signup.received_at) > 0
and datediff('week', activity.received_at, signup.received_at) < 3
),

--select * from signups_from_email

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