6 Replies Latest reply on Dec 8, 2011 12:02 AM by janpontzen

    calculating concurrent users based off of login time and logout time

      On a per account basis, the following information is currently logged in our database: login time and logout time, both in (mm-dd-yyyy hh:mm) format.

       

      With that info, I'm trying to find a way to calculate the following data points based off the available data:

      * concurrent users, i.e. amount of users that were logged in to the product within the given timeframe

      * 1st login users, i.e. the amount of users that logged in to the product for the first time within the given timeframe

       

      Concurrent users should include users that logged in to the product within the given timeframe and that were already logged in within the given timeframe.

       

      Thanks a lot in advance for your help, greatly appreciated!

        • 1. Re: calculating concurrent users based off of login time and logout time
          Dimitri.B

          See attached workbook for a solution.

          I faked up some data and used parameters and Boolean logic.

          Use parameter control on the dashboard to set the timeframe and it will count both types of users and show them graphically.

          The concurrent users was straghtforward, but for 1st login users I assumed users who logged in during the specified timeframe, as opposed to those who were already logged in. If that is the case, it is also easy, but if you meant users who had their very first login ever which happened to occur during that timeframe, then it is a different matter and will need further work.

          • 2. Re: calculating concurrent users based off of login time and logout time

            Thank you for your reply! The file attached is very helpful for me to better learn the use of Tableau, so thanks a lot for that.

             

            However this does not fully match my needs. Basically, via calculated fields, parameters, etc., I'd like to be able to do the following with "concurrent users":

            * drag the date to the columns field

            * drag the "concurrent users" calculated measure into the measures field

            * adjust the concurrent users calculated measure to my liking, e.g. to AVG(concurrent users) to get the average concurrent users or to MAX(concurrent users) to get the peak CCU for the product

            * adjust the date field to my liking, e.g. adjusting the date format to MDY with an active MAX(concurrent users) would display the peak CCU for each day, changing it to month would display the MAX(concurrent users) for each particular month etc.

             

            Is this possible or is this beyond the limitations of Tableau?

             

            The "first time user" graph I am currently getting via an SQL command and an according SQL table. To my knowledge, Tableau cannot gather the 1st time user information in a way that can be drilled down with further?

            • 3. Re: calculating concurrent users based off of login time and logout time
              Dimitri.B

              Jan, it would be a lot easier if you can have a go at doing it and post the workbook with data and attach a sketch of what you want it to look like. Without knowing anything about the data, the business and the task at hand, it is quite hard to figure out what needs to be done.

              Tableau is a fairly flexible tool and it may even be possible to do the 1st time user in it, but that depends on the structure of data.

              • 4. Re: calculating concurrent users based off of login time and logout time

                Hello Dimitri, thanks for your reply. I've attached a file with sample login data.

                - ACCOUNT ID is the unique ID per user

                - DATE & TIME lists the moments of time tracked in the login history

                - ACTIVITY identifies the activity performed by the user, 1 is "login", 2 is "logout"

                - TERRITORY lists the territory the data was tracked in

                 

                What I would like to get out of this, is a historical (per day) chart with the following line graphs, all in one graph:

                - DAU = daily active users = amount of unique users logged in per day

                - PCCU = peak concurrent users = maximum amount of users that were logged in at the same time, per day

                - new users = amount of users that logged in for the first time, per day

                 

                In a different sheet, I would further like to see percentage-based information like

                - engagement = DAU / MAU = daily active users divided by monthly active users to determine how many users returned to the product daily

                - churn = percentage of users that didn't log in to the product within the last 7 days

                 

                In short: I have account IDs and activities available in my logs, but cannot find a good and convenient way to use those login logs for drill-down calculations, because I'd have to further aggregate already aggregated data (i.e. countd([ACCOUNT ID]). My current way of thinking is to generate the aggregated data (DAU, MAU, last login, first login, etc.) via additional SQL commands and then feed that generated data upon loading according Tableau workbooks. Just using the raw login history data doesn't seem to suffice for my needs.

                • 5. Re: calculating concurrent users based off of login time and logout time
                  Dimitri.B

                  Hi Jan,

                  I had a look at your data and I tend to agree with you that it would not be worth the effort (even if it is possible) to use Tableau for this kind of transactional processing on raw data. This job is much easier with simple SQL.

                  Once data is transformed into a right shape, Tableau can do the rest. This is usually an iterative process, and I do it all the time using custom SQL connections.

                  • 6. Re: calculating concurrent users based off of login time and logout time

                    Thank you for the reply. I'm glad that you agree and will make sure the data structure and SQL commands we use is set up / adjusted accordingly.