3 Replies Latest reply on Feb 4, 2019 9:51 AM by monica singh

    Calculated Field to find visitor Drop-offs

    monica singh
    monica singh Jan 30, 2019 8:06 PM

      Hello everyone,

      I am looking to create a report to show drop-offs in steps in a visitor's path. My data at granular level looks like this:

      Header 1Header 2Header 3
      VisitorEventdateEventname
      15541/10/2019  5:03:06 AMhome
      15541/10/2019  5:04:06 AMproduct
      15541/10/2019  5:05:00 AMregister
      15541/10/2019  5:06:00 AMconfirm
      15551/10/2019  7:03:00 AMhome
      15561/10/2019  7:03:06 AMhome
      15561/10/2019  7:04:05 AMregister
      15571/10/2019  5:03:06 AMproduct
      15571/10/2019  5:03:09 AMregister
      15571/10/2019  5:03:29 AMconfirm
      15581/10/2019  5:03:06 AMproduct

                                                                

      visitoreventdateeventname
      15541/10/2019   5:03:06home
      15541/10/2019   5:04:07productview
      15541/10/2019   5:04:07register
      15541/10/2019   5:05:30confirmation
      15541/10/2019   5:05:40thankyou
      15561/10/2019   1:01:10home
      15571/10/2019   1:05:10home
      15571/11/2019   1:06:10register
      15571/12/2019   1:09:10confirmation

       

      I want to get a count of visitors who followed this exact sequence from home -> product. In the above case, that would be just 1 visitor (1554). Similarly visitors who went from home->product->register, again only visitor 1554.

      I know how to achieve this using custom sql left joins, etc. But would like to do this using tableau calculated fields. I have looked at LOD, windows functions but cant put it together yet. Any help is appreciated.

      • 250 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Calculated Field to find visitor Drop-offs
          Prasanna R
          Prasanna R Jan 30, 2019 9:33 PM (in response to monica singh)

          Hi Monica,

           

          I have used Tableau Calculation to get the result. I have attached the 2018.3 version workbook for reference.

           

          So you create lookup functions to see what is available in the next rows and compare them if they are in sequence as you want.

           

           

          Note that initially you need to drag these 4 Stages into the row shelf as discrete and edit Table Calculation as shown below

          Do the same for all the Stages so that we can fix how the table calculations work for these fields when used in any other calculated field.

           

          Your final step will to check if they are in sequence

           

          Best Regards

          RP

           

          *If this helps, then please mark it Helpful and Correct to close this thread

          • 2. Re: Calculated Field to find visitor Drop-offs
            Zhouyi Zhang
            Zhouyi Zhang Ambassador Jan 30, 2019 9:53 PM (in response to monica singh)

            Hi, Monica

             

            Not sure how you define the sequence of event name

            One of the possible solution is to use parameters to define the sequence as shown attached workbook.

             

            Below is the result, and you can drag the calculation keep or not to filter and select True only.

             

            Please let me know if any question.

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: Calculated Field to find visitor Drop-offs
              monica singh
              monica singh Feb 4, 2019 9:51 AM (in response to Zhouyi Zhang)

              I like both the approaches using Lookup or LOD to get to the results. Will work on it and ask more questions as my actual data is far more complicated.

              Thank you Prasanna and Zhouyi