Customer Retention Part 3: Period Of Stay – Cohort Analysis

[amitchandak]

Using PowerBI, analyze the customer behavior while making a purchase

Objective:  

Period Of Stay – Cohort Analysis provide visibility on how many customers were retained after their first date of purchase.

What is Cohort analysis.

As per Wikipedia

“Cohort analysis is a subset of behavioral analytics that takes the data from a given data set (e.g. an EMRS, an e-commerce platform, web application, or online game) and rather than looking at all users as one unit, it breaks them into related groups for analysis. These related groups, or cohorts, usually share common characteristics or experiences within a defined time-span. Cohort analysis allows a company to “see patterns clearly across the life-cycle of a customer (or user), rather than slicing across all customers blindly without accounting for the natural cycle that a customer undergoes.”

Cohort Analysis is studying the behavioral analysis of customers. In this blog, we will try to understand the customers and sales relationship by representing customers in groups or cohorts based on their first purchase ever in a store with their coming visits in a year.  

In this analysis both Axes are time. We have time on both row and column. We observe how the customers reduced over a period from time from the first date of purchase. This is retention over a period of time. Here we are creating a month bucket. Percent retain after n months.

Assume there are 100 new customers (consumers who made the first purchase in the store) in Jan 2020. Out of these 100, how many customers came back in the second month (Feb 2020). Then how many returned in the third month (March -2020) and so on for every month in 2020.

Implementation

We have 4 tables – Sales, Customer, Geography, and Item

Below are the new columns, we created to form the Cohort Analysis Graph.

• A new column - First Sales. You can also consider this as the first purchase made by the customer. The filtering is based on the Customer.
• Another new column named Age. This column describes the aging period of the purchase. Here, we take the Month difference of Sales Date and First Sales (in months).

First Sales = minx(FILTER(Sales,[Customer Id] =EARLIER([Customer Id])),[Sales Date])
Customer Age = DATEDIFF([First Sales],[Sales Date],MONTH)+1

We created two Date tables 

• Date Joined with Sales Date
• The first Date joined with First Sales.

Next created a series using generate series and joined it with the Age column.

Customer Age Bucket = ADDCOLUMNS(GENERATESERIES(1,max(Sales[Customer Age])+1) ,"Age in Month" , "Month " &[Value])

And then we created a matrix with First Sales Month on row and month Age on the column. This matrix is based on distinct customers.

Customers = DISTINCTCOUNT(Sales[Customer Id])

Then we Create Retain % based on the First Month

Retain % = CALCULATE(divide(DISTINCTCOUNT(Sales[Customer Id]),CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1)

Let us know what you think about these measures. Share your thoughts on Customer Retention Part 3.

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403

The file can found at https://community.powerbi.com/t5/Quick-Measures-Gallery/Customer-Retention-Part-3-Period-Of-Stay-Coh...

For a more detailed understanding of Cohort Analysis in Customer Retention, you can check out the webinar. You can also follow my channel (YouTube) and LinkedIn (profile) to get information on the upcoming webinars.