The Retention Rate Analysis Notebook is used to understand Retention Rate of a brand for active and lapsed users for the given time period.
What is Retention Rate?
Retention rate is the ratio of the number of retained customers to the number of total customers shopped before the given date. This data helps brands to understand the impact of the engagement strategies which they are using to attract the customers. For example, if retention rate is coming down for a month, then they can further deep dive into their engagement strategies which they used in the previous month to find the factors for less retention.
Understanding the Output
Cmd 16, 17 & 18 has visual representation of year wise monthly retention rate, Lapsed Winback Repeater Rate & Lapsed Winback One Timer Rate respectively.
Steps to run the notebook
- Input the Org ID of the brand
- Start date and End date for your analysis
- Provide lapsed period and Inactive Period days of your brand
- Input the column name of geographical level 1 and geographical 2 from the zone_tills table of your brand.
- Provide the database and table name of your product master.
- Input the primary key column name to join with bill_lineitems item_code
- Input the column name for the main category from the product table.
This column should not contain any duplicate values. For example, if your column has 'Gold' and 'GOLD' in the main category then kindly change it to either one before running this notebook otherwise we will get duplicate categories in our analysis.
- Make necessary changes in the SQL code to add any other filters for the main category tag in cmd 10 line 51.
The sample code has '-' and 'null' values tagged as others. You can change it according to your brand's data.