December 17, 2014 at 4:06 am
Hi guys,
I need to profile approx 35K customers into some cohorts based on their payment patterns. Each customer gets a weekly invoice and I need to understand break them down into the following groups
Those that never payn(balance increases each week)
Those that pay in total every week: The balance on the account goes from one week outstanding to zero
Those that pay in Total every month (4 or 5 weeks): The balance increases each week and then drops to zero
Those that pay every week/month but not the full amount - Same as above but the payment does not cover the entire cost (i.e. the balance is increasing within each payment cycle)
Those that are have a more erratic payment pattern or follow one of the above patterns but with anomolies.
Untimately what I am trying to identify is the underlying debt trend for each customer so I want to exclude the normal variance based on payment profile and then understand the trend line so that we can identify customers whose payment profile has changed and be proactive about managing those customers.
I have two datasets available to me: the first is the actual transactions (invoices, payments and adjustments) and the other is a daily/weekly snapshot of the balances.
Does anyone have any suggestions on how to approach this problem?
My first feeling is to compare the balances each week to the previous week and build a string using STUFF() FOR XML to create two strings. One showing the movement and one showing the balance at a multiple of the weekly charge so for each account I would get
UUUDUUUDUUUUDUUUDUUUDUUUUD and 12301230123401230123012340 I can then filter and group accounts based on these patterns, but this may require me to do a string pattern match using LIKE (%'UUUDUUUUD'%) or even regex patterns which is going to be slooooooow
Thanks in advance. BTW we are on SQL2008 so LEAD() and LAG() are not an option 🙁
December 17, 2014 at 7:09 am
May be compute some statistics over a period. For example number of debt values where
n0: debt =0,
n1: 0 < debt <= past week amout,
n4: past week amout< debt <= past 4 weeks amout,
nBad: debt >past 4 weeks amout.
So if last year it was (20,25, 5, 4) and this year it is (10,25, 16, 3), one can conlude a customer in question shifted to more irregular paiments.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply