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

    Retention analysis for customers retained

    Nirav Gupta

      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.

       

       

       

       

      Elissa FinkAmy SchneiderForums Zhouyi Zhang