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:

Which are then used to derive:

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

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

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.

subscriptions_active

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)

days_in_status

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)

subscription_status

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.

TBD…

Data Wishlist

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