topic Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis in Quick Measures Gallery

Customer Retention Part 3: Period Of Stay – Cohort Analysis

amitchandak — Mon, 05 Apr 2021 16:20:16 GMT

Objective:

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

Cohort Analysis is studying the behavioral analysis of customers.

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.

Columns

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

Table

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

Measures

Customers = DISTINCTCOUNT(Sales[Customer Id]) 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)

eyJrIjoiYWM4MGY3ZTUtZmZhZS00ZDQ4LWE1NzUtMGUwMDc3N2U4MmI0IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9

Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis

Annie_Do_1413 — Tue, 27 Sep 2022 02:43:54 GMT

Great! Thanks for your sharing

Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis

ewu — Wed, 29 Mar 2023 01:21:20 GMT

After the First Sales and Customer Age Columns are done, and then Customer Age Bucket Tables is created, then Measures steps for Retain %, I guess, are created in the original data table? How is it linked to the Customer Age Bucket table? amitchandak Annie_Do_1413

Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis

amitchandak — Wed, 29 Mar 2023 01:55:32 GMT

ewu , Customer Age has been calculated using the first sales date and sales date. That has been joined with the dimension table. If needed we can keep the Age bucket in the sales table.

Power BI Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg

Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis

ewu — Wed, 29 Mar 2023 02:12:09 GMT

Thanks. I am stuck at the Measures steps - 3rd step in the response above. How do you keep the age bucket in the sales table?

Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis

amitchandak — Wed, 29 Mar 2023 02:17:25 GMT

ewu ,There is column rename value -> age (is that creating confusion)

In that case you create a new column in sales

Customer Age Bucket = "Month " & format([Customer Age],"00")

You can also use measures like

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

Re: Customer Retention Part 3: Period Of Stay – Cohort Analysis

waisal01 — Sat, 02 Mar 2024 07:00:22 GMT

Hi! Thanks for sharing the file. Its awesome.
From your COHORT chart, i would like to change the visualization from "First Month Year" to display "First Year" and observe the trend for next 24 months, need your advice to amend the code.