We had the following user story:
A customer's importance is determined by a mixture of measures. In the sales perspective, we started to determine a customer's importance using a Pareto analysis over sales. The following diagram shows the results of a customer stratification based on sales:
We can use Pareto analysis to stratify all measurements whose total is the sum of its parts, such as gross profit and quantity. However, there is another set of customer metrics whose total is an average of its parts. For example, the total company DSO is a weighted average of the DSO of each customer. In this case, we use quartiles to stratify customers.
Finally, once we have more than one measurement that stratifies customers, we look at how to combine them both numerically and visually. Even though we discuss customer stratification, the same principles apply to stratification based on any other dimension, such as item, sales representative, or supplier. The only difference between these is the exact measurements that we use to stratify them.
When the measurement that we want use to stratify customers is based on averages, we use the distribution of the averages to classify them. As we use four letters to stratify customers in the example, we group them by quartiles. Each quartile will contain the same—or nearly the same—number of customers.
Let's create the following chart in the next exercise to see how quartiles group customers by DSO:
Let's create a bar chart with the following property options:
Dimensions | |
---|---|
Label |
Value |
Customer |
|
Expressions | |
Label |
Value |
DSO |
avg({$<_Periodicity={'Monthly'} ,[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'} ,[AsOf Months Ago]={">0<=12"}>} [A/R Invoice Balance]),[Year-Month],[Customer]))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Net Sales])* 365 |
fractile(aggr(avg({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>} [A/R Invoice Balance]),[Year-Month],Customer))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Net Sales])*365,Customer),.25)
fractile
function from .25
to .5
, .75
, and 1
, respectively. We make slight changes to the aggr()
function in the expression we used to calculate DSO in Exercise 15.2. calculated total DSO by year-month, We replace [AsOf Year-Month] with [Customer] as this is the dimension we use this chart. We also go as far as to use a second aggr()
function to calculate each fractile as the fractile()
function only works over a set of numbers. This second aggr()
function creates a list that contains the DSO of every customer for the fractile()
function.
Stratification by distribution divides the customers into nearly equal-sized bins. The bar belongs to the nearest quartile reference line above it. According to the previous chart, Divanoodle, Fanoodle, and Skipfire are in the first quartile. Every other quartile has two customers. As the best customers have a low DSO, we classify customers in the first quartile as A, in the second quartile as B, in the third quartile as C, and in the fourth quartile as D.
Let's add DSO to the customer stratification we started in the sales perspective:
Variable | |
---|---|
Label |
Value |
|
avg({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>} [A/R Invoice Balance]),[Year-Month],Customer))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Net Sales])*365 |
|
fractile(Totalaggr($(vExp_DSOCustomer),Customer),$1) |
Expressions | |
---|---|
Label |
Value |
DSO |
|
DSO Class |
if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.25)),'A',if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.5)),'B',if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.75)),'C','D' ))) |
Total Weighted |
match([Sales Class],'D','C','B','A') * .6+match([DSO Class],'D','C','B','A') * .4 |
Total Class |
pick(round([Total Weighted]),'D','C','B','A') |
DSO Class
expression to the following expression:if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.25)),blue(100),if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.5)),blue(75),if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.75)),blue(50),blue(25))))
pick(round([Total Weighted]),blue(25),blue(50),blue(75),blue(100))
We create the vExp_DSOcustomerStratification
variable with a $1
parameter so that we can calculate different factiles using only one variable. In general, when we encounter several expression variables whose only difference is a number, we reduce them to one variable and add a parameter.
The Total Weighted
stratification is calculated by first converting the letters A, B, C, and D of each individually stratified metric into the numbers 1, 2, 3, and 4, respectively. In this example, We use the match()
function to efficiently turn the letters into numbers. We then multiple each number by a factor that allows us define how important each metric is to the final customer stratification. Other than the fact that the sum of the factors should be equal to one, they are completely arbitrary and depend on the business's strategy. For example, as we want to put more emphasis on the sales stratification, we multiply it by .6 and the DSO stratification by .4.
As the sum of factors is equal to one, the sum of the all the weighted stratifications is between one and four which makes it possible for us to convert it back to a letter format. In this example, we use the pick()
function in Total Class
to convert a rounded Total Weight
back into letters. In this way, we can combine multiple customer stratifications into one. For many business users, such as sale representatives, this can help them more easily determine a customer's importance according to the business's strategy. Finally, we introduce a way to visualize how individual customer measures influence how they are classified.
We can use a native scatterplot to compare two measures used for stratification. For example, we create the following chart using the expressions we use in the customer stratification table. The legend at the top is a group of eight text objects—one for each dot and letter:
The scatterplot helps us identify whether each classification describes a tight group of closely-related customers or a disparate group of loners. It also helps describe the characteristics of an ideal customer. Although scatterplots appear to be too simple for complex stratifications that use more than two variables, it has the advantage of being easy to read. For most business users, we can add two cyclical expressions to a scatterplot and give them the power to compare any two of a potentially large group of customer stratification metrics. For the more experienced analysts, we can also create a more involved visualization called a scatterplot matrix.
The following figure shows a scatterplot matrix that compares three variables: Sales, DSO, and Gross Profit:
Variable | |
---|---|
Label |
Value |
|
if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.25)),'A',if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.5)),'B',if($(vExp_DSOCustomer) < $(vExp_DSOCustomerStratificationBoundaries(.75)),'C','D'))) |
|
if(len(only({A_CustomerSales} Customer)) <> 0, 'A',if(len(only({AB_CustomerSales} Customer)) <> 0, 'B',if(len(only({ABC_CustomerSales} Customer)) <> 0, 'C',if(len(only({$} Customer)) <> 0,'D')))) |
|
if(len(only({A_CustomerGrossProfit} Customer)) <> 0, 'A',if(len(only({AB_CustomerGrossProfit} Customer)) <> 0, 'B',if(len(only({ABC_CustomerGrossProfit} Customer)) <> 0, 'C',if(len(only({$} Customer)) <> 0,'D')))) |
In the Web View, add a New Sheet Object called Scatterplot Matrix:
Dimensions | |
---|---|
Label |
Value |
Customer |
|
Customer Classification |
=aggr( pick( round( match($(vExp_SalesCustomerStratification),'D','C', 'B','A') * .35 + match($(vExp_DSOCustomerStratification),'D','C','B','A') * .3 + match($(vExp_GrossProfitCustomerStratification),'D','C','B','A') * .35 ) ,'D' ,'C' ,'B' ,'A' ) ,Customer) |
Expressions | |
Label |
Value |
Rolling 12-month net sales |
=sum({$<$(vSetRolling12Modifier),_ActualFlag={1}>} [Net Sales]) |
DSO |
$(vExp_DSOCustomer) |
Rolling 12-month gross profit |
=sum({$<$(vSetRolling12Modifier),_ActualFlag={1}>} [Gross Profit]) |
Multivariate analysis leads to complex data visualization. The scatterplot matrix serves as a tool for more advanced analysts who want a rough idea of correlations and clustering between multiple variables at a glance. For example, we can observe that there is a stronger relationship between gross profit and sales than there is between DSO and either of these two metrics.
In the same way we use DSO for customer stratification, we can also use DSI and DPO for product and supplier stratification. This type of analysis helps us understand each working capital element within the context of other measurements. For example, a top-selling customer with a high DSO may be acceptable. However, we might lower the credit available to customers that buy little and have a high DSO.