1 Reply Latest reply on Sep 26, 2017 9:57 AM by Deepak Rai

    Creating customer cohorts for retention analysis

    helen.mertes

      Hi,

      I am working on analysing customer cohorts / retention and want to create cohorts based different basket types per order, e.g. basket type A, B and C.

      The first order that defines the customer acquisition date should meet the following conditions:

      • Order status: “complete” or “open”
      • Revenues : > 0 EUR
      • Certain basket type -> I want to be able to define/filter customer cohorts based on the basket type of their first order

      I am using the following LOD calculated fields to create the viz showing customer acquired and retained in subsequent month:

      1. 1.      Customer acquisition date:

      { FIXED [Customer Number]:MIN(

          IF ([Order Status]="complete"

      or [Order Status]="open")

      and [Revenues]>0 then [orderdate]

      else null end)}

      1. 2.      1st order type:

      iif([Ordered At]=[Acq date (ID, Orderstatus,totalnet)],[Basket Type],NULL)

      1. 3.      Cohort by basket type:

      { FIXED [Customer Number]:MAX([1st order (ID, basket)])}

       

      Fields 1 (month) is used as row, order month as column, and Field 3 is placed as a filter. If I want to show only customers whose first order was comprised of basket type A I apply use the cohort filter (Field 3).

      The difficulties arise when, for example, I want to show the customer cohort based on basket type A, but subsequently only their purchases of basket type B (and not A or C).

      It does not work with basket type as a normal dimension filter as this seems to kick out the cohort defined by that basket type (i.e. A).

      My understanding was that a fixed LOD filter should rank higher than a dimension filter, so I am not sure where my mistake lies as the basket type filter removes customers defined through the LOD?

       

      I tried also a calculated field “later order” (“=iif([orderdate]>[Customer acquisition date)],[Basket Type],null)”) as additional filter in the viz, which does not work either (showing in some cases actually more returning customers than acquired).

       

      How can I achieve the analysis described above?

      Thanks a lot!