Google Sheet - Defining Churn based on the set of criteria

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.

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