New customer analysis using set analysis

[rwunderlich]

I'm working on the classic new/lost customer problem using set analysis. I've found a few examples on the forum, but they don't seem to fit exactly what I'm after. I'm using the AsOf table approach with a "MonthsBack" field. Attached is a simple example of where I'm getting stuck.

I expected that the set

{ <MonthsBack={'0'}> - <MonthsBack={'>0<4'}>}

would give me Customers who ordered in the current month and did not order in the prior three months. It's not working. Any idea where I'm going wrong?

Thanks,

Rob

[johnw]

Well, here's a quick alternative that works while I try to figure out what's going wrong with the set analysis:

Added Count = -sum(aggr(only(MonthsBack)=0,AsOfYearMonth,Customer))
Added = concat(aggr(if(only(MonthsBack)=0,Customer),AsOfYearMonth,Customer),',')

[Oleg_Troyansky]

I have a "gut feeling" that it doesn't work because the Set Analysis condition is being evaluated "globally", - e.g. once for the whole chart, and therefore cannot be sensitive to the individual "As of Month" values.

If you apply your Set Analysis condition "globally" (without any relation to AsOfMonthYear), you'll see that the only Customer and Date that remains in the set after subtracting one set from another, is Customer C1 with Order Date 5/15 - and that's I believe the reason you see an unexpected result in May.

On the other hand, I don't have a great solution for this problem - you probably have to go back to calculating flags in the load script, based on certain conditions, and using flags in combination with Set Analysis...

cheers,

[johnw]

You're exactly right, Oleg. I was just about to post that very explanation when I saw that you'd already posted it. 🙂

[rwunderlich]

Thanks to both of you. I understand the problem better.The aggr() solution helps. I'll see if I can work it into the complete problem.

-Rob

[rwunderlich]

Aggr() works for computing the Added, but how about Lost? Attached is an other example with everything but Lost. Any ideas?

Thanks,

Rob

[johnw · Accepted solution]

I really should have just posted this yesterday. Here's an example I have with added, retained and lost customers, though obviously it isn't for your specific sample data. Hopefully you'll be able to adapt it to your own data, but if not, I can take a closer look later.

[rwunderlich]

Thanks John. That's a big help. I've now got something of a useful lost count by testing only(MonthsBack)=3. Still have some odd results but moving forward.

Thanks,
-Rob

[johnw]

Looking a little more closely, I'd think you'd want this for lost customers, assuming by lost customers you mean "customers I had last month but do not have this month". It seems to produce the results I'd expect.

concat(aggr(if(only({<MonthsBack={0,1}>} MonthsBack)=1,Customer),AsOfYearMonth,Customer),',')
count(aggr(if(only({<MonthsBack={0,1}>} MonthsBack)=1,Customer),AsOfYearMonth,Customer))

I switched to count() instead of -sum() because my first try with -sum() didn't work, count() just makes more sense, and it also more closely matches the concat(). Overall, it should be easier for maintenance.

[rwunderlich]

John,

In this app, the definition given for Lost is "Sale over previous 3 months but no sales for the past 3 months current month included.".

Which means customers I had 3 months ago but had no sales in the current month, the -1 month and the -2 month. So the only() technique works excellent, testing only the oldest month.

-sum(aggr(only(MonthsBack)=3, AsOfYearMonth,Customer))

Attached is my sample including the Lost. Thanks for the help.

-Rob