3 Replies Latest reply on Nov 14, 2017 3:01 PM by Jatin Kakani

# Help Needed with Cohort Analysis

Hi folks,

I am doing a retention cohort analysis to see what percentage of merchants activated in a month expired in 0 months after activation , 1 month after activation, and so on.

However at each point the total number of active merchants should change to the number in previous month less the merchants expired in previous month.

For example,

For Jan 2015, there were total 376 active merchants for channel local. 11 of them churned in month 1. So churn percent would be 11/376 = 2.9%
In month 2, 11 more merchants churned. Now churn percentage should be 11/(376-11) = 3.01%.

I have created dummy data and attached the packaged workbook.
Could you please check and help?

Thanks.

Regards,

Jatin

• ###### 1. Re: Help Needed with Cohort Analysis

Check, if this is what you need:

(IF NOT ISNULL(count([months_since_activation_exp]))

THEN ([No. of merchants])

END)/(IF INDEX()=1 THEN[Total merchants] ELSE ([Total merchants]-(IF NOT ISNULL(count([months_since_activation_exp]))THEN ([No. of merchants])END))END)

• ###### 2. Re: Help Needed with Cohort Analysis

workbook attached

• ###### 3. Re: Help Needed with Cohort Analysis

Thanks Deepak !

However the calculation is incorrect.

What this calculation is doing is taking the difference between total active and churned merchants in the same bucket (months_since_activation_exp)

For example, Activation month: Jan 2015, Month 3: The calculation should be: 29/(376-11-11) = 8.2%

whereas what it is doing is: 29/(376-29) = 8.4%

Also I could not use running sum because:

1. I have hidden one bucket "null" which means that the accounts are active hence months since activation expiration is null.

2. Running sum will also take current bucket into consideration.