How to perform a customer cohort analysis in Pandas

Cohort analysis is unlike most other customer segmentation techniques in that it typically uses a time-based element. It’s typically used to segment customers into groups, or cohorts, based on their acquisition date so that their behaviour can be examined over time.

For example, segmentation looks at customers at a given point in time, such as their current [RFM score] (/data-science/how-to-assign-rfm-scores-with-quantile-based-discretization). However, unlike the RFM model cohort analysis looks at a given metric at multiple points in time, allowing you to see how their behaviour changes.

While cohort analysis can be performed across the whole customer-base, it’s most powerful when it is combined with another segment or categorical variable so the behaviour of multiple cohorts can be compared.

My Master’s research, for example, studied how customers differed according to the level of discount they were given on their first order. Deep discounting meant customers were less likely to be retained, and the customers spent less and needed further big discounts to get them to repurchase.

Similarly, customers acquired via certain marketing channels, or through purchases of specific products or product categories, can differ markedly in their long-term business value, and cohort analysis can help you identify this and adapt your marketing strategy accordingly.

Analysing cohorts

Good cohort analysis usually starts with a business question. For example, my customer acquisition research was aiming to identify whether the company should use deep discounting via coupon codes to acquire new customers quickly and cheaply, or whether it should favour the slower and more expensive approach of using paid search advertising for new customer acquisition.

To perform this study, the first step was to assign customers to cohorts, and the second was to examine the behaviour of the cohorts over time. Cohorts can be constructed in several ways. The most common approach is to put all the customers acquired during a calendar month into a given “acquisition cohort”.

For future periods, various metrics can then be calculated over time, such as the number of orders placed, the AOV, or the number of items in each customer’s basket. However, cohorts are easy to create based on other factors, such as the marketing channel used, the level of discount offered, and countless other things. Here’s how it’s done.

Load the packages

Most cohort analysis work can be undertaken solely within Pandas, however, I’m also using the Python operator module for one function, and Seaborn and Matplotlib for some data visualisation. Anything you don’t have installed can be loaded into your environment using pip3 install packagename.

import pandas as pd
import operator as op
import seaborn as sns
import matplotlib.pyplot as plt

pd.set_option('max_columns', 10)

Load the data

Cohort analysis requires standard transactional data, that we can generate from a transactional item dataset. This needs to include the order_id, the customer_id and order_date, plus any metrics you wish to calculate.

The order_date column needs to a DateTime, which you can apply automatically when loading the data using the parse_dates argument on read_csv(). As the functions I’ve written below specifically reference these column names, you might want to rename your dataframe columns to match.

df = pd.read_csv('transaction_items.csv', parse_dates=['order_date'])
df.head()

Calculate the order cohort and acquisition cohort

For this simple demonstration of the process, our Pandas dataframe includes all customers acquired during a period. However, to modify this and select only those acquired via a specific channel during a period, all you need to do is add the appropriate categorical variable to your dataframe and filter accordingly before splitting the cohorts.

The acquisition cohort is a label which identifies the period during which each customer was acquired. Depending on the size of your dataset and the frequency at which your customers order, you might want to use monthly, quarterly, or yearly cohorts. The function below creates these for us.

By default, this uses a monthly cohort, so a customer ordering on 2017-04-07 04:55:58 would be placed in a cohort called 2017-04. If this was set to quarterly, they’d be placed in cohort 2017Q2. The new cohort labels get added to a DataFrame containing the unique customer_id, order_id, and order_date.

def get_cohorts(df, period='M'):
    """Given a Pandas DataFrame of transactional items, this function returns
    a Pandas DataFrame containing the acquisition cohort and order cohort which
    can be used for customer analysis or the creation of a cohort analysis matrix.
    
    Parameters
    ----------

    df: Pandas DataFrame
        Required columns: order_id, customer_id, order_date
    period: Period value - M, Q, or Y
        Create cohorts using month, quarter, or year of acquisition
    
    Returns
    -------
    products: Pandas DataFrame
        customer_id, order_id, order_date, acquisition_cohort, order_cohort
    """
    
    df = df[['customer_id','order_id','order_date']].drop_duplicates()
    df = df.assign(acquisition_cohort = df.groupby('customer_id')\
                   ['order_date'].transform('min').dt.to_period(period))
    df = df.assign(order_cohort = df['order_date'].dt.to_period(period))
    return df

