1 2 Previous Next 23 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!


                      1 of 1 people found this helpful
                      • 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? 



                        • 9. 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?

                          • 10. Re: Calculating User Retention Rate
                            Joe Mako

                            In the Percent formula, instead of:

                            THEN 1 ELSE 0 END


                            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.

                            • 11. 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%.)

                              • 12. Re: Calculating User Retention Rate
                                Ashley Flanagan



                                I still haven't figured out what problem I was having with my data, so on a whim I tried Jim's method for this on the "Sample - Superstore Subset" sample data (using Customer ID & Order Date) and the "Sample - Superstore - English" sample data (using Customer Name & Order Date) - and I'm getting exactly the same problem I saw with my data.


                                Can anyone confirm whether they can make Jim's method work using the sample data? This might help isolate what I'm doing wrong.

                                • 13. Re: Calculating User Retention Rate
                                  Matt Lutton

                                  I'm not sure exactly what you're referring to here when you say you tried Jim's method, but why not post your sample packaged workbook here?  To post an attachment, in your reply choose "Use advanced editor" in the top right corner and then "Attach" will appear in the bottom right corner next to "@ Mention"

                                  • 14. Re: Calculating User Retention Rate
                                    Ashley Flanagan

                                    All right - this is a quick and dirty attempt to replicate the method from Jim's video posted upthread, using Sample-Superstore-English. Sheet 1 has the bar graph version; Sheet 2 is what happens when I try to turn it into a line graph by making the Date field continuous, dragging a combined Order Date & Customer Name field onto Path, and sorting the pill by Order Date with Aggregation: Minimum.

                                    1 2 Previous Next