FAQ - Skio Subscription Status

Current Practices

Active, inactive, and dunning subscriptions and subscribers are ultimately derived from the subscription table under skio_master_raw_transfer with schema:


The relevant fields necessary to perform the calculations are:


This is either one of ['FAILED' , 'ACTIVE' , 'CANCELLED']


This is either one of ['DUNNING', 'CHURNED', 'PERMANENTLY_CANCELLED', null]

Which are then used to derive:


If the status = ‘ACTIVE’ then this is set to true otherwise it is false


If the statusContext = 'DUNNING' or status = 'FAILED' and statusContext is null then it is true otherwise it is false


If status = 'CANCELLED' and statusContext != 'CHURNED' or statusContext is null then it is actively cancelled

If statusContext = 'CHURNED' then it is passively cancelled

Everything else is null


If there is a subscription belonging to the subscriber that was created before and cancelled then true otherwise false

is_return = coalesce(
										lag(createdAt) over (partition by storefrontUserId order by createdAt) < createdAt
										lag(status) over (partition by storefrontUserId order by createdAt) = 'CANCELLED'

The old and new fields from the subscription table are then joined onto a date spine to calculate:


Joined on cancelled_at and counts active cancellations using cancel_type


Joined on cancelled_at and counts passive cancellations using cancel_type


Joined on dunning_at and counts dunning subscriptions using is_dunning


New and returning subscriptions using is_return less cancelled subscriptions and trials (where applicable)

The logic for subscribers is just an extension of subscriptions with additional rollup logic for handling multiple subscriptions. For example, if you have 10 subscriptions but one of them is in dunning on a certain date, then you’re counted as a dunning subscriber on that date.

As we can conclude from the above, this approach relies on the most recent versions of subscription records. And the particular reliance on statusContext for dunning given the field's static nature is problematic. Hence the decision to change the status quo and move towards a new framework.

Future Framework

The dunning dashboard and its underlying data models will be moving away from relying purely on the subscription table and will instead leverage the billing_attempt table which chronicles the charge history of each subscription with schema:


However, there are subscriptions in the table which do not have any billing attempts recorded.

Most of these are either subscriptions that were migrated or subscriptions that were not rebilled yet. In these cases, it is important to have a fallback framework to help classify these records.

This fallback logic we’re proposing repurposes some of the existing logic from above while avoiding the known issues with statusContext . The entire logic workflow of the framework is captured below.


Data Wishlist

Even with the the architecture above, there are improvements that can be made that will likely need to come from upstream:

1) Certain subscriptions appear to reactivate using the same subscription_id

—> Need a flag and a timestamp for when this happens

2) Expanded error codes for billing attempts

—> ‘MAX_RETRIES’ will be very helpful in improving the logic for passive cancellations

3) The first billing attempts for all subscriptions and their orders

—> The first one through Shopify seems be omitted in certain cases

4) Changes in store-specific dunning settings over time

—> Need a flag and a timestamp for when this happens

5) “Never Cancel” flag for dunning

—> Need a flag to indicate if they should keep always trying with charge attempts for dunning subs