def _annotate_steps(
self,
team_id: int,
funnel_steps: QuerySet,
date_query: Dict[str, datetime.date],
properties: Dict[str, str],
) -> Dict[str, Subquery]:
annotations = {}
for index, step in enumerate(funnel_steps):
filter_key = ("event" if step.get("type")
== TREND_FILTER_TYPE_EVENTS else "action__pk")
annotations["step_{}".format(index)] = Subquery(
Event.objects.all().annotate(person_id=OuterRef("id")).filter(
**{
filter_key: step["id"]
},
team_id=team_id,
distinct_id__in=Subquery(
PersonDistinctId.objects.filter(
team_id=team_id, person_id=OuterRef(
"person_id")).values("distinct_id")),
**({
"timestamp__gt": OuterRef("step_{}".format(index - 1))
} if index > 0 else {}),
**date_query).filter(properties_to_Q(properties)).filter(
properties_to_Q(step['properties']) if step.
get('properties') is not None else Q()).order_by(
"timestamp").values("timestamp")[:1])
return annotations
def _filter_events(self, request: request.Request) -> Q:
filters = Q()
date_from, date_to = self._get_dates_from_request(request=request)
if date_from:
filters &= Q(timestamp__gte=date_from)
if date_to:
interval = request.GET.get('interval')
relativity = relativedelta(days=1)
if interval == 'hour':
relativity = relativedelta(hours=1)
elif interval == 'minute':
relativity = relativedelta(minutes=1)
elif interval == 'week':
relativity = relativedelta(weeks=1)
elif interval == 'month':
relativity = relativedelta(
months=1
) - relativity # go to last day of month instead of first of next
filters &= Q(timestamp__lte=date_to + relativity)
if not request.GET.get('properties'):
return filters
properties = json.loads(request.GET['properties'])
filters &= properties_to_Q(properties)
return filters
def person_ids(self):
person_ids = []
for group in self.groups:
if group.get('action_id'):
action = Action.objects.get(pk=group['action_id'],
team_id=self.team_id)
people = Person.objects.filter(
team_id=self.team_id, ).annotate(has_action=Subquery(
Event.objects.filter_by_action(action).filter(
person_id=OuterRef('id'),
**({
'timestamp__gt':
timezone.now() -
relativedelta(days=group['days'])
} if group.get('days') else {}
)).values('id')[:1])).filter(
has_action__isnull=False)
person_ids.extend([person.id for person in people])
elif group.get('properties'):
properties = properties_to_Q(group['properties'])
person_ids.extend([
person_id for person_id in Person.objects.filter(
properties, team_id=self.team_id).order_by(
'-id').values_list('pk', flat=True)
])
return person_ids
def test_contains(self):
Event.objects.create(team=self.team, event='$pageview')
event2 = Event.objects.create(
team=self.team,
event='$pageview',
properties={'$current_url': 'https://whatever.com'})
properties = {'$current_url__icontains': 'whatever'}
events = Event.objects.filter(properties_to_Q(properties))
self.assertEqual(events.get(), event2)
def test_simple(self):
Event.objects.create(team=self.team, event='$pageview')
Event.objects.create(
team=self.team,
event='$pageview',
properties={'$current_url': 'https://whatever.com'})
properties = {'$current_url': 'https://whatever.com'}
events = Event.objects.filter(properties_to_Q(properties))
self.assertEqual(events.count(), 1)
def _filter_events(self, request: request.Request) -> Q:
filters = Q()
date_from, date_to = self._get_dates_from_request(request=request)
if date_from:
filters &= Q(timestamp__gte=date_from)
if date_to:
filters &= Q(timestamp__lte=date_to + relativedelta(days=1))
if not request.GET.get('properties'):
return filters
properties = json.loads(request.GET['properties'])
filters &= properties_to_Q(properties)
return filters
def test_does_not_contain(self):
event1 = Event.objects.create(team=self.team, event='$pageview')
event2 = Event.objects.create(
team=self.team,
event='$pageview',
properties={'$current_url': 'https://something.com'})
Event.objects.create(
team=self.team,
event='$pageview',
properties={'$current_url': 'https://whatever.com'})
properties = {'$current_url__not_icontains': 'whatever.com'}
events = Event.objects.filter(properties_to_Q(properties))
self.assertEqual(events[0], event1)
self.assertEqual(events[1], event2)
self.assertEqual(len(events), 2)
def _filter_request(self, request: request.Request, queryset: QuerySet) -> QuerySet:
for key, value in request.GET.items():
if key in ('event', 'ip'):
pass
elif key == 'after':
queryset = queryset.filter(timestamp__gt=request.GET['after'])
elif key == 'before':
queryset = queryset.filter(timestamp__lt=request.GET['before'])
elif key == 'person_id':
person = Person.objects.get(pk=request.GET['person_id'])
queryset = queryset.filter(distinct_id__in=person.distinct_ids)
elif key == 'distinct_id':
queryset = queryset.filter(distinct_id=request.GET['distinct_id'])
elif key == 'action_id':
queryset = queryset.filter_by_action(Action.objects.get(pk=value)) # type: ignore
elif key == 'properties':
queryset = queryset.filter(properties_to_Q(json.loads(value)))
return queryset
def people_filter(self):
filters = Q()
for group in self.groups:
if group.get("action_id"):
action = Action.objects.get(pk=group["action_id"],
team_id=self.team_id)
events = (Event.objects.filter_by_action(action).filter(
team_id=self.team_id,
**({
"timestamp__gt":
timezone.now() - relativedelta(days=group["days"])
} if group.get("days") else {})).order_by("distinct_id").
distinct("distinct_id").values("distinct_id"))
filters |= Q(persondistinctid__distinct_id__in=events)
elif group.get("properties"):
properties = properties_to_Q(group["properties"])
filters |= Q(properties)
return filters
def test_multiple(self):
event2 = Event.objects.create(team=self.team,
event='$pageview',
properties={
'$current_url':
'https://something.com',
'another_key': 'value'
})
Event.objects.create(
team=self.team,
event='$pageview',
properties={'$current_url': 'https://something.com'})
properties = {
'$current_url__icontains': 'something.com',
'another_key': 'value'
}
events = Event.objects.filter(properties_to_Q(properties))
self.assertEqual(events[0], event2)
self.assertEqual(len(events), 1)
def people_filter(self):
filters = Q()
for group in self.groups:
if group.get('action_id'):
action = Action.objects.get(pk=group['action_id'],
team_id=self.team_id)
events = Event.objects.filter_by_action(action).filter(
team_id=self.team_id,
**({
'timestamp__gt':
timezone.now() - relativedelta(days=group['days'])
} if group.get('days') else
{})).order_by('distinct_id').distinct(
'distinct_id').values('distinct_id')
filters |= Q(persondistinctid__distinct_id__in=events)
elif group.get('properties'):
properties = properties_to_Q(group['properties'])
filters |= Q(properties)
return filters
def _filter_events(self, filter: Filter) -> Q:
filters = Q()
if filter.date_from:
filters &= Q(timestamp__gte=filter.date_from)
if filter.date_to:
relativity = relativedelta(days=1)
if filter.interval == 'hour':
relativity = relativedelta(hours=1)
elif filter.interval == 'minute':
relativity = relativedelta(minutes=1)
elif filter.interval == 'week':
relativity = relativedelta(weeks=1)
elif filter.interval == 'month':
relativity = relativedelta(
months=1
) - relativity # go to last day of month instead of first of next
filters &= Q(timestamp__lte=filter.date_to + relativity)
if filter.properties:
filters &= properties_to_Q(filter.properties)
return filters
