Analyzing Customer Performance with Advanced SQL Queries in SAP Business One
With businesses that continue to grow and automation of processes is taking place, it is important to take a deep dive to gain insights into customer’s behaviour and its performance that can have a significant impact on the outcome on the business. SAP Business One has that flexibility and it also provides tools to extract key information from our data that can help us make informed decisions. Through this blog, we will try to explore and advanced SQL query that can help us analyze different aspects of business partner.
Businesses pay utmost importance on customer relationship and utilise that effectively for their business growth and success. SAP Business one allows us to dig deep into our customer data which helps us understand the purchasing behaviour, their credit utilization, payment terms and average pay days. In this below discussion, we will try to go through a comprehensive SQL query that allows us to have insights into customer patterns.
-- Declare the period of analysis DECLARE @StDt DateTime DECLARE @EndDt DateTime -- Define/Set the start and end dates for the analysis SET @StDt = DATEFROMPARTS(YEAR(GETDATE()), 1, 1) -- Start of the current year SET @EndDt = GETDATE() -- Current date -- Calculate the average pay days using a Common Table Expression (CTE) ;WITH AvgDaysToPay AS ( SELECT T4.CardCode, T4.CardName, AVG(CASE WHEN T4.DocDate = T1.DocDate THEN 0 -- Same day payment, 0 days to pay ELSE DATEDIFF(DAY, T4.DocDate, T1.DocDate) END) AS 'AverageDaysToPay' FROM OCRD T0 INNER JOIN ORCT T1 ON T0.CardCode = T1.CardCode INNER JOIN RCT2 T3 ON T3.DocNum = T1.DocNum INNER JOIN OINV T4 ON T4.DocEntry = T3.DocEntry AND T3.InvType = '13' WHERE (T4.DocDate BETWEEN @StDt AND @EndDt) GROUP BY T4.CardCode, T4.CardName), -- Subquery that calculates AvgDaysToDN and AvgDaysToInvoice -- This query is not time restricted OrderDeliveryInvoice AS ( SELECT DISTINCT T7.Cardcode, T3.[DocDate] AS OrderDate, T5.DocDate AS DeliveryNoteDate, T7.Docdate AS InvoiceDate FROM OPKL T0 right outer JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry right outer JOIN RDR1 T2 ON T2.LineNum = T1.OrderLine AND T1.OrderEntry = T2.DocEntry AND T1.BaseObject = 17 right outer JOIN ORDR T3 ON T3.DocEntry = T2.DocEntry LEFT outer JOIN DLN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T2.LineNum AND T4.BaseType = T3.ObjType LEFT outer JOIN ODLN T5 ON T5.DocEntry = T4.DocEntry LEFT outer JOIN INV1 T6 ON T6.BaseEntry = T4.DocEntry AND T6.BaseLine = T4.LineNum AND T6.BaseType = T5.ObjType LEFT outer JOIN OINV T7 ON T7.DocEntry = T6.DocEntry WHERE T3.[CANCELED] = 'N' AND T7.Cardcode IS NOT NULL ) -- Main query that analyzes customer performance and join with the subquery SELECT T2.CardCode [Customer Code], T2.CardName [Customer Name], RANK() OVER (ORDER BY ISNULL((ArInvoices.ArcLineTotal - ISNULL(ArCredits.ArcLineTotal, 0)), 0) DESC) AS CustomerRank, T3.GroupName [Group Name], T2.[CreditLine] AS [Credit Limit], (ISNULL((SELECT SUM(Balance) FROM OCRD WHERE CardCode = T2.CardCode), 0) / NULLIF(T2.[CreditLine], 0)) * 100 [Credit Utilization %], T1.PymntGroup AS [Payment Terms], T2.[Currency], T4.SlpName [Sales Employee Name], ISNULL((SELECT SUM(Balance) FROM OCRD WHERE CardCode = T2.CardCode), 0) [Current Receivables], CASE WHEN ISNULL(ArInvoices.ArcLineTotal, 0) = 0 THEN NULL ELSE CAST(CEILING((ISNULL((SELECT SUM(Balance) FROM OCRD WHERE CardCode = T2.CardCode), 0) / ISNULL(ArInvoices.ArcLineTotal, 0)) * DATEDIFF(DAY, @StDt, @EndDt)) AS INT) END [Accounts Receivable Days], AvgDaysToPay.AverageDaysToPay, ISNULL(ArInvoices.ArcLineCount, 0) [A/R Invoices], ISNULL(ArCredits.ArcLineCount, 0) [A/R Credits], ISNULL((ISNULL(ArInvoices.ArcLineTotal, 0) - ISNULL(ArCredits.ArcLineTotal, 0)), 0) [Total Net Sales], ISNULL((ISNULL(ArInvoices.ArcGrssProfit, 0) - ISNULL(ArCredits.ArcGrssProfit, 0)), 0) [Gross Profit], CASE WHEN ArInvoices.ArcLineCount = 0 THEN NULL ELSE (ISNULL((ISNULL(ArInvoices.ArcGrssProfit, 0) - ISNULL(ArCredits.ArcGrssProfit, 0)), 0) / NULLIF((ISNULL(ArInvoices.ArcLineTotal, 0) - ISNULL(ArCredits.ArcLineTotal, 0)), 0)) * 100 END [Gross Profit %], OrderDeliveryAvg.AvgDaysToDN, OrderDeliveryAvg.AvgDaysToInvoice FROM [dbo].[OCRD] T2 LEFT JOIN [dbo].[OCRG] T3 ON T2.GroupCode = T3.GroupCode LEFT JOIN [dbo].[OSLP] T4 ON T2.SlpCode = T4.SlpCode LEFT JOIN [dbo].[OCTG] T1 ON T2.GroupNum = T1.GroupNum LEFT JOIN ( -- Subquery that calculates total AR invoices and their attributes SELECT T0.CardCode, COUNT(DISTINCT T0.DocNum) AS ArcLineCount, SUM(CASE WHEN (T1.[Quantity] = 0 AND (T1.[StockPrice] * T1.[Quantity]) = 0) THEN T1.[LineTotal] ELSE (T1.[INMPrice] * T1.[Quantity]) END) AS ArcLineTotal, SUM(T1.GrssProfit) AS ArcGrssProfit FROM [dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate BETWEEN @StDt AND @EndDt AND T0.[CANCELED] = 'N' GROUP BY T0.CardCode ) ArInvoices ON T2.CardCode = ArInvoices.CardCode LEFT JOIN ( -- Subquery that calculates the total AR credits and their attributes SELECT T0.CardCode, COUNT(DISTINCT T0.DocNum) AS ArcLineCount, SUM(CASE WHEN (T1.[Quantity] = 0 AND (T1.[StockPrice] * T1.[Quantity]) = 0) THEN T1.[LineTotal] ELSE (T1.[INMPrice] * T1.[Quantity]) END) AS ArcLineTotal, SUM(T1.GrssProfit) AS ArcGrssProfit FROM [dbo].[ORIN] T0 INNER JOIN [dbo].[RIN1] T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate BETWEEN @StDt AND @EndDt AND T0.[CANCELED] = 'N' GROUP BY T0.CardCode ) ArCredits ON T2.CardCode = ArCredits.CardCode LEFT JOIN AvgDaysToPay ON T2.CardCode = AvgDaysToPay.CardCode -- Join with the subquery LEFT JOIN ( SELECT Cardcode, AVG(CASE WHEN OrderDate = DeliveryNoteDate THEN 0 ELSE DATEDIFF(DAY, OrderDate, DeliveryNoteDate) END) AS AvgDaysToDN, AVG(CASE WHEN OrderDate = InvoiceDate THEN 0 ELSE DATEDIFF(DAY, OrderDate, InvoiceDate) END) AS AvgDaysToInvoice FROM OrderDeliveryInvoice GROUP BY Cardcode ) OrderDeliveryAvg ON T2.CardCode = OrderDeliveryAvg.Cardcode WHERE EXISTS ( -- Subquery that filters valid customers based on invoice and credit transactions SELECT 1 FROM ( SELECT T0.CardCode FROM [dbo].[OINV] T0 WHERE T0.DocDate BETWEEN @StDt AND @EndDt and T0.[CANCELED] = 'N' UNION SELECT T0.CardCode FROM [dbo].[ORIN] T0 WHERE T0.DocDate BETWEEN @StDt AND @EndDt and T0.[CANCELED] = 'N' ) X WHERE X.CardCode = T2.CardCode ) ORDER BY CustomerRank, T2.CardCode;
- Customer Ranking: This query helps to calculate customer ranks based on their sales, identifying our top customers.
- Credit Utilizations: This query helps us to understand how much percentage of credit limit a customer has utilised.
- Payment Terms vs Days to Pay: Comparing the terms of payment with the average days to pay can help us understand the payment performance of a specific customer and we can identify if there are any payment delays.
- Receivables vs Receivable Days: This area of analysis can offer insights into the outstanding balances and the average number of days it takes for customers to pay by that customer.
- Invoice vs Credit Counts: To identify the customer activity and interaction of customer with our business can be analysed in this section of the report by seeing the number of invoices and credits issued during that period.
- AR Sales vs Gross Profit: Total receivables outstanding and how much gross profit a customer is having can be evaluated here with its financial impact.
- Gross Profit Percentage (%): The gross profit (GP) percentage defines the profitability of customer during the defined period.
- Average days to serve: the query also helps us to understand the customer service aspect of our business by providing us average days deliver/invoice a customer. This section can help us improve our inventory management as well if our business is serving a customer longer than normal due to out-of-stock issues.
It is critical to analyze customer performance to optimize business operations effectively. The above SQL query allows us to see how SAP Business one can used as a tool to extract maximum and meaningful information from our data. By analyzing various matrices, one can make informed decisions to effectively grow and enhance customer relationship. This also helps to improve credit management and on the other side, It also evaluates our performance that in how many days we can serve a customer once a sales order is placed to our business.