0 Replies Latest reply on Jan 8, 2018 11:02 AM by Nirav Gupta

    Retention cohort analysis - day wise + week wise + month wise

    Nirav Gupta

      Hi Friends,

       

      I have a sheet where i have to show retained customers for a specific duration.

       

      Available data and columns:

      Start date: signup/starting date of customer

      Cancellation date: cancellation date of customer

      Account id: unique identifier to count the customers

       

      Rows and columns in sheet (already tried):

      Column: Cohort column: day/week/Month number of the selected duration.

      Row: Start for time period: day/week/Month of exact start-date in available data, for selected duration.

       

      The retained customers can be calculated by their lifetime with the company (length of contract) : start date - cancellation date.

       

      The filters will be :

       

      Start date picker: user will select a start date from which he wants to see the data.

      End date picker: user will select a end date till which he wants to see the data.

      Days/weeks/months dropdown: This will be the aggregation/arrangement of data in which the user wants to view the data. Example:

                                                          -> if start date is selected as 1/1/2017 , and end date as 1/20/2017, and dropdown is selected as days, then the view will be adjusted and retention for each day of those                                                             20 days will be shown. Like day 1 , day 2 , day 3 ...etc or directly the dates can be shown

                                                          -> For the same duration, if from dropdown, 'weeks' is selected, the weekly retention will be shown. like week 1, week 2, week 3...etc.

                                                          -> if from dropdown, 'months' is selected, the monthly retention will be shown. like month 1 , month 2...etc.

       

      Attached is a sheet which i have already tried (monthly retention cohort sheet), and the desired sheet will be under selectable retention.

       

      The expected results should be in above format                                         

                                                           Total Retained Month

      ( when date selected from filter is: start date - 1st jan, end date - 31st april , and from dropdown, 'months' is selected)

      Start date month        Total Sign Up                  Jan   Feb  March April

        Jan                                   100                            80   70      80      60 

      Feb                                    80                             60   50      60      30 

       

      ( when date selected from filter is:  startdate - 1st jan, end date - 4th jan , and from dropdown, 'days' is selected)

      Start date month         Total Sign Up                        day1   day2  day3 day4

        1st Jan                                    10                              10        9        9        8

      2nd Jan                                   12                              12       11     10      10

      2nd Jan                                    15                             15       14     14     13

      2nd Jan                                    10                            10         9        8        8

       

      ( when date selected from filter is:  startdate - 1st jan, end date - 21st jan , and from dropdown, 'weeks' is selected)

      Start date month         Total Sign Up                        week 1st-7th   week 8-14th  week 15th-21st

      week 1st-7th                                30                                  30                 28                   27

      week 8-14th                                35                                   34                  33                   30

      week 15th-21st                            33                                  31                  27                   26

       

       

      Please do help me with formula and calculations that will be done for parameters and date conversions.Jennifer VonHagel