1 2 Previous Next 26 Replies Latest reply on Oct 31, 2014 12:19 PM by Shawn Wallwork

    Calculating User Retention Rate

    Ashley Flanagan

      I’m working with a data set that tracks user sessions for an online service. For each session, there’s a column for Session Start Time (in the format MM/DD/YYYY 00:00:00 PM), another for Session End Time, and another for User ID.

      I’m looking to answer the following question: for any given day the game was active, out of the users who logged in on that day, what percentage of them logged in again within the next two weeks?

      I'm still fairly new at Tableau, so I'm not entirely sure how to even approach this - any suggestion that might point me in the right direction is welcome.

        • 1. Re: Calculating User Retention Rate
          Joe Mako

          How about something like the attached?

           

          This is actually fairly complex, and I would be happy to go into detail on what enables this work over a screen sharing session, you are welcome to email me (found on my profile).

           

          In brief, this uses nested table calculations, first to get the date of the next record with a LOOKUP to get a difference in days (partitioning on ID, addressing on Date), and second uses a WINDOW_SUM to aggregate a count of IDs that meet the day difference requirement, and divide by the number of IDs to get a percent (partitioning on Date, addressing on ID).

           

          There are a number of other factors at play here, and I am happy to go in great detail over a screen sharing session.

          1 of 1 people found this helpful
          • 2. Re: Calculating User Retention Rate
            Ashley Flanagan

            Thanks so much for replying, Joe. When I try to apply your solution to my data, though, the calculations are just returning a bunch of nulls.

             

            I did find a completely alternate way to get what I need, but I'd still like to understand your solution and how it works better. Are there some tutorials I should watch (I haven't done much with table calcs before)?

            • 3. Re: Calculating User Retention Rate
              Joe Mako

              I know Tableau is working on making more resources in regards to Table Calculations, but I do not know if any they currently have cover all the techniques used in this workbook.

               

              I am happy to have a screen sharing session with you to walk though what is going on. Please let me know if you have any concerns. Thank you!

              • 4. Re: Calculating User Retention Rate
                Matt Lutton

                If a screen share is to take place, I would love to join in! 

                • 5. Re: Calculating User Retention Rate
                  Ashley Flanagan

                  I'd love a screen-sharing session with both of you, if you're willing. I'm pretty new at this, though - how will we set it up?

                  • 6. Re: Calculating User Retention Rate
                    Matt Lutton

                    I was also interested in viewing how Joe approached this problem -- a join me session or google hangout will typically work if you can plan the timing out.  I'm on Indiana time, and Joe's 3 hours earlier than I am, so that is the difficulty.

                     

                    If I were you, I would reach out to Joe directly (his email is in his profile) to try and set something up.   If it works out, I would love to be watch and learn more as well.

                    • 7. Re: Calculating User Retention Rate
                      Matt Lutton

                      Hi Ashley:

                       

                      I reached out to Joe early this afternoon and within a matter of hours he was walking me through his thought process in building a solution for your type of problem in Tableau. He also recorded the entire thing!

                       

                      There are some complex Tableau concepts discussed, so feel free and do not be afraid to ask questions. Reach out to either of us via email if you have comments on the video response idea as a whole.

                       

                      I, personally, got a lot out of watching Joe work and asking for some clarifications along the way. Let this video serve as proof that all Zen Masters are human (and we make mistakes too).

                       

                      Cheers and thanks to Joe and Ashley both!


                      https://vimeo.com/106699670

                      • 8. Re: Calculating User Retention Rate
                        Matt Lutton

                        I'm interested in getting your feedback on the video in particular, Ashley Flanagan - did the video help you, or did we miss the mark completely?  Any honest feedback will be appreciated, and we of course want to know if you've resolved your User Retention problem -- and if so, what route did you take? 

                         

                        Cheers

                        • 9. Re: Calculating User Retention Rate
                          kettan

                          Thanks. This is very helpful indeed.

                           

                          PERFECT

                          I am only a few minutes into the video and already believe it will be what truly helped me understand table calculations (partitioning, addressing, data densification, marks, etc).

                           

                          MISSING

                          I wish I had the same data, so I could imitate what is going on on the screen.

                          Could you (or Joe) share the exact same underlying dataset?

                           

                          Ps. I look forward to understand how come that I could use a table calculation to create a pseudo row for an individual partition while avoiding having other partitions experiencing data densification.

                          See what I am referring to in  Re: How to identify and table gaps in a time series

                           

                          Tags:   t_study t_table_calculation      search tags & kettan

                          • 10. Re: Calculating User Retention Rate
                            Matt Lutton

                            The example workbook Joe posted has the same data structure, but I believe he mocked up some data on the fly for the video example.  Joe Mako: Any chance you saved the workbook/data for this one?  I know it makes this a bit more recipe-like, but it may help people to verify their specific results if they work through an example like this.

                             

                            Cheers Johan, and thanks for the feedback!

                             

                            We're trying to keep track of some good Forum threads for examples; we're not planning too much in advance.  For me, part of the fun is showing the complexities of a simple problem, seeing "Zen Masters" make mistakes and work through problems, etc.  Keeping it "live" helps demonstrate some of this--another reason I love the TDT videos (if the audio and video would just match on some of them!);  we get to see people working in their "natural habitat" which I think adds some value as well.

                             

                            We'll see where it goes next.  No big plans at the moment, but I hope to keep it going.  And by the way, this is not limited to Joe and I - I would invite anyone to do a screen share with me to go over a sample problem, wherever time allows (with or without the recording).  Now, I do not have the level of understanding that Joe has, but I love teaching and learning from others.

                            • 11. Re: Calculating User Retention Rate
                              kettan

                              I would invite anyone to do a screen share with me to go over a sample problem, wherever time allows (with or without the recording).  Now, I do not have the level of understanding that Joe has, but I love teaching and learning from others.

                              I hope others will join you & Joe. If more videos will be done, I will promote them in  Do you want to be skilled in table calculations?

                              • 12. Re: Calculating User Retention Rate
                                Ashley Flanagan

                                I apologize for not getting back to you all right away - I've been out of town for a few days. Joe, Matthew, thank you so much - the video was incredibly helpful.

                                 

                                Going through your steps with my data worked perfectly right up until nearly the last step - creating a combined ID & Date field and dragging it onto Path. Doing that produced the following totally bizarre graph:wonky_retention_graph.jpg

                                Anyone have any idea what went wrong here?

                                • 13. Re: Calculating User Retention Rate
                                  Joe Mako

                                  In the Percent formula, instead of:

                                  THEN 1 ELSE 0 END

                                  use:

                                  THEN 1 END

                                   

                                  That way you get null values for days that are zero, then you can adjust the Format->Pane for the Percent pill on the Rows shelf, setting Marks under Special Values to whatever you like. see 34:30 in the video for example.

                                   

                                  Although there may be some other factor at play here, eg Show Missing Values, I would need an example packaged workbook that represents your situation, that recreates what you are experiencing to provide actual assistance.

                                  • 14. Re: Calculating User Retention Rate
                                    Ashley Flanagan

                                    Unfortunately, the actual data is confidential, and I'm really not sure what about it might be causing the issue.

                                     

                                    I've got two other questions about the video, if that's all right.

                                     

                                    1. Around 28:39, when you first make the line chart, the y-axis goes from 0% to 4%, and the mark seems like it's averaging around 2%. As soon as you change it to a bar chart, the y-axis goes up to 100%, and it looks as if the average day is somewhere around 60%. Why does simply changing the mark type cause this?
                                    2. Why does the line chart only go crazy after you change the Date dimension from discrete to continuous? I think there's something I'm not understanding about the nature of discrete vs. continuous values here. (See below.) Come to think of it, what exactly would be wrong with just leaving the dates discrete?

                                     

                                    (EDITED TO ADD: So after doing some research on my own, I'm guessing the answer to my initial question in #2 is something to do with data densification. I learned from this thread that turning on Stacked Marks is a way to turn on data densification, so I tried doing that instead of creating the combined field and putting it on Path, and I've got something that looks like a normal line chart with continuous dates now...except that the y-axis range is still really low, just like in question #1; it's giving me a retention rate of 2% on a date when the bar chart says I have 72%.)

                                    1 2 Previous Next