I have a report where i want to get number of customers retained each month. Below are the fields that i have :
1. signup date - date of signup
2. signup year - year of signup
3. signup month - month of signup
4. cancellation date - date of cancellation, no direct measure for cancellation count, have to do it by sum(float(countd(cancellation)))
5. start date - same as signup date
6. transaction date - date when customer did a transaction
7. signup count - measure for total signups , can be aggregated by year, month etc
Customer_count : COUNTD([Customer ID])
Cancels_running: RUNNING_SUM(COUNTD(FLOAT([Cancellation Date])))
Retained_count: int([Customer count])-int(RUNNING_SUM(COUNTD(FLOAT([Cancellation Date]))))
Running_retained: RUNNING_SUM(COUNTD(FLOAT([Global Signups].[Signup Date])))-RUNNING_SUM(COUNTD(FLOAT([Cancellation Date])))
Currently i have built the report on below two columns:
- X axis (columns): ‘month of start date’
- Y axis (rows) : ‘month of transaction date’
- Measure values as Customer_count
This above combination should give me closest possible number of customers that we retained in a month, since if a customer cancelled in next month, automatically he wont have a transaction and hence wont get counted in customer count.
1. Another logic which i was thinking was:
To have a calculated measure of retained_count = (signup count - cancellation count),
but this logic is not giving me right numbers, and i am also not sure for: which 2 date columns should i plot this retained_count measure?
2. Should we also subtract the customer_count from cancellation_count , if yes, how to include running total of cancellations to be subtracted from existing customer_count, and what will be the formula for this?
Its a very important report, so all quick and detailed help would be really appreciated.