Aller au contenu

Comment calculer la réalisation des prix sur de grands jeu de données ?

Le calcul de la réalisation du prix à la granularité client-SKU devient difficile à mesure que les données de facturation augmentent. Dans cet article, nous expliquerons comment exécuter une analyse de réalisation des prix à l’aide de requêtes SQL, afin que vous puissiez calculer une réalisation des prix très précise, quelle que soit la taille de vos données de facturation.

Commençons par une brève définition de ce qu’est la réalisation du prix : la réalisation du prix est le revenu supplémentaire que vous avez réalisé par rapport à la période précédente, grâce à la facturation d’un prix plus élevé pour le même produit.

Nous pouvons donner un exemple simple pour mieux l’expliquer :

AnnéeRéf.ClientUnitésPrix UnitaireCA
2020BIC-1C123100EUR 2.0EUR 200
2021BIC-1C123100EUR 2.2EUR 220

Dans l’exemple ci-dessus, le même client a acheté la même quantité du même produit pendant 2 années consécutives, avec un prix unitaire augmenté.

  • La différence de revenus (220 EUR – 200 EUR = 20 EUR) correspond au prix absolu de réalisation.
  • En termes relatifs, la réalisation du prix / le revenu de base (20 EUR / 200 EUR = 10 %) nous donne le pourcentage de réalisation du prix.

La formule utilisée pour le calcul est :

(Prix de vente moyen, année précédente – Prix de vente moyen, année en cours) x Quantité de l’année en cours

Le calcul pour l’exemple ci-dessous est le suivant :

(2.2 – 2.0) x 100 = 20 EUR

Appliquons maintenant ce calcul simple sur un grand ensemble de données à l’aide de Microsoft SQL. Vous pouvez télécharger les ensembles de données dans cet article et exécuter également cet exemple sur votre ordinateur.

Veuillez noter les commentaires en gris clair dans le code, qui vous aideront à mieux comprendre les résultats.

-- 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

Notre calcul est terminé. Nous pouvons maintenant exécuter une requête rapide pour casser la réalisation des prix par région.

-- 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

Le tableau des résultats de la requête ci-dessus ressemblera à ceci :

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%

Et vous pouvez l’importer dans Excel et créer des graphiques simples comme suit :

Voilà! J’espère que ça aide. Dans un autre article, nous approfondirons cet ensemble de résultats et verrons comment les exceptions faussent le calcul global de la réalisation du prix et comment nous pouvons supprimer le bruit.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *