Repository: PostHog/posthog
Pull request: Add people breakdown on retention
## Changes
- adds ability to click and breakdown who is retaining


Todo:
- [ ] order by people who go through flow
- [ ] paginate
- [ ] add people tests

## Checklist

- [ ] All querysets/queries filter by Team (if applicable)
- [ ] Backend tests (if applicable)
- [ ] Cypress E2E tests (if applicable)


Problem statement:
Surface People on Retention 
**Is your feature request related to a problem? Please describe.**
I want to be able to see who's being retained

**Describe the solution you'd like**
When I click on a square in the retention table, I want to see the people who have been retained

**Describe alternatives you've considered**
List below

**Additional context**


***Thank*** you for your feature request - we love each and every one!


Retention table improvements
### Is your feature request related to a problem? Please describe

I’d love to be able to (1) choose which event counts as activity and (2) choose the relevant period (eg. we care about weekly retention more than daily retention).

I've also noticed that the retention table doesn't actually load for me on app and crashes when it times out.

### Describe the solution you'd like

1. Allow users to choose the event that counts as retention
2. Allow filtering of both the interval and the period
3. Make sure the query is optimised

### Describe alternatives you've considered

-

### Additional context

cc @EDsCODE as you're looking at retention table

#### *Thank you* for your feature request – we love each and every one!

Hints from issue discussion:
oh yeah huh my retention on app errors out too
1. #1234 
2. #1230 
3. #1214 (merged)

breakdown people onclick: #1202 

@timgl  All the above are implemented but theyll have several merge conflicts so whenever you get the chance, I'd suggest we review and merge one at a time in an agreed order so i can fix the merge conflicts
The best Order IMO is in the order they were created 1202 -> 1230 -> 1234. So I can make the adjustments to each as necessary
@EDsCODE Will review those now!

Commit message: add message for no users

Patch to review:
diff --git a/frontend/src/scenes/retention/RetentionTable.js b/frontend/src/scenes/retention/RetentionTable.js
index fa7ad7f2ea0cd..14cc1d90495ee 100644
--- a/frontend/src/scenes/retention/RetentionTable.js
+++ b/frontend/src/scenes/retention/RetentionTable.js
@@ -1,10 +1,15 @@
-import React from 'react'
-import { useValues } from 'kea'
-import { Table } from 'antd'
+import React, { useState } from 'react'
+import { useValues, useActions } from 'kea'
+import { Table, Modal, Button, Spin } from 'antd'
 import { PropertyFilters } from 'lib/components/PropertyFilters/PropertyFilters'
+import { percentage } from 'lib/utils'
+import { Link } from 'lib/components/Link'
 
 export function RetentionTable({ logic }) {
-    const { retention, retentionLoading } = useValues(logic)
+    const { retention, retentionLoading, peopleLoading, people, loadingMore } = useValues(logic)
+    const { loadPeople, loadMore } = useActions(logic)
+    const [modalVisible, setModalVisible] = useState(false)
+    const [selectedRow, selectRow] = useState(0)
 
     let columns = [
         {
@@ -15,7 +20,7 @@ export function RetentionTable({ logic }) {
         {
             title: 'Users',
             key: 'users',
-            render: (row) => row.values[0],
+            render: (row) => row.values[0]['count'],
         },
     ]
 
@@ -28,12 +33,15 @@ export function RetentionTable({ logic }) {
                     if (dayIndex >= row.values.length) {
                         return ''
                     }
-                    return renderPercentage(row.values[dayIndex], row.values[0])
+                    return renderPercentage(row.values[dayIndex]['count'], row.values[0]['count'])
                 },
             })
         })
     }
 
