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.
Thank you for providing your feedback on the effectiveness of the article.