I. An Overview of Retention Analysis
Concept
Retention analysis, an analysis model, is used to analyze users' engagement/activeness, examining customer behaviors (such as login and consumption) that still exists after a certain period of time among customers who have initial behaviors.
Retention is an indicator that not only reflects customer stickiness, but also reflects the attractiveness of a product to users.
Formula: new users' (in Period A) retention number in Period B after several days / the total number of new users in Period A
Preview
How to Implement
Calculate the ratio of the number of users who perform logins (starting from activating a product) within one week, two weeks, and three weeks to the total number of login users.
Daily retention rate: number of users who activate and log in a product on a day / number of users who activate on the day
First week retention rate: number of login users (Time difference is between 1 and 7) /number of activated users on the activation day (the dimension)
Second retention rate: number of login users (Time difference is between 8 and 14) / number of activated users on the activation day (the dimension)
II. Steps to Create a Retention Analysis
Sample data:Retention Analysis.xlsx
Upload the data to FineBI.
Creating a Subject
Click New subject in My Analysis, click Local Excel > Upload data to add the data table Retention Analysis.
Preview the data added and click OK. The analysis subject is created.
Configuring a Component
Click Component.
Calculating the Retention Rate of Activated Users
Note:
The sample data has included time difference between activation and login. If there is existing data that has not been processed, you can use the time difference function to calculate.
Current Day Retention Rate
Click to add a calculation field, enter the formula COUNTD_AGG(IF(Time Difference Between Activation and Login=0,Telephone Number,null))/COUNTD_AGG(Telephone Number), enter the field name Current Day Retention Rate, and click OK.
Formula description:
Formula | Description |
IF(Time Difference Between Activation and Login=0,Telephone Number,null) | Check if a user logs in on the current day. If is, return to the user's phone number, otherwise return to null. |
COUNTD_AGG(IF(Time Difference Between Activation and Login=0,Telephone Number,null)) | Calculate the number of users logging in on the current day (remove the duplicate data of their telephone numbers). |
COUNTD_AGG(IF(Time Difference Between Activation and Login=0,Telephone Number,null))/COUNTD_AGG(Telephone Number) | Calculate user retention rate: number of users logging in on the current day / number of users activating on the current day |
First Week Retention Rate
Click to add a calculation field, enter the formula COUNTD_AGG(IF(AND(Time Difference Between Activation and Login>=1,Time Difference Between Activation and Login<=7), Telephone Number, null))/COUNTD_AGG(Telephone Number), enter the field name First Week Retention Rate, and click OK.
Retention rates are calculated in the same way for the second week, third week, and fourth week.
Dragging Calculation Fields
Drag The Earliest Activation Date into Dimensions and retention rates into Indicators.
Set The Earliest Activation Date to display as Year Month.
Set the format of retention rate fields as Percentage.
Thus, the retention rate of activated users in each month is obtained (take The Earliest Activation Date as the dimension).
Demonstration
Click View all data.
Conclusion Analysis
First Week Retention Rate has decreased by more than 35% compared to the average of Current Day Retention Rate. It is necessary to improve users' stickiness and enhance the usage value of the product.
Fourth Week Retention Rate has declined relatively slowly, indicating that some users has been conversed. It is necessary to operate and manage these users refinedly, stabilizing user conversion.
