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

# Calculating retention - how do I group, then sum

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.

• ###### 1. Re: Calculating retention - how do I group, then sum

Melissa,

Did you ever figure this out?  I'm trying to do a very similar thing.  ....classic cohort retention analysis by registration date, and % retained by 30 day periods.

I'd love to hear if you--or anyone--has figured out how to do this.

Many thanks,

Jeff