df = get_cohorts(df, period='Q')
df.head()

Calculate the “retention” for each cohort

Next, we’ll create a function that counts how many of the customers acquired in a given cohort purchased again in subsequent periods. It’s common in cohort analysis to call this customer retention, but arguably it’s just a repurchase metric.

As with the above function, the period used can be modified using the period argument. This will create a Pandas dataframe containing the count of unique customers who purchased within each subsequent period. This is only handling counts, but you could easily change this to calculate revenue generated, AOV, or any other metrics you wish to measure.

def get_retention(df, period='M'):
    """Calculate the retention of customers in each month after their acquisition 
    and return the count of customers in each month. 
    
    Parameters
    ----------

    df: Pandas DataFrame
        Required columns: order_id, customer_id, order_date
    period: Period value - M, Q, or Y
        Create cohorts using month, quarter, or year of acquisition
    
    Returns
    -------
    products: Pandas DataFrame
        acquisition_cohort, order_cohort, customers, periods
    """

    df = get_cohorts(df, period).groupby(['acquisition_cohort', 'order_cohort'])\
                                .agg(customers=('customer_id', 'nunique')) \
                                .reset_index(drop=False)
    df['periods'] = (df.order_cohort - df.acquisition_cohort)\
    .apply(op.attrgetter('n'))

    return df

retention = get_retention(df)
retention.head()

Create a cohort matrix

Next, we’ll use the above function to create a cohort matrix. This is a dataframe which shows each acquisition cohort on its own line and shows the number of customers who purchased in each subsequent period. If you set the percentage argument to True, the function will return the percentage of customers, instead of the raw numbers. As with the previous functions, these can be monthly, quarterly, or yearly.

def get_cohort_matrix(df, period='M', percentage=False):
    """Return a cohort matrix showing for each acquisition cohort, the number of 
    customers who purchased in each period after their acqusition. 
    
    Parameters
    ----------

    df: Pandas DataFrame
        Required columns: order_id, customer_id, order_date
    period: Period value - M, Q, or Y
        Create cohorts using month, quarter, or year of acquisition
    percentage: True or False
        Return raw numbers or a percentage retention

    Returns
    -------
    products: Pandas DataFrame
        acquisition_cohort, period
    """
    
    df = get_retention(df, period).pivot_table(index = 'acquisition_cohort',
                                               columns = 'periods',
                                               values = 'customers')
    
    if percentage:
        df = df.divide(df.iloc[:,0], axis=0)*100
    
    return df

Monthly cohorts

df_matrixm = get_cohort_matrix(df, 'M', percentage=True)
df_matrixm.head()

Quarterly cohorts

df_matrixq = get_cohort_matrix(df, 'Q', percentage=True)
df_matrixq.head()

Annual cohorts

df_matrixy = get_cohort_matrix(df, 'Y', percentage=True)
df_matrixy.head()

df_matrix = get_cohort_matrix(df, 'Y', percentage=False)
df_matrix.head()

Creating cohort analysis heatmaps

Finally, we can create a heatmap to visualise our cohort. For each of the cohorts in our cohort matrix, this shows the percentage of the acquired customers who came back and purchased within a later period. Note that this isn’t the same as customer retention, which records the percentage of customers who came back up to a given period.

del df_matrixq[0]

f, ax = plt.subplots(figsize=(20, 5))
cmap = sns.color_palette("Blues")

monthly_sessions = sns.heatmap(df_matrixq, 
                    annot=True, 
                    linewidths=3, 
                    ax=ax, 
                    cmap=cmap, 
                    square=False)

ax.axes.set_title("Cohort analysis",fontsize=20)
ax.set_xlabel("Acquisition cohort",fontsize=15)
ax.set_ylabel("Period",fontsize=15)
plt.show()

If you look at cohort 2017Q2, you’ll notice that customers acquired here are more likely to come back at the same point each year, implying that this is a valuable seasonal cohort to this business. The Q2 periods in future years don’t follow exactly the same pattern, so it would be worth investigating how these customers were acquired as they are behaving differently to the rest.

For this company, once this is known, it might be worth spending more on acquisitions during this period using the same technique, since more of the customers are likely to return than those acquired in other periods. It also shows that a single site-wide cohort analysis is really just the start, and that drilling-down through the data is often required using simple modifications of the process above.

Matt Clarke, Sunday, March 14, 2021