KNOWLEDGE BASE

How to calculate the number of repeat and new customers


Published: 26 Apr 2022
Last Modified Date: 18 May 2022

Question

How to calculate the number of repeat and new customers

Environment

  • Tableau Desktop

Answer

*At first, in this case [Number of repeat customers] as defined is refers to all customer records other than 1st-time purchases.
(Example.)
 Purchase history for 2019/1
     Customer A : 2019/1/1 (1st purchase)
     Customer A : 2019/1/5 (2nd purchase)
     Customer B : 2019/1/5 (1st purchase)
=> [Number of repeat customers] = 1
     [Number of new customers] = 2

 Purchase history for 2019/2
     Customer B : 2019/2/1 (2nd purchase)
     Customer B : 2019/2/5 (3rd purchase)
     Customer C : 2019/2/5 (1st purchase)
=> [Number of repeat customers] = 2
     [Number of new customers] = 1

<Procedure>
1. Connect to Sample Superstore.
2. While right-clicking drag [Order Date] to Columns -> Select MY(Order Date)
3. Drag [Order Date] to Filter Pane -> Select Years -> Check in '2018' and '2019'.
4. Create new Calculated Field as following.
     Name : Date of first purchase
     Calculation : { FIXED [Customer Name] : MIN([Order Date])}
                          //Calculate the date of 1st purchase for each customer.
5. Create new Calculated Field as following.
     Name : New or Repeat Flag
     Calculation : IF [Order Date] = [Date of first purchase]
                          THEN 1     //New customers
                          ELSE 0     //Repeat customers
                          END
6.  Create new Calculated Field as following.
     Name : Number of new customers
     Calculation : SUM([New or Repeat Flag])
                          //Calculate the total of New customers. 
7.  Create new Calculated Field as following.
     Name : Number of purchasers
     Calculation : COUNT([Customer Name])
8. Create new Calculated Field as following.
     Name : Number of repeat customers
     Calculation : [Number of purchasers] - [Number of new customers]
9. Drag [Number of new customers] to Rows.
10. Drag [Number of repeat customers] to View.
User-added image
 
Did this article resolve the issue?