2 Replies Latest reply on Dec 13, 2018 8:51 PM by Raoul Verhaegen

    Number of new & lapsed customers for each month

    Raoul Verhaegen



      I'm stuck on a basic dashboard I'd like to create... I'd like to show for each month :

      1. the number of customers for which this is the first order (new customers)
      2. the number of customers whom we lost (one year without an order)


      I've started doing it on the Sample data but I'm having trouble with it and I've read the numerous threads that address this topic but to me they never really address the very issue I'm having.


      Any help ?



        • 1. Re: Number of new & lapsed customers for each month
          Manuel Velasquez

          Hi Raoul


          Hi hope this gives you ideas on how to get the result you are looking, I'll show you one example to display New Customers per month and Customers that place an order on or after 365 days (Resurrected customers) per month.


          For this example, I'm using SQL Server and a version of Superstore dataset.

          We need to calculate the difference in days between orders for the Resurrected customer (Customer with a year or more that a year with no orders)


          To do this I will generate another column that has the previous order date per each Order ID, also match all this information per customer, having this structure it will be a lot easier to analyze.

          I followed Bronson Shonk article  Tableau Doctor: 1 Column, 2 Dates? Use Custom SQL | Tableau Software  to create the customs queries.



          When I establish the connection to my server on the data source page create the first Custom SQL


          --Custom Query 1 to get the order number with DENSE_RANK() function




          Then I created another Custom SQL, this second custom SQL is to generate the previous order date value. Also, we generated a previous order number for a LEFT JOIN.




          Perform a LEFT JOIN between these 2 Custom SQL




          The data look like this




          Customer first order



          New Customer Condition




          Days Between orders




          Resurrected customer





          Raw data view




          Visualizing the values by month







          Attached .twbx file for reference


          Let me know if this helps!


          Manuel Velasquez

          1 of 1 people found this helpful
          • 2. Re: Number of new & lapsed customers for each month
            Raoul Verhaegen

            Thanks Manuel! Sorry for the late reply