Retention Rate from Month 0

zfeinberg

Hi all,

Wondering if it is possible to calculate the retention rate of monthly cohorts by calculating the number of active users in any month over the initial month of acquisition (Month 0). I have created the table below showing monthly active users by cohort. An example of what I'd ideally calculate is in the 2023-1 row taking Month 2 total of 7,402 divided by Month 0 total of 18,051. The Months from Acq is a beastmode using period_diff function.

Thank you in advance!

DashboardDude

Hi @zfeinberg ,

Not sure if this will help, but I hope it does:

https://www.loom.com/share/bace71ac56a745248aec0cf08a4358fc?sid=a37525ae-ab30-4695-b146-59228d1cc322

marcel_luthi

It depends on what your raw data looks like. If the card you showed us is a Pivot Table, you can do this purely with Beast Modes. I'll assume the values you have are a COUNT of activities for this purpose.

In this case a Beast Mode like the one below might do the trick (FIXED is a powerful function once you figure it out)

COUNT(`Id`)/AVG(COUNT(CASE WHEN `Months_from_acq` = 0 THEN `Id` END) FIXED (BY `Acquisition Month`))

In this case since Months_from_acq is a beast mode, you might need to write your logic in there preferably in between parenthesis, unless your instance is able to reference Beast Modes inside Beast Modes already (I don't think that is GA yet and can't wait for it to be).

Hope this helps :)

DashboardDude

Hi @zfeinberg ,

So all of these numbers divided by month 0?

zfeinberg

Yes, that's right. Looking to see retention from month 0 over time.

DashboardDude

Hi @zfeinberg ,

Not sure if this will help, but I hope it does:

https://www.loom.com/share/bace71ac56a745248aec0cf08a4358fc?sid=a37525ae-ab30-4695-b146-59228d1cc322

zfeinberg

That's exactly what I'm trying to do. Assuming I need to get my data into a new table since the Months from Acq is a beast mode and not an actual column in the original dataset?

marcel_luthi

It depends on what your raw data looks like. If the card you showed us is a Pivot Table, you can do this purely with Beast Modes. I'll assume the values you have are a COUNT of activities for this purpose.

In this case a Beast Mode like the one below might do the trick (FIXED is a powerful function once you figure it out)

COUNT(`Id`)/AVG(COUNT(CASE WHEN `Months_from_acq` = 0 THEN `Id` END) FIXED (BY `Acquisition Month`))

In this case since Months_from_acq is a beast mode, you might need to write your logic in there preferably in between parenthesis, unless your instance is able to reference Beast Modes inside Beast Modes already (I don't think that is GA yet and can't wait for it to be).

Hope this helps :)