Stripe
Paid Invoice
Customer paid their invoice
Type: Activity Transformation
select
i.id as activity_id,
i.received_at as ts,
NULL as anonymous_customer_id,
cc.email as customer,
'paid_invoice' as activity,
i.description as feature_1,
ii.id as feature_2,
NULL as feature_3,
i.amount / 100.0 as revenue_impact,
NULL as link
FROM stripe.charges i
JOIN stripe.customers cc
on (i.customer_id = cc.id)
LEFT JOIN stripe.invoices ii
on (ii.id = i.invoice_id)
WHERE i.status='succeeded'
Received Invoice
Customer was billed for products or services
Also includes the Received Null Invoice activity. Sometimes customers will receive an invoice without a payment amount (ex. 3 months free). It can be helpful to have an activity for this when you need to track total payment periods for the customer.
Type: Activity Transformation
select
i.id as activity_id,
i.date as ts,
NULL as anonymous_customer_id,
cc.email as customer,
CASE WHEN i.subtotal=0 then 'received_null_invoice'
else 'received_invoice' end as activity,
d.coupon_id as feature_1,
NULL as feature_2,
NULL as feature_3,
i.total/100.0 as revenue_impact,
NULL as link
FROM stripe.invoices i
LEFT OUTER JOIN stripe.discounts d
on (d.id = i.discount_id)
JOIN stripe.customers cc
on (i.customer_id = cc.id)
WHERE i.subtotal>0
Started Subscription
Customer started a subscription in stripe
Type: Activity Transformation
SELECT
s.id as activity_id,
GREATEST(s.trial_end, s.start) as ts,
NULL as anonymous_customer_id,
c.email as customer,
'started_subscription' as activity,
p.nickname as feature_1, -- plan name
cp.name as feature_2, -- coupon
p.tiers_mode as feature_3,
p.amount /100.0 as revenue_impact,
NULL as link
FROM ft_stripe.subscription s
join ft_stripe.subscription_discount sd
on (sd.subscription_id = s.id)
join ft_stripe.coupon cp
on (co.id = sd.coupon_id )
join ft_stripe.subscription_item si
on (si.subscription_id = s.id )
join ft_stripe.plan p
on (si.plan_id = p.id )
join ft_stripe.customer c
on (c.id = s.customer_id)
where (s.canceled_at is null or s.trial_start is NULL or s.canceled_at > s.trial_end)
-- handle the case where the user canceled during the trial
Ended Subscription
A subscription ended
Type: Activity Transformation
SELECT
s.id as activity_id,
s.ended_at as ts,
NULL as anonymous_customer_id,
c.email as customer,
'ended_subscription' as activity,
p.nickname as feature_1, -- plan name
cp.name as feature_2, -- coupon
p.tiers_mode as feature_3,
p.amount /100.0 as revenue_impact,
NULL as link
FROM ft_stripe.subscription s
join ft_stripe.subscription_discount sd
on (sd.subscription_id = s.id)
join ft_stripe.coupon cp
on (co.id = sd.coupon_id )
join ft_stripe.subscription_item si
on (si.subscription_id = s.id )
join ft_stripe.plan p
on (si.plan_id = p.id )
join ft_stripe.customer c
on (c.id = s.customer_id)
where (s.canceled_at is null or s.trial_start is NULL or s.canceled_at > s.trial_end)
-- handle the case where the user canceled during the trial
and s.ended_at is not null
Cancelled Subscription
Customer cancelled their subscription
Type: Activity Transformation
SELECT
s.id as activity_id,
s.cancelled_at as ts,
NULL as anonymous_customer_id,
c.email as customer,
'ended_subscription' as activity,
p.nickname as feature_1, -- plan name
cp.name as feature_2, -- coupon
p.tiers_mode as feature_3,
p.amount /100.0 as revenue_impact,
NULL as link
FROM ft_stripe.subscription s
join ft_stripe.subscription_discount sd
on (sd.subscription_id = s.id)
join ft_stripe.coupon cp
on (co.id = sd.coupon_id )
join ft_stripe.subscription_item si
on (si.subscription_id = s.id )
join ft_stripe.plan p
on (si.plan_id = p.id )
join ft_stripe.customer c
on (c.id = s.customer_id)
where (s.canceled_at is null or s.trial_start is NULL or s.canceled_at > s.trial_end)
-- handle the case where the user canceled during the trial
and s.cancelled_at is not null
Contribute to the Narrator Community
Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.
Have questions?
Our data team is here to help! Here are a couple ways to get in touch...
š¬ Chat us from within Narrator
š Email us at [email protected]
š Or schedule a 15 minute meeting with our data team
Updated over 2 years ago
Don't see what you're looking for? Browse the Activity Index for more examples...