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