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:
status
This is either one of ['FAILED' , 'ACTIVE' , 'CANCELLED']
statusContext
This is either one of ['DUNNING', 'CHURNED', 'PERMANENTLY_CANCELLED', null]
Which are then used to derive:
is_active
If the status = ‘ACTIVE’ then this is set to true otherwise it is false
is_dunning
If the statusContext = 'DUNNING' or status = 'FAILED' and statusContext is null then it is true otherwise it is false
cancel_type
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
is_return
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
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:
subscriptions_cancelled
Joined on cancelled_at and counts active cancellations using cancel_type
susbcriptions_cancelled_passive
Joined on cancelled_at and counts passive cancellations using cancel_type
subscriptions_dunning
Joined on dunning_at and counts dunning subscriptions using is_dunning
subscriptions_active
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