Retention Rate from Month 0

zfeinberg
zfeinberg Member
in Cards, Dashboards, Stories

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.

image.png

Thank you in advance!

Best Answers

  • DashboardDude
    DashboardDude Coach
    Answer ✓

    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

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • marcel_luthi
    marcel_luthi Coach
    edited March 13 Answer ✓

    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 :)

Answers

  • DashboardDude
    DashboardDude Coach

    Hi @zfeinberg ,

    So all of these numbers divided by month 0?

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • zfeinberg
    zfeinberg Member

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

  • DashboardDude
    DashboardDude Coach
    Answer ✓

    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

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • zfeinberg
    zfeinberg Member

    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
    marcel_luthi Coach
    edited March 13 Answer ✓

    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 :)

Categories