2 Replies Latest reply on Oct 3, 2017 6:06 PM by Nirav Gupta

# Retention analysis for customers retained

Hi,

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

Calculated measures:

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.

My questions:

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.

• ###### 1. Re: Retention analysis for customers retained

Hi Nirav, your twbx is connected to the data source. Can you extract the data with the workbook and re-attach it?

Thanks,
Jennifer

• ###### 2. Re: Retention analysis for customers retained

Jennifer, Thanks for replying back here, can i connect to you for this over mail?