I have a table that tracks subscriptions for a specific product add-on. It tracks subscription movement for this product based on the unique account business id. Every time a user subscribes, upgrades, downgrades, or churns it adds a record with the new quota, business id, date of upgrade, month of upgrade, and the expansion or contraction MRR.
A churn is recorded very similarly to a downgrade or contraction record so I'm trying to come up with a way to tell the difference for reporting purposes. Basically if the business id doesn't occur in the list again, has a 0 quota, and a negative MRR then it's a churn. If it does occur again but has a 0 quota and negative MRR then it is contraction and not a churn.
I'd like to add a formula field in Airtable that based on the instance of the business id it labels the record with either New subscription (if it's the first instance of the business id in the table), expansion (if it's after the first instance of the business id AND has a positive MRR associated with it), contraction (if it's after the first instance of the business id, is not the LAST instance, and has a negative MRR associated with it), or churn (if it's the last instance of the business id AND there is a negative MRR associated AND the quota is listed as 0).
Is there a way to do this in a formula field? or if anyone has alternative suggestions I'm open to them! The business id field unfortunately is not a linked record field so I can only use the information recorded within this table.
I've included a screenshot of the records for an example.
