daniel_puck_1234

[dusk parrot]

Hello, is this duplicating the logic from something in our dashboard?

[dark niche]

yea i'm trying to do that but break it out by our different products

as opposed to have aggregate amounts

[dusk parrot]

We don't know much about Sigma on here but I do think you should only need those two tables. A subscription can't be re-activated after it is cancelled so you should be able to figure that out by timestamps on the subscription if I understand this correctly

I will see if I can find the logic for that widget quickly but I may not be able to and may need to direct you to support. Will get back to you in a minute...

[dark niche]

thanks so much

[dusk parrot]

Unfortunately I'm not finding it specifically. Our support may be able to help provide you the logic from that one. As far as I can tell from the description, it looks like it would count any subscription that is active or past_due .

With Sigma, I think that would mean you would say the subscription started being active the trial_end if it has one and the start_date otherwise and it stopped being active on the end_date if it has one

I don't know how to write the time series part of that but it sounds like you have a good enough grasp of SQL there?

[dark niche]

yea good with sql, but not sure how i can check if a customer was in an active state in a different part of time

[dusk parrot]

From what I can see, we'd consider a customer active as long as any one of their subscriptions are active here. So you actually may only need the subscription table and count distinct customer IDs

[dark niche]

but what if we have a monthly product, so you can subscribe for 1 month

and they subscribed for a certain mount and canceled the next month. they were active but currently in time they no longer are, right?

[high cypress]

👋 Hopping in since Pompey has to head out soon

Can you elaborate on this part: "they were active but currently in time they no longer are, right"

If you have a customer that subscription on July 1st, and their subscription will automatically cancel on September 1st are you asking how to verify the Subscription was active in august?

[dark niche]

yes

and for july and september

and which tables i would need to get the full view of that..can i just use subscriptions table?

[high cypress]

Are you trying to limit this specifically to active status subscriptions, or do you just mean any subscription that isn't cancelled?

[dark niche]

i'm trying to look at the number of active subscribers by each month for the past year

and the number that cancelled that month as well

to get churn, but to break it out by different products

[high cypress]

Then all you need to look at it is the cancel_at timestamp on the Subscription. If the timestamp comes before the month you're looking at, then you knew the Subscription was cancelled at that point and it no longer active

[dark niche]

okay perfect so i just need the subscription table and nothing else, correct?

[high cypress]

Yup! I believe that should be enough

[dark niche]

and what about to get new subscribers by month

i just aggregate for start_date month, count(distinct customer_id) from subscriptions

[high cypress]

Yeah, start_date is what you'll want to check to find new subscriptions

[dark niche]

hmm weird i'm still overshooting stripes count when i do it that way

im not sure if i need to account for anything else

[high cypress]

I don't know the specifics of how we're calculating these values, but it could be that we filter out certain subs (like ones that expired and moved to incomplete_expired)

[dark niche]

is there someone who i can talk to in order to get the mechanics of this?