daniel-subscription-analytics | Stripe Developers | Page 1

grizzled zephyrBOT Oct 5, 2022, 8:41 PM

#

timber bone Oct 5, 2022, 8:47 PM

#

Are you looking for a Dashboard solution or an API solution?

elder galleon Oct 5, 2022, 8:50 PM

#

api

#

preferably

icy birch Oct 5, 2022, 8:51 PM

#

daniel-subscription-analytics

#

There isn't really an API for this today, you'd use the List Subscriptions API: https://stripe.com/docs/api/subscriptions/list and you can filter for canceled ones specifically and then you have to paginate them all as they are ordered by most recently created and not cancellation date

#

If it happened in the past 30 days you could use the List Events API: https://stripe.com/docs/api/events/list and filter for customer.subscription.deleted

elder galleon Oct 5, 2022, 8:52 PM

#

sorry by api i mean database

#

i imagine the api feeds the database. but i think it's a redshift database that integrates with stripe directly

#

so i have a subscriptions table

#

so is there something from the subscription object that i can filter on?

icy birch Oct 5, 2022, 8:57 PM

#

I'm sorry but you need to be a bit more specific

#

like which Database? Sigma? The Stripe Data Pipeline? Your own database?

elder galleon Oct 5, 2022, 8:58 PM

#

yea our own database, that has all of our stripe data

#

but it overlaps heavily with the api docs

#

i didn't make it, and am not sure how it was formed

#

but i dont have lists in the subscription table for example

icy birch Oct 5, 2022, 8:59 PM

#

I can't really help you with your own database unfortunately

#

tht's something you would figure out, or talk to the developer(s) who set this up

elder galleon Oct 5, 2022, 8:59 PM

#

batch_timestamp
billing
billing_cycle_anchor
billing_thresholds_amount_gte
billing_thresholds_reset_billing_cycle_anchor
cancel_at
cancel_at_period_end
canceled_at
cancellation_reason
cancellation_reason_text
created
current_period_end
current_period_start
customer_id
days_until_due
default_source_id
discount_coupon_id
discount_customer_id
discount_end
discount_start
discount_subscription
ended_at
id
merchant_id
pause_collection_behavior
pause_collection_resumes_at
plan_id
price_id
quantity
start_date
start_time
status
tax_percent
trial_end
trial_start

icy birch Oct 5, 2022, 8:59 PM

#

You should be able to find exactly what you need in that list in seconds at most

#

and it all maps to waht exists in our API Reference with detailed explanation of every property

elder galleon Oct 5, 2022, 9:00 PM

#

well based on stripe docs it's not that clear how to calculate a daily churn count

icy birch Oct 5, 2022, 9:00 PM

#

We have no such thing anywhere in our API

#

so that's expected

#

you can use Sigma, which has a lot of revenue recognition example queries, or you can use our revenue recognition product

#

ultimately, if what you want is to use your own system and database, then you have to adapt to your data

#

churn count daily would be number of people whose subscription ended on each day

#

and there's an ended_at property in your list, which maps to https://stripe.com/docs/api/subscriptions/object#subscription_object-ended_at I assume which says

If the subscription has ended, the date the subscription ended.
which seems to be exactly what you want

elder galleon Oct 5, 2022, 9:02 PM

#

but which fields are used to find the amount of people who no longer are active on a certain date? as current_period_end can shift

#

okay got it

icy birch Oct 5, 2022, 9:02 PM

#

if you take the time to read the API Reference for the Subscription object you will find the definition of every property

#daniel-subscription-analytics