def stickiness(filtered_events: QuerySet, entity: Entity, filter: Filter,
team_id: int) -> Dict[str, Any]:
if not filter.date_to or not filter.date_from:
raise ValueError("_stickiness needs date_to and date_from set")
range_days = (filter.date_to - filter.date_from).days + 2
events = (filtered_events.filter(filter_events(
team_id, filter, entity)).values("person_id").annotate(day_count=Count(
functions.TruncDay("timestamp"), distinct=True)).filter(
day_count__lte=range_days))
events_sql, events_sql_params = events.query.sql_with_params()
aggregated_query = "select count(v.person_id), v.day_count from ({}) as v group by v.day_count".format(
events_sql)
aggregated_counts = execute_custom_sql(aggregated_query, events_sql_params)
response: Dict[int, int] = {}
for result in aggregated_counts:
response[result[1]] = result[0]
labels = []
data = []
for day in range(1, range_days):
label = "{} day{}".format(day, "s" if day > 1 else "")
labels.append(label)
data.append(response[day] if day in response else 0)
return {
"labels": labels,
"days": [day for day in range(1, range_days)],
"data": data,
"count": sum(data),
}
def stickiness(self, entity: Entity, filter: Filter,
team_id: int) -> Dict[str, Any]:
if not filter.date_from:
filter._date_from = (Event.objects.filter(
team_id=team_id).order_by("timestamp")[0].timestamp.replace(
hour=0, minute=0, second=0, microsecond=0).isoformat())
if not filter.date_to or not filter.date_from:
raise ValueError("_stickiness needs date_to and date_from set")
range_days = (filter.date_to - filter.date_from).days + 2
events = process_entity_for_events(
entity=entity,
team_id=team_id,
order_by=None,
)
events = events.filter(filter_events(team_id, filter, entity))
events = (events.filter(filter_events(
team_id, filter,
entity)).values("person_id").annotate(day_count=Count(
functions.TruncDay("timestamp"), distinct=True)).filter(
day_count__lte=range_days))
events_sql, events_sql_params = events.query.sql_with_params()
aggregated_query = "select count(v.person_id), v.day_count from ({}) as v group by v.day_count".format(
events_sql)
counts = execute_custom_sql(aggregated_query, events_sql_params)
return self.process_result(counts, range_days)
def _aggregate_by_day(self, action: Action, filters: Dict[Any, Any],
request: request.Request):
append: Dict[str, Any] = {}
aggregates = Event.objects.filter_by_action(action)\
.filter(self._filter_events(request))\
.annotate(day=functions.TruncDay('timestamp'))\
.values('day')\
.annotate(count=Count('id'))\
.order_by()
if filters.get('math') == 'dau':
aggregates = aggregates.annotate(
count=Count('distinct_id', distinct=True))
if len(aggregates) > 0:
date_from, date_to = self._get_dates_from_request(request)
if not date_from:
date_from = aggregates[0]['day'].date()
dates_filled = self._group_events_to_date(date_from=date_from,
date_to=date_to,
aggregates=aggregates)
append = self._append_data(append, dates_filled)
if request.GET.get('breakdown'):
append['breakdown'] = self._breakdown(
aggregates, breakdown_by=request.GET['breakdown'])
return append
def _stickiness(self, filtered_events: QuerySet, entity: Entity, filter: Filter, team: Team) -> Dict[str, Any]:
if not filter.date_to or not filter.date_from:
raise ValueError('_stickiness needs date_to and date_from set')
range_days = (filter.date_to - filter.date_from).days + 2
events = filtered_events\
.filter(self._filter_events(team, filter, entity))\
.values('person_id') \
.annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\
.filter(day_count__lte=range_days)
events_sql, events_sql_params = events.query.sql_with_params()
aggregated_query = 'select count(v.person_id), v.day_count from ({}) as v group by v.day_count'.format(events_sql)
aggregated_counts = self._execute_custom_sql(aggregated_query, events_sql_params)
response: Dict[int, int] = {}
for result in aggregated_counts:
response[result[1]] = result[0]
labels = []
data = []
for day in range(1, range_days):
label = '{} day{}'.format(day, 's' if day > 1 else '')
labels.append(label)
data.append(response[day] if day in response else 0)
return {
'labels': labels,
'days': [day for day in range(1, range_days)],
'data': data,
'count': sum(data)
}
def _calculate_people(events: QuerySet):
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")))
people = Person.objects.filter(
team=team, id__in=[p["person_id"] for p in events[0:100]])
people = people.prefetch_related(
Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
return serialize_people(people=people, request=request)
def _calculate_people(events: QuerySet):
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')
))
people = Person.objects\
.filter(team=team, id__in=[p['person_id'] for p in events[0:100]])
people = people.prefetch_related(Prefetch('persondistinctid_set', to_attr='distinct_ids_cache'))
return self._serialize_people(
people=people,
request=request
)
def _serialize_action(self, action: Action, filters: Dict[Any, Any], request: request.Request, date_from: datetime, date_to: datetime) -> Dict:
append = {
'action': {
'id': action.pk,
'name': action.name
},
'label': action.name,
'count': 0,
'breakdown': []
}
aggregates = Event.objects.filter_by_action(action)\
.filter(**self._filter_events(request))\
.filter(timestamp__gte=date_from)\
.annotate(day=functions.TruncDay('timestamp'))\
.values('day')\
.annotate(count=Count('id'))\
.order_by()
if filters.get('math') == 'dau':
aggregates = aggregates.annotate(count=Count('distinct_id', distinct=True))
if len(aggregates) > 0:
dates_filled = self._group_events_to_date(date_from=date_from, aggregates=aggregates, steps=(date_to - date_from).days)
values = [value[0] for key, value in dates_filled.iterrows()]
append['labels'] = [key.strftime('%-d %B') for key, value in dates_filled.iterrows()]
append['data'] = values
append['count'] = sum(values)
if request.GET.get('breakdown'):
append['breakdown'] = self._breakdown(aggregates, breakdown_by=request.GET['breakdown'])
return append
def _aggregate_by_day(self, filtered_events: QuerySet, filters: Dict[Any,
Any],
request: request.Request) -> Dict[str, Any]:
append: Dict[str, Any] = {}
aggregates = filtered_events\
.filter(self._filter_events(request))\
.annotate(day=functions.TruncDay('timestamp'))\
.values('day')\
.annotate(count=Count('id'))\
.order_by()
aggregates = self._process_math(aggregates, filters)
if len(aggregates) > 0:
date_from, date_to = self._get_dates_from_request(request)
if not date_from:
date_from = aggregates[0]['day'].date()
dates_filled = self._group_events_to_date(date_from=date_from,
date_to=date_to,
aggregates=aggregates)
append = self._append_data(append, dates_filled)
if request.GET.get('breakdown'):
append = self._breakdown(append,
filtered_events,
filters,
request,
breakdown_by=request.GET['breakdown'])
return append
def people(self, request: request.Request, *args: Any, **kwargs: Any) -> Response:
actions = self.get_queryset()
actions = actions.filter(deleted=False)
actions_list = []
for action in actions:
events = Event.objects.filter_by_action(action, order_by=None).filter(self._filter_events(request))
if request.GET.get('shown_as', 'Volume') == 'Volume':
events = events.values('person_id').distinct()
elif request.GET['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)
people = Person.objects\
.filter(team=self.request.user.team_set.get(), id__in=[p['person_id'] for p in events[0:100]])
actions_list.append(self._serialize_people(
action=action,
people=people,
request=request
))
return Response(actions_list)
def _stickiness(self, filtered_events: QuerySet, filters: Dict[Any, Any],
request: request.Request) -> Dict[str, Any]:
date_from, date_to = self._get_dates_from_request(request)
range_days = (date_to - date_from).days + 2
events = filtered_events\
.filter(self._filter_events(request))\
.values('person_id') \
.annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\
.filter(day_count__lte=range_days)
events_sql, events_sql_params = events.query.sql_with_params()
aggregated_query = 'select count(v.person_id), v.day_count from ({}) as v group by v.day_count'.format(
events_sql)
aggregated_counts = self._execute_custom_sql(aggregated_query,
events_sql_params)
response: Dict[int, int] = {}
for result in aggregated_counts:
response[result[1]] = result[0]
labels = []
data = []
for day in range(1, range_days):
label = '{} day{}'.format(day, 's' if day > 1 else '')
labels.append(label)
data.append(response[day] if day in response else 0)
return {
'labels': labels,
'days': [day for day in range(1, range_days)],
'data': data
}
def stats(self, last):
qs = self.filter(start_time__gte=now() - timedelta(days=last))
qs = qs.annotate(
day=dbfunc.TruncDay('start_time'),
month=dbfunc.TruncMonth('start_time'),
year=dbfunc.TruncYear('start_time'),
)
return OrderedDict(day=self._get_history_stats_by(qs, 'day'),
month=self._get_history_stats_by(qs, 'month'),
year=self._get_history_stats_by(qs, 'year'))
def _get_interval_annotation(self, key: str) -> Dict[str, Any]:
map: Dict[str, Any] = {
'minute': functions.TruncMinute('timestamp'),
'hour': functions.TruncHour('timestamp'),
'day': functions.TruncDay('timestamp'),
'week': functions.TruncWeek('timestamp'),
'month': functions.TruncMonth('timestamp'),
}
func = map.get(key)
if func is None:
return {'day': map.get('day')} # default
return { key: func }
def get_interval_annotation(key: str) -> Dict[str, Any]:
map: Dict[str, Any] = {
"minute": functions.TruncMinute("timestamp"),
"hour": functions.TruncHour("timestamp"),
"day": functions.TruncDay("timestamp"),
"week": functions.TruncWeek("timestamp"),
"month": functions.TruncMonth("timestamp"),
}
func = map.get(key)
if func is None:
return {"day": map.get("day")} # default
return {key: func}
def _calculate_people(events: QuerySet):
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()
people = Person.objects\
.filter(team=team, id__in=[p['person_id'] for p in events[0:100]])
return self._serialize_people(people=people, request=request)
def get_interval_annotation(key: str) -> Dict[str, Any]:
map: Dict[str, Any] = {
"minute": functions.TruncMinute("timestamp"),
"hour": functions.TruncHour("timestamp"),
"day": functions.TruncDay("timestamp"),
"week": functions.TruncWeek(
ExpressionWrapper(F("timestamp") + datetime.timedelta(days=1), output_field=DateTimeField())
),
"month": functions.TruncMonth("timestamp"),
}
func = map.get(key)
if func is None:
return {"day": map.get("day")} # default
return {key: func}
def _calculate_people(entity: Entity, events: QuerySet):
if request.GET.get('shown_as', 'Volume') == 'Volume':
events = events.values('person_id').distinct()
elif request.GET['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)
people = Person.objects\
.filter(team=team, id__in=[p['person_id'] for p in events[0:100]])
return self._serialize_people(entity=entity,
people=people,
request=request)
def get_group_queryset(self, period_step, start, end):
query = self.get_span_queryset(period_step, start, end)
ann_kwargs = {}
if period_step == models.STEP_DAY:
ann_kwargs['_date'] = db_functions.TruncDay(
self.date_field, output_field=DateField())
ann_kwargs['_month'] = db_functions.TruncMonth(self.date_field)
ann_kwargs['_year'] = db_functions.TruncYear(self.date_field)
if period_step == models.STEP_MONTH:
ann_kwargs['_date'] = db_functions.TruncMonth(
self.date_field, output_field=DateField())
ann_kwargs['_year'] = db_functions.TruncYear(self.date_field)
if period_step == models.STEP_YEAR:
ann_kwargs['_date'] = db_functions.TruncYear(
self.date_field, output_field=DateField())
return query.annotate(**ann_kwargs).values(*ann_kwargs.keys())
def set_user_scores(apps, schema_editor):
from accounts.models import User
UserTPScore = apps.get_model("pootle_score.UserTPScore")
scorelogs = apps.get_model("pootle_statistics.ScoreLog").objects.exclude(
user__username__in=User.objects.META_USERS)
scorelogs = scorelogs.annotate(
day=functions.TruncDay("creation_time")).values(
"day", "user", "submission__translation_project").annotate(
score=Sum("score_delta"),
suggested=Sum(
Case(
When(
action_code=TranslationActionCodes.SUGG_ADDED,
then='wordcount'),
default=0,
output_field=IntegerField())),
translated=Sum(
Case(
When(
translated_wordcount__isnull=False,
then='translated_wordcount'),
default=0,
output_field=IntegerField())),
reviewed=Sum(
Case(
When(
action_code__in=[
TranslationActionCodes.SUGG_REVIEWED_ACCEPTED,
TranslationActionCodes.REVIEWED,
TranslationActionCodes.EDITED],
translated_wordcount__isnull=True,
then='wordcount'),
default=0,
output_field=IntegerField())))
UserTPScore.objects.bulk_create(
UserTPScore(
date=score["day"],
user_id=score["user"],
tp_id=score["submission__translation_project"],
score=score["score"],
reviewed=score["reviewed"],
suggested=score["suggested"],
translated=score["translated"])
for score in scorelogs.iterator())
def trends(self, request: request.Request, *args: Any,
**kwargs: Any) -> Response:
actions = self.get_queryset()
actions = actions.filter(deleted=False)
actions_list = []
steps = int(request.GET.get('days', 7))
date_from = datetime.date.today() - relativedelta(days=steps)
date_to = datetime.date.today()
for action in actions:
append = {
'action': {
'id': action.pk,
'name': action.name
},
'label': action.name,
'count': 0,
'breakdown': []
}
aggregates = self._filter_events(Event.objects.filter_by_action(action), request=request)\
.filter(timestamp__gte=date_from)\
.annotate(day=functions.TruncDay('timestamp'))\
.values('day')\
.annotate(count=Count('id'))\
.order_by()
if len(aggregates) > 0:
dates_filled = self._group_events_to_date(
date_from=date_from, aggregates=aggregates, steps=steps)
values = [value[0] for key, value in dates_filled.iterrows()]
append['labels'] = [
key.strftime('%-d %B')
for key, value in dates_filled.iterrows()
]
append['data'] = values
append['count'] = sum(values)
if request.GET.get('breakdown'):
append['breakdown'] = self._breakdown(
aggregates, breakdown_by=request.GET['breakdown'])
actions_list.append(append)
return Response(actions_list)
def _stickiness(self, action: Action, filters: Dict[Any, Any],
request: request.Request):
events = Event.objects.filter_by_action(action)\
.filter(self._filter_events(request))\
.annotate(day=functions.TruncDay('timestamp'))\
.annotate(distinct_person_day=Concat('person_id', 'day', output_field=TextField()))\
.order_by('distinct_person_day')\
.distinct('distinct_person_day')
date_from, date_to = self._get_dates_from_request(request)
people: Dict[int, int] = {}
for event in events:
if not people.get(event.person_id):
people[event.person_id] = 0
people[event.person_id] += 1
labels = []
data = []
for day in range(1, (date_to - date_from).days + 2):
label = '{} day{}'.format(day, 's' if day > 1 else '')
labels.append(label)
data.append(
len([key for key, value in people.items() if value == day]))
return {'labels': labels, 'data': data}
def trunc_day(self):
"""
Truncates date or datetime at a whole day.
"""
return functions.TruncDay(self._name)
