8 Replies Latest reply on Apr 18, 2017 12:03 PM by P N

    Cohort analysis based upon first order behaviour

    Thom Naylor

      Hi Folks,

       

      I am struggling with a problem to try and build 'cohorts' based upon a certain type of behavior in the first order we see from a customer. I have attached a workbook with some dummy data but it presents the scenario which I am facing fairly accurately. I have some basic dimensions relating to orders; date, time, value, customer ID and, most importantly for this query, "Order fulfillment" (this can be either 'fulfilled' or 'unfulfilled'). What I would like to be able to do in Tableau is to identify, for each specific customer, what the fulfillment status of their first order was and then use this status to build cohorts. In this way I can compare the average number of orders per customer and, by using the cohorts, understand whether the fact that a user has an unfulfilled order on their first order impacts the number of times they return to make further orders when compared to a user whose first order was successfully fulfilled.

       

      When building this dummy set of data in excel I was able to calculate this "1st Order fulfillment" dimension and therefore you are able to see what the visualisation should look like on sheet 2. In practical terms I am working with a large data set which is constantly expanding so I need something 'dynamic' in tableau that can handle this calculation for me.

       

      Any help or guidance would be much appreciated.

       

      Cheers, Thom.

        • 1. Re: Cohort analysis based upon first order behaviour
          Matt Lutton

          Nowadays, anytime I see "cohort analysis", I immediately think of #2 at the link below -- have you checked this out?

           

          Top 15 LOD Expressions | Tableau Software

           

          By using a FIXED expression, the First purchase date per customer can be found, and that can then be used to create your Customer "Cohort" buckets.  Pure genius!

          2 of 2 people found this helpful
          • 2. Re: Cohort analysis based upon first order behaviour
            P N

            Hi Thom:

             

            As Matt pointed out your solution is there in the "Top 15 LOD Expressions | Tableau Software". Thanks Matt.

             

            Being new to Tableau I tried to solve it and thought that you might find the attached solution helpful. Look at the 3 calculated fields that i have added.

             

            Cheers

            • 3. Re: Cohort analysis based upon first order behaviour
              Thom Naylor

              Hi Matt,

              thanks for the guidance. I had actually already tracked down that article and used exactly the section that you recommended. It works perfectly in terms of creating the cohorts based upon first order date. The bit I was really struggling with was the ability to then identify the fulfillment status of that first transaction and use that to further subdivide the cohorts. That said, the follow up from PN with some new calculated fields, including a further LOD calculation really did the trick.

               

              Thanks for you help.

               

              Cheers, Thom.

              • 4. Re: Cohort analysis based upon first order behaviour
                Thom Naylor

                Hi P N,

                 

                spot on, that worked perfectly. The additional LOD expression gives me my sub-divided cohorts.

                 

                Thanks for your effort.

                 

                Cheers, Thom.

                • 5. Re: Cohort analysis based upon first order behaviour
                  helen.mertes

                  Hi, I have a very similar use case, I want to define customer cohorts by first order date and also by the type of product the initial order contained. Until now I could not figure out how to do this and I just came across your post.

                  Would you mind sharing the solution with the additional LODs? That would be great!

                  Thanks!

                  • 6. Re: Cohort analysis based upon first order behaviour
                    P N

                    Hi Helen:

                     

                    There are 3 steps to define customers cohorts  using LOD's:

                     

                    1. First Order date: {fixed[Customer ID Proxy]:min([Order Date])}
                    2. What they ordered on the first order date:  IIF([Order Date]=[Calculation1],[Product],NULL)
                    3. Use the product in first order to define the customer cohort: {fixed[Customer ID Proxy]:max([Calculation2])}

                     

                    Hope this helps. Let me know if you have any more clarifications or if you are thinking of something more complicated than illustrated here.

                     

                    Thanks

                    1 of 1 people found this helpful
                    • 7. Re: Cohort analysis based upon first order behaviour
                      helen.mertes

                      HI P N,

                      Thanks a lot! I am very new to Tableau and not yet familiar with the logic and syntax, so sorry for potentially stupid questions, but maybe you can help me out further…

                       

                      I understand the LOD to define the customer acquisition date (i.e. first order) and managed to use the 2nd LOD you suggested (“what they ordered on the first order date”) as a filter, to select the items that were in a customer’s initial order’s basket. Not sure that is right though as you mentioned a 3rd LOD, but I am not sure how to implement/apply the 3rd LOD to define the customer cohort?

                       

                      I am then using “iif([Ordertime]>[Acq date],[Ordertime],null)” to define repeat purchases, and counting distinct # of customer IDs to get active returning customers.

                      Basically, the retention/cohort data should be displayed in a sort of triangle table format (this is just test data, so looks a bit weird):

                      In the end I would like to display retention rates in % for the specified cohorts (e.g. on a monthly basis and also on average for each month n+1, e.g. 12m/24m). In the acquisition month (i.e. month 1) the retention rate should obviously be based on the # of new acquired customers, so 100%. At the moment it still counts the repeat orders of customers in the same month as their first order, so the “repeat purchase” needs to be amended with a condition to only show repeat orders from month 2. I tried to include this as second condition “month(ordertime)<>month(acq date)" using a “case when” function but did not yet succeed…

                       

                      In addition to % retention, I need to also include respective revenues, avg basket value and avg # of orders of these active customers in the defined cohorts.

                       

                      Thanks very much in advance for your help!

                       

                      Helen

                      • 8. Re: Cohort analysis based upon first order behaviour
                        P N

                        Hi Helen:

                         

                         

                        Let me take another stab at this to clarify the LOD's used. Let's take an example for a single customer.

                        • Customer A purchased toys on 1/1/2017
                        • Customer A purchased shoes on 2/15/2017
                        • Customer A purchased shirts on 3/20/2017.

                        The first LOD captures the first purchase date of the customer (in this case 1/1/2017), The second LOD defines the customer based on purchase made on first purchase date (in this case toys). The third LOD defines the customer as a part of "toys" cohort and hence his later purchases are defined as "toys" cohort even though he purchased shoes/shirts subsequently.

                         

                        I think something is getting mixed up. If you can attach the mock data then we can try to work on this.

                         

                        Thanks,

                        P N