0
$\begingroup$

I am trying to look at the customer retention & churn by using cohorts for an e-commerce usecase. From a business perspective, a client is defined as churned if it hasn't performed any transactions in the following 3 months after the month of its first purchase.

Now, I've done some R&D but all studies that I've found look at the churn on a monthly basis and thus all the coding is done this way.

Now, I am trying to adapt my code in Python so that the aggregation is done on a 3-months level rather than at monthly level.

grouped = txn_new_cif.groupby(['CohortGroup', 'Txn_date'])
cohorts = grouped.agg({'CIF': pd.Series.nunique,
                     'Merchant': pd.Series.nunique,
                     })[![enter image description here][1]][1]

Monthly aggregation

What I would want is that for 2018-05 cohort, the 06-2018 to look at any transactions performed in 2018-06, 2018-07 and 2018-09. If there is any transactions performed by a user across this 3 months interval, then he would be considered active ( For 2018-07 projection, look for transactions in 2018-07, 2018-08 and 2018-09).

$\endgroup$
  • $\begingroup$ Can you update your question to include what txn_new_cif dataframe looks like? Perhaps provide some dummy records? Without seeing the data, I would suggest creating a new column that will be set to 1 if the Txn_date falls within the subsequent 3 months after the ChortGroup, 0 otherwise -- and then utilize that column to count the number churners. $\endgroup$ – Vishal May 9 '19 at 18:01
  • $\begingroup$ txn_new_cif contains client_id. How would you code that? $\endgroup$ – Remus Raphael May 9 '19 at 19:49
1
$\begingroup$

To demonstrate how this can be done, I created some sample/dummy records. See my answer below with comments explaining each step:

(1) Prepare data and create a column to capture all ID's of active customers

import pandas as pd
import numpy as np
from dateutil.relativedelta import relativedelta

# Create a dataframe with five customers from three cohorts
df = pd.DataFrame({'txn_new_cif': [1, 1, 2, 3, 4, 5],
                   'CohortGroup': [np.datetime64('2018-01-01'), np.datetime64('2018-01-01'),
                                   np.datetime64('2018-02-01'), np.datetime64('2018-02-01'),
                                   np.datetime64('2018-02-01'), np.datetime64('2018-03-01')],
                   'Txn_date': [np.datetime64('2018-01-30'), np.datetime64('2018-07-01'),
                                np.datetime64('2018-05-01'), np.datetime64('2018-08-01'),
                                np.datetime64('2018-06-01'), np.datetime64('2018-12-30')]})

# Count the number of days between each transaction date and the cohort date
days_diff = df['Txn_date'] - df['CohortGroup']

# Convert into integer
df['Diff_days'] = days_diff.dt.days

# If the transaction occured during the first 120 days (30 days of cohort month + 90 days)..
# Then those customers are considered as 'active'
# Capture the ID of those active customers
df['Active_cif'] = df.loc[df['Diff_days'] <= 120]['txn_new_cif']

# Find the date of each of the following three months after the end of the cohort month
df['First_month_after'] = [x+ relativedelta(months=2) for x in df['CohortGroup']]
df['Second_month_after'] = [x+ relativedelta(months=3) for x in df['CohortGroup']]
df['Third_month_after'] = [x+ relativedelta(months=4) for x in df['CohortGroup']]

# Calculate the cut-off date to determine whether the txn occured with the 1st/2nd/3rd month after
days_diff_1 = df['First_month_after'] - df['CohortGroup']
days_diff_2 = df['Second_month_after'] - df['CohortGroup']
days_diff_3 = df['Third_month_after'] - df['CohortGroup']

df['First_month_days'] = days_diff_1.dt.days
df['Second_month_days'] = days_diff_2.dt.days
df['Third_month_days'] = days_diff_3.dt.days

# Based on these cut-offs, determine if the activity took place within the 1st/2nd/3rd month after
df['Active_month'] = np.where(df['Diff_days'] <= df['First_month_days'], df['First_month_after'], 
                              np.where(df['Diff_days'] <= df['Second_month_days'], df['Second_month_after'],
                                      np.where(df['Diff_days'] <= df['Third_month_days'], df['Third_month_after'], None)))

df['Active_month'] = pd.to_datetime(df['Active_month'])

df.head()

(2) Aggregate data by cohort

# Count the total number of customers in each cohort
cohort_cust_ct = pd.DataFrame(df.groupby(['CohortGroup', 'Active_month'])['txn_new_cif'].nunique()).reset_index()

# Count the total number of customers who were active in each cohort
cohort_act_ct = pd.DataFrame(df.groupby(['CohortGroup', 'Active_month'])['Active_cif'].nunique()).reset_index()

# Combine both results
df_grp = cohort_cust_ct.merge(cohort_act_ct, left_on=['CohortGroup', 'Active_month'], right_on=['CohortGroup', 'Active_month'],
                             how='outer')

# Calculate churn rate for each cohort
df_grp['Churn_rate'] = 1 - (df_grp['Active_cif'] / df_grp['txn_new_cif'])

# View results
df_grp.head()
|improve this answer|||||
$\endgroup$
  • $\begingroup$ Hi Vishal! Thank you for your comment You solutions is good and elegant but further needs to be adapated to calculate the cohort of active users also 3 months after the CohortGroup eg: for cohort 2018-05 I should be able to see the sliding active clients in every months (that is clients who performed a txn in the followig 3 months). $\endgroup$ – Remus Raphael May 13 '19 at 14:28
  • $\begingroup$ I have updated my answer based on your comment above. Please note that the small set of dummy records that I have created are not sufficient to see this in full action. I'd recommend running it on your (larger) dataframe to see the results yourself. I've removed the OUTPUT from my previous answer because of this reason. $\endgroup$ – Vishal May 13 '19 at 20:04
  • $\begingroup$ Hi Vishal! Thank you for the update! Indeed, now the cohort is analyzed over a 3 monts period but I want this to be sliding month by month. Eg 05/2018 cohort to be analyzed from 05-2018 to 05-2019 and on each month the number of active users to be users who performed any transactions in one of the 3 months after the projected month. $\endgroup$ – Remus Raphael May 14 '19 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.