Analyzing Customer Performance with Advanced SQL Queries in SAP Business One

[shahmed]

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.

Introduction:
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.

The Query:

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

 

Insights:

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

Conclusion:

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.