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