3 Replies Latest reply on Jan 12, 2019 4:19 PM by Shinichiro Murakami

    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