1 Reply Latest reply on Dec 1, 2013 9:19 PM by Jeff Day

    Calculating retention - how do I group, then sum

    Melissa Tan

      I am trying to calculate retention %, which is number of players / number of players who registered.

      I would like to group this by cohort (e.g. 0 days ago, 1 day ago, 2-6 days ago, etc.) and display this by date. For example:

       

                     | 1 May | 2 May |

      Day 0     | 80%  |  83% |

      Day 1     | 20%  | 22% |

      Day 2-3 | 10%  | 12% |

       

      I have these player fields:

      - player ID, date, reg date, days since registration (calculated)

      e.g.

      Player ID | Date    | Registration date | Days since registration = date - registration date |

      1             | 1 May | 30 April               | 1                                                                     |

      2             | 1 May | 30 April               | 1                                                                     |

      3             | 1 May | 1 May                 | 0                                                                     |

       

      And for registrations:

      - reg date, no. registrations

      Registration date | No. registrations |

      30 April              | 2,000                 |

      1 May                | 1,000                 |

       

      I can get this:

      Date   | Player | Day since reg | Registrations

      1 May | 1          |    1                     |  2000

      1 May | 2          |    1                     |  2000

      1 May | 3          |    0                     |  1000

       

      But I want to be able to group Days since reg (cohorts) together:

      Date   | Days since reg | Players | Reg  | Retention |

      1 May | 0, 1                 | 3          | 3000 | 0.1%       |

       

      But I'm not sure how to group by days since reg first (doing a COUNTD of players and an AVG of registrations), then do a further calculation on this (i.e. SUM of COUNTD of players / SUM of AVG of registrations)

       

      I can alter the data source a bit, but I'm not even sure what table structure would work.

       

      I've attached an example workbook.

       

      Thank you for any help you can give me.