cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cohort analysis - retention rate

Hi, I'm new to Power BI and I want to calculate and show retention rate on dashboard.

I have tables regarding user cohort data and we calculate retention rate as below.

 

ex.

Retention Rate for Android JP user who installed App A through Facebook network, campaign_1, Image_1 during 2020-01-01~2020-01-02

- Retention Rate (D+1) = (50+140)/(100+200)

Retention Rate for iOS JP user who installed App A through Google network, campaign_2, Video_1 during 2020-01-01~2020-01-02

- Retention Rate (D+1) = (30+40)/(50+100)

* Retention Rate (D+n) = retained_users with period n / retained_users with period 0

 

AppOSCountryNetworkCampaignAd GroupCreativeDatePeriodRetained_users
AAndroidJPFacebookcampaign_1ImageImage_12020-01-010100
AAndroidJPFacebookcampaign_1ImageImage_12020-01-01150
AAndroidJPFacebookcampaign_1ImageImage_12020-01-020200
AAndroidJPFacebookcampaign_1ImageImage_12020-01-021140
A

iOS

JPGooglecampaign_2

Video

Video_1

2020-01-01050
AiOSJPGooglecampaign_2VideoVideo_12020-01-01130
AiOSJPGooglecampaign_2VideoVideo_12020-01-020100
AiOSJPGooglecampaign_2VideoVideo_12020-01-02140

 

I want to show retention rate by variables (App, OS, Country, Network, Campaign, Ad Group, Creative, Date).

How can I solve this problem ?

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You cannot show the retention rate by date.  Remove the [date] column from your table visual.

 

lbendlin_0-1611368244958.png

 

Then it is easy to create the measure. For each "row" of the table visual identify if the period is 0. If yes, skip the row (return BLANK() or 1). If no, create a variable to calculate the sum of Retained_users , replacing the Period filter with 0. Lastly divide the sum of Retained_users for the current "row" by the calculated variable.

 

lbendlin_1-1611368691811.png

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

Please provide sample data in usable format (not as a picture) and show the expected outcome.

Thank you in advance for your time and guidance..I am more than happy to attach a file the info below isn't clear. 

The formula for the Output is below

Month 3 Sum( Month active Column: rows 0-3 Jan-Jan 2021)/Total Column :Jan-Mar 2021

Month 5 Sum (Month Active Column: rows 0-5 Jan-Nov)/Total Column :Jan-Nov

 

DATA TABLE

04,1033,6743,8584,0154,3915,9854,4205,3905,8012,9052,8803,7223,4213,7894,9482,885
12,5751,9172,0722,7293,0673,8032,7944,1694,2212,0131,9932,5352,5183,5983,505 
21,4049611,2071,3241,5602,0951,5931,6481,9399691,0141,7591,1901,204  
31,3079671,0481,4142,0422,3751,9631,6741,6621,2471,1261,3691,276   
48466809571,3961,6051,8911,3341,5131,9649248991,198    
57801,0331,1861,1171,2571,4499481,1991,206739689     
61,0639878388761,0441,1739448791,000560      
TOTAL46,79839,75942,52842,54152,16663,27149,46956,15259,59434,98434,89849,54841,96843,66658,53236,388
OUTPUT                
 Attrition               
Same Month (0)        8.8%               
M+1     14.9%               
M+2     17.9%               
M+3     21.0%               
M+4     23.7%               
M+5     26.2%               
M+6     28.2%               

Sorry for the view above it trimmed out the month headers on the columns when it converted it. I am more than happy to send an email if you have time

 

lbendlin
Super User
Super User

You cannot show the retention rate by date.  Remove the [date] column from your table visual.

 

lbendlin_0-1611368244958.png

 

Then it is easy to create the measure. For each "row" of the table visual identify if the period is 0. If yes, skip the row (return BLANK() or 1). If no, create a variable to calculate the sum of Retained_users , replacing the Period filter with 0. Lastly divide the sum of Retained_users for the current "row" by the calculated variable.

 

lbendlin_1-1611368691811.png

 

@lbendlin lbendlin

 

Johann1978_0-1626474422686.png

What I am struggling with is how to get the cummulative totals. (See examples from Excel)

I have spent a week wracking my brain on the caluclation to perform this, but I haven't been succesful.

 

Period 3 Example

Johann1978_1-1626474553355.png

Period 6 Example

Johann1978_2-1626474597553.png

I was trying to do a date diff based on the max date where the subscriber count wasn't blank, but I can't get it accross the finish line. I would GREATLY appreciate anyone who could help

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors