def stickiness_process_entity_type(target_entity: Entity, team: Team,
filter: StickinessFilter) -> QuerySet:
events: Union[EventManager, QuerySet] = Event.objects.none()
if target_entity.type == TREND_FILTER_TYPE_EVENTS:
events = base.process_entity_for_events(target_entity,
team_id=team.pk,
order_by=None).filter(
base.filter_events(
team.pk, filter,
target_entity))
elif target_entity.type == TREND_FILTER_TYPE_ACTIONS:
actions = Action.objects.filter(deleted=False, team=team)
actions = actions.prefetch_related(
Prefetch("steps", queryset=ActionStep.objects.order_by("id")))
try:
actions.get(pk=target_entity.id)
except Action.DoesNotExist:
return Event.objects.none()
events = base.process_entity_for_events(target_entity,
team_id=team.pk,
order_by=None).filter(
base.filter_events(
team.pk, filter,
target_entity))
else:
raise ValueError("target entity must be action or event")
return events
def filter_by_type(entity: Entity, team: Team, filter: Filter) -> QuerySet:
events: Union[EventManager, QuerySet] = Event.objects.none()
if filter.session:
events = Event.objects.filter(team=team).filter(
base.filter_events(team.pk, filter)).add_person_id(team.pk)
else:
if entity.type == TREND_FILTER_TYPE_EVENTS:
events = base.process_entity_for_events(entity,
team_id=team.pk,
order_by=None).filter(
base.filter_events(
team.pk, filter,
entity))
elif entity.type == TREND_FILTER_TYPE_ACTIONS:
actions = Action.objects.filter(deleted=False)
try:
actions.get(pk=entity.id)
except Action.DoesNotExist:
return events
events = base.process_entity_for_events(entity,
team_id=team.pk,
order_by=None).filter(
base.filter_events(
team.pk, filter,
entity))
return events
def _retrieve_people(self, target_entity: Entity, filter: StickinessFilter, team: Team) -> ReturnDict:
from posthog.api.person import PersonSerializer
if target_entity.type == TREND_FILTER_TYPE_EVENTS:
filtered_events = base.process_entity_for_events(target_entity, team_id=team.pk, order_by=None).filter(
base.filter_events(team.pk, filter, target_entity)
)
elif target_entity.type == TREND_FILTER_TYPE_ACTIONS:
actions = Action.objects.filter(deleted=False, team=team)
actions = actions.prefetch_related(Prefetch("steps", queryset=ActionStep.objects.order_by("id")))
try:
actions.get(pk=target_entity.id)
except Action.DoesNotExist:
return PersonSerializer([], many=True).data
filtered_events = base.process_entity_for_events(target_entity, team_id=team.pk, order_by=None).filter(
base.filter_events(team.pk, filter, target_entity)
)
else:
raise ValueError("target entity must be action or event")
events = (
filtered_events.values("person_id")
.annotate(day_count=Count(filter.trunc_func("timestamp"), distinct=True))
.filter(day_count=filter.selected_interval)
)
people = Person.objects.filter(
team=team, id__in=[p["person_id"] for p in events[filter.offset : filter.offset + 100]],
)
people = people.prefetch_related(Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
return PersonSerializer(people, many=True).data
def _serialize_lifecycle(self, entity: Entity, filter: Filter,
team_id: int) -> List[Dict[str, Any]]:
period = filter.interval or "day"
num_intervals, prev_date_from, date_from, date_to, after_date_to = get_time_diff(
period, filter.date_from, filter.date_to, team_id)
interval_trunc, sub_interval = get_trunc_func(period=period)
# include the before and after when filteirng all events
filter = filter.with_data({
"date_from": prev_date_from.isoformat(),
"date_to": after_date_to.isoformat()
})
filtered_events = (Event.objects.filter(
team_id=team_id).add_person_id(team_id).filter(
filter_events(team_id, filter, entity)))
event_query, event_params = queryset_to_named_query(
filtered_events, "events")
earliest_events_filtered = (Event.objects.filter(
team_id=team_id).add_person_id(team_id).filter(
filter_events(team_id, filter, entity, include_dates=False)))
earliest_events_query, earliest_events_params = queryset_to_named_query(
earliest_events_filtered, "earliest_events")
with connection.cursor() as cursor:
cursor.execute(
LIFECYCLE_SQL.format(
action_join=ACTION_JOIN
if entity.type == TREND_FILTER_TYPE_ACTIONS else "",
event_condition="{} = %(event)s".format(
"action_id" if entity.type ==
TREND_FILTER_TYPE_ACTIONS else "event"),
events=event_query,
earliest_events=earliest_events_query,
),
{
"team_id": team_id,
"event": entity.id,
"interval": interval_trunc,
"one_interval": "1 " + interval_trunc,
"sub_interval": "1 " + sub_interval,
"num_intervals": num_intervals,
"prev_date_from": prev_date_from,
"date_from": date_from,
"date_to": date_to,
"after_date_to": after_date_to,
**event_params,
**earliest_events_params,
},
)
res = []
for val in cursor.fetchall():
label = "{} - {}".format(entity.name, val[2])
additional_values = {"label": label, "status": val[2]}
parsed_result = parse_response(val, filter, additional_values)
res.append(parsed_result)
return res
def run(self, filter: Filter, team: Team, *args,
**kwargs) -> List[Dict[str, Any]]:
calculated = []
events = Event.objects.none()
for entity in filter.entities:
events |= process_entity_for_events(
entity, team_id=team.pk).filter(
filter_events(team_id=team.pk,
filter=filter,
entity=entity,
include_dates=False))
if not len(events):
events = self.events_query(filter, team)
# get compared period
if filter.compare and filter._date_from != "all" and filter.session == SESSION_AVG:
calculated = self.calculate_sessions(
events.filter(filter.date_filter_Q), filter, team)
calculated = convert_to_comparison(calculated, filter, "current")
compare_filter = determine_compared_filter(filter)
compared_calculated = self.calculate_sessions(
events.filter(compare_filter.date_filter_Q), compare_filter,
team)
converted_compared_calculated = convert_to_comparison(
compared_calculated, filter, "previous")
calculated.extend(converted_compared_calculated)
else:
events = events.filter(filter.date_filter_Q)
calculated = self.calculate_sessions(events, filter, team)
return calculated
def get_people(self,
request: request.Request) -> Union[Dict[str, Any], List]:
team = self.team
filter = Filter(request=request)
offset = int(request.GET.get("offset", 0))
def _calculate_people(events: QuerySet, offset: int):
events = events.values("person_id").distinct()
if request.GET.get(
"breakdown_type"
) == "cohort" and request.GET.get("breakdown_value") != "all":
events = events.filter(
Exists(
CohortPeople.objects.filter(
cohort_id=int(request.GET["breakdown_value"]),
person_id=OuterRef("person_id"),
).only("id")))
if request.GET.get("breakdown_type") == "person":
events = events.filter(
Exists(
Person.objects.filter(
**{
"id":
OuterRef("person_id"),
"team_id":
self.team.pk,
"properties__{}".format(request.GET["breakdown"]):
request.GET["breakdown_value"],
}).only("id")))
people = Person.objects.filter(
team=team,
id__in=[p["person_id"] for p in events[offset:offset + 100]])
people = people.prefetch_related(
Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
return PersonSerializer(people,
context={
"request": request
},
many=True).data
filtered_events: QuerySet = QuerySet()
if request.GET.get("session"):
filtered_events = (Event.objects.filter(team=team).filter(
base.filter_events(team.pk, filter)).add_person_id(team.pk))
else:
entity = get_target_entity(request)
if entity.type == TREND_FILTER_TYPE_EVENTS:
filtered_events = base.process_entity_for_events(
entity, team_id=team.pk, order_by=None).filter(
base.filter_events(team.pk, filter, entity))
elif entity.type == TREND_FILTER_TYPE_ACTIONS:
actions = super().get_queryset()
actions = actions.filter(deleted=False)
try:
action = actions.get(pk=entity.id)
except Action.DoesNotExist:
return []
filtered_events = base.process_entity_for_events(
entity, team_id=team.pk, order_by=None).filter(
base.filter_events(team.pk, filter, entity))
people = _calculate_people(events=filtered_events, offset=offset)
current_url = request.get_full_path()
next_url = paginated_result(people, request, offset)
return {
"results": [{
"people": people,
"count": len(people)
}],
"next": next_url,
"previous": current_url[1:]
}
def get_people(self,
request: request.Request) -> Union[Dict[str, Any], List]:
team = request.user.team
filter = Filter(request=request)
offset = int(request.GET.get("offset", 0))
def _calculate_people(events: QuerySet, offset: int):
shown_as = request.GET.get("shown_as")
if shown_as is not None and shown_as == "Stickiness":
stickiness_days = int(request.GET["stickiness_days"])
events = (events.values("person_id").annotate(day_count=Count(
functions.TruncDay("timestamp"), distinct=True)).filter(
day_count=stickiness_days))
else:
events = events.values("person_id").distinct()
if request.GET.get(
"breakdown_type"
) == "cohort" and request.GET.get("breakdown_value") != "all":
events = events.filter(
Exists(
CohortPeople.objects.filter(
cohort_id=int(request.GET["breakdown_value"]),
person_id=OuterRef("person_id"),
).only("id")))
if request.GET.get("breakdown_type") == "person":
events = events.filter(
Exists(
Person.objects.filter(
**{
"id":
OuterRef("person_id"),
"properties__{}".format(request.GET["breakdown"]):
request.GET["breakdown_value"],
}).only("id")))
people = Person.objects.filter(
team=team,
id__in=[p["person_id"] for p in events[offset:offset + 100]],
)
people = people.prefetch_related(
Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
return serialize_people(people=people, request=request)
filtered_events: QuerySet = QuerySet()
if request.GET.get("session"):
filtered_events = (Event.objects.filter(team=team).filter(
base.filter_events(team.pk, filter)).add_person_id(team.pk))
else:
if len(filter.entities) >= 1:
entity = filter.entities[0]
else:
entity = Entity({
"id": request.GET["entityId"],
"type": request.GET["type"]
})
if entity.type == TREND_FILTER_TYPE_EVENTS:
filtered_events = base.process_entity_for_events(
entity, team_id=team.pk, order_by=None).filter(
base.filter_events(team.pk, filter, entity))
elif entity.type == TREND_FILTER_TYPE_ACTIONS:
actions = super().get_queryset()
actions = actions.filter(deleted=False)
try:
action = actions.get(pk=entity.id)
except Action.DoesNotExist:
return []
filtered_events = base.process_entity_for_events(
entity, team_id=team.pk, order_by=None).filter(
base.filter_events(team.pk, filter, entity))
people = _calculate_people(events=filtered_events, offset=offset)
current_url = request.get_full_path()
next_url: Optional[str] = request.get_full_path()
if people["count"] > 99 and next_url:
if "offset" in next_url:
next_url = next_url[1:]
next_url = next_url.replace("offset=" + str(offset),
"offset=" + str(offset + 100))
else:
next_url = request.build_absolute_uri("{}{}offset={}".format(
next_url, "&" if "?" in next_url else "?", offset + 100))
else:
next_url = None
return {
"results": [people],
"next": next_url,
"previous": current_url[1:]
}
def get_people(
self,
filter: Filter,
team_id: int,
target_date: datetime,
lifecycle_type: str,
limit: int = 100,
):
entity = filter.entities[0]
period = filter.interval or "day"
num_intervals, prev_date_from, date_from, date_to, after_date_to = get_time_diff(
period, filter.date_from, filter.date_to, team_id)
interval_trunc, sub_interval = get_trunc_func(period=period)
# include the before and after when filteirng all events
filter = Filter(
data={
**filter._data, "date_from": prev_date_from.isoformat(),
"date_to": after_date_to.isoformat()
})
filtered_events = Event.objects.filter(team_id=team_id).filter(
filter_events(team_id, filter, entity))
event_query, event_params = queryset_to_named_query(filtered_events)
earliest_events_filtered = Event.objects.filter(
team_id=team_id).filter(
filter_events(team_id, filter, entity, include_dates=False))
earliest_events_query, earliest_events_params = queryset_to_named_query(
earliest_events_filtered, "earliest_events")
with connection.cursor() as cursor:
cursor.execute(
LIFECYCLE_PEOPLE_SQL.format(
action_join=ACTION_JOIN
if entity.type == TREND_FILTER_TYPE_ACTIONS else "",
event_condition="{} = %(event)s".format(
"action_id" if entity.type ==
TREND_FILTER_TYPE_ACTIONS else "event"),
events=event_query,
earliest_events=earliest_events_query,
),
{
"team_id": team_id,
"event": entity.id,
"interval": interval_trunc,
"one_interval": "1 " + interval_trunc,
"sub_interval": "1 " + sub_interval,
"num_intervals": num_intervals,
"prev_date_from": prev_date_from,
"date_from": date_from,
"date_to": date_to,
"after_date_to": after_date_to,
"target_date": target_date,
"status": lifecycle_type,
"offset": filter.offset,
"limit": limit,
**event_params,
**earliest_events_params,
},
)
pids = cursor.fetchall()
people = Person.objects.filter(
team_id=team_id,
id__in=[p[0] for p in pids],
)
people = people.prefetch_related(
Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
from posthog.api.person import PersonSerializer
return PersonSerializer(people, many=True).data
