Customer Retention Part 4:Customers to Retain- Segment in 4 quadrant based on Margin % and Discount

[amitchandak]

Objective:  

We would always like to retain profitable customers, Those who give High Margin. And If there are buying less discounted items, that would be best.

Divide customer into 4 quadrants based on Margin % and Discount %

Best Customer - High Margin, Less Discount

Good Customer -  High Margin, High Discount

Bad Customer -  Low Margin, Less Discount

Worst Customer -  Low Margin, High Discount

Implementation

We have 4 tables – Sales, Customer, Geography and Item

Create a date table and joined the salesdate of the sales table with the Date of Date.

Important Measures 

Discount % = DIVIDE(Sum(Sales[Discount]),sum(Sales[Gross Sales]))
Margin % = DIVIDE([Margin Amount],sum(Sales[net sales]))

Now what does the above definition means in term of these measures 

Best Customer – Margin% >= Over All Margin % , Discount % <= Over All Discount %

Good Customer -  Margin% >= Over All Margin % , Discount % > Over All Discount %

Bad Customer -  Margin% < Over All Margin % , Discount % <= Over All Discount %

Worst Customer -  Margin% < Over All Margin % , Discount % < Over All Discount %

We would plot these customers on the scatter visual and color based on the above logic. So let us have a color measure

Color Dot = 
    var _avgDisc = CALCULATE([Discount %],ALL(Sales))
    var _avgMargin = CALCULATE([Margin %],ALL(Sales))
    return switch(TRUE(),
    [Margin %]>=_avgMargin && [Discount %] <=_avgDisc , "Green",
    [Margin %]>=_avgMargin && [Discount %] >_avgDisc , "Blue",
    [Margin %]<_avgMargin && [Discount %] <=_avgDisc , "Yellow",
    [Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")

The final visual

Let us know what you think about these measures. Share your thoughts on Customer Retention in Part 4.

The file can found at https://community.powerbi.com/t5/Quick-Measures-Gallery/Customer-Retention-Part-4-Customers-to-Retai...

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403

For a more detailed understanding of Margin discount Clustering in Customer Retention, you can check out the webinar. You can also follow my channel (YouTube) and LinkedIn (profile) to get information on the upcoming webinars.

[Gjakova]

Nice article!