4 Replies Latest reply on Nov 18, 2015 11:46 PM by Amos Mbugua

    Cohort Analysis

    Amos Mbugua

      Hi,

       

      I need to replicate the following calculations on excel in tableau with regards to churn calculations. Am mostly interested on the rows which is sales for each cohort group for month 1,row 2 will sales across the cohorts for month 2 and so on

        • 1. Re: Cohort Analysis
          Mark Bradbourne

          There are no calcs that I see in the attached spreadsheet? Can you share the raw data? I'm sure that the chart in the excel file can be duplicated.

          • 2. Re: Cohort Analysis
            Amos Mbugua

            Hi Mark,

             

            Sorry for the delayed response. I have attached workbook with the data.

             

            Instead of having the actual months of order on the rows, I would like to have Month 1,month 2 and so on. This will enable me to average  month 1 for all customers who started in different months.

            • 3. Re: Cohort Analysis
              Mark Bradbourne

              Maybe a calculation like this, on both dates would do the trick? I'm guessing to normalize it for each customer you need to check to see if there was an order in the given month to make it their true "Month n"?

               

              Hopefully this can get you started on a path.

               

              If [Date Order] = { FIXED [Customer] : MIN(([Date Order])) } Then "Month 1"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+1 then "Month 2"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+2 then "Month 3"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+3 then "Month 4"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+4 then "Month 5"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+5 then "Month 6"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+6 then "Month 7"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+7 then "Month 8"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+8 then "Month 9"

              ElseIf Month([Date Order]) = (MONTH({ FIXED [Customer] : MIN(([Date Order])) }))+9 then "Month 10"

              END

              1 of 1 people found this helpful
              • 4. Re: Cohort Analysis
                Amos Mbugua

                Thanks Mark.