6 Replies Latest reply on Jan 17, 2019 8:19 AM by Sarah Yue

    Calculate New or Existing Customers

    Sarah Yue

      Hi,

       

      I'm working with some data that requires me to distinguish an existing or new customer and show me how many there are in 2018 and every month.

       

      A new customer is someone who has ordered for the first time ever, or who has ordered for the first time in 12 months.

      An existing customer is someone who has ordered in the past 12 months.

       

      I've created a table calculation that is capable of distinguishing the new or existing customers, but I can't figure out a way to create a graph with the data that shows me how many new/existing customers there were in 2018 by month and year.

       

      I've attached a workbook with an example data set.

       

      There is also a quantity ordered that may help. It shows how much they ordered in each shipment, though this doesn't distinguish between new/existing.

       

      Thanks!!

        • 1. Re: Calculate New or Existing Customers
          Joseph Wibowo

          Tbh, your method doesn't make any sense to me. You need to create a flag for each customer ID to indicate if they are a new or existing customer. I did it this way:

           

          {FIXED [Customer ID]: MAX(IIF([Order Date] >= DATEADD('day', -365, TODAY()) AND

                                    {FIXED [Customer ID]: COUNTD([Order Date])} > 1

                                    , 1, 0))

          }

           

          This is a LOD calc that flags if the customer has had at least 2 orders in the past 12 months because if they have had only 1 order in the past 12 months, they are considered new. You should really use COUNTD([Order ID]) if you have that field. Then you can just mark if the customer is new or existing using a new calculated field:

           

          IIF([is_existing_flag] = 1, "Existing", "New")

           

          Then you just add date and flag dimension + a metric to get the groups over time

          • 2. Re: Calculate New or Existing Customers
            Sarah Yue

            Thanks for your reply!

             

            I tried adding this calculation into the workbook and it doesn't work. I'm trying to get: if the previous order is >12 months before the next order, then they are considered new again.

             

            So if a customer orders on 2/1/2017 but not again until 3/1/2018, they are a "new" customer. The calculation you provided only flags the customer as new if they've had <2 orders from today.

             

            See customer 133602 is "new" for their 6/20/2018 order because their order prior was 5/4/2017, which my table calculation flags as new, but the calculation you provided flags them as existing because they've had >2 orders since today.

             

            • 3. Re: Calculate New or Existing Customers
              Shinichiro Murakami

              Actually, This type of table calc is quite tough.

               

               

               

               

               

               

               

               

               

               

               

               

               

               

              Hide "hide"

               

               

              Thanks,

              Shin

              • 4. Re: Calculate New or Existing Customers
                Sarah Yue

                Hi Shinichiro!

                 

                Thank you so much for your help!!

                 

                This looks like exactly what I need - I'm fairly new to Tableau and I can't get my table to look like yours when I put it together. Could you show me how you created Sheet 4(2) from a blank page?

                 

                Thank you so much!!

                • 5. Re: Calculate New or Existing Customers
                  Shinichiro Murakami

                  I cannot do more detail than what I already done.

                  It's almost most detailed step by step explanation..

                   

                  If you say this is not enough, better start from very basic training first.

                   

                  Learning | Tableau Software

                   

                   

                  Shin

                  • 6. Re: Calculate New or Existing Customers
                    Sarah Yue

                    Hi Shin,

                     

                    I understand how to create all the calculations. My actual data set has a lot more fields so when I try to make the chart, it doesn't show up the way yours did, which is why I was asking if you could show the order you put the fields in.

                     

                    Thanks!

                     

                    Sarah