1.1 Concept
Retention analysis is an analysis model used to analyze user participation/activity. It examines users who have performed initial behaviors. After a period of time, there are still customer behaviors (such as login, consumption).
Retention is not only an indicator that can reflect customer stickiness, it also reflects the attractiveness of the product to users.
Calculation formula: the number of new users retained in a certain period of time (period a) in another period of days (period b) / the total number of new users (period a)
1.2 Expected effect
1.3 Implementation ideas
This example calculates the ratio of the number of users who log in on the same day, within one week, within two weeks and within three weeks after activating a product to the total number of logins.
Current day retention rate: number of users activated and logged in on the current day / number of users activated on the current day
Retention rate in the first week: the activation day is the dimension. Calculate: Logged-in user whose "Activate_login time difference" is 1-7 / Number of active users on activation day
Retention rate in the second week: the activation day is the dimension. Calculate: Logged-in user whose "Activate_login time difference" is 8-14 / Number of active users on activation day
.....
2. Example
Sample data:
366.User retention analysis.xlsx
Upload the data to FineBI.
2.1 Create a dashboard
Click "Dashboard> New Dashboard", set the name and location, and click "OK", as shown in the figure below:
Click "+", select the "User retention analysis" dataset, and click "OK", as shown in the figure below:
2.2 Calculate the retention rate of active users
Note: The sample data has already calculated the activate_login time difference. If the existing data has not been processed, you can use the new column time difference calculation.
2.2.1 Current day retention rate
Click "+", add a calculation indicator, and enter a formula: COUNTD_AGG(IF(Activate_login time difference=0, contact number, NULL))/COUNTD_AGG(contact number). The name of the input field is "Current day retention Rate", click "OK", as shown in the figure below:
Formula description:
| formula | description |
|---|
| IF (Activate_login time difference=0, contact number, NULL) | Determine whether it is the same day login, if yes, return the user's phone number, otherwise return a null value |
| COUNTD_AGG(IF(Activate_login time difference=0, contact number, null)) | Calculate the number of users who logged in on the day (de-counting the contact information of users who logged in on the day) |
| COUNTD_AGG(IF(Activate_login time difference=0, contact number, null))/COUNTD_AGG(contact number) | Calculate user retention rate: the number of logged-in users on the day/the number of all logged-in users |
2.2.2 Retention rate in the first week
Click "+", add a calculation indicator, and enter a formula: COUNTD_AGG(IF(AND(Activate_login time difference>=1, activate_login time difference<=7), contact number, null))/COUNTD_AGG(contact number). The input field name is "Retention rate in the first week", click "OK", as shown in the figure below:
The retention rates in the second week, third week, and fourth week are calculated in the same way.
2.3 Drag the calculation indicator in
Drag the "Earliest activation date" into the dimension axis, drag the retention rate indicator into the indicator axis, and set the "Earliest activation date" to be displayed as "Year Month", as shown in the following figure:
Get the monthly retention rate of activated users with the earliest activation date as the dimension.
2.4 Effect view
See section 1.2 of this article.
3. Conclusion analysis
The retention rate in the first week has dropped by more than 35% on average compared to the same day. It is necessary to increase user stickiness and increase product use value.
The relative decline of the retention rate in the fourth week has slowed down, indicating that a part of the conversion has been carried out, and it is necessary to carry out refined operation and management of these users to help users stabilize the conversion.
