This post looks at how we can display what percentage of customers is made up of return customers, and what percentage is new customers. To achieve this, I’m using Level of Detail expressions to essentially create bins of customers, depending on when they placed their first order. I am doing this at the year level, but it can be done at any other level, as well. First off, here is a picture of the worksheet I created with this technique, using Tableau Superstore data.
We see how our first group of customers placed their initial order in 2014 and how this is by far the largest group overall. Every year, new colours are added to the view as new customers make their first order.
Getting Started
To start off, we can draw Order Date to columns, selecting discrete years, and distinct count of Customer Name to rows. We now have the total number of customers that placed orders each year.
Next, we create a calculated field that will brush the bars depending on the year of first purchase. The calculation is fixed on customer name and returns the minimum order date, truncated to year. So, for every customer we have create a new column with the year in which they placed their first order.
Dragging this new field onto colours will separate the bars into our customer cohorts. It’s now instantly visible that the number of new customers is shrinking drastically, from 595 in 2014 down to a mere 11 in 2017.
Digging Deeper
We can now add some more detail to explore this further: For example, we could add a percent of total calculation to see more clearly the exact percentage made up by each cohort. Doing this, we see that our oldest customers consistently place 75% of orders:
Another useful detail is to add the percentage of customers that are being retained in each cohort compared to the first year. We can see, for example, that the blue bar in 2015 is slightly smaller than in 2014, but to find out just how much smaller we need to add another LOD calculation. I’m calling this new field Retention Rate, and it shows what percentage of customers are still ordering after X amount of years.
To calculate the retention rate, I’m divining the distinct count of customers (which in the view is separated by years and cohorts) by the total number of customers in that respective cohort. To make the calculation work, we need to fix the denominator on the first LOD calculation, as this will return only the number of customers belonging to that group.
We can draw this new field to the tooltip shelf to have the percentage displayed in a tooltip, or we can create a new separate chart that only shows the retention rate over time by customer cohort:
This view shows nicely how for both the 2014 and 2015 group, around 25% of customers are lost after the first year, but the rate goes back up to almost 90% after 2 years. There are many other interesting facets to explore from here, such as sum of sales by year and cohort rather than number of customers. Overall, it’s a useful tool to get a quick insight into the behaviour of customers.