Calculate Lost Customers using T-SQL

A common question in the Business Intelligence world from customers is “how many customers did we lose this year” ? Businesses wants to know this so they can act faster to keep customers doing business with them. In this article, I will help you to create SQL to calculate this number. On the internet I have found more articles but most time it will explain how to do this in MDX. I really don’t like MDX. It’s complicated and hard to understand. I like OLAP cubes but I also like to keep things simple. By simple I mean keep all logic in SQL and calculate kpi’s in SQL and write them in a table.

The first step in the calculation of lost customers is to determine the definition of a lost customer. For example; a lost customer is a customer who hasn’t received any invoice this calendar year BUT received an invoice last calendar year. You have to be very explicit with this definition by using timeframes and criteria. Maybe you want a rolling 52 weeks period for the invoice time? In this example, I will take calendar year.

By using the definition from above, all customer are lost in the beginning of the year because they didn’t receive any invoice yet in the first month of the year. This is fine. The line will go down when weeks or months passes by. The outcome of the formula will look something like this (chart created in SQL Server Reporting Service 2008R2);

The first step is to determine a reference point for the calculation. I use the first day of the month . You can also do a calculation every week of every day if you want. This is important because you want to count number of lost customers by month or by week in your chart.

Now let’s jump to the SQL. It may look a bit complicated but it isn’t. The comments are in Dutch but I will explain the principles here.

Step 1 – Generate a list of all customer/DateID combinations, bases on the invoice date

Step 2 – Generate an “explosion” of all customers and first-day-of-the-month combination

Step 3- Combine step 1 and step 2 and determine logic if the customer is lost or not. We check if the customer received an invoice, prior to the date you look at but in the same year and in the WHERE clause, we check if it has received an invoice last year. If not, it’s lost. Sounds easy right?

Now here comes the code. If you got questions, leave a comment. This code is written on Microsoft Dynamix AX for one of my clients. It costed me maybe 2 hours to create it. A little investment for the client but really handy!

WITH KL
AS
-- Genereer een overzicht van alle datum/klant combinaties
 (SELECT DISTINCT DK.ACCOUNTNUM AS KlantID
 , CONVERT(VARCHAR, FH.InvoiceDate, 112) AS DatumID
 , FH.InvoiceDate AS Datum
 , DD.Jaar
 , FR.LINEAMOUNT AS Bedrag
 FROM dbo.CUSTINVOICETRANS AS FR -- FactuurRegel
 
 INNER JOIN dbo.CUSTINVOICEJOUR AS FH -- FactuurHeader
 ON (FR.INVOICEID = FH.INVOICEID)
 
 INNER JOIN dbo.CUSTTABLE AS DK -- Klant
 ON (FH.ORDERACCOUNT = DK.ACCOUNTNUM)
 
 INNER JOIN dbo.SMMBUSRELSALESDISTRICTGROUP AS SD -- SalesDistrict
 ON (DK.SALESDISTRICTID = SD.SALESDISTRICTID)
 AND (SD.DATAAREAID = 'pp')
 
 INNER JOIN DimDatum AS DD -- DimDatum
 ON ( CONVERT(VARCHAR, FH.INVOICEDATE, 112) = DD.DatumId) 
 
 INNER JOIN INVENTTABLE AS DP -- DimProduct
 ON (FR.ITEMID = DP.ITEMID) 
 
 WHERE FR.DATAAREAID = 'pp'
 AND FH.DATAAREAID = 'pp'
 AND DK.DATAAREAID = 'pp'
 AND DP.DATAAREAID = 'pp'
 ) 
 
-- Genereer een lijst van alle klanten en eerste dag van maand-combinaties
-- voor alle dagen in het huidige jaar en in het vorige jaar.
-- Later gaan we voor deze datums bepalen of de klant verloren is op deze datum
, KD -- KlantDatum
AS (SELECT DISTINCT ACCOUNTNUM AS KlantID, 
 DK.NAME AS KlantNaam,
 SD.[DESCRIPTION] AS SalesDistrict,
 DD.DatumId,
 DD.DatumWaarde,
 DD.Jaar
 FROM CUSTTABLE AS DK
 INNER JOIN SMMBUSRELSALESDISTRICTGROUP AS SD
 ON (DK.SALESDISTRICTID = SD.SALESDISTRICTID)
 CROSS JOIN DimDatum AS DD
 
 WHERE DD.Jaar IN ( YEAR(DATEADD(YY,-1,GETDATE())), YEAR(GETDATE()) )
 AND DD.DagNummerVanMaand = 1
 AND DD.DatumId <= CONVERT( VARCHAR, GETDATE(), 112)
 -- evt aanzetten als je het hele jaar wilt tonen, ook de toekomst
 -- AND DD.Jaar <= YEAR(GETDATE())
 
 )
 
SELECT DISTINCT KLD.KlantID
 , KlantNaam
 , KLD.SalesDistrict
 , KLD.DatumId
 , DD.Jaar
 , DD.MaandNummerVanJaar
 , DD.MaandCode
 , (CASE WHEN KL.KlantID IS NULL THEN 1 ELSE 0 END) AS IsVerloren
FROM KD AS KLD
-- Met deze join controleer je of de klant in het huidige jaar gefactureerd is op een tijdstip
-- die voor de datum ligt waarop je kijkt. Als dit niet het geval is dan is de klant op dit moment
-- verloren. Op het moment dat hij gefactureerd is dan is hij niet meer verloren
LEFT OUTER JOIN KL AS KL ON KLD.KlantID = KL.KlantID
 AND KL.DatumID <= KLD.DatumId
 AND KLD.Jaar = KL.Jaar
 
INNER JOIN DimDatum AS DD
 ON (KLD.DatumId = DD.DatumId)
 
-- Controleer of hij vorig jaar gefactureerd is
WHERE EXISTS ( SELECT 1 FROM KL AS VJ WHERE VJ.Jaar= KLD.Jaar-1 AND KLD.KlantID = VJ.KlantID)
 
ORDER BY 1,2