7 Replies Latest reply on Jun 29, 2016 3:43 AM by Jennifer Zoerns

    Cohort - cumulating distinct customers over periods

    Jennifer Zoerns

      Hi everyone,

       

      I am new in tableau and now trying to build a cohort analysis. My big question here is how many new Customers of my cohort month are buying at least a second time after x periods.

      The other topics in the community are helpful, but are not giving me the answer for my question.

      I want to cumulate the buyers distinct over months. I’ve already build the basic cohort, but my cumulation is not correct.

       

      For example:

      Person A is a new customer of 2016-01. He has other purchases in 2016-02 and 2016-05. In the cohort we are counting this person in period, 0, 1 and 3.

       

      I think I just need to get the order month after the cohort month per customer.

      Or is there another way to do a count distinct of customers within the cumulation?

       

      Thanks,

      Jennifer

        • 1. Re: Cohort - cumulating distinct customers over periods
          Mark Fraser

          HI Jennifer

           

          I'm in the middle of something (hence the brief reply), but I saw your question and I hoped this may help (if you have v9)

          Top 15 LOD Expressions | Tableau Software

           

          Number 10.

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: Cohort - cumulating distinct customers over periods
            Ashish Chaudhari

            Hi Jennifer,

             

            Can you please draw some rough output which you are expecting?

             

            I tried something like this. Please refer to the screenshot below.

            This is showing Customer-wise first purchase then order months and orders that he placed. i.e Aaron first purchase was in Feb 2011 and March 2011 is 2nd purchase and Nov 2013 is 3rd purchase.

             

            I would suggest please give some rough output which you are looking for.

             

            Thanks and Regards,

            -Ashish Chaudhari

            • 3. Re: Cohort - cumulating distinct customers over periods
              Jessica Fan

              Hi Jennifer,

               

              My answer differs slightly from Ashish's suggestion, again it would be helpful if you gave an idea of the output you are expecting.

               

              The first sheet shows the number of months between each purchase for each customer

               

              I then pivoted the data to show how many customers purchased after x months

               

              Please see attached workbook, hope this helps

               

              Jessica

              • 4. Re: Cohort - cumulating distinct customers over periods
                jonathan.o'grady.0

                Jennifer,

                 

                 

                Tableau is really good at this type of analysis.

                 

                 

                I find the best solution is to use Level of Detail:

                 

                 

                Calculated field 1: date first order by client

                 

                 

                {fixed  [User Id]:MIN( [order date] )}

                 

                 

                This calculates the date of each customers first order

                 

                 

                Calculated field 2: date first order by order

                 

                 

                {fixed  [order number]:MIN( [order date] )}

                 

                 

                (This field may not be necessary, depending on how your data is organised)

                 

                 

                Now move calculated field 1 to the row shelf. Change to month/year and discreet.

                 

                 

                Move calculated field 2 to the columns shelf. Again change to month/year and discreet.

                 

                 

                Right click on [order number] and dragged to the body of the table. Select count discreet.

                 

                 

                You should now have a table which reading across, shows the total number of orders made by customers in say August, and then the orders by those same customers in September, October and November.

                 

                 

                Reading the table down, you can see that in November the business received X number of orders. The column shows how many of these orders were from first-time November customers, and how many came from customers from previous cohorts.

                 

                 

                Hope this helps!

                 

                 

                Best wishes,

                 

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Cohort - cumulating distinct customers over periods
                  Ashish Chaudhari

                  Hi Jennifer,

                   

                  Please find the attached workbook. There are total 793 customers.

                   

                  I have created unique customer count by year and month (Normal and Cumulative). Let me know if this helps.

                   

                  Year level

                   

                  Year Level Cumulative

                   

                  Month Level

                   

                  Month level Cumulative

                   

                   

                  let me know if this helps.

                   

                  Thanks and Regards,

                  -Ashish Chaudhari

                  • 6. Re: Cohort - cumulating distinct customers over periods
                    Jennifer Zoerns

                    Hi Mark,

                     

                    thats exactly what I did. I am using the fixed function with min() just on a monthly base (with datetrunc) , then an iif and the datediff to get the periods since the 1st order.

                     

                    Thanks and Regards,

                    Jennifer

                    • 7. Re: Cohort - cumulating distinct customers over periods
                      Jennifer Zoerns

                      Hi everyone,

                       

                      here is an example what I am trying to do:

                      I've got 4 customers who had their first purchase on Jan 2016, with different number of orders.

                       

                      The result I am trying to build should be like this:

                      Here 50% of the new customers of January bought at least a second time after 1 period. 75% bought until now.

                      I am excluding the period 0. If someone had an order in period 0 he is not relevant.

                       

                       

                       

                      Steps between:

                       

                      Customers per period:

                      Customers cumulated distinct:

                       

                       

                       

                       

                       

                      Thanks!

                      Regards,

                      Jennifer