Creating Cohorts | Python
Picture this — you’re on a journey to make a successful product. A few months in and your database says you’ve been adding a good volume of new customers day in and day out. But when you check the company’s profits, let’s just say the numbers aren’t moving.
Turns out the new folks have made their orders but the previous ones haven’t been returning, which implies your ‘Retention Rate’ is low. Understanding this phenomenon may take a few hours (speaking from experience).
That’s when I started thinking — there must be a simpler way to analyse customers behaviour to understand and improve the Retention Rate. Turns out, there is a much simpler way. Curious? Read on!
> Breathe In — Breathe Out, Lets go!
First of all we need to decide on who are old customers and who are the new ones, then we need to figure out if they came back or not, if they came back then after what time they did or vice versa. Too technical? Let me make it a bit simpler, let’s do ‘Cohort Analysis’.
Cohort — A group of people.
Cohort Analysis — Analyzing a group of people.
Straight to the point, Cohort Analysis is to make a ‘group of people’ with a common attribute within a certain segment and comparing them over a range of timeframes. This gives you a basic understanding and an overview of behaviour of that group. And yes, you can compare multiple groups at once too.
> Seems Interesting, Go on!
Cohort Analysis is the most important weapon to understand the user behaviour and their trends. Our aim is to make revenue and not to collect redundant user data. Acquiring new users is one thing and maintaining a long term relationship with the old ones is another. Cohort Analysis helps to determine the lifespan of a user which can be important while making crucial business decisions.
Cohort Analysis offers a more precise and lucid perspective on user data rather than declaring the performance of your company by mixing up growth and churn rates.
> Alright, but what/how is Cohort?
Cohort Analysis was first introduced by a Mathematician/Doctor named Frost in 1935 to study diseases experienced by groups of people born in different time periods.
Let me show you what a cohort looks like:
My first reaction to this was What the Frost!
But turns out it is easier to understand Cohorts than to see them! Trust me, You can read the cohorts without even understanding the data set.
Lets read the above snippet, lets say you run an e-commerce business:
- X axis denotes the ‘Incrementing Months’ and Y axis denotes ‘Time Periods’ in the form of months.
- Cohorts are usually read in a horizontal manner
- Navigating on to 2011–04 on Y axis:
- 100% is right above the ‘month range’ 0, this implies the Total New Users who lets say ordered something from your platform in that month (2011–04: April).
- Going ahead Horizontally, towards the right side of 100% now we see 27% which is right above the ‘month range’ 1, this means out of same New Users of the month April, now 27% of them are ordering again in the 1st next month to April (May).
- Doing the same thing again, Going ahead Horizontally, now we see 21% right above the month range 2, this again implies that of New Users who ordered in the month of April, out of all 100%, 21% of them are buying again in the 2nd next month to April (June).
Yep, it’s that easy to read the cohorts.
> Sure but How do I make it?
Since now you can read Cohorts, let’s make one.
Trust me, only 3 tools are required to make a Cohort: Access to Python, a data set and an ability to type.
Lets work around with a valid data set (Link to the dataset).
This is a dataset of an E-Commerce platform which gives us the insight about their orders, who placed these orders (Customer ID), at what price the ordered, quantity, location and an unique Invoice number.
Lets now jump on to out Jupyter notebook or VS code. Here starts the magic (link to my notebook — GitHub).
Get Jay Mewada’s stories in your inbox
Join Medium for free to get updates from this writer.
— Importing the libraries, well we need them:
import pandas as pd
import plotly
from plotly import express as px
import numpy as np
from operator import attrgetter
import matplotlib.pyplot as plt
import seaborn as sns— Thanks to Pandas, importing CSVs is an effortless job:
raw_df = pd.read_csv(r"online_retail_II.csv")
# Dataset -> https://www.kaggle.com/datasets/mashlyn/online-retail-ii-uci— Lets see how the data set looks like:
— Following the basic cleanup part, identifying and filtering out the Null values (Null Values are the ones where redundant rows are created due to loss of data):
raw_df.isnull().sum()
a = []
for i in raw_df.loc[raw_df['Customer ID'].isnull()].index:
a.append(i)# changing dtype from float to int
raw_df['Customer ID'] = raw_df['Customer ID'].astype(int)
raw_df.drop(index=a, inplace = True)
raw_df.isnull().sum()
— Remember where I had mentioned ‘making a group of people sharing a common attribute over a certain classification’? Lets now group the customers
- The common attribute here is the activity of ordering products.
- And the ‘certain classification’ for making a group would be their First Ever order.
- We can group them on monthly basis of Customers first order — “This many users did their first order in this month”
— Identifying month their First ever order:
# Converting the Invoice date to Month
raw_df['InvoiceDate'] = pd.to_datetime(raw_df['InvoiceDate']).dt.to_period('M')
raw_df1 = raw_df[['Customer ID', 'InvoiceDate','Invoice', 'Country']]
# Identifying Customers first ever Month they ordered
mindate = raw_df1.groupby('Customer ID').agg({'InvoiceDate': ['min']})
mindate.head()
# making a dataframe of Customers and their first Month
mindate.columns = mindate.columns.droplevel(-1)
mindate.reset_index(inplace=True)
mindate.rename(columns={'InvoiceDate':'min InvoiceDate'}, inplace = True)
mindate
Lets Plot the unique classifier against all the Customers (Vlookup in easy terms):
df = raw_df1.merge(mindate, how='left', on = 'Customer ID')
df
Customer ID here is unique parameter for every user, ‘InvoiceDate’ is the month of the each order and ‘min InvoiceDate’ is the Month of the first order of each customer.
The task now here is to identifying the incremented months which will help us to classify if they ordered in the next month or not. And to do so, we have already Identified which month they came from — that becomes our 0th month, and if they came after two months, we just subtract the 2nd month’s Invoice date and their First Order’s date (‘min InvoiceDate’) resulting in month difference of 2 and this will place them right in the box which is vertically above ‘2’ on the ‘Month Range’ (X Axis) and next on to their Unique Classifier (‘min InvoiceDate’) on Y axis.
df['Month range'] = (df['InvoiceDate'] - df['min InvoiceDate']).apply(attrgetter('n'))
df.rename(columns={'min InvoiceDate':'Month'}, inplace = True)
df
Well, now we have every thing we need, Lets make a pivot table out of this!
With ‘Month range’ in columns, ‘min InvoiceDate’ in Rows and counting the unique customers in their respective ‘Month Range’ and ‘min InvoiceDate’, we have something like this:
main = df[['Customer ID','Month','Month range', 'Country']]
main.drop_duplicates(inplace= True)
cohort = main.pivot_table(index='Month', columns='Month range', values='Customer ID', aggfunc='count')
cohort
Alright! It already looks like a Cohort! A few changes here and there and we’ll have it what we require.
Since we need those number in percentage of the 0th month, lets divide every values to it 0th month value:
cc = cohort.iloc[:,0]
cohort = cohort.divide(cc, axis = 0)
cohort
We’re almost there! We have the layout ready, we just need to put some colours into it and convert these number into ‘%’, thats why we have Matplotlib and Seaborn!
plt.figure(figsize=(20, 10))
sns.heatmap(cohort, annot = True,cmap="flare", fmt=".0%")
plt.show()Done?! But wait, the above cohort represents data over a range of years, remember ‘certain classification’?
Along with Invoice date we also have another data about the location from where the order was made, well lets use it!
Adding a few extra lines onto our script, instead of using ‘Months’ lets insert ‘Country’ into the Pivot and lets see what happens:
cohort_country = main.pivot_table(index='Country', columns='Month range', values='Customer ID', aggfunc='count')
cc_country = cohort_country.iloc[:,0]
cohort_country = cohort_country.divide(cc_country, axis = 0)
plt.figure(figsize=(20, 10))
sns.heatmap(cohort_country, annot = True,cmap="flare", fmt=".0%")
plt.show()
> And yes, done! Finally, Its this easy!
Having a big data set is of no use if it just exercises your hard drive or your cloud, we need it for us as well as our users when they come back. Cohorts play an integral part in understanding the user behaviour any business, it helps you know the status of your business and to analyze the patterns that your users are following. Whats the harm if investing a few minutes to read this article and 2 mins of python scripting can get you a overview of your customers!
Data Set — online-retail-ii-uci.csv
GitHub — jayxmewada/Cohort_Analysis/
LinkedIn — linkedin.com/in/jay-mewada/
