Month by month customer retention

[#1]

Hi,

I'm trying to make a table to show month by month customer retention. After reading lost of posts, I have achieved to create expressions that work for a selected year-month, using p() and e() and a continuous Month Count but can't get to make it work using year-month as dimension, even with an as of Month Count table that flags current and previous month.

The source data contains user, date and product for each of the customer transactions.

The goal is to get the following counts, and these are the expressions:

- Customers that bought a product the current OR the previous month.

=count(distinct{1<username=p({1<product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)+p({1< product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)>}username)

- Customers that bought a product the current AND the previous month.

=count(distinct{1<username=p({1< product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)*p({1< product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)>}username)

- Customers that bought a product the current AND NOT the previous month.

=count( distinct{1<username=
p({1<product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)
*e({1<product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)
>}username)

- Customers that bought a product the previous AND NOT the current month.

=count(distinct{1<username=e({1< product={'apples'},MonthCount ={$(=max(MonthCount))}>}username)*p({1< product={'apples'},MonthCount ={$(=max(MonthCount)-1)}>}username)>}username)

In the asof table i have asofmonthcount , monthcount, and monthsago, where asofmonthcount is linked to each monthcount and the previous, which are flagged as monthsago=1 or monthsago=0.I have tried using asofmonthcount as dimension and changing MonthCount ={$(=max(MonthCount))} for monthsago={0}, and MonthCount ={$(=max(MonthCount)-1)} for monthsago={1}, but not getting the desired result.

Any help will be much appreciated, I have been a long time stucked with this!

Thans in advance,

Jorge

[#2]

Thanks a lot stalwar1‌ and vinieme12‌!

I'm also working on it, tried something with aggr(min(month),username) and aggr(max(month),username) inside of the asofmonth dimension but still couldn't make it work.

Looking forward for your insight

[sunny_talwar · Accepted solution]

first things first, do you have QV12 or not? If not then you will need to fix the sort order for your month field in the script. I did that by adding a simple inline load which was later dropped.

At start of the script

Temp:

LOAD RecNo() as month

AutoGenerate 12;

At end of the script

DROP Table Temp;

Once you have this done, the aggregate function should start working for you.

Dimension

month

Expressions

1) =RangeSum(Column(2), Column(3), Column(4))

2) =Sum(Aggr(If(username = Above(Only({1}username)) and month = Above(Only({1}month)) + 1, 1, 0) , username, month))

3) =RangeSum(Count(DISTINCT username),

-Sum(Aggr(If(username = Above(Only({1}username)) and month = Above(Only({1}month)) + 1, 1, 0) , username, month)))

4) =RangeSum(Above(Count({1}DISTINCT username)),

-Sum(Aggr(If(username = Above(Only({1}username)) and month = Above(Only({1}month)) + 1, 1, 0) , username, month)))

[#4]

It's working perfectly! I would have never come to this approach, I'll have to explore a bit more the aggr() and above() functions.

I'll try now to implement it in the real app, I guess it will work fine.

Thank you very much stalwar1‌ and vinieme12‌‌‌!

[sunny_talwar]

If in case you have QV12, this will be a good place to read -> The sortable Aggr function is finally here!‌‌. Actually even if you don't have QV12, I would suggest you to read this

[#6]

thanks for the info, I'm using QV11, but I'll check it anyway!