Skip to content

How to calculate price realization on large datasets?

Calculating price realization at customer-SKU granularity becomes challenging as the invoice data grows larger. In this post we will explain how to run price realization analysis using SQL queries, so that you can calculate very precise price realization no matter how large your invoice data is.

Let’s start with a brief definition of what price realization is: Price realization is the incremental revenue your realized versus previous period, thanks to charging more for the same product.

We can give a simple example to explain it better:

Year of salesSKU CodeCustomer CodeSold QuantityUnit PriceRevenue
2020BIC-1C123100EUR 2.0EUR 200
2021BIC-1C123100EUR 2.2EUR 220

In the above example, the same customer bought the same quantity of the same product in 2 consecutive years, with an increased unit price.

  • The difference in revenue (EUR 220 – EUR 200 = EUR 20) is the absolute price realization.
  • In relative terms price realization / base revenue (EUR 20 / EUR 200 = 10%) gives us percentage price realization.

The formula used for calculation is:

(Average Selling Price, previous year – Average Selling Price, current year) x Current Year Quantity

The calculation for the example below is as follows:

(2.2 – 2.0) x 100 = 20 EUR

Now let’s apply this simple calculation on a large data set using Microsoft SQL. You can download the data sets in this post, and run this example on your computer too.

Please note the comments in light gray color in the code, which will help you understand the results even further.

-- We usually find errors and reiterate the code. To have a reiterative code
-- we have to drop results file each time we start a new iteration
DROP TABLE Invoice_Lines_processed;
GO
-- To be able to have an reiterative code, we do not touch the source tables.
-- Instead, we created a duplicate table (with a suffix _processed), and enrich the data there.
SELECT * INTO Invoice_Lines_processed FROM Invoice_Lines
GO
-- Clean unnecessary invoice lines. By removing these lines we increase the accuracy
-- of our calculation. Also, we avoid "division by zero" errors.
-- 1- We delete items with no quantity. These items might be one-off charges like handlind etc, that we do not need in our calculation.
DELETE FROM Invoice_Lines_processed WHERE Quantity = 0;
-- 2- We delete items with no revenue. These are called free-of-charge shipments (FOCs), 
-- and they can be promotional samples, quality related deliveries etc. 
-- We have to understand why these exist, and then remove them from the calculation. 
-- If needed, you can keep these in your calculation, but they can geneare div/0 errors.
DELETE FROM Invoice_Lines_processed WHERE Revenue = 0;
-- Enrich the invoice table as needed. We need to create the periods we would like to compare.
-- In this example we will compare the price realization between 2020 and 2021.
ALTER TABLE Invoice_Lines_processed ADD Invoice_Year float; -- add new column
GO
UPDATE Invoice_Lines_processed SET Invoice_Year = YEAR(Invoice_Date) -- update the column
DELETE FROM Invoice_Lines_processed  WHERE Invoice_Year NOT IN ('2020','2021') -- Delete the data out of scope
-- This is a result table as well. To have an reiterative code, we drop the result table first.
DROP TABLE Summary_Table;
GO
-- Now we do the calculation. We summarize the sales per customer-SKU,
-- so that we have one line per customer-SKU.
-- Later on, we can import this results into Excel or Power BI
-- and create charts, pivot tables that makes it easier to present.
SELECT
      SKU_Nbr,
      Customer_No,
      ROUND(SUM(PY_Quantity),2) AS PY_Quantity,
      ROUND(SUM(PY_Revenue),2) AS PY_Revenue,
      ROUND(SUM(CY_Quantity),2) AS CY_Quantity,
      ROUND(SUM(CY_Revenue),2) AS CY_Revenue
INTO Summary_Table
FROM 
    (
    SELECT
    SKU_Nbr,
    Customer_No,
    CASE WHEN Invoice_Year = 2020 THEN Quantity ELSE 0 END AS PY_Quantity,
    CASE WHEN Invoice_Year = 2020 THEN Revenue ELSE 0 END AS PY_Revenue,
    CASE WHEN Invoice_Year = 2021 THEN Quantity ELSE 0 END AS CY_Quantity,
    CASE WHEN Invoice_Year = 2021 THEN Revenue ELSE 0 END AS CY_Revenue
    FROM Invoice_Lines_processed
    )
    T
GROUP BY
       T.[SKU_Nbr]
      ,T.[Customer_No]
        
GO

ALTER TABLE Summary_Table ADD Price_Realization float;
GO
UPDATE Summary_Table SET Price_Realization = ( (CY_Revenue/CY_Quantity) - (PY_Revenue/PY_Quantity) ) * CY_Quantity WHERE CY_Quantity <> 0 AND PY_Quantity <> 0
GO
-- Good, now our calculation is performed at the top level. 
-- Now let's enrich our calculation table with product and customer master data
-- So that we can break the results down by product family, region etc.

ALTER TABLE Summary_Table ADD SKU_Description varchar(50), Product_Family varchar(50), Product_Series varchar(50), Region varchar(50);
GO
-- We use the query below to enrich the summary table with master data.
-- For more information on this query, you can check this website: https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match

-- First lets enrich with product data
UPDATE
    Summary_Table
SET
    Summary_Table.SKU_Description = P.SKU_Description,
    Summary_Table.Product_Family = P.Product_Family,
    Summary_Table.Product_Series = P.Product_Series
FROM
    Summary_Table S
INNER JOIN
    Products P
ON 
    S.SKU_Nbr = P.SKU_No
GO
-- Now lets enrich with customer data
UPDATE
    Summary_Table
SET
    Summary_Table.Region = c.Region
FROM
    Summary_Table S
INNER JOIN
    Customers C
ON 
    S.Customer_No = C.Customer_No
GO

Our calculation is over. Now we can run a quick query to break the price realization by region.

-- Done, our calculation is over. Now we can analyze this summary table in Excel, Power BI or other
-- data analytics solutions. I will give one example in SQL do break the data down by region.
SELECT
	Region,
	PY_Revenue,
	CY_Revenue,
	Price_Realization AS Price_Realization_ABS,
	ROUND(Price_Realization / PY_Revenue,2) AS Price_Realization_Perc
	FROM 
	(
		SELECT 
			Region,
			ROUND(SUM(PY_Revenue),2) AS PY_Revenue,
			ROUND(SUM(CY_Revenue),2) AS CY_Revenue,
			ROUND(SUM(Price_Realization),2) AS Price_Realization
		FROM
			Summary_Table
		GROUP BY
			Region
	) T

The results table of the above query will look like below:

RegionPY_RevenueCY_RevenuePrice_Realization
_ABS
Price_Realization
_Perc
North752,646.28773,234.639,907.241.0%
East82,577.9586,182.74456.391.0%
Central398,532.32541,321.6610,722.293.0%
Export186,156.55236,661.123,602.932.0%
South1,242,384.241,376,300.1624,660.842.0%
West10,819,595.5810,232,606.01-238,111.88-2.0%

And you can import this into Excel and create simple charts as follows:

Voila! I hope this helps. In another post, we will dig down in this result set and see how exceptions distort the overall price realization calculation, and how we can remove the noise.

Leave a Reply

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