+    function dismissModal() {
+        setModalVisible(false)
+    }
     return (
         <>
             <PropertyFilters pageKey="RetentionTable" />
@@ -46,7 +54,107 @@ export function RetentionTable({ logic }) {
                 dataSource={retention.data}
                 columns={columns}
                 loading={retentionLoading}
+                onRow={(_, rowIndex) => {
+                    return {
+                        onClick: () => {
+                            !people[rowIndex] && loadPeople(rowIndex)
+                            setModalVisible(true)
+                            selectRow(rowIndex)
+                        },
+                    }
+                }}
             />
+            {retention.data && (
+                <Modal
+                    visible={modalVisible}
+                    closable={false}
+                    onCancel={dismissModal}
+                    footer={<Button onClick={dismissModal}>Close</Button>}
+                    style={{
+                        top: 20,
+                        minWidth: retention?.data[selectedRow]?.values[0]?.count === 0 ? '10%' : '90%',
+                        fontSize: 16,
+                    }}
+                    title={retention.data[selectedRow].date}
+                >
+                    {retention && !peopleLoading ? (
+                        <div>
+                            {retention?.data[selectedRow]?.values[0]?.count === 0 ? (
+                                <span>No Users during this period</span>
+                            ) : (
+                                <div>
+                                    <table className="table table-bordered table-fixed">
+                                        <tbody>
+                                            <tr>
+                                                <th />
+                                                {retention.data &&
+                                                    retention.data
+                                                        .slice(0, retention.data[selectedRow].values.length)
+                                                        .map((data, index) => <th key={index}>{data.label}</th>)}
+                                            </tr>
+                                            <tr>
+                                                <td />
+                                                {retention.data &&
+                                                    retention.data[selectedRow].values.map((data, index) => (
+                                                        <td key={index}>
+                                                            {data.count}&nbsp;{' '}
+                                                            {data.count > 0 && (
+                                                                <span>
+                                                                    (
+                                                                    {percentage(
+                                                                        data.count /
+                                                                            retention.data[selectedRow].values[0][
+                                                                                'count'
+                                                                            ]
+                                                                    )}
+                                                                    )
+                                                                </span>
+                                                            )}
+                                                        </td>
+                                                    ))}
+                                            </tr>
+                                            {people[selectedRow] &&
+                                                people[selectedRow].map((person) => (
+                                                    <tr key={person.id}>
+                                                        <td className="text-overflow" style={{ minWidth: 200 }}>
+                                                            <Link to={`/person_by_id/${person.id}`}>{person.name}</Link>
+                                                        </td>
+                                                        {retention.data[selectedRow].values.map((step, index) => {
+                                                            return (
+                                                                <td
+                                                                    key={index}
+                                                                    className={
+                                                                        step.people.indexOf(person.id) > -1
+                                                                            ? 'retention-success'
+                                                                            : 'retention-dropped'
+                                                                    }
+                                                                />
+                                                            )
+                                                        })}
+                                                    </tr>
+                                                ))}
+                                        </tbody>
+                                    </table>
+                                    <div
+                                        style={{
+                                            margin: '1rem',
+                                            textAlign: 'center',
+                                        }}
+                                    >
+                                        {retention.data[selectedRow].values.some((element) => element.next) && (
+                                            <Button type="primary" onClick={() => loadMore(selectedRow)}>
+                                                {loadingMore ? <Spin /> : 'Load more people'}
+                                            </Button>
+                                        )}
+                                    </div>
+                                </div>
+                            )}
+                        </div>
+                    ) : (
+                        <Spin></Spin>
+                    )}
+                </Modal>
+            )}
         </>
     )
 }
diff --git a/frontend/src/scenes/retention/retentionTableLogic.js b/frontend/src/scenes/retention/retentionTableLogic.js
index 3a553520d855b..5b3fec6b3117f 100644
--- a/frontend/src/scenes/retention/retentionTableLogic.js
+++ b/frontend/src/scenes/retention/retentionTableLogic.js
@@ -4,17 +4,39 @@ import api from 'lib/api'
 import { toParams, objectsEqual } from 'lib/utils'
 
 export const retentionTableLogic = kea({
-    loaders: (props) => ({
+    loaders: ({ values }) => ({
         retention: {
             __default: {},
             loadRetention: async () => {
-                const urlParams = toParams({ properties: props.values.properties })
-                return await api.get(`api/action/retention/?${urlParams}`)
+                const urlParams = toParams({ properties: values.properties })
+                const result = await api.get(`api/action/retention/?${urlParams}`)
+                return result
+            },
+        },
+        people: {
+            __default: {},
+            loadPeople: async (rowIndex) => {
+                const people = values.retention.data[rowIndex].values[0].people
+
+                if (people.length === 0) return []
+                let results = (await api.get('api/person/?id=' + people.join(','))).results
+                results.sort(function (a, b) {
+                    return people.indexOf(a.id) - people.indexOf(b.id)
+                })
+                return {
+                    ...values.people,
+                    [`${rowIndex}`]: results,
+                }
             },
         },
     }),
     actions: () => ({
         setProperties: (properties) => ({ properties }),
+        loadMore: (selectedIndex) => ({ selectedIndex }),
+        loadMorePeople: (selectedIndex, peopleIds) => ({ selectedIndex, peopleIds }),
+        updatePeople: (selectedIndex, people) => ({ selectedIndex, people }),
+        updateRetention: (retention) => ({ retention }),
+        updateLoadingMore: (status) => ({ status }),
     }),
     reducers: () => ({
         initialPathname: [(state) => router.selectors.location(state).pathname, { noop: (a) => a }],
@@ -24,6 +46,21 @@ export const retentionTableLogic = kea({
                 setProperties: (_, { properties }) => properties,
             },
         ],
+        people: {
+            updatePeople: (state, { selectedIndex, people }) => ({
+                ...state,
+                [`${selectedIndex}`]: [...state[selectedIndex], ...people],
+            }),
+        },
+        retention: {
+            updateRetention: (_, { retention }) => retention,
+        },
+        loadingMore: [
+            false,
+            {
+                updateLoadingMore: (state, { status }) => status,
+            },
+        ],
     }),
     selectors: ({ selectors }) => ({
         propertiesForUrl: [
@@ -64,7 +101,40 @@ export const retentionTableLogic = kea({
             }
         },
     }),
-    listeners: ({ actions }) => ({
+    listeners: ({ actions, values }) => ({
         setProperties: () => actions.loadRetention(),
+        loadMore: async ({ selectedIndex }) => {
+            actions.updateLoadingMore(true)
+            let peopleToAdd = []
+            for (const [index, { next, offset }] of values.retention.data[selectedIndex].values.entries()) {
+                if (next) {
+                    const params = toParams({ id: next, offset })
+                    const referenceResults = await api.get(`api/person/references/?${params}`)
+                    let retentionCopy = { ...values.retention }
+                    if (referenceResults.offset) {
+                        retentionCopy.data[selectedIndex].values[index].offset = referenceResults.offset
+                    } else {
+                        retentionCopy.data[selectedIndex].values[index].next = null
+                    }
+                    retentionCopy.data[selectedIndex].values[index].people = [
+                        ...retentionCopy.data[selectedIndex].values[index].people,
+                        ...referenceResults.result,
+                    ]
+                    actions.updateRetention(retentionCopy)
+                    if (index === 0) peopleToAdd = referenceResults.result
+                }
+            }
+
+            actions.loadMorePeople(selectedIndex, peopleToAdd)
+            actions.updateLoadingMore(false)
+        },
+        loadMorePeople: async ({ selectedIndex, peopleIds }) => {
+            if (peopleIds.length === 0) return []
+            const peopleResult = (await api.get('api/person/?id=' + peopleIds.join(','))).results
+            peopleResult.sort(function (a, b) {
+                return peopleIds.indexOf(a.id) - peopleIds.indexOf(b.id)
+            })
+            actions.updatePeople(selectedIndex, peopleResult)
+        },
     }),
 })
diff --git a/frontend/src/style/retention-table.scss b/frontend/src/style/retention-table.scss
index 6173090720bc9..dd9cd5663e815 100644
--- a/frontend/src/style/retention-table.scss
+++ b/frontend/src/style/retention-table.scss
@@ -7,3 +7,10 @@
         padding: 8px;
     }
 }
+
+.retention-success {
+    background-color: lighten($blue, 20%);
+}
+.retention-dropped {
+    background-color: $gray-200;
+}
diff --git a/posthog/api/action.py b/posthog/api/action.py
index 21ee93ca16001..52ac9089aa4dc 100644
--- a/posthog/api/action.py
+++ b/posthog/api/action.py
@@ -351,12 +351,12 @@ def calculate_retention(filter: Filter, team: Team, total_days=11):
     labels_format = "%a. %-d %B"
     resultset = Event.objects.query_retention(filter, team)
 
-    by_dates = {(int(row.first_date), int(row.date)): row.count for row in resultset}
-
     result = {
         "data": [
             {
-                "values": [by_dates.get((first_day, day), 0) for day in range(total_days - first_day)],
+                "values": [
+                    resultset.get((first_day, day), {"count": 0, "people": []}) for day in range(total_days - first_day)
+                ],
                 "label": "Day {}".format(first_day),
                 "date": (date_from + timedelta(days=first_day)).strftime(labels_format),
             }
diff --git a/posthog/api/person.py b/posthog/api/person.py
index daa49507891c9..0321d2c662842 100644
--- a/posthog/api/person.py
+++ b/posthog/api/person.py
@@ -9,6 +9,7 @@
 from typing import Union
 from .base import CursorPagination as BaseCursorPagination
 import json
+from django.core.cache import cache
 
 
 class PersonSerializer(serializers.HyperlinkedModelSerializer):
@@ -121,3 +122,23 @@ def values(self, request: request.Request) -> response.Response:
         return response.Response(
             [{"name": convert_property_value(event[key]), "count": event["count"]} for event in people[:50]]
         )
+
+    @action(methods=["GET"], detail=False)
+    def references(self, request: request.Request) -> response.Response:
+        reference_id = request.GET.get("id", None)
+        offset = request.GET.get("offset", None)
+
+        if not reference_id or not offset:
+            return response.Response({})
+
+        offset_value = int(offset)
+        cached_result = cache.get(reference_id)
+        if cached_result:
+            return response.Response(
+                {
+                    "result": cached_result[offset_value : offset_value + 100],
+                    "offset": offset_value + 100 if len(cached_result) > offset_value + 100 else None,
+                }
+            )
+        else:
+            return response.Response({})
diff --git a/posthog/api/test/test_action.py b/posthog/api/test/test_action.py
index bb05f07f6ec3f..59cec56b3ddaa 100644
--- a/posthog/api/test/test_action.py
+++ b/posthog/api/test/test_action.py
@@ -1075,7 +1075,7 @@ def test_retention(self):
         self.assertEqual(result["data"][0]["date"], "Wed. 10 June")
 
         self.assertEqual(
-            self.pluck(result["data"], "values"),
+            self.pluck(result["data"], "values", "count"),
             [[1, 1, 1, 0, 0, 1, 1], [2, 2, 1, 0, 1, 2], [2, 1, 0, 1, 2], [1, 0, 0, 1], [0, 0, 0], [1, 1], [2],],
         )
 
@@ -1119,7 +1119,7 @@ def test_retention_with_properties(self):
         )
         self.assertEqual(result["data"][0]["date"], "Wed. 10 June")
         self.assertEqual(
-            self.pluck(result["data"], "values"),
+            self.pluck(result["data"], "values", "count"),
             [[1, 1, 1, 0, 0, 1, 1], [1, 1, 0, 0, 1, 1], [1, 0, 0, 1, 1], [0, 0, 0, 0], [0, 0, 0], [1, 1], [1]],
         )
 
@@ -1132,5 +1132,5 @@ def _create_pageviews(self, user_and_timestamps):
     def _date(self, day, hour=5):
         return datetime(2020, 6, 10 + day, hour).isoformat()
 
-    def pluck(self, list_of_dicts, key):
-        return [d[key] for d in list_of_dicts]
+    def pluck(self, list_of_dicts, key, child_key=None):
+        return [self.pluck(d[key], child_key) if child_key else d[key] for d in list_of_dicts]
diff --git a/posthog/decorators.py b/posthog/decorators.py
index 7e5aa3d961e97..dc63c23562a9b 100644
--- a/posthog/decorators.py
+++ b/posthog/decorators.py
@@ -1,16 +1,10 @@
 import json
-import hashlib
 from posthog.models import Filter, DashboardItem
 from django.core.cache import cache
 import json
 from posthog.celery import update_cache_item
 from datetime import datetime
-
-
-def generate_cache_key(obj):
-    stringified = json.dumps(obj)
-    return hashlib.md5(stringified.encode("utf-8")).hexdigest()
-
+from .utils import generate_cache_key
 
 TRENDS_ENDPOINT = "Trends"
 FUNNEL_ENDPOINT = "Funnel"
diff --git a/posthog/models/event.py b/posthog/models/event.py
index ad8db03304c45..8c7d46ce4a75e 100644
--- a/posthog/models/event.py
+++ b/posthog/models/event.py
@@ -1,3 +1,4 @@
+from django.core.cache import cache
 from django.conf import settings
 from django.db import models, transaction
 from django.db.models import (
@@ -28,6 +29,8 @@
 from .filter import Filter
 from .utils import namedtuplefetchall
 
+from posthog.utils import generate_cache_key
+
 from posthog.tasks.slack import post_event_to_slack
 from typing import Dict, Union, List, Optional, Any, Tuple
 
@@ -35,6 +38,8 @@
 import copy
 import datetime
 import re
+import random
+import string
 
 attribute_regex = r"([a-zA-Z]*)\[(.*)=[\'|\"](.*)[\'|\"]\]"
 
@@ -203,7 +208,7 @@ def filter_by_event_with_people(self, event, team_id, order_by="-id") -> models.
             events = events.order_by(order_by)
         return events
 
-    def query_retention(self, filters, team, event="$pageview") -> models.QuerySet:
+    def query_retention(self, filters, team, event="$pageview") -> dict:
         filtered_events = (
             Event.objects.filter_by_event_with_people(event=event, team_id=team.id)
             .filter(filters.date_filter_Q)
@@ -221,7 +226,8 @@ def query_retention(self, filters, team, event="$pageview") -> models.QuerySet:
             SELECT
                 DATE_PART('days', first_date - %s) AS first_date,
                 DATE_PART('days', timestamp - first_date) AS date,
-                COUNT(DISTINCT "events"."person_id")
+                COUNT(DISTINCT "events"."person_id"),
+                array_agg(DISTINCT "events"."person_id") as people
             FROM ({events_query}) events
             LEFT JOIN ({first_date_query}) first_event_date
               ON (events.person_id = first_event_date.person_id)
@@ -238,7 +244,41 @@ def query_retention(self, filters, team, event="$pageview") -> models.QuerySet:
                 full_query, (filters.date_from,) + events_query_params + first_date_params,
             )
             data = namedtuplefetchall(cursor)
-        return data
+
+            scores: dict = {}
+            for datum in data:
+                key = round(datum.first_date, 1)
+                if not scores.get(key, None):
+                    scores.update({key: {}})
+                for person in datum.people:
+                    if not scores[key].get(person, None):
+                        scores[key].update({person: 1})
+                    else:
+                        scores[key][person] += 1
+
+        by_dates = {}
+        for row in data:
+            people = sorted(row.people, key=lambda p: scores[round(row.first_date, 1)][int(p)], reverse=True)
+
+            random_key = "".join(
+                random.SystemRandom().choice(string.ascii_uppercase + string.digits) for _ in range(10)
+            )
+            cache_key = generate_cache_key("{}{}{}".format(random_key, str(round(row.first_date, 0)), str(team.pk)))
+            cache.set(
+                cache_key, people, 600,
+            )
+            by_dates.update(
+                {
+                    (int(row.first_date), int(row.date)): {
+                        "count": row.count,
+                        "people": people[0:100],
+                        "offset": 100,
+                        "next": cache_key if len(people) > 100 else None,
+                    }
+                }
+            )
+
+        return by_dates
 
     def create(self, site_url: Optional[str] = None, *args: Any, **kwargs: Any):
         with transaction.atomic():
diff --git a/posthog/urls.py b/posthog/urls.py
index 1b0b194800e9d..43659f5de7111 100644
--- a/posthog/urls.py
+++ b/posthog/urls.py
@@ -107,7 +107,9 @@ def setup_admin(request):
         try:
             return render_template("setup_admin.html", request)
         except TemplateDoesNotExist:
-            return HttpResponse("Frontend not built yet. Please try again shortly or build manually using <code>./bin/start-frontend</code>")
+            return HttpResponse(
+                "Frontend not built yet. Please try again shortly or build manually using <code>./bin/start-frontend</code>"
+            )
     if request.method == "POST":
         email = request.POST["email"]
         password = request.POST["password"]
diff --git a/posthog/utils.py b/posthog/utils.py
index 822be979a6d37..b8a168bacfb2c 100644
--- a/posthog/utils.py
+++ b/posthog/utils.py
@@ -17,6 +17,7 @@
 import re
 import os
 import pytz
+import hashlib
 
 
 def relative_date_parse(input: str) -> datetime.datetime:
@@ -220,3 +221,8 @@ def authenticate(self, request: request.Request):
                 raise AuthenticationFailed(detail="User doesnt exist")
             return (user.first(), None)
         return None
+
+
+def generate_cache_key(obj):
+    stringified = json.dumps(obj)
+    return hashlib.md5(stringified.encode("utf-8")).hexdigest()


Code review comments:
diff_hunk: @@ -46,7 +54,107 @@ export function RetentionTable({ logic }) {
                 dataSource={retention.data}
                 columns={columns}
                 loading={retentionLoading}
+                onRow={(_, rowIndex) => {
+                    return {
+                        onClick: () => {
+                            !people[rowIndex] && loadPeople(rowIndex)
+                            setModalVisible(true)
+                            selectRow(rowIndex)
+                        },
+                    }
+                }}
             />
+            {retention.data && (
+                <Modal
+                    visible={modalVisible}
+                    closable={false}
+                    onCancel={dismissModal}
+                    footer={<Button onClick={dismissModal}>Close</Button>}
+                    style={{
+                        top: 20,
+                        minWidth: retention?.data[selectedRow]?.values[0]?.count === 0 ? '10%' : '90%',
+                        fontSize: 16,
+                    }}
+                    title={retention.data[selectedRow].date}
+                >
+                    {retention && !peopleLoading ? (
+                        <div>
+                            {retention?.data[selectedRow]?.values[0]?.count === 0 ? (
+                                <span>No Users during this period</span>
path: frontend/src/scenes/retention/RetentionTable.js
text: @user1:
Nit but we don't capitalise users anywhere else I think