6 Replies Latest reply on Apr 27, 2018 10:31 AM by Sunil Chiluveru

    Abandoned cases in a Funnel

    Sunil Chiluveru

      I have a dataset that is based on google analytics data. Here is a shortened version of the problem based on my data.Assume there are 3 pages in my website HomePage -> ProductPage -> SubmitPage. Google analytics tags each session using a PhoneNumber in our case. Some people drop off once they reach ProductPage. Some Drop off after the HomePage itself. Some go all the way to the end and reach the SubmitPage. I have the following dataset


      Customer    PhoneNumber    Stage

      --------------   --------------------    --------

      Cust1          11111                   HomePage

      Cust1          11111                   ProductPage

      Cust2          22222                  HomePage

      Cust3          33333                  HomePage

      Cust3          33333                  ProductPage

      Cust3          33333                  SubmitPage


      I have a Funnel report as shown in the attached picture. When I click on any of the bar I show the detailed rows that are there in that particular stage. This was easy to do. What I am now looking for is abandaned/lost opportunities. So for example when I click on SubmitPage bar I want to see all the PhoneNumbers that reached till ProductPage but did not reach SubmitPage. So from the above dataset I should see 1 record "11111" when I click SubmitPage bar. When I click on the ProductPage bar I should see 1 record "22222". Obviously I can do such stuff easily in traditional reporting solutions using dynamic queries. But I want to know if such thing can be done in Tableau.

        • 1. Re: Abandoned cases in a Funnel
          Jennifer VonHagel

          Hi Sunil,


          Something can be done to list the phone numbers of lost opportunities, but your sample data is too simplistic to give very concrete examples. Usually a customer's path in web analytics is not such a pre-defined funnel (if yours is, cheers, you're lucky ). The key here is going to be to figure out concrete business rules, see if you can shape your data before bringing to Tableau (if not, you still have options).


          For instance, if a customer can browse multiple pages along their path to hitting Submit, their session path is going to look different than merely which Stage # they are on (see Cust1 example below). The Session Path here is created by ranking the Datetime along the Session ID.

          In this case, I'd recommend showing lost opportunities on the step abandoned, rather than on the next step, since if a customer has multiple potential next steps you can't possibly guess which one to show.  So here you could create a calculation that checks if the customer's session step is the last step of the session. If so, flag it, and then show the list of phone numbers by Stage Desc where Last Page of Session = Yes.


          However, if what you're measuring is just a few set of pages that are extremely restricted so that the customer only has the choice to move forward to one pre-determined page or abandon, then you could create a lookup table to determine the customer's next step that they didn't take:


          You could then assign the predicted next stage to each individual record, or maybe more helpfully at the session level. This way, if you wanted, you would also have the option to show the entire clickstream of any given customer based on where they abandoned.


          Hope this helps. Again, Tableau can do some of this, but rules for the data need to be thought out and handled.




          • 2. Re: Abandoned cases in a Funnel
            kumar bharat

            Hi Sunil,

            Please try using sets.it should help your case.

            you can check the below links.



            • 3. Re: Abandoned cases in a Funnel
              Sunil Chiluveru


              Thanks for the answer. So my dataset is relatively simple because I group together traffic to certain pages into "Stages". My data indeed has a SessionId. In the second screenshot that you have you have the field "Last Page of Session". How can I add a calculated field that does that exact calculation? That will solve my problem.




              • 4. Re: Abandoned cases in a Funnel
                Jennifer VonHagel

                Hi Sunil, try this. For your data, you will need a timestamp for each stage, or a path number like in my example.


                Here I have customers with multiple order dates. I first want to find the max order date per customer. In your data, you'll want the max timestamp per session id.


                Then I can simply say: Order Date = Customer's Max Order Date to get a True/False flag.


                The formula to find the max order date per customer is this. This says: across each customer name (or group by customer name) find the max order date.

                { FIXED [Customer Name] : MAX([Order Date]) }


                To create a True/False Flag - just add this:

                [Order Date] = { FIXED [Customer Name] : MAX([Order Date]) }


                Some notes about FIXED calcs:

                FIXED formulas will disregard filters in your data. So say you filter this example to show only the year 2014, Aaron Bergman's Max date will still show as 11/10/2016.


                To get around this, filters that should apply to FIXED formulas need to be added to context:

                And now the FIXED formula will find the max only within year 2014.


                Hope this helps,


                • 5. Re: Abandoned cases in a Funnel
                  Sunil Chiluveru

                  Excellent. That works. I see the other solution proposed that uses Sets also works but this one is much easier. Thanks a bunch.

                  • 6. Re: Abandoned cases in a Funnel
                    Sunil Chiluveru

                    Thanks Bharat. This solution works too. I needed to create multiple sets that match each of my stage to make this work. The solution suggested by Jennifer is much easier so I marked that as the answer but this solution works too.