2 Replies Latest reply on Feb 20, 2018 12:50 PM by Jack Grinblatt

    Calculating User Retention Rates via Customer Segmentation - Issue w/ Solving Inactive and Reactivated Customer Segments

    Jack Grinblatt



      I am trying to conduct a retention analysis to better understand the changes in our product's user base. The posts I have reviewed thus far have been unhelpful at solving the particular issue I'm working on.


      The ideal outcome is a stacked bar graph with a date cadence on the X axis (e.g. weeks), the y-axis being a distinct count of customer ID's - and the colors of the stacked bars representing the five categories below:


      New Users (users who are active in the app this week and this is also their first week)

      Returning Users (users who are active in the app this week but this is not their first week)

      Inactive Users (users who are not active in the app this week)

      Reactivated Users (users who were present in the app at some point, were inactive for at least 1 week, and are active in the current week)

      Lost Users (users who are active in the current week but this is the last time the user was active in the app - this field is technically dynamic considering if the user comes back at a later date their status will change to 'inactive' during this period and 'reactivated' during the later period)



      The calculation for New, Returning, and Lost users are fairly straightforward, but I am lost when it comes to Reactivated and Lost Users.


      Regarding Reactivated Users - I've attempted the following method (per recommendations on other forums):

      Add an LOD expression designating the 'First Visit Each Week' - assigned to each customer ID and Week ( {Fixed [customerid], datetrunc('week',[startdatetime]) : min([startdatetime]) } )

      Find the last 'First Visit Each Week' (aka last week's first visit) with a Lookup ( Lookup(attr([First Visit Each Week],-1) )

      Conduct a Date Diff between the two

      Create an If Then Statement to determine if the value of the Datediff is less than -14, then the user must be a 'reactivated user' in the given week (considering a value of 14 days or over must designate that the user was inactive last week, thereby making them 'reactivated' this week)


      This method was unsuccessful.


      Regarding Inactive Users, I am totally lost.


      I've attached a sample workbook and raw data file incase anyone would like to attempt to solve this problem. I'm sure it has been done before, this does not seem like an uncommon business problem to solve.

      Any help anyone can provide will be greatly appreciated. Let me know if you are able to find the solution.


      - Jack

        • 1. Re: Calculating User Retention Rates via Customer Segmentation - Issue w/ Solving Inactive and Reactivated Customer Segments
          Jim Dehner

          Hi Jack


          Interesting problem


          see the attached


          it will return this - Note - I used some different terms because there logically an issue between Re-active and returning


          you can change them to what ever -



          this is the workhorse calc


                    if ([count of visits])=1 and min([count of weeks new])=1 then 'New'

                    elseif min([count of weeks new])>[count of visits] then 'Reactive'

                    elseif min([count of weeks new])=[count of visits] then 'current active'

                    elseif [count of visits]>0 and ISNULL(min([count of weeks new])) then 'Lost'

                     else NULL end







          the table calc is


          you can convert it into a chart




          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Calculating User Retention Rates via Customer Segmentation - Issue w/ Solving Inactive and Reactivated Customer Segments
            Jack Grinblatt

            Hey Jim Jim Dehner


            Thanks for taking a stab - this is extremely helpful.

            Your calculation for 'lost' customers completely solves the problem for what i was calling 'inactive' customers in the body above.

            Regarding 'reactive' users - I think the queries you put together will set me down the right path - but are not quite where I need them to be yet.


            I am intending to designate a user as 'reactive' the first time they come back from being 'inactive' (or lost, as you have it labeled). If they continue to engage with the platform past that point they will be labeled as 'current active' (as labeled on your analysis). The reason for this is we want to be able to review what brings users back to the platform, rather than only what brought them back after the first period of inactivity.


            For example, if there were a user that was engaging consistently and then dropped off for three months, continued to engage when they came back but dropped off again for another month and then came back - we would want to evaluate what happened each of those periods to make this particular user come back - or in general what periods (correlated with product releases) had higher 'reactive' or 'return' rates. In this sense, calling back that first reactive period is important - but not to continuously label them as 'reactive' for ongoing engagement.


            I appreciate your help in this, I will explore how to bounce off the logic you've already put together to  find a solve for the reactive users based on my particular use of it.


            Thank you again.


            - Jack Grinblatt