cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

Screenshot 2020-09-10 18.17.56.pngScreenshot 2020-09-10 18.17.59.pngScreenshot 2020-09-10 18.18.01.pngScreenshot 2020-09-10 18.18.09.png

 

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

Screenshot 2020-09-10 18.18.32.png

 

 

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

 

Screenshot 2020-10-08 10.45.45.png

 

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.

Comments

Nice article!