Question
How to calculate the number of repeat and new customers
Last Modified Date: 18 May 2022
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.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials