11 Replies Latest reply on Oct 30, 2017 3:59 AM by Yuriy Fal

    Calculating Application Session Duration

    Daniel VanderMeer

      I'm looking for a few pointers on how I might calculate web application session duration given user activity log.

       

      1. I have web application usage data in the following format:

      applicationSessionData.png

      2. I would like to be able to calculate session duration beginning with app/login, ending with the latest activity that takes place post login.
      calculatedSessions.png

      3. I would like to be able to chart session duration as follows:

      chart.png

      4. And from there I want to calculate average session duration by user over many sessions.

       

      Do you guys have suggestions on how I can accomplish this within Tableau? I've attached a small sample data set.

        • 1. Re: Calculating Application Session Duration
          Jonathan Drummey

          Hi Daniel,

           

          There are a couple of ways to approach this. If the volume of data you will analyze in any given view is more than ~100K records, I'd suggest building the session duration calculation in a query in your data source, because it will be a lot faster and scale better. You could use Tableau's Custom SQL or inside your data source.

           

          For a smaller number of records, while you could use table calculations they get very complicated very quickly, so I still suggest building the session durationg calculation in your data source.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Calculating Application Session Duration
            kettan

            Attached workbook uses a query that might do what you want.

             

            Ps. The SQL code is a little bit longer than it would be in a real database, because I had to repeat a 3 level subquery null check with IIF. This is because Excel Jet SQL doesn't support ISNULL(value, if value is null show alternative).

             

            Ps. I added an extra login for one of the users. It is needed to test if the query can handle that the same user logins more than once.

             

            Ps. The DurationMinutes calculation uses DATEDIFF to calculated minutes. You might like to change this to seconds and divide with 60, because DATEDIFF is not particularly precise.

            1 of 1 people found this helpful
            • 3. Re: Calculating Application Session Duration
              Jonathan Drummey

              Nice, Johan! I presume that you mean IFNULL and not ISNULL (since the Custom SQL does use ISNULL)?

               

              Daniel - with Johan's work, all you need to compute the avg minutes per session per user is to put the LoginID on Rows and then use AVG(DurationMinutes) for your measure.

               

              Jonathan

              • 4. Re: Calculating Application Session Duration
                Daniel VanderMeer

                Thanks, guys! I had not considered a SQL statement as an option, let me try this approach.

                 

                I appreciate your help.

                 

                Daniel.

                • 5. Re: Calculating Application Session Duration
                  Daniel VanderMeer

                  Thanks, Johan.  I just tried your solution and it works perfectly!

                   

                  I now have some work to do studying subselect statements so that I can put this type of solution in my regular toolbox.


                  I didn't realize that Tableau let's you transform an Excel data source using a custom SQL Statement. Do you know if you can apply a SQL Statement against a server side Tableau Extract data source as well?


                  Thanks a lot!

                  Daniel.



                  • 6. Re: Calculating Application Session Duration
                    Nemanja Tanaskovic

                    Hello kettan ,
                    I have the same problem as Mark VanderMeer and I've found this your solution but i can't open this workbook because I'm working on Mac, and there is a lot of reasons why I can't open it on Mac.

                     

                    It would be great if you could do this again in the newer version of Tableau, or give me some screenshots on how to get this App sessions problem done.

                     

                    Thank in an advance!

                    • 7. Re: Calculating Application Session Duration
                      kettan

                      Custom SQL isn't available on Mac

                      This is mentioned in  Where is custom SQL in Tableau 9.1 for Mac

                       

                      LoDs seemingly can't do it

                      As for LoD calculations, I gave it a go, but couldn't figure it out.

                      The original sample data is attached for those who like to try 

                      I also attached an unzipped version of the original tableau workbook (I couldn't open it while zipped)

                      An old "feature" of this forum was to autozip attached workbooks 

                       

                      A correlated subquery was used

                      The custom SQL (see bottom) uses a correlated subquery, which isn't featured in Tableau.

                       

                      Then what to do?

                      The simplest option I can think of is to do this with formulas in Excel.

                       

                      Influence Tableau to feature this

                      You may like to influence Tableau to feature custom SQL on Mac and Correlated Subquery in general by up-voting following ideas:

                       

                      custom sql for excel sources on mac   4

                      Correlated Subquery   30

                       

                       

                      -oOo-

                       

                       

                      THIS IS THE CUSTOM SQL USED AS CORRECT ANSWER IN THIS THREAD

                       

                      SELECT s.[Company],

                        s.[LoginId],

                        s.[PageRequest],

                        s.[DateTime] AS SessionStart,

                        IIF(ISNULL(

                        (SELECT MAX(s2.[DateTime])

                         FROM [Sheet1$] s2

                         WHERE s2.[LoginId] = s.[LoginId]

                         AND s2.[DateTime] < (

                           SELECT MIN(s3.[DateTime])

                           FROM [Sheet1$] s3

                           WHERE s3.[PageRequest] = 'app/login'

                           AND s3.[LoginId] = s.[LoginId]

                           AND s3.[DateTime] > s.[DateTime]

                           )

                        )),

                         (SELECT MAX(s2.[DateTime])

                         FROM [Sheet1$] s2

                         WHERE s2.[LoginId] = s.[LoginId]

                         ),

                        (SELECT MAX(s2.[DateTime])

                         FROM [Sheet1$] s2

                         WHERE s2.[LoginId] = s.[LoginId]

                         AND s2.[DateTime] < (

                           SELECT MIN(s3.[DateTime])

                           FROM [Sheet1$] s3

                           WHERE s3.[PageRequest] = 'app/login'

                           AND s3.[LoginId] = s.[LoginId]

                           AND s3.[DateTime] > s.[DateTime]

                           )

                         )) AS SessionEnd

                      FROM [Sheet1$] s

                      WHERE s.[PageRequest] = 'app/login'

                       

                       

                      Ps. If the coloring ( and thickness bold/normal and height/size ) of the parenthesis made it less difficult to read the code, please consider up-voting this idea by  Gabriel A. Zorrilla

                       

                      Make formula's parenthesis pairs more evident  5

                       

                      I don't think its score of 5 is a fair picture of how many would appreciate having such parenthesis guides in Tableau calculations. Please prove me right 

                       

                       

                       

                      Attached Workbook Version:  8.0

                      1 of 1 people found this helpful
                      • 8. Re: Calculating Application Session Duration
                        Jonathan Drummey

                        Hi Nemanja & Kettan,

                         

                        I use Tableau for Mac & LODs extensively and can speak to the issues here. There are several factors at play that determine what is possible:

                         

                        a) Tableau for Mac vs. Tableau for Windows and the availability of connectors in each

                        b) the data source that you are actually using

                        c) the data itself, in particular whether there is one set of page requests per login/customer ID or multiple

                        d) what you are trying to do with the data (i.e. the calculations and your final view)

                         

                        I'll walk through each:

                         

                        a) When Kettan first built this workbook back in 2013 the example was prepared using Tableau at the time, so that meant an Excel file using what is now called the legacy connector on Tableau for Windows. Tableau for Mac does not have the legacy connector (which is actually the Microsoft JET connector) and the new connector that Tableau for Mac uses does not support Custom SQL. More details here: Re: Where is custom SQL in Tableau 9.1 for Mac

                         

                        One other note on this - if the data source that Kettan had created had been extracted first then the workbook could be opened on Tableau for Mac. The user would see the Custom SQL warning message (and be able to see the SQL) and then see the workbook. The reason why this is the case is that connecting to an extract (whether built via Custom SQL or not) is different from connecting to the original source, and Tableau supports extracts on both Windows & Mac.

                         

                        b) Given Tableau for Mac if our data source isn't an Excel file but instead something like SQL Server, etc. then we *can* use custom SQL or a custom view in a database to replicate what Kettan created. We just can't do it on Tableau Desktop for Mac as-is, we need some database software to run the query.

                         

                        c) A mental model I have for this kind of calculation is that we need to do inter-row comparison to create a cohort of rows based on each app/login and get the time associated with that row along with the time associated with the last row. This is a case where the data is in an atomic transactional form and what we want is a more aggregated "lookup" format where we have all the details that we need for our analysis in each row. There's a similar problem in healthcare in readmissions & returns analyses where a patient may have multiple encounters over time and we're looking to find the index encounters for each series of visits.

                         

                        If there is only one app/login for each login id/customer then we can do this in Tableau using LOD expressions, if there are 1 to N app/logins for each login id/customer then it is not solvable using LODs expressions. If there are only 2 or 3 app/logins for each login id/customer we can conceivably use LOD expressions however we start running into multiple levels of nested subqueries and performance can nosedive.  There *is* a way we can count this using table calculations in Tableau, however...

                         

                        d) If we are using table calculations to figure out the start/end times and durations then all aggregations after that (like determining averages) are nested table calculations and that drastically increases the complexity and lowers the performance of the final view. LOD expressions can be a lot faster, however that's dependent on the data source as I described in point c).

                         

                        Therefore my recommendation is to *not* do this first layer of calculation in Tableau but instead do it outside of Tableau whether that's in SQL, using Python/R, a data prep tool like Alteryx, etc.

                         

                        Jonathan

                        • 9. Re: Calculating Application Session Duration
                          Yuriy Fal

                          Hi Kettan,

                          kettan wrote:

                          ...

                          LoDs seemingly can't do it

                          As for LoD calculations, I gave it a go, but couldn't figure it out.

                          The original sample data is attached for those who like to try

                          ...

                           

                          With the (10.5 Beta) "Hyper"-helpful Non-Equi Joins we might do it :-)

                          Please find the attached (for testing only, don't use in production :-)

                           

                          Yours,

                          Yuri

                          1 of 1 people found this helpful
                          • 10. Re: Calculating Application Session Duration
                            Jonathan Drummey

                            I didn’t know 10.5 can do non-equi-joins, that’s fantastic!!

                             

                            Jonathan

                            • 11. Re: Calculating Application Session Duration
                              Yuriy Fal

                              Besides, there is the Assume Referential Integrity flag in there.

                              So when connecting "Live" to file datasources --

                              or even better using Cross-DS connections --

                              Join Culling is effectively enforced with this flag

                              (as evident in the Performance Recording).

                               

                              One could imagine the possibilities.