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.

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.