6 Replies Latest reply on Jul 5, 2016 1:43 PM by Shinichiro Murakami

    Sales conversion by monthly cohort

    jonathan.o'grady.0

      Dear tableau fans!

       

       

      I've been struggling with this little problem for a few days, and out of desperation I am now reaching out to you all to see if anyone might be able to help… Please!

       

       

      My problem is quite simple, at least I thought! I would like to track the progression of orders from initial enquiries through to verified sales.

       

       

      When an initial enquiry enters the system it is given the status 10. Upon reaching status 20, this initial enquiry is considered a bona fides enquiry for record purposes. The enquiry then progresses through further stages, ultimately resulting in an invoice or the enquiry being lost. Each of these stages is recorded, however an order will not necessarily progress sequentially through every stage, and may skip a stage. Once an enquiry reaches stage 45, 50 or 55 (whichever first) it is considered a converted sale for commission purposes.

       

       

      I would like to create a simple table which takes all of the enquiries received in a particular month, and then displays the enquiries progress to order or lost over subsequent months. Ultimately my solution will look something like the following:

       

       

      This says that the business received 650 enquiries in February 2016. 140 of these enquiries converted to orders. 60 in February, 40 in March et cetera.

       

       

      However this table can also be read downwards, where it shows that 100 orders were generated in March 2016. 40 of these orders were from February enquiries.

       

       

      The problem in getting is that the enquiry status fields are dimensions. I'm not sure how to contrast two dimensions in one table.

       

       

      Anonymized data attached. Would really love some helpful suggestions as to how to take this forward.

       

       

      Many thanks as always,

       

       

      Jonathanorder progression.jpg

        • 1. Re: Sales conversion by monthly cohort
          Shinichiro Murakami

          Not quite sure what you want to accomplish, anyways, here is my approach.

          Just put submit date to row shelf and outcome date to column shelf.

          And add Enquiry Outcome to Column shelf/Color shelf.

           

          I added two more different versions in other sheets.

           

          By the way, version 10.0 is not released yet, and you might have trouble to share the workbook with other people.

           

          Thanks,

          Shin

           

          10.0 attached.

          • 2. Re: Sales conversion by monthly cohort
            jonathan.o'grady.0

            Shin,

             

             

            Thank you very much for taking the time to reply to my query.

             

             

            Apologies about using tableau 10 (beta)… Forgot about that when posting question!

             

             

            Your solution is a good start, but it does not solve my problem specifically. Let me explain, perhaps you will have another idea…

             

             

            You suggest bringing enquiry outcome to the columns shelf. My problem is that an enquiry is considered a sale once it gets to stage 45, 50 or 55. Some enquiries will go through all three of these stages, whereas others will just progress through one or two.

             

             

            My issue is that I don't want to double count. If an enquiry gets to stage 20 in March, I wish to count this enquiry as part of the "March cohort". If this enquiry then reaches stage 45 in April, I wish to say that it converted to a sale in April. If the enquiry subsequently goes to stage 50 or 55 in subsequent months, I do not want to count that enquiry in those months. However, if the enquiry never goes through stage 45, but achieve stage 50 in June, I want to be able to say that the enquiry converted to a sale in June.

             

             

            You see my problem. If I simply group the stages 45, 50 and 55, I run the risk of double counting. However, if I only focus on stage 45 I will miss certain sales. I need to create a calculation which will look for the earliest month which achieves stage 45, 50 or 55, and count that as the conversion month. Subsequent months will then reference that a sale has already been recognised in an earlier month and so the count for the sale will not be recorded in following months.

             

             

            Moving enquiry outcome to the columns shelf does not solve this little riddle. However, using a filter is also incorrect. I thought about using a dynamic set, but I'm a little unsure as to the exact calculation.

             

             

            Would love to know your thoughts!

             

             

            Best wishes,

             

             

            Jonathan

            • 3. Re: Sales conversion by monthly cohort
              jonathan.o'grady.0

              By the way, please find attached the data file which you can upload to tableau 9.X if needs be…

               

               

              Best wishes,

               

               

              Jonathan

              • 4. Re: Sales conversion by monthly cohort
                Shinichiro Murakami

                I hope I understand the logic correctly, but the data / logic is quite tricky and might be wrong.

                You can understand the logic behind and modify the formula/ VIZ as you want.

                 

                Anyways,

                 

                Several Steps.

                 

                [Count 40,45,50 midway]

                {fixed [Record],[Modified],[Enquiry Status]:countd(

                if [Enquiry Status]=45 or [Enquiry Status]=50 or [Enquiry Status]=55

                then [Record] end)}

                [Earliest 45,50,55 date]

                {fixed [Record]:min(if [Count 40,45,50 midway]>=1 then [Modified] end)}

                [Status at Earliest]

                if [Modified]= [Earliest 45,50,55 date]

                and

                ([Enquiry Status] = 45 or

                [Enquiry Status] = 50 or

                [Enquiry Status] = 55 )

                then [Enquiry Status] END

                [Last Status / Record]

                {fixed [Record]:max([Status at Earliest])}

                [Converted or Not]

                if [Last Status / Record]=45

                or [Last Status / Record]=50

                or [Last Status / Record]=55

                then "Converted"

                else "Not Converted"

                END

                [Count Record]

                countd([Record])

                 

                Thanks,

                Shin

                 

                9.0 attached.

                • 5. Re: Sales conversion by monthly cohort
                  jonathan.o'grady.0

                  Shin,

                   

                   

                  You're an absolute genius! Thank you so much for taking the time to look into this gnarly little problem…

                   

                   

                  I love your elegant solution, especially "Count 40,45,50 midway". I didn't realise that you could use if statements inside LOD expressions in that way. Fantastic!

                   

                   

                  I have a strong feeling that a lot of other users will find information above useful also.

                   

                   

                  Great work! Thank you so much!

                   

                   

                  Best wishes,

                   

                   

                  Jonathan

                  • 6. Re: Sales conversion by monthly cohort
                    Shinichiro Murakami

                    That's my pleasure.

                    Enjoy!

                     

                    Thanks,

                    Shin