• Bernard

Calculating Cumulative Unique Customers in Tableau


In this post, I will show you how to create a viz to show the cumulative number of unique customers.

The Problem

Using this data.

I would like to create a viz showing the cumulative number of unique customers – without double counting. i.e. Once a customer makes the first purchase, he or she is not counted in the subsequent purchases.

Using data in the snapshot above, I have created a simple viz showing what you will get after computing cumulative number of unique customers – the normal way.

According to this viz, the total cumulative number of customers is 26. This is so because Tableau does not exclude customers making the 2nd, 3rd, 4th purchases in its running count distinct – hence leading to a higher value.

How can we correct this?

Solution

To solve this, first we need to compute the first purchase date of every customer using the formula below

Next, classify customers as either ‘New customer’ or ‘Return customer’ using the formula below

Now, let build the view as follows;

  • Drag Date field to the columns shelf

  • Drag COUNTD(Customer id) to the rows shelf

  • Add ‘New/Return Customer’ to the filter and chose ‘New customer

  • Add a running total on COUNTD(Customer id)

According to this view, the total cumulative number of unique customers (without double counting) is 12, unlike in the first view where we are getting 26 due to double count.

So, next time you need to evaluate cumulative number of customers or any other metric without considering the subsequent occurrences of the metric. Try this technique.

Thank you for reading.