Google Sheet - Defining Churn based on the set of criteria-CodePudding

I have a dataset with all the subscriptions I receive on a monthly or yearly basis from my clients. There are 3 columns: client IDs, Subscription charge at (date), subscription type (monthly or yearly).

I want to add 2 columns to get visibility on

whether a client has churned or is still an existing client.
The last subscription date

To be consider a churn, a client has to:

Not have made any new subscription during the last 90 days if the last subscription type is monthly
Not have made any new subscription during the last 1 year and 90 days if the last subscription type is yearly

I've made a dummy sheet to help better understand my issue. Any help on the formulas in column E and F would be highly appreciated.

CodePudding user response：

You can get the formula and the result in Neo Test sheet in your dummy sheet

As you can easily get today's date, here's a working formula giving your expected result :

=IF(
    D3="Monthly",
    SI(
        DAYS($A$1,F3)<=90,
        "Still a client",
        "Churned"
    ),
    IF(
        DAYS($A$1,F3)<=365 90,
        "Still a client",
        "Churned"
    )
)

Checks the subscribtion type, then get the day's difference ( you can replace $A$1 by Today() ). If it's before the deadline it's still a client, if not it's churned