Cohort Retention

[tsbrian]

I'm trying to get a retention data over time based on customer sign-up date.

I have data as follows:

Customer	Start_Date	Months_Active
A	1/1/2024	4
B	1/1/2024	2
C	2/1/2024	1
D	2/1/2024	3
E	3/1/2024	2

I want to know how many active customers I had for each month number broken down by Start_Date. This is what the data should look like (the numeric values at the column headers indicate # of months post Start_Date, so '1' indicates 2/1/2024 for customers started on 1/1/2024)

Start_Date	count(Customer)	1	2	3	4
1/1/2024	2	2	1	1	-
2/1/2024	2	1	1	-	-
3/1/2024	1	1	-	-	-

I was trying to find an expression formula that would take value in Months_Active at the top of the pivot table and count Customers whose Months_Active <= to that dimension value. I just can't get there. It's either the wrong approach or I just can't find the right combination of set analyses and functions to get me there.

Any help will be appreciated.

[Vegar · Accepted solution]

What if you do something like this.

Load Customer, Start_Date, Months_Active From Source;

For m=0 to 60 //60=5years

Load 

$(m) as Months_Active,

iterno() as Active_N_Months_Ago

Autogenerate 1

While iterno() <= $(m)

Next m

And with this data model create a pivot with Start_Date as row dimension, Months_Active as column dimension and count(Customer) as your measure.

[tsbrian]

Awesome. Thank you @Vegar for the alternate approach.

When implementing, I found 2 issues.

1. The semicolon should be removed after 'Autogenerate 1;' and should, instead, be placed at the end of the 'While...' line
2. In line '$(m) + iterno() -1 as Active_N_Months_Ago'  the '$(m)' should be replaced with '1' so that the records that are created start from 1 and not $(m).

If you correct these, I'll mark your approach as a correct Solution to this issue.

[Vegar]

@tsbrian thanks for pointing that out. Instead of having 1+iterno()-1 i simplified it to only iterno(), since +1-1=0.

[seanbruton]

Kudos nice solution!!