topic Re: Table with customer retention by month in App Development

Table with customer retention by month

Defo — Tue, 15 Oct 2024 15:38:32 GMT

Hi all,

I'm here for a very common issue, retention rate. I want to show in a table the number of customers of the specific month and the number of customers that have bought in the specific month and in the previous too.

I can only solve this problem at front end level, not have permission to access to load editor. Imagine that I have available a table for all transaction at CustomerCode and YearMonth level.
Somenthing like:

YearMonth	CustomerCode	Flag Buyer
2024-01	20	1
2024-01	21	1
2024-01	22	1
2024-01	23	0
2024-01	24	1
2024-02	20	1
2024-02	23	1
2024-02	39	1

Resulting in:

YearMonth	# Customers	# Retained Customer
2024-01	4	-
2024-02	3	2

I'm looking to a formula that can be evaluated at row level, letting me compute for each row how many customer have bought in the row's Month and in the previous.

Thanks,
Davide

Re: Table with customer retention by month

Qrishna — Tue, 15 Oct 2024 21:32:39 GMT

Hi - is see only customer 20 is the only customer that bought in both current month and previous month, so how is the retained customers = 2 in the second table when its actually 1 for 2024-02?

Re: Table with customer retention by month

Defo — Wed, 16 Oct 2024 14:00:41 GMT

Sorry, my bad! Hoping that you got the point.

How did you get that result you are showing? That's exactly what I'm looking for.

Thanks, Davide

Re: Table with customer retention by month

Kushal_Chawda — Wed, 16 Oct 2024 15:35:45 GMT

@Defo try below expression for # Retained Customer

=sum(aggr(if(CustomerCode = Above(Only({1}CustomerCode)) and YearMonth <> Above(Only({1}YearMonth)) and (Above(Only({1}[Flag Buyer])=1 and [Flag Buyer]=1)) ,1,0), CustomerCode,YearMonth))