1 Reply Latest reply on Jan 23, 2018 4:19 PM by Manuel Velasquez

    Number of new & lapsed customers for each month

    Raoul Verhaegen

      Hi,

       

      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 ?

       

      Thanks

        • 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

           

          2.PNG

           

          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.

           

          3.PNG

           

          Perform a LEFT JOIN between these 2 Custom SQL

           

          4.PNG

           

          The data look like this

           

          5.PNG

           

          Customer first order

          6.PNG

           

          New Customer Condition

          7.PNG

           

           

          Days Between orders

          8.PNG

           

           

          Resurrected customer

           

          9.PNG

           

           

          Raw data view

           

          10.PNG

           

          Visualizing the values by month

           

          11.PNG

           

           

           

           

          Attached .twbx file for reference

           

          Let me know if this helps!

           

          Manuel Velasquez