Skip to content

Scan your portfolio for discount outliers

You set price lists and discount levels – larger accounts get larger discounts. But do they? We provide you with SQL queries to analyze sales and find outliers.

As explained in this precisionlender website, “price Getting covers people and process issues, but it essentially boils down to one question. How good are we at actually booking that price after we set it?”. Pricing leaks may occur by design (price setting), or during execution (price getting). I noticed that quite a lot of time pricing managers assume that working on the rules make the difference, but they do not create a feedback mechanism. We need a review/feedback mechanism that validates whether our pricing structures work as intended. We have to find exceptions to our rules, understand the reason behind, and either eliminate them or add them to our rule set.

In this post, we will refer to the sample datasets we had provided before for running pricing analysis. We will follow the below methodology:

  • Sort the customers by revenue, to see what meaningful revenue brackets you can use the group the customers
  • Group the customers by revenue brackets
  • In each revenue bracket, calculate the average discount
  • Lay the customers on a chart by revenue and discount percentage
  • Visually find the outliers, and take action.

Let’s start.

Identify revenue brackets

Initially we test 0  – 10K – 25K – 50K – 100K – 250K – 1M brackets, and see whether this makes sense. We ran the below query to group the customers by revenue bracket:

SELECT
	T2.Revenue_Bracket,
	COUNT(T2.Customer_No) AS No_of_Customers,
	ROUND(SUM(T2.Annual_Revenue),0) AS Total_Revenue
FROM
	(
	SELECT 
		T1.Customer_No,
		T1.Annual_Revenue,
		CASE 
			WHEN T1.Annual_Revenue <= 10000 THEN 'A- 0 < 10.000'
			WHEN T1.Annual_Revenue <= 25000 THEN 'B- 10.000 < 25.000'
			WHEN T1.Annual_Revenue <= 50000 THEN 'C- 25.000 < 50.000'
			WHEN T1.Annual_Revenue <= 100000 THEN 'D- 50.000 < 100.000'
			WHEN T1.Annual_Revenue <= 250000 THEN 'E- 100.000 < 250.000'
			WHEN T1.Annual_Revenue < 1000000 THEN 'F- 250.000 < 1.000.000'
			ELSE 'G- > 1.000.000'
		END AS Revenue_Bracket
	FROM
		(
		SELECT 
			  [Customer_No]
			  ,SUM([Revenue]) AS Annual_Revenue
		  FROM [PriBlo].[dbo].[Invoice_Lines]
		  WHERE YEAR(Invoice_Date) = '2021'
		  GROUP BY [Customer_No]
		  ) T1
	) T2
GROUP BY T2.Revenue_Bracket
ORDER BY T2.Revenue_Bracket

The result of the query is as seen below:

Revenue_BracketNo_of_CustomersTotal_Revenue
A- 0 < 10.00021873,993,366
B- 10.000 < 25.0001862,852,959
C- 25.000 < 50.000682,346,999
D- 50.000 < 100.000271,687,903
E- 100.000 < 250.000101,441,015
F- 250.000 < 1.000.0002924,065

Ok, this doesn’t look bad. We have a declining amount of revenue and number of customers at the table, which is a rule-of-thumb for validating our brackets.

Now let’s pick a bracket and analyze the customer discounts. I will pick Bracket C for the first round.

Our hypothesis is that the more revenue a customer makes, the larger their discount should be. An exception to that could be the potential of a customer (i.e. the larger the potential the larger the discount), and in the first months of partnership these high-potential-low-revenue customers might be outliers. But else than that, the other customers should be following either linear increase or  a stepped increase.

So let’s test our hypothesis with the below query:

SELECT
T2.Customer_No,
ROUND(T2.Net_Revenue,0),
ROUND((T2.Gross_Revenue - T2.Net_Revenue) / T2.Gross_Revenue,2) AS Discount
FROM
	(
	SELECT
		T1.Customer_No,
		SUM(Net_Revenue) AS Net_Revenue,
		SUM(Gross_Revenue) AS Gross_Revenue
	FROM
		(
			SELECT 
				   IL.[Invoice_Date]
				  ,IL.[Invoice_No]
				  ,IL.[SKU_Nbr]
				  ,IL.[Customer_No]
				  ,IL.[Quantity]
				  ,IL.[Revenue] AS Net_Revenue
				  ,P.List_Price
				  ,P.List_Price * IL.Quantity AS Gross_Revenue
			  FROM [PriBlo].[dbo].[Invoice_Lines] IL
			  LEFT OUTER JOIN Products P ON IL.SKU_Nbr = P.SKU_No
			  WHERE YEAR(IL.Invoice_Date) = 2021
		) T1
	GROUP BY T1.Customer_No
	) T2
WHERE T2.Gross_Revenue <> 0 AND T2.Net_Revenue > 25000 AND T2.Net_Revenue < 50000

The result is a 68-row table, with customer revenues in 2021 and the weighted-average discount they received over the list prices. A snapshot of the table is as below:

Customer_NoRevenueDiscount
1990313225,23548%
1596004635,77163%
1565383829,55352%
1318537434,40955%
1792440034,23240%

If we scatter these results in excel, we get the chart below:

Now let’s mark our hypothesis on the chart:

In a revenue bracket, customers should either

  1. have the same discount or
  2. the discount should be increasing by revenue.

I will use a green-band to mark the hypothesis-a and a yellow band to mark hypothesis-b. The rest are positive or negative outliers and should be investigated.

As you see, we can identify the accounts that have deeper discount than their peers, despite the fact that they are doing less revenue than their peers.

You can take these accounts to the sales team and ask why they are granted the discount levels they have today. Ideally, the discount levels (or price agreements) have an end date (like they are an appendix to the annual distributor agreement or so). At the next review, you can put the figures in front of the customer and advise that they will need to move up in the discount bracket. This is never an easy discussion of course (especially not one that a salesperson would like to have).

You can have this analysis in different groups as well:

  • Revenue / discount scatter per region
  • Revenue / discount scatter without revenue brackets
  • Revenue / discount scatter filtered by particular product group

Depending on how your customers are assigned a price list or a discount bracket, you can customize your analysis.

Leave a Reply

Your email address will not be published. Required fields are marked *