def calculate_retention(self,
request: request.Request) -> List[Dict[str, Any]]:
team = self.team
filter = RetentionFilter(request=request)
if not filter.date_from:
filter._date_from = "-11d"
result = retention.Retention().run(filter, team)
return result
def _retrieve_people_in_period(self, filter: RetentionFilter, team: Team):
filter._date_from = (
filter.date_from +
filter.selected_interval * filter.period_increment).isoformat()
format_fields, params = self._determine_query_params(filter, team)
final_query = """
SELECT person_id, count(person_id) appearance_count, array_agg(date) appearances FROM (
SELECT DISTINCT
{fields}
"events"."person_id"
FROM ({event_query}) events
LEFT JOIN ({reference_event_query}) first_event_date
ON (events.person_id = first_event_date.person_id)
WHERE event_date >= first_date
AND {target_condition} AND {return_condition}
OR ({target_condition} AND event_date = first_date)
) person_appearances
WHERE first_date = 0
GROUP BY person_id
ORDER BY appearance_count DESC
LIMIT %s OFFSET %s
""".format(**format_fields)
result = []
from posthog.api.person import PersonSerializer
with connection.cursor() as cursor:
cursor.execute(
final_query,
params + (100, filter.offset),
)
raw_results = cursor.fetchall()
people_dict = {}
for person in Person.objects.filter(
team_id=team.pk, id__in=[val[0] for val in raw_results]):
people_dict.update({person.pk: PersonSerializer(person).data})
result = self.process_people_in_period(filter, raw_results,
people_dict)
return result
def _retrieve_people(self, filter: RetentionFilter, team: Team):
period = filter.period
trunc, fields = self._get_trunc_func("timestamp", period)
is_first_time_retention = filter.retention_type == RETENTION_FIRST_TIME
entity_condition, _ = self.get_entity_condition(
filter.target_entity, "events")
returning_condition, _ = self.get_entity_condition(
filter.returning_entity, "first_event_date")
_entity_condition = returning_condition if filter.selected_interval > 0 else entity_condition
events = Event.objects.filter(team_id=team.pk).add_person_id(team.pk)
reference_date_from = filter.date_from
reference_date_to = filter.date_from + filter.period_increment
date_from = filter.date_from + filter.selected_interval * filter.period_increment
date_to = date_from + filter.period_increment
filter._date_from = date_from.isoformat()
filter._date_to = date_to.isoformat()
filtered_events = events.filter(filter.date_filter_Q).filter(
filter.properties_to_Q(team_id=team.pk))
filter._date_from = reference_date_from.isoformat()
filter._date_to = reference_date_to.isoformat()
inner_events = (Event.objects.filter(team_id=team.pk).filter(
filter.properties_to_Q(team_id=team.pk)).add_person_id(
team.pk).filter(**{
"person_id": OuterRef("id")
}).filter(entity_condition).values("person_id").annotate(
first_date=Min(trunc)).filter(
filter.custom_date_filter_Q("first_date")).distinct()
if is_first_time_retention else Event.objects.filter(
team_id=team.pk).filter(
filter.date_filter_Q).filter(
filter.properties_to_Q(
team_id=team.pk)).add_person_id(
team.pk).filter(
**{
"person_id": OuterRef("id")
}).filter(entity_condition))
filtered_events = (filtered_events.filter(_entity_condition).filter(
Exists(
Person.objects.filter(**{
"id": OuterRef("person_id"),
}).filter(Exists(inner_events)).only("id"))).values(
"person_id").distinct()).all()
people = Person.objects.filter(
team=team,
id__in=[
p["person_id"]
for p in filtered_events[filter.offset:filter.offset + 100]
],
)
people = people.prefetch_related(
Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
from posthog.api.person import PersonSerializer
return PersonSerializer(people, many=True).data
