Forum Discussion

AmeenVanakar's avatar
AmeenVanakar
Advocate II
1 year ago
Solved

Customer Retention Cohort Analysis - Help Needed

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

 

Need Help
  • AmeenVanakar's avatar
    AmeenVanakar
    1 year ago

    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

     

3 Replies

  • AmeenVanakar's avatar
    AmeenVanakar
    Advocate II
    1 year ago

    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