2

Mandrill + Stripe – revenue per campaign

Question

Analyze the conversion rate and revenue from an email campaign

Sources

Mandrill (see full visual schema here)

Stripe (see full visual schema here

Results

Query

with

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

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 mandrill.activity activity
 on activity.msg_email = customers.email
-- allow for 3 week conversion window
where datediff('week', activity.received_at, charges.created) > 0
and datediff('week', activity.received_at, charges.created) < 3
-- and only successful positive charges
and charges.amount > 0 and charges.captured is true
),

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