4 Replies Latest reply on May 2, 2018 7:42 AM by Nerea Cid Vieytes

    Calculated Field across rows - Web Funnel

    Nerea Cid Vieytes

      Hi all,

       

      I am looking at Web Analytics data where a visitor which has an idvisit goes from one page to the other and each of those have a pagename. The data looks therefore like the following :

       

      idvisit    page    time

      123     mainpage   12:30

      123     contactus    12:35

      124     mainpage    2:30

      125     mainpage    2:40

      125     step1           2:45

      125     step2           2:46

      126     step1           2:50

       

      etc

       

      I would like to create a parameter that returns me the number of visits that were on mainpage and step1 to create a Click-Throug-Rate (visits on step 1/ visits on mainpage) and eventually the funnels. There are different entries to the web funnel so if I just take the steps separetely it doesn't really work as I will have 2 (125, 126) / 3 (123,124,125) = 66% while in reality I am looking for 1 (123) / 3 (123,124,126) = 33%.

       

      One way I was able to do this is creating a set that gives me the common members of the group 1 (page = mainpage) and the group 2 (page=step1)  but I am not able to replicate that automatically.

       

      I am guessing there is an easier way (maybe with an LOD?) to do this but I wasn't able to find it..

       

      Anyone has any ideas?

       

      Thanks!

        • 1. Re: Calculated Field across rows - Web Funnel
          Jennifer VonHagel

          Hi Nerea, you can use either sets or the FIXED LOD calcs to find this.

           

          Here I am showing the two FIXED formulas which are used in the final CTR calculation. It isn't necessary to break these out into separate calculations - you can put all these calculations in one single field.

           

          The FIXED calcs are in blue, the Sets are in purple. You can see they give the same result.

           

          When you use FIXED calculations and sets, any filters you want to apply to these calculations need to be Added to Context in order to have the right effect.

           

          Hope this helps,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Calculated Field across rows - Web Funnel
            Nerea Cid Vieytes

            Thanks @Jennifer! This is awesome. Just for my understanding, why does the MAX expression return a boolean in this case ? This is exactly what I was looking for but I wouldn´t have found the right answer without your help as Tableau describes the MAX function as "returns the maximum of a single expression" so I was automatically associating it to numerical values.

             

             

            Adding the screenshot of the calculated field in case it serves as reference to other people, thanks again!

             

             

            • 3. Re: Calculated Field across rows - Web Funnel
              Jennifer VonHagel

              Hi Nerea, glad it helped!

               

              The inside of the max expression is a boolean expression - "[Page] = 'mainpage'" will return True or False on each record.  The max of a boolean field - max(True,False) - returns True.

              Here's a breakout of the intermediate formulas. Does it make sense?

               

              1. Finds True/False for each session id whether that session has 'mainpage' at all, or 'step1' at all.

              2. Checks if [Sess has MP] and [Sess has Step1] are both true

              Best,

              Jennifer

              1 of 1 people found this helpful
              • 4. Re: Calculated Field across rows - Web Funnel
                Nerea Cid Vieytes

                Yes, that is great! So simple when you see it !