1 Reply Latest reply on Sep 6, 2018 7:57 PM by swaroop.gantela

    Conditions to Begin Counting and End Counting

    Howie Lim U

      Hey guys,

       

      I'm wondering if there is a way to count records based on start and end conditions in Tableau. Please bear with me as I try to explain this as I'm still new to Tableau. What I'm trying to achieve is to calculate visitor engagement into a retail outlet. I've also attached a sample packaged workbook.

       

      1. The conditions to start counting each device (identified by its MAC Address) is to have a record with RSSI > 15, this opens a 30 minute "session".
      2. This would then continue the count as long as the following records have an RSSI > 10, this maintains the 30 minute "session".
        • If the device maintains an RSSI > 10 for at least 10 minutes in a 30 minute window (or "session"), we consider them as a Visitor.
        • Else if the device maintains an RSSI > 10 but for less than 10 minutes, we consider them a Bounce.

       

      Up to now, you'll see that I've only been able to segment the Seen Time UTC+8 into 30 minute bins and calculate the Seen Epoch Difference (LOD of max Seen Epoch - min Seen Epoch) in that fixed 30 minute window, but that's not what I'm really after. Rather, for higher accuracy, I'd like to count based on an individual device's own 30 minute window ("session") based on conditions above.

       

      Hope this makes sense. Would really appreciate the help!

        • 1. Re: Conditions to Begin Counting and End Counting
          swaroop.gantela

          Howie,

           

          Well, this attempt is highly unsatisfactory, but there is a hope that it could maybe give some ideas.

           

          It is unsatisfactory because it is requires the dataset to be joined to itself, it is overly complicated,

          and it will likely get bogged down with large amounts of data.

          Nonetheless, I think it does match your criteria, so maybe it can spur other methods.

           

          Also, I took a different approach to a session, which also may be overly complicated.

          I looked for any Seen Time with RSSI > 15 and used that as a starting point for a 30 min lookahead,

          even if the Seen Time just before it was already RSSI > 15. So one client could have multiple

          Visitor sessions, but I did some rollups to try to account for this.

           

          The self-join: I joined the dataset to itself with a join criterion to only combine rows of the same Client Mac.

          So this gives me essentially a start time (copy 1) and a compare time (copy 2).

           

          I then filter all those combinations down to just those where the compare time > start time and

          compare time within 30 min of start time. This defines the sessions.

           

          Then there are further checks to see if:

          -the next time has RSSI > 10,

          -if the string of Seen Times continuously is RSSI>10 in this session, and

          -if at any point the difference between compare time and start time is > 10 minutes

           

          The determination of Visitor vs. Bounce is done at three levels: each row, each session, each client.

           

          I flag a row with a '1' if the above criteria were met, which indicates Visitor.

          I flag it with a '-1' if time difference is <10 minutes to indicate a Bounce.

           

          For a session, if anywhere within there was a 1 visitor flag, this session is a visitor.

          Failing that, if there is a -1, it is a bounce.

          The same method is applied to the Client level.

           

          Attached in the Forum Thread is the workbook v18.1.

          The far right three columns are the flags for Visitor or Bounce.

          In each calculated field are some comments which attempt to explain what it's supposed to be doing.

           

          I colored the sessions

          Visitor Session Example:  ...76:f5 9/3/2018 7:45:17 PM

          Bounce Session Example: ...OF:5b 9/3/2018 2:52:56 PM