In LOD we trust Or How to create your first customers segmentation?

In LOD we trust Or How to create your first customers segmentation?

LOD functions used for segmentation

Every marketing strategy starts with a customers’ segmentation. You can group your customers by their age, their sex, their education, their address, their marital status or by their sales numbers. A good segmentation is a key to optimization of your marketing efforts.

Today to create a customers’ classification we will use our sales data. Knowing that 2013 is the most recent year we have in our database let’s start with calculating sales numbers for 2013. As you remember, in my last article (learn more) I explained how we can create a calculated field to measure the Profits in 2013. We will use this field to create our Sales 2013 field. You can proceed as follows

Duplicate a Calculated Field in Tableau

Customers Segmentation using the IF formula

Now as you have your Sales 2013 field, you can create your segments. We will divide our customers into four groups using the IF formula

  • A Customers having ordered for more than 5000 dollars
  • B Customers having ordered for more than 2500 dollars
  • C Customers having ordered for more than 1250 dollars
  • D Customers having ordered less than 1250 dollars

It goes without saying that you can select only three segments, decide to make your segmentation based on the profit indicator etc…

If formula in Tableau to create a segmentation

Let’s now use our newly created field to analyze our customers’ numbers per segment. Double click on your Segment and on Customer Name. Even though it’s not exactly what we expected we can see a list of customers with a color code showing if it’s an A customer or a B/C/D one. As we want to have a number of customers per segment, we will transform our Customer Name pill into a measure CNTD(Customer Name). Something went definitely wrong as we now have only A CUSTOMERS.

Bar chart in Tableau customers segments

Level of Detail formula to manage your segments

Let’s duplicate our Segment field and make some small, but extremely important changes. This is a typical situation where we need to use an LOD (Level on Detail) formula. Whereas a concept is quite difficult to explain, it becomes really obvious when we have some concrete examples. We will use the FIXED formula on the Customer Name level. Basically, we will say to Tableau to create our segmentation based on the Sales 2013 aggregated on the customer level.

LOD fixed formula based on sales

As you can see our first calculated field (Segments) is a measure, whereas the second one (Segments LOD) is a dimension. Let’s bring it to our view (by double-clicking), now we can easily see the distinct segments of our customers. The D segment is the most important one. Of course, you can change the sales ranges if needed or even give the control to your users through the parameters to determine the ranges of sales defining each segment.

Number of Customers based on LOD function

And more importantly, we can now analyze different kind of indicators based on our segmentation. You can use bar chart, tables and whatever you want to bring your insights to the customers’ analysis!

Table with additional indicators per customers segment

Share your way of analyzing your customers!

.

A trap to avoid while analyzing your top 10 customers with Tableau

A trap to avoid while analyzing your top 10 customers with Tableau

Top 10 customers filter in Tableau

A top N analysis is one of the most widely spread methods to analyze your customers, sales persons’ performance, the most profitable regions. With Tableau you can easily produce this kind of reports or dashboards. Moreover, you can let your users extend the Top 5 list to Top 10 and the most exciting thing is that you can filter your customers based on their profit and show their sales numbers and vice versa! Sounds great, doesn’t it? Let me show how you should proceed.

1. Create your TOP N list

  1. Start by bringing your customers names on the Row shelf
  2. Add the sum of Sales to your view by double clicking on Sales in the Measures list
  3. Bring the CustomerName into the Filter Pane
  4. Go to the Top Tab
  5. Select By Field
  6. Replace 10 by Create a New Parameter
  7. Choose the Name of your Parameter
  8. Select your Maximum Value
  9. Choose your step value (in our case 1)

Top Customers List creation with a parameter in Tableau

As you can see the Parameter control will be automatically added to your view. You can now easily move from Top 10 to Top 5 customers list by changing your parameter value.

Change parameter value in Tableau

Nevertheless, you can see that our list isn’t perfect. Let do some extra changes:

  1. Sort the sales numbers
  2. Add INDEX() on a row shelf
  3. Transform INDEX into discrete measure
  4. Bring INDEX before Customer Name
  5. Adjust the column’s width
  6. Hide Fields Labels for Rows

Sorting the Customers Sales values in Tableau

Whereas our list is correct, in a real life we are much more specific. We don’t want to have a Top N list based on all the data that we have in our database, we need a Top N list for a specific year or month. That’s why we need to bring an extra filter to our view, the Order Date. As you can see surprisingly our list moved from 10 members to 8! This is precisely a trap I was speaking about in the title of this post.

Order Date and Top 10 trap in Tableau

 

2. Context filters

Context filters are really great, whereas they improve the performances of your calculations, they explicitly tell to Tableau the order to use while filtering. As you probably know, by default all the filters are treated independently by Tableau. Adding a filter to a context creates a temporary table, it means that your context filter is applied before the others. Let me illustrate it through our example.

Adding year to context  filter in Tableau

As you can see the filter becomes grey, what indicates to us, that it is a context filter and the most important part is that we have our Top 10 customers again!

 

3. Calculated field

Another way of dealing with our problem is through creating a calculated field. Knowing that we are interested in the ranking based on the last year (2013 in our case), when can proceed as follows

Create a calculated field in Tableau to filter your data

By the way, it goes without saying that you can replace 2013 by a Parameter allowing your user to select year of ranking.

 

Conclusion

 

Even though these two methods work well, I personally prefer the second one as it’s more flexible: you can calculate Year over Year growth, bring to your view their previous years’ sales numbers and so on. Eager to find out what kind of solution you use!

Advantages of the calculated field while filtering top 10 customers in Tableau