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 Deltas - Current vs New Framework

Compiled a spreadsheet here that looks at the subscription and subscriber metrics under the current vs new framework proposed. This dataset looks at a sampling of dates in the last few months for several merchants with large customer bases.

The most prominent deltas in these metrics stem from key changes made between the current framework (which relies on status, status_context, and cancel_type) and the new framework illustrated above which uses billing attempts (where there are billing attempts for subscriptions) and revamped logic borrowed from the current framework (where there are no billing attempts for subscriptions). These changes are discussed below:

1 - Dunning Subscriptions/Subscribers

Generally speaking, there will be a significant difference in the number of dunning subscriptions and subscribers due to the simple fact that status_context is currently never reset. Case in point, subscriptions and subscribers that have in fact exited dunning never have their initial status reclassified under the old framework. This has historically led to dunning becoming a terminal state even when there are subsequent billing attempts with different statuses under the current framework. Under the new framework, dunning will have clearly defined boundaries based on the success or failure of each charge with subscriptions and subscribers being re-evaluated with each charge attempt.

2 - Cancelled Subscriptions/Subscribers

In terms of passive cancellations, this will correlate closely with the changes discussed in dunning. But in terms of active cancellations, the shift here will depend on if there are subsequent charge attempts. Currently, if you have a cancelled_at date filled then you are simply considered as having actively cancelled on that date and will remain as such in perpetuity based on the logic in cancel_type. Yet, for many subscriptions and subscribers it’s possible that they later resume a billing schedule. Under the new framework, these subscriptions and subscribers will only be considered actively cancelled between the time they have been marked as having cancelled and the next charge attempt.

3 - First-time vs Returning Subscribers

Right now the logic around the is_return flag that's being consumed in counting subscriptions vs subscribers is different. As such, subscribers without the correct subscription criteria were being classified as returning when they were in fact first-time. Under the new framework, the number of first-time vs returning subscribers will be reflected accurately.

Finally, it is important to note we’ve officially separated out dunning subscriptions from the active subscriptions calculation. Previously these metrics were rolled up together but they will now be calculated individually and reported on as their own truly distinct categories.

New Framework Schema

The table is on a daily grain with each row being unique for the combination of datetime and subscription_id. The charge_id is non-unique and can be shared by a subcsription_id over many dates. This is because the model carries the status of a subscription from one charge attempt to the next charge attempt. Therefore, the days in between those attempts will necessarily share the same charge


New Framework Definitions

The following section defines how popular metrics are calculated using the new subscription status model.


Definition: The total number of active subscriptions on a given date as determined by the net inflow of subscriptions (new and returning) and outflow of cancelled and dunning subscriptions

Calculation: sum(subscriptions_new + subscriptions_returning - subscriptions_cancelled_active - subscriptions_cancelled_passive - subscriptions_dunning) over (partition by merchant_id order by date)


Definition: A subscription that was created and turned active for the first time

Calculation: case when days_in_status = 1 and subscription_created_date = date and subscription_status = 'ACTIVE’ and is_return = false


Definition: A new subscription activated by a subscriber with a previously activated subscription

Calculation: case when days_in_status = 1 and subscription_created_date = date and subscription_status = 'ACTIVE’ and is_return = true


Definition: A subscription that was actively cancelled

Calculation: days_in_status = 1 and subscription_status = 'ACTIVE_CANCELLATION’


Definition: A subscription that was passively cancelled

Calculation: days_in_status = 1 and charge_attempted_date = date and subscription_status = 'PASSIVE_CANCELLATION’


Definition: A subscription that was currently in dunning

Calculation: days_in_status = 1 and charge_attempted_date = date and subscription_status = 'DUNNING’


Definition: The number of calendar days that the subscription has been in its current status. Resets with each status change

Calculation: rank() over (partition by merchant_id, subscription_id, subscription_status order by date)


Definition: The status of the given subscription at the given date based on charge status and charge attempts. The status set is a mutually exclusive and collectively exhaustive list of [’ACTIVE’, ’RECOVERED’, ‘DUNNING’, ‘PASSIVE_CANCELLATION’, ‘ACTIVE_CANCELLATION’]

Calculation: See workflow diagram

Note: This is used for subscriptions WITH charge attempts

Walkthrough Examples

The following section highlights using examples how to think about the mechanics of the new subscription status model.


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