1

Mandrill + Segment – conversion of email campaign to signup

Question

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

Sources

Mandrill (see full visual schema here)

Analytics.js (docs here)

Results

Query

with

email_events as (
select
event,
msg_email email,
received_at
from mailchimp.activity activity
),

-- join signup event with email 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 mandrill activity
 on activity.msg_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
),

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