6 Replies Latest reply on Feb 13, 2019 1:56 AM by Diego Parker

    Average number of sessions and average time between sessions

    Tatiana Mazurenok

      Dear Tableau Community,

       

      I am stuck with the problem how to calculate average number of sessions and average time between sessions of our users. I tried to use table calculations, but the numbers were wrong, and I'm completely confused.

      I would be grateful for any advice and guidance on how to solve my problem.

       

      So, we have several users (in real data we have thousands) who can be active (one or more times) through the day. Let Session is those days when the user was active and the difference between the days of activity does not exceed, for example, 1 day (for simplicity in this example. In reality, I want to set the number of inactivity days as a parameter).

      Please, see the part of example dataset below:

       

      Screen Shot 2019-02-09 at 20.26.27.png

       

      I would like to receive the following results in Tableau:

       

      - for every user (this view is optional, because the size of real data, but I want to learn how I can calculate it)

       

      Screen Shot 2019-02-09 at 20.29.55.png

       

      - for cohorts (in real data I want to use other types of cohorts too, for example "first activity month" or "by loyalty" or "by profit" e.t.c.):

      Screen Shot 2019-02-09 at 20.33.39.png

       

      - for all users:

      Screen Shot 2019-02-09 at 20.37.15.png

       

      Please, help me to understand the right direction how I can solve this problem. Example workbook plus packaged workbook are attached.

       

      Thank you,

      Tatiana

        • 1. Re: Average number of sessions and average time between sessions
          Shinichiro Murakami

          Not quite sure but something like this?

          Data is not sufficient to calculate avg number of sessions and I ignore this.

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Average number of sessions and average time between sessions
            Diego Parker

            Hey Tatiana!

             

            This was a fantastic exercise for me to practice LODs and Table Calculations. I believe Shinchiro's answer will probably fulfill your requirement but in my workbook I created 6 different worksheets, one for each of your requirements. I thought you may find this helpful to understand the workarounds. I grouped the different calculations in folders so you can see them easily.

             

            In order to differentiate each session, I modified the data by adding a ROW ID (just a 1, 2 ,3 ,4 ,...n for each row).

             

            Snip1.PNG

            Snip2.PNG

            Snip3.PNG

            Snip4.PNG

            Snip5.PNG

            Snip6.PNG

             

            I hope you find this helpful! if you do please mark it as helpful so other users can refer to it.

             

            If you have any questions regarding a calculation or anything, do not hesitate on asking.

             

            Best,

            Diego

            1 of 1 people found this helpful
            • 3. Re: Average number of sessions and average time between sessions
              Tatiana Mazurenok

              Hi  Shin and Diego!

               

              Thank you very much for your help, I'm very appreciate it.

               

              The only thing that is important, but I didn't find in your solutions - is about "Session". Session - is not the one-time activity, it is a sequence of dates (without gaps in our example). In real world it may by the sequence of dates in which user was active, and the intervals between the dates of activity did not exceed, say, 7 days (or 30 days). A lot of people use our service on vacation (and not on everyday basis), so I need to know average time between such "vacation" Sessions (not between active days inside one vacation).

               

              So, I tried to combine both of your solutions in one (thank you again).

               

              I received numbers that looks like correct for User, but I am stuck again with Cohort table calculation.

               

              First of all, I created field Session_flag - this field flag the start day of Session.

               

              Second, I calculated the number of Sessions for User (sum of Session_flags).

               

              Then, I calculated Total User Lifetime (it is LOD function), and received AVG Time between Session as Lifetime/Session_Number. May by it is not as accurate as AVG(Day from last session), but I could not calculate this yet for my extended Session meaning.

               

              For Cohort calculations I have to exclude Users, who had only 1 Session (with given number of Inactivity Days between Sessions) and repeat the calculations. But my numbers aren't correct, I think the mistake origins from mix of LOD and table calculations. And I didn't find how to exclude users with only 1 Session from calculation (filter exclude them from view, but doesn't exclude it from calculation).

               

              If you can help, it will be great! May be you can give me tips or advice?

               

              I attached workbook. The first sheet - I repeated Shin's calculations (and measures which start with 0 are for these sheet). On the other sheets I tried to solve the problem.

               

              Thank you,

              Tatiana

              • 4. Re: Average number of sessions and average time between sessions
                Tatiana Mazurenok

                Hi, Diego!

                 

                It is very elegant solution, and I like it, because it based on LOD functions (I am not very confident in table calculation, they often give me unpredictable values). But in real life it is absolutely unreal for me to have RAW_ID in my table. If you'll find some time, please, see my next iteration above.

                 

                 

                Thank you,

                Tatiana

                • 5. Re: Average number of sessions and average time between sessions
                  Shinichiro Murakami

                  No exactly sure, but you mean this?

                   

                   

                   

                   

                   

                  User

                   

                   

                   

                  Cohort

                   

                   

                   

                   

                   

                   

                   

                  Thanks,

                  Shin

                  • 6. Re: Average number of sessions and average time between sessions
                    Diego Parker

                    Hey Tatiana,

                     

                    In the example you sent us, your DATE field only goes to the day level 01/01/1900. However, I assume that in your real database the sessions go to the "seconds" level 01/01/1900 01:01:01. Therefore, you can use your date field instead of a row id. The only reason why I used a Row Id was to differentiate the sessions that happened in the same day but you can easily do this if the sessions have different times. If this is the case, just replace the Row_Id in my formulas for your date field.

                     

                    Also, I had in my example the time between sessios, however now I added a parameter so you can highlight the moments when the time between sessions has been bigger than X days.

                     

                    I hope you find this helpful! if you do please mark it as helpful so other users can refer to it.

                     

                    If you have any questions regarding a calculation or anything, do not hesitate on asking.

                     

                    Snip.PNG

                     

                    Best,

                    Diego