Converting New BP's into New Customers

ZachZach Business Process Manager

/*
Created by: Zach Hill
Date: 3/28/2019
Summary: find customers who haven't bought from you
Business case: When the office staff onboard a new customer sometimes the sales team do not know about it. This query can be used with alerts to email your sales team every Monday to let them know there are customers that need to be followed up on. Never let an eager new customer become a dead lead again. Cheers!
*/

SELECT T0.[CardCode]
, T0.[CardName]
, (DateDiff(dd,T0.[CreateDate],GetDate())) AS 'Aging Lead'
, Concat(T0.[MailStrNo], ' ' ,T0.[MailAddres] , ', ' , T0.[MailCity] , ', ' , T0.[State2] , ' ' , convert(varchar(5),T0.[MailZipCod])) AS 'Location'
, T0.[CntctPrsn]
, T0.[Phone1]
, T0.[Phone2]

FROM OCRD T0
LEFT JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

WHERE T0.[CardType] = 'C'
AND T1.[DocNum] IS NULL
AND T0.[validFor] = 'Y'

ORDER BY T0.[CreateDate]

Tagged:

Comments

  • ZachZach Business Process Manager

    Higher the "Aging Lead" the longer the BP has been in your system and have not bought. Higher the number the "deader" the lead becomes. Don't let the Aging Lead become too high! Convert new BPs into purchasing customers. GET DAT MONEY! Unfortunately some BPs will be onboard and for whatever reason won't buy. This sucks, and it will cause them to remain on the list forever unless you do 1 of 2 things.

    First you can make their BP card "Inactive". Inactive customers will now fail the 3rd conditional WHERE check and will no longer show up in the report.

    Second you could add this as a 4th conditional WHERE check:
    AND (DateDiff(dd,T0.[CreateDate],GetDate())) <= 90
    Any BP that has gone past 90 days from being onboard to not being sold to is most likely a dead lead at that time. You can change 90 to whatever you want.

    If you have any questions on how to tweak this then let me know!

Sign In or Register to comment.