Skip to content

Average vs Median Price – how to avoid the trap?

Running a profitability analysis based on average price is quick and easy – but does it give the full picture? I explain why and how to compare average price with median price, and take insightful pricing decisions.

As a pricing professional, I am sure you have run regular profitability reviews based on average prices. In the end the calculation is very easy – total sales divided by total quantity gives you the average selling price, then you compare it to the standard cost, and you have your profitability.

However, taking a price increase decision solely based on the profitability calculated on average selling price may lead to mistakes.

The average price might be heavily influenced by extremities – majority of the customers might be buying the product at the right price, but a “special customer” might be buying it in big quantities at low prices. Or there might have been a “special project” where the sales team granted a handsome discount on single transaction. These are called the “customer mix impact”, or the “project mix impact”.

(Note: If you like to know more about average versus median from a statistics perspective, please refer to this post from Michigan State University)

How can we analyze the difference, and find out what drives the difference? In the below example, we will use the sample datasets we had posted before and run some SQL queries on the invoice data to discover what drives average sales price of a single SKU.

Let’s start with seeing the average price of our SKUs. We can run the below analysis to see the average price per SKU:

SELECT 
	SKU_Nbr,
	ROUND(Revenue,0) AS Revenue,
	Quantity,
	ROUND(Revenue / Quantity,2) AS ASP
FROM
	(
	SELECT 
		SKU_Nbr,
		SUM(CY_Revenue) AS Revenue,
		SUM(CY_Quantity) AS Quantity
	FROM [PriBlo].[dbo].[Summary_Table]
	GROUP BY SKU_Nbr
	) T
ORDER BY SKU_Nbr

The result of the above query ran on our dataset would be as follows:

SKU_NbrRevenueQuantityASP
BIC-F1,123,1952,713414.00
BIC-S4,070,11417,287235.44
BRA-011,097,671114,6369.58
BRA-021,182,77243,59927.13
DRV-011,322,25716,51980.04
DRV-02714,8815,748124.37
GEA-01-S947,39128,50733.23
GEA-02-F2,159,73192,44323.36
GEA-03-F628,29327,44122.90

Let’s pick DRV-01 for further analysis. The average selling price (ASP) seems to be 80.04 €. Now imagine this SKU has a profitability issue (i.e. the gross margins are lower than desired), and you’d like to understand whether the response to that is a price increase. Let’s dig down deeper on the distribution of the net selling price of DRV-01 by customers.

SELECT
	Customer_No,
	ROUND(Revenue,0) AS Revenue,
	Quantity,
	ROUND(Revenue / Quantity,2) AS ASP
FROM
	(
	SELECT 
		Customer_No,
		SUM(CY_Revenue) AS Revenue,
		SUM(CY_Quantity) AS Quantity
	FROM [PriBlo].[dbo].[Summary_Table]
	WHERE
		SKU_Nbr = 'DRV-01' AND CY_Quantity > 0 -- exclude returns to avoid div/0 
	GROUP BY
		Customer_No
	) T
ORDER BY ASP

Looking at the loooooong results table of the above query, we note that 835 customers bought this product, and they paid between 12.93 € and 276.88 € for this product. What a large bracket! I suggest you always take the end of the extremities with care, there’s always a accounting-technical story behind it.

The results table is too long to read, so it’s hard to drive actions from it. With the below query, we will group the ASPs in the brackets of 10 €, summing up the revenue per bracket. This should help us what drives the ASP better.

SELECT
	*
FROM
	(
	SELECT
		Order_Rank,
		ASP_Group,
		SUM(Revenue) AS Revenue
	FROM
		(
		SELECT
			Customer_No,
			ROUND(Revenue,0) AS Revenue,
			Quantity,
			ROUND(Revenue / Quantity,2) AS ASP,
			FLOOR((Revenue / Quantity) / 10) AS Order_Rank,
			CONCAT( FLOOR((Revenue / Quantity) / 10) * 10 , ' € - ' , (FLOOR((Revenue / Quantity) / 10) + 1)  * 10 , ' €')  AS ASP_Group
		FROM
			(
			SELECT 
				Customer_No,
				SUM(CY_Revenue) AS Revenue,
				SUM(CY_Quantity) AS Quantity
			FROM [PriBlo].[dbo].[Summary_Table]
			WHERE
				SKU_Nbr = 'DRV-01' AND CY_Quantity > 0 -- exclude returns to avoid div/0 error
			GROUP BY
				Customer_No
			) T
		) X
	GROUP BY Order_Rank, ASP_Group
	) Y
ORDER BY Order_Rank

The result of the query above is the table below:

Order_RankASP_GroupRevenue
110 € – 20 €260
330 € – 40 €55,312
550 € – 60 €26,765
660 € – 70 €261,737
770 € – 80 €83,023
880 € – 90 €406,977
990 € – 100 €334,202
10100 € – 110 €123,869
11110 € – 120 €27,523
12120 € – 130 €2,383
13130 € – 140 €265
14140 € – 150 €148
27270 € – 280 €277

We can better look at that at a column-chart, to visualize the differences (using Excel).

As we visually analyze, we expect to have a median price somewhere between 90-100 EUR (don’t trust the visual analyze always, you can also run the median average on SQL or on Excel if you like to be sure). Therefore, we can suspect that exceptions are driving average selling price further down the median selling price.

Here, we could see 2 brackets driving the ASP down:

  • A large revenue registered between 60-70 EUR,
  • A small but significant revenue registered between 30-40 EUR.

Now, we have dig down to understand who these customers are, and what these invoices are. Since it is more extreme, I will only dig down to EUR 30-40 now, so that I have the material in my hand to be able to go discuss this with the sales team.

SELECT [Invoice_Date]
      ,[Invoice_No]
      ,[SKU_Nbr]
      ,[Customer_No]
      ,[Quantity]
      ,[Revenue]
      ,[Invoice_Year]
	  , ROUND(Revenue / Quantity,2) AS ASP
  FROM [PriBlo].[dbo].[Invoice_Lines_processed]
  WHERE SKU_Nbr = 'DRV-01' AND Revenue / Quantity > 30 AND Revenue / Quantity < 40

The result of the query is as follows:

Invoice DateInvoice NoSKU NbrCustomer NoQuantityRevenueASP
05/10/202194604DRV-01156227782117,74536.71
15/11/202197464DRV-011562277825510,07139.49
06/02/202045464DRV-01156227781203,64730.39
03/03/202047893DRV-01156227781154,34437.78
28/04/202051696DRV-0115622778451,36830.39
01/03/202178368DRV-0115622778893,41338.34
28/06/202187920DRV-0115622778982,97830.39
22/06/202187503DRV-0115622778-12-36530.39
19/07/202189357DRV-01156227781063,50533.06

Here you go! Now we have some meat to chew on. It is always the customer 15622778 who is getting a crazy-low price consistently (in both 2020 and 2021). If this customer paid like an average customer, the ASP of this product would be EUR 83.81 – 4.7% higher than what it is now.

So instead of applying a 4.7% price increase on this product, you could go simply fix this crazy-low price of customer 15622778, and maybe this product will be out of the profitability-watch.

I hope this helps. We went through a lot of data, analysis and results. What other points do you see in these data to analyze further? What could help you “correct” the transactional price of DRV-01 further?

Leave a Reply

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