Forum Discussion

Quentin_Ernesti's avatar
Quentin_Ernesti
Regular Visitor
2 years ago
Solved

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

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 0Month 1Month 2Month 3Month 4
2024-0100111
      

 

 

Any idea on how to do that?

I m losing hope! 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Quentin_Ernesti
      Regular Visitor
      2 years ago

      Hi Anonymous !

      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 0Month 1Month 2Month 3Month 4
      2024-0211110
      2024-0111221

       

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

       

       JanuaryFebruaryMarchAprilMay
      A00111
      B11110
      C01111

       

       

      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 !

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

         

         

  • Quentin_Ernesti's avatar
    Quentin_Ernesti
    Regular Visitor
    2 years ago

    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 !