2 Replies Latest reply on Jun 30, 2017 11:40 AM by Tehmin Lee

    User retention, churn, and resurrection

    Kent Liu

      I'm trying to produce a user growth chart like the one below (described in detail here).

      1*x09N6OuNlqQTaFxyC6apvA.png

       

      A user that makes a purchase in a given week is considered active. Within each time period, a user can can have states of active or inactive (or non-existent).

       

      As I interpret the blog post, the following metrics are descriptions of transitions between a given period and the previous period:

      1. Retained - users who were active the previous period and active during given period.

      2. Churned - users who were active the previous period and inactive during given period.

      3. Resurrected - users who were inactive the previous period and active during given period.

       

      I created some raw sample data (attached) that depicts a list of orders, the user who made the purchase, and the date of purchase.

      User activity (by hand analysis) is summarized here:

           

      UserWeek 1Week 2Week 3Week 4Week 5
      1ActiveActiveActiveActiveActive
      2Active----
      3ActiveActive--Active
      4Active-Active-Active
      5ActiveActiveActive-
      6ActiveActiveActive-
      7Active-ActiveActive
      8ActiveActive
      9ActiveActive
      10Active

       

      This ultimately produces this chart:

        • 1. Re: User retention, churn, and resurrection
          Dan Sanchez

          Hi Kent!

           

          Thanks for attaching the sample data.  I'm not sure if this is the most elegant solution or not as it does involve quite a fair number of nested calculated fields, but it does let us show the bar chart you created in Excel.  I attached a workbook (used Tableau 9.2) and have included a screenshot below of all the different worksheets I made to validate the calculations.

           

          One area to pay attention to is that the table calculations need to have their advanced Compute Using settings entered (accessed by right-clicking the field on the viz > Edit Table Calculation > select Advanced in the Compute Using drop down).

           

          Hopefully this helps get you started on your analysis.  Thanks!

           

          3-18-2016 10-34-26 AM.png

          • 2. Re: User retention, churn, and resurrection
            Tehmin Lee

            Hi,

             

            This is great!  One question, I have millions of user ID's and just to do a count of them in each periods by bucket in a table format.  I've tried using your example but can't get it to work.  Do you have any suggestions?  Thx!