1 2 Previous Next 17 Replies Latest reply on Jul 26, 2018 4:03 AM by Nolan Esser

    Trying to implement a churn/reactivate analysis

    Nolan Esser

      Hello everyone,

       

      As I mentioned in the title, I am currently trying to build a chart to display the churn rate and the reactivation rate of my users on a weekly basis. Unfortunately, I failed after several attempt. I just began using Tableau and I am learning as much as I can, but I could use a little help here since I did not succeed even using the multiple thread about this subject on the forum. I am surely doing some obvious mistakes or missing some obvious parameters.

       

      The problem I encounter is that in the weekly audience analysis, I can not get any satisfying result. As you will see in my chart, I only get results for the end of my time period:

      The truth is, I do not really know how to do this. Here is an example of the result I would like to achieve:

       

      Also, I tried to add my files in order for you to help me but I am not sure I have get it done correctly (I extracted my data source and made a .rar with my tableau file).

      In advance, sorry for my english level (as for Tableau, I am trying to improve).

      Thanks in advance for the help I could receive.

        • 1. Re: Trying to implement a churn/reactivate analysis
          kumar bharat

          Hi Nolan,

          Please check these,You can download the twbx files from the site and check the logic.

          1. Tableau Public
          2. Tableau Public
          3. Tableau Public
          4. Tableau Public
          5. Tableau Public

          BR,

          bharat

          • 2. Re: Trying to implement a churn/reactivate analysis
            Nolan Esser

            Thanks for the fast answer, However, those examples are quite far from what I want to do since the calculations are not based on the presence of an ID during a certain period of time.

            The trouble I have is to calculate at each period of time if somebody was here before, meaning that maybe he came back then he is a reactivated user. Or maybe he is was not there last week and then he is a churner this week, but could become reactivate next week  (or remain a churner).

            • 3. Re: Trying to implement a churn/reactivate analysis
              kumar bharat

              Hi Esser,

              Please check the links for cohort analysis along with curn details,it shows exactly what you want.

              Re: Cohort Charts

              Cohort Analysis : Reactivation, Retention, Churn

              https://www.iglooanalytics.com/blog/cohort-analysis-in-tableau.html

              BR,

              bharat

              • 4. Re: Trying to implement a churn/reactivate analysis
                Hari Ankem

                I do not understand your data completely and so not clear on how you plan to compute the churn rate. Can you explain or state the formula you plan to use?

                • 5. Re: Trying to implement a churn/reactivate analysis
                  Nolan Esser

                  Hello Hari, thanks for being here.

                  Well, I tried few ideas but the original one was to compare the date to the last activity of the user_id, something like that:

                   

                  IF DATEDIFF('day',[Last Activity],[Event Timestamp])=0

                  THEN    0

                  ELSEIF DATEDIFF('day',[Last Activity],[Event Timestamp])>1

                  THEN {FIXED:countd([User Id])}

                  END

                   

                  If the date is the same, then the user was connected this day, then was not a churner. If the dates have more than 1 day between them, then the guy did not connect last day and then churn daily.

                   

                  The thing that I would like to do is that:

                   

                  Cohort Analysis : Reactivation, Retention, Churn

                   

                  This is really the best thing I found, you can even chose if you want a daily/weekly or monthly analysis.

                   

                  What exactly do you find unclear in the data ? What is useful here in my data are:

                       - event_timestamp :  date the user connect

                       - user_id : unique identifier

                       - last_activity : {FIXED [User Id]: MAX([Event Timestamp])}

                   

                  Thanks again, feel free if you have any other question or need more information than what I provided here.

                  • 6. Re: Trying to implement a churn/reactivate analysis
                    Hari Ankem

                    I am looking into this. From a churn perspective, I don't think the last activity can be considered to be the maximum value for the user id.

                    For example, the user id 1 is active from July 2nd to 4th, 6th to 10th, 12th to 17th, etc. So, would you not consider it as a churn on 5th, 11th, etc? Let me know.

                    • 7. Re: Trying to implement a churn/reactivate analysis
                      Nolan Esser

                      The last activity is supposed to be the maximum value of the event_timestamp (which is the date the user connect), if it is not then it is my bad, or is it also a mistake ?

                      Yes I would totally consider it a churner the 5th and the 11th etc. And I would consider it a reactivation the 6th, 12th etc.

                      Thanks again, really appreciate your effort to help me.

                      • 8. Re: Trying to implement a churn/reactivate analysis
                        Hari Ankem

                        What you are really missing is a column which will identify the group of dates (daily/weekly/etc.) through which a user was continuously active. While this can be derived using LOOKUP function, it will not help because we need to use LODs for further calculations. Note: LODs do not work with Table Calculations.

                         

                        To achieve the desired charts here, I had to copy your data into an Excel sheet (attached) and have created a column named "Group ID" and assigned values to it. Based on whether you need a daily/weekly chart, you need to assign the Group ID correctly. I am doing a daily analysis and hence assigned Group ID accordingly. This column could actually be derived using analytical functions when you are fetching the data from a database. If you however cannot derive it, then you may have to manually add the values as I did here.

                         

                        1.png

                         

                        You also need another sheet which will get you the day/week number for the number of days/weeks you plan to do your analyses. I have data for 30 days in the second sheet in the above attached file. However, I have filtered out the days beyond 13 in the analysis below since they were all 0.

                         

                        After joining the above 2 files, and doing a bunch of calculations, here is what I get:

                         

                        Cohort Analysis:-

                        1.png

                         

                        Retention Chart:-

                        1.png

                         

                        The calculations I have created are:

                         

                        1. User - Group: STR([User Id])+"-"+STR([Group ID])

                         

                        2. Start Date: DATETRUNC("day",[Event Timestamp])

                         

                        3. End Date: { FIXED [User - Group]:MAX([Start Date])}

                         

                        4. User Count: {FIXED [Start Date],[Day Num]:COUNTD([User - Group])}

                         

                        5. Retained User Count:

                        { FIXED [Start Date],[Day Num]:COUNTD(

                        IF DATEADD("day",[Day Num],[Start Date])<={FIXED [User - Group]:MAX([Start Date])} THEN

                            IF [End Date]>DATEADD("day",[Day Num],[Start Date]) THEN

                                [User - Group]

                            ELSE

                                NULL

                            END

                        END

                        )}

                         

                        6. Percent: SUM([Retained User Count])/AVG([User Count])

                         

                        7. % of Users Retained:

                        SUM([Retained User Count])

                        /

                        SUM(IF NOT ISNULL([Retained User Count]) THEN

                            [User Count]

                        END)

                         

                         

                         

                        Hope this helps. The workbook is attached.

                        1 of 1 people found this helpful
                        • 9. Re: Trying to implement a churn/reactivate analysis
                          Nolan Esser

                          Hello Hari,

                           

                          Wow thanks for your wonderful work here.

                           

                          I am not sure to understand what the « group id » is used for and to what it is corresponding. It seems that in your example the « groupe id » increase by 1 every 6 days for the « user_id » 1, but I do not get why. I do not really now how I could integrate it to my database for now, maybe I will when I will understand the logic behind it. I feel it is something related to the day/week a user began to connect which is incrementing over time.

                           

                          The data you are using have all been generated by you, is that correct ? because mine are not 0 after 13, unless we are not talking about the same thing. I mean, I have event_timestamp going until 7/31/2018. Also, mine were randomly generated, that is why I was quite surprised to see such a clean retention chart. By the way, isn'nt the retention supposed to at 100% at day0 ?

                           

                          Your « End Date » is the same as the « last Activity » I had, in the end. But the « Start date », maybe I do not get the label but : DATETRUNC("day",[Event Timestamp]), it could be the third day the same user id is connecting, and it will not be the the start date for this user then. Also, what is « Day Num » ? It is not corresponding to the number in the month if i compare it to the event_timestamp.

                           

                          I hope I am not too confusing or anything, I can not thank you enough for your tremendous help Hari.

                          • 10. Re: Trying to implement a churn/reactivate analysis
                            Hari Ankem

                            Ok. Here is my explanation:

                             

                            Firstly, the data am using is not generated by me. I simply copied all the data from your data source and put into an Excel sheet and worked off of it.

                             

                            Secondly, the Group ID is a group of dates through which a user was continuously active. To assign the Group ID, I sorted the data in ascending order for every User ID. Look at the timestamps below for the user id 1. If the user logged in the same day as the prior record, or the next day, I am assigning the same Group ID. So, the records of July 2nd, 3rd and 4th are all grouped into Group ID 1. The user did not login on July 5th, which means there is a churn here. He/she logged in again on July 6th and logged in every day through July 10. This is part of Group ID 2. He/she didn't login again on July 11th, so one more churn. Similarly, July 12th through 17th is Group 3; July 19th through 21st is Group 4; and finally July 26th to 31st is Group 5. This grouping is suitable for a daily analysis.

                            1.png1.png

                             

                            For a weekly analysis, the grouping should be done this way. Assumption: Week starts Monday and ends Sunday.

                            1.png1.png

                             

                            Thirdly, yes, the retention should be 100% on zeroth day. That can be achieved by correcting the formula for the Retention User Count as given below. I have also included the updated charts below and attached the updated workbook.

                             

                            { FIXED [Start Date],[Day Num]:COUNTD(

                            IF DATEADD("day",[Day Num],[Start Date])<={FIXED [User - Group]:MAX([Start Date])} THEN

                                IF [End Date]>=DATEADD("day",[Day Num],[Start Date]) THEN

                                    [User - Group]

                                ELSE

                                    NULL

                                END

                            END

                            )}

                             

                            1.png1.png

                             

                            Fourthly, for a user, am just truncating the time portion and assigning it as the Start Date. I don't have to assign the start date as the lowest value in a group. If I do, the output will go wrong.

                             

                            Fifthly, the End Date should be the maximum value for a user group. If we simply take the maximum value for the user, irrespective of the group he belongs to, then you are not considering all the churn in between. In the above example, for User ID 1, if we do the maximum value by not considering the groups, we will get a single End Date of July 31. In such a case, all the intermediate churns for the user are being ignored, and that can't be correct.

                             

                            Finally, Day Num are simple numbers ranging from 0 to 30 so that we can monitor the daily churn over a 30 day period. It is included in the Excel sheet and is being joined with your base Events data.

                            1.png

                             

                            Hope it's clear now. Let me know if something is not right so that I can correct it. Thanks.

                            1 of 1 people found this helpful
                            • 11. Re: Trying to implement a churn/reactivate analysis
                              Nolan Esser

                              Hello Hari,

                               

                              1/ oh ok, I was misguided by the datasource which contain 1000 rows with only user_id = 1. But yeah, the excel file contain the same data.

                               

                              2/ Ok, totally clear for the daily part. Not sure how I will implement it, directly in the tracking plan or figure a way out to do it a bit manually. For the weekly part, it should always be 1 if we follow the same logic as daily, because the users connect every week, am I wrong ?

                               

                              3/ Ok, the correction for the day 0 seems correct. Now that I am sure that you are using the same data as me, I can see that the retention chart can not be correct. I explain, I only have 15 users, whose almost connect every day if we believe the DAU count. After seeing the table you get with the retained user count, I do believe that a user that will churn one day will be considered gone forever in the calculation. Retention is theoretically able to increase over time, and it will certainly happens here as my datas are randomly generated and are not representing people boredom at any point (or worst, uninstall). That is why with 15 users the user count is the same from the beginning to the end of the month. With that being said, we would expect an almost flat retention chart (as people are never leaving)

                               

                              4/ Ok, I believed you were saying the player starts to play on this particular date, which is not the the case (sometimes it is by chance)

                               

                              5/ Now that I understand group_id I can see the difference, that is very smart.  

                               

                              6/ Ok, but maybe we should see that as cohort. I mean, after July 2nd, all the people connecting already logged in in the past, if my chart and calculation are correct here.

                               

                               

                              7/ Once we achieve the correct retention chart, can we be able to construct this chart ? I already have the FTL rate, and I do belive we are going to have the Churn with the retention. Reactivation is still a mystery to me but I explored a way where I tried to sort people between churn/reactivation by comparing the period between the last activity and the activity just before it. If it was more than one day I considered it a reactivation because the guy churned before (unless it was First Time Login).

                               

                               

                              Again, I do not know how I could thank you enough for your help, I hope my english formulation is not too bad, I take my time to read myself.

                              • 12. Re: Trying to implement a churn/reactivate analysis
                                Hari Ankem

                                You are right about the weekly group ids.

                                 

                                To validate if the retention chart is correct or not, apply User Id filters and see if it looks right or not.

                                 

                                Here are the individual views with the data filtered for User Id 1 and 3 respectively. Note that the dates on which the users didn't exist, they are excluded.

                                1.png1.png

                                 

                                And now here is view for the User Id 1 and 3 combined:

                                1.png

                                 

                                Do they look right to you? Let me know.

                                • 13. Re: Trying to implement a churn/reactivate analysis
                                  Nolan Esser

                                  Hello,

                                   

                                  There are two problems here:

                                   

                                  1/ When a user churn, he is never counted anymore. For example, for the first row of the user_id 1, the column 3 is 0 it is correct, but column 4, 5, 6, 7, 8 should be 100% because the user connect from 6th to 10th.

                                   

                                  2/ The row "Grand Total" is not representing the retention. An easy way to see this is to notice that a player connecting for example july 3rd is counting 3 times in the chart, he is counting row 1 column 3, row 2 column 2 and row 3 column 1.

                                   

                                  To achieve the correct retention chart, we need to take into account in "grand total" only the rows starting by the first connection of a user. For user 1 it is july 2nd and user 3 july 1st. That is what I mean by "cohort", it is a group of people that started playing the same day/week/month. Here we could use daily cohort otherwise we would have only 1 cohort. A good example is this one because it shows than people can skip days and still be counted afterwards (during april it even goes up from 15% to 16%). And people from a row are not the same that in another one.

                                   

                                  Let me know if I am unclear.

                                  • 14. Re: Trying to implement a churn/reactivate analysis
                                    Hari Ankem

                                    OK. I understood what you are looking for. But, for a moment, refer the Cohort Chart image you included. The percentages always go down when you read the data from left to right. In the daily analysis for User ID 1 now, if I make the change as you are expecting, the percentage would keep switching between 100 and 0. Is that really right? Think about it.

                                    1.png

                                    And for all the users, it would look like this, where the percentages keep going up and down.

                                    1.png

                                     

                                    What do you think?

                                    1 of 1 people found this helpful
                                    1 2 Previous Next