3 Replies Latest reply on Mar 15, 2017 3:44 AM by Stephane Marx

    Calculating # of new customers

    Zankar Thakkar

      Hi,

       

      I have large data set (40 million plus records). The data set already has a flag (is_first) that marks first transaction of the customer. So, I dont really need to use min_date function to identify first transaction.

      Whats most efficient way to get number of new customers and number of repeat customers by year?

       

      I have these calculations but numbers are not looking right.

      Total customers: COUNTD([customer_id])

      New customers: COUNTD(if [is_first] = 1 THEN [customer_id] ELSE "" END)

      Repeat customers: [Total Customers] - [New Customers]

       

      Should this be LOD calculations? Can we add conditional count in LOD? Basically, I want to count distinct customers where is_first = 1. Should I create sets?

      I also have visualizations that segment the new/repeat customers by price points etc.

       

      I cant share the dataset.

       

      Thanks,

      Zankar.

        • 1. Re: Calculating # of new customers
          Lisa Li

          Hello Zankar,

           

          Your total customer count should be correct. What do you define was new customers? The calculation that you currently have for new customers returns all of the customers (same as total customers) because each customer has a first transaction.

           

          If I understand you correctly, you should probably calculate new customers with a calculation referencing to the latest month or a date. Repeat customers on the other hand should be calculated by checking if a customer has more than one record (given that your data has rows for each transaction).

          if {fixed [customer_id]: count([customer_id])} >1 then [customer_id] end

           

          Hope this helps! If not, please share your workbook and the version of Tableau you're working with.

           

          Lisa Li

          CoEnterprise | Home

          • 2. Re: Calculating # of new customers
            Budi Lubis

            Add a flag for filter: IIF([is_first]=1,"New","Existing")

            You can then create a calculated field with the filter above set to "New" RUNNING_SUM(COUNTD([Customer_ID]))

            • 3. Re: Calculating # of new customers
              Stephane Marx

              Hi Zankar,

               

              Another approach could be to count based on the flag is new, like that:

              countd(if [Is new]=1 then ([Customer ID]) END )

              I attach a workbook with a dummy dataset similar to your use case.


              Hope it helps,

               

               

              Stephane Marx

              Interworks EU