Customer Retention Cohort Analysis - Help Needed

[AmeenVanakar]

Hi,

I have created a logic to find the number of repeat customers & I want to build a retention cohort over 24 months. However, I am unable to match retention numbers between 2 formulas that I created i.e. # Repeat Customer vs # Customer Retention. Any help will be great.

Here is the Repeat Customer Logic

_# Repeat Customers = COUNTROWS(FILTER(DISTINCT(Customer_Data[CUSTOMER MOBILE NO]), CALCULATE(DISTINCTCOUNT(Customer_Data[BILL NO]))>1))

Where:

Customer Data = CRM Extracted Data

CUSTOMER MOBILE NO = Unique Customer ID

BILL NO = Invoice (some invoices have multiple rows as the customer purchases more than 1 product i.e. more than 1 BARCODE)

The formula used to create the cohort

# Customer Retention =

VAR CurrentMonthAfter = SELECTEDVALUE('_Months After'[Value])

VAR CurrentFirstOrderMonth = SELECTEDVALUE(Customer_Data[_First Order Date EOM])

RETURN

CALCULATE(

    DISTINCTCOUNT(Customer_Data[CUSTOMER MOBILE NO]),

    FILTER(

        Customer_Data,

        EOMONTH(Customer_Data[BILL DATE],0) = EOMONTH(CurrentFirstOrderMonth, CurrentMonthAfter)

    )

)

Where:

1. _Months After = GENERATESERIES(0,24,1)

2. _First Order Date EOM =

VAR CurrentCustomer = Customer_Data[CUSTOMER MOBILE NO]

RETURN

CALCULATE(

    EOMONTH(MIN(Customer_Data[BILL DATE]),0),

    FILTER(

        Customer_Data,

        Customer_Data[CUSTOMER MOBILE NO]=CurrentCustomer

    )

)

3. BILL DATE = Customer invoice date

Result using _# Repeat Customers

Cohort Using # Customer Retention

Data Relationships

[lbendlin · Accepted solution]

New and returning customers – DAX Patterns

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similr question in the attached file.

Hope this helps.

[AmeenVanakar · Accepted solution]

Hi @lbendlin and @Ashish_Mathur 

Thanks for sharing solutions.

I repeated all my steps & found a logical reasoning to the differences in numbers between retention cohort vs repeat although they share same DAX.

Logic:

Since I am using End of month methodology, the cohort analysis is counting all the customers who purchase within the same month as 1 transaction. However I noticed a small group of customers who purchased multiple times within the same month.

Hence, if we check the repeat customers within May'23, the actual repeat is 128 (having more than 1 invoice)

whereas, if we conside all repeats by all customers using End of month methodology & want to know how many customer who purchased in Jun'23 have also purchased May'23, then the answer is 83 as shown in cohort analysis which uses End of month methodology.

Here is a screenshot

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similr question in the attached file.

Hope this helps.

[lbendlin · Accepted solution]

New and returning customers – DAX Patterns