How to create a Cohort analysis based on the arrival date?

[Quentin_Ernesti]

Hi all!

So, I have two tables : one called "Clients" and another called "Interventions". "Clients" is simply the list of my customers, and "Interventions" is a list of all the shifts that the customers (the "Clients") bought.

"Clients" contains :

• a column called "Date 1er Contact" which is a date that tells me when did we first heard about this customer
• a column called "ID Client" that gives me the ID of the Client

"Interventions" contains: 

• a column called "Date" giving me the date of the shift
• a column called "ID AT Client" that tells me which customer bought the shift

Obviously, the two tables contain more information but I don't think it's relevant, and they are obviously linked with one another thanks to the "ID Client" and "ID AT Client".

Now, what I want, is a cohort analysis that tells me my customer retention on a monthly basis based on when I first heard about them. I consider them "retained" if they made at least one purchase of a shift in the month considered.

To tell you the full story, I successfully did it by considering a Client in the cohort of the month of his first "Intervention" purchased. But it is not perfect because I actually want to link it with say for instance my Google Ads campaigns, and sometimes my customers get in touch with us in January, but end up buying their first shift in March! And that could lead to false interpretation. So I really want to be able to get it based on the first time we heard about them (the "Date 1er Contact") basis.

To clarify then, for a customer arrived in January but who bought Interventions from March to May, the table would look like :

	Month 0	Month 1	Month 2	Month 3	Month 4
2024-01	0	0	1	1	1

Any idea on how to do that?

I m losing hope! 

[Quentin_Ernesti]

Thanks, I know it is definitely possible because I have seen it elsewhere, but I shall wait for someone else's help. 🙂
Thanks again tho !

[v-tianyich-msft · Accepted solution]

Hi @Quentin_Ernesti ,

I made simple samples and you can check the results below:

Count = 
var _t = SUMMARIZE('Interventions',[Date],[Date 1er Contact],[ID AT Client])
var _t2 = ADDCOLUMNS(_t,"Count",COUNTAX(FILTER(_t,[Date 1er Contact]=EARLIER([Date 1er Contact])),[Date]<=[Date 1er Contact]))
RETURN MAXX(_t2,[Count])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Quentin_Ernesti]

Hi @v-tianyich-msft !

Thank you so much for your answer, however it was not exactly what I wanted, and I was wondering if you could help me get it to the finish line.

What I wanted was that in the columns of the matrix, I have Month 0, Month 1, etc., and not the months.

Let me put here again my table with some information added.
Customer A : arrived in January and bought Interventions from March to May

Customer B : arrived in January and bought Interventions from January to April

Customer C : arrived in February and bought Interventions from February to May

Here is what the table would look like :

	Month 0	Month 1	Month 2	Month 3	Month 4
2024-02	1	1	1	1	0
2024-01	1	1	2	2	1

While your table would give me this if I understood it correctly :

	January	February	March	April	May
A	0	0	1	1	1
B	1	1	1	1	0
C	0	1	1	1	1

As you can see, the results are very different.

I tried to find a solution from what you already did but it feels like I am missing something, maybe a measure to get those columns looking indeed like "Month 0", "Month 1", etc. ?

Any ideas ? 

Thanks !

[v-tianyich-msft]

Hi @Quentin_Ernesti ,

Month 0, month 1, seems to be hard to define because for different people, their month 0 doesn't seem to be the same time. I can't think of anything better at the moment.

Best regards,
Community Support Team_ Scott Chang