pythonpandasdataframedata-analysis

How to calculate "churned" customers in Pandas? (Customers who have stopped buying regularly)

I've cleaned and formatted some data. Among others, I have these columns: Name, Order Date, and Subscription (which has booleans).

How would you acheive this in pandas?

Example DataFrame:

    Date    Name                Subscription
2020/06     Super Mario             False   

2020/06     Princess Peach      False 

2020/06     Bowser              False   

2020/06     Koopa               False   

2020/06     Yoshi               False

Many of these customers show up with multiple dates.

Solution

  • Customer churn is when existing customers stop doing business with you. This can mean different things depending on the nature of your business. Examples include:

    Cancelation of a subscription

    1. Closure of an account
    2. Non-renewal of a contract or service agreement
    3. Consumer decision to shop at another store/use another service provider

    Before you can figure out what your churn rate is, you need to decide how you’re going to quantify actions such as those above and agree on what defines customer churn for your business. Based on the sample data that you have shared, best bet would be to calculate, Total number of customers lost during a specific period. You need to define time frame to consider customer as Churned. For ex. Customer which have not purchased in past 30 days, 60 days etc based on the business you are looking at.

    Sample Data:

    import pandas as pd
import datetime as dt
df = pd.DataFrame({'Date':['2020-06-30','2020-05-05','2020-04-10','2020-02-26'],'Name':['Super Mario','Princess Peach','Bowser','Super Mario'],'Subscription':['False','False','False','False']})
df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

    Using the data we will get max of transaction date for each customer, and decide if customer has churned or survived.

    last_trans=df.groupby('Name')['Date'].max().reset_index()

    Get Current date for calculating duration for last purchase.

    today=pd.to_datetime(dt.date.today())
last_trans['last_purchase']=today-last_trans['Date']
last_trans['last_purchase']=last_trans['last_purchase'].apply(lambda x: x.days)

    I am considering 90 day window to consider if a customer has churned. (i.e. If customer has not purchased within last 90 days I am considering it as Churned else Survived).

    last_trans.loc[last_trans['last_purchase']>90,'Category']='Churned'
last_trans.loc[last_trans['last_purchase']<=90,'Category']='Survived'