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


What’s Next

Don't see what you're looking for? Browse the Activity Index for more examples...