def track_lifecycle_person_property_filter_materialized(self):
filter = Filter(
data={
"insight":
"LIFECYCLE",
"events": [{
"id": "$pageview",
"type": "events"
}],
"interval":
"week",
"shown_as":
"Lifecycle",
"date_from":
"-14d",
"properties": [{
"key": "email",
"operator": "icontains",
"value": ".com",
"type": "person"
}],
**DATE_RANGE,
},
team=self.team,
)
ClickhouseTrends().run(filter, self.team)
def calculate_trends(self, request: Request) -> Dict[str, Any]:
team = self.team
filter = Filter(request=request)
if filter.insight == INSIGHT_STICKINESS or filter.shown_as == TRENDS_STICKINESS:
stickiness_filter = StickinessFilter(
request=request,
team=team,
get_earliest_timestamp=get_earliest_timestamp)
result = ClickhouseStickiness().run(stickiness_filter, team)
else:
trends_query = ClickhouseTrends()
result = trends_query.run(filter, team)
self._refresh_dashboard(request=request)
return {"result": result}
def track_lifecycle_event_property_filter_materialized(self):
filter = Filter(
data={
"insight":
"LIFECYCLE",
"events": [{
"id": "$pageview",
"type": "events"
}],
"interval":
"week",
"shown_as":
"Lifecycle",
"date_from":
"-14d",
"properties": [{
"key":
"$host",
"operator":
"is_not",
"value": [
"localhost:8000",
"localhost:5000",
"127.0.0.1:8000",
"127.0.0.1:3000",
"localhost:3000",
],
}],
**DATE_RANGE,
},
team=self.team,
)
ClickhouseTrends().run(filter, self.team)
def test_breakdown_filtering_bar_chart_by_value(self):
self._create_events()
# test breakdown filtering
with freeze_time("2020-01-04T13:01:01Z"):
response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-7d",
"breakdown": "$some_property",
"events": [{"id": "sign up", "name": "sign up", "type": "events", "order": 0,},],
"display": TRENDS_BAR_VALUE,
}
),
self.team,
)
self.assertEqual(response[0]["aggregated_value"], 2) # the events without breakdown value
self.assertEqual(response[1]["aggregated_value"], 1)
self.assertEqual(response[2]["aggregated_value"], 1)
self.assertEqual(
response[0]["days"],
[
"2019-12-28",
"2019-12-29",
"2019-12-30",
"2019-12-31",
"2020-01-01",
"2020-01-02",
"2020-01-03",
"2020-01-04",
],
)
def test_action_with_prop(self):
person = Person.objects.create(
team_id=self.team.pk, distinct_ids=["blabla", "anonymous_id"], properties={"$some_prop": "some_val"}
)
sign_up_action = Action.objects.create(team=self.team, name="sign up")
ActionStep.objects.create(
action=sign_up_action, event="sign up", properties={"$current_url": "https://posthog.com/feedback/1234"}
)
with freeze_time("2020-01-02T13:01:01Z"):
_create_event(
team=self.team,
event="sign up",
distinct_id="blabla",
properties={"$current_url": "https://posthog.com/feedback/1234"},
)
with freeze_time("2020-01-04T13:01:01Z"):
action_response = ClickhouseTrends().run(
Filter(
data={
"actions": [{"id": sign_up_action.id, "math": "dau"}],
"properties": [{"key": "$current_url", "value": "fake"}],
}
),
self.team,
)
# if the params were shared it would be 1 because action would take precedence
self.assertEqual(action_response[0]["count"], 0)
def test_breakdown_user_props_with_filter(self):
Person.objects.create(team_id=self.team.pk, distinct_ids=["person1"], properties={"email": "*****@*****.**"})
Person.objects.create(team_id=self.team.pk, distinct_ids=["person2"], properties={"email": "*****@*****.**"})
person = Person.objects.create(
team_id=self.team.pk, distinct_ids=["person3"], properties={"email": "*****@*****.**"}
)
create_person_distinct_id(person.id, self.team.pk, "person1", str(person.uuid))
_create_event(event="sign up", distinct_id="person1", team=self.team, properties={"key": "val"})
_create_event(event="sign up", distinct_id="person2", team=self.team, properties={"key": "val"})
response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "email",
"breakdown_type": "person",
"events": [{"id": "sign up", "name": "sign up", "type": "events", "order": 0,}],
"properties": [
{"key": "email", "value": "@posthog.com", "operator": "not_icontains", "type": "person"},
{"key": "key", "value": "val"},
],
}
),
self.team,
)
self.assertEqual(len(response), 1)
self.assertEqual(response[0]["breakdown_value"], "*****@*****.**")
def test_breakdown_filtering_persons_with_action_props(self):
Person.objects.create(team_id=self.team.pk, distinct_ids=["person1"], properties={"email": "*****@*****.**"})
Person.objects.create(team_id=self.team.pk, distinct_ids=["person2"], properties={"email": "*****@*****.**"})
Person.objects.create(team_id=self.team.pk, distinct_ids=["person3"], properties={})
_create_event(event="sign up", distinct_id="person1", team=self.team, properties={"key": "val"})
_create_event(event="sign up", distinct_id="person2", team=self.team, properties={"key": "val"})
_create_event(event="sign up", distinct_id="person3", team=self.team, properties={"key": "val"})
action = _create_action(
name="sign up",
team=self.team,
properties=[{"key": "key", "type": "event", "value": ["val"], "operator": "exact"}],
)
response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "email",
"breakdown_type": "person",
"actions": [{"id": action.pk, "type": "actions", "order": 0}],
}
),
self.team,
)
self.assertEqual(response[0]["label"], "sign up - none")
self.assertEqual(response[1]["label"], "sign up - [email protected]")
self.assertEqual(response[2]["label"], "sign up - [email protected]")
self.assertEqual(response[0]["count"], 1)
self.assertEqual(response[1]["count"], 1)
self.assertEqual(response[2]["count"], 1)
def test_breakdown_filtering(self):
self._create_events()
# test breakdown filtering
with freeze_time("2020-01-04T13:01:01Z"):
response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "$some_property",
"events": [
{"id": "sign up", "name": "sign up", "type": "events", "order": 0,},
{"id": "no events"},
],
}
),
self.team,
)
self.assertEqual(response[0]["label"], "sign up - none")
self.assertEqual(response[1]["label"], "sign up - value")
self.assertEqual(response[2]["label"], "sign up - other_value")
self.assertEqual(response[3]["label"], "no events - none")
self.assertEqual(sum(response[0]["data"]), 2)
self.assertEqual(sum(response[1]["data"]), 2)
self.assertEqual(sum(response[2]["data"]), 1)
self.assertEqual(sum(response[3]["data"]), 1)
def test_combine_all_cohort_and_icontains(self):
# This caused some issues with SQL parsing
sign_up_action, _ = self._create_events()
cohort = Cohort.objects.create(team=self.team,
name="a",
groups=[{
"properties": {
"key": "value"
}
}])
action_response = ClickhouseTrends().run(
Filter(
data={
"actions": [{
"id": sign_up_action.id,
"math": "dau"
}],
"properties": [{
"key": "$current_url",
"value": "ii",
"operator": "icontains"
}],
"breakdown": [cohort.pk, "all"],
"breakdown_type":
"cohort",
}),
self.team,
)
self.assertEqual(action_response[0]["count"], 0)
def track_trends_filter_by_action_current_url_materialized(self):
action = Action.objects.create(team=self.team, name="docs view")
ActionStep.objects.create(
action=action, event="$pageview", url="docs", url_matching="contains",
)
filter = Filter(data={"actions": [{"id": action.id}], **DATE_RANGE}, team=self.team)
ClickhouseTrends().run(filter, self.team)
def test_breakdown_filtering_with_properties(self):
with freeze_time("2020-01-03T13:01:01Z"):
_create_event(
team=self.team,
event="sign up",
distinct_id="blabla",
properties={"$current_url": "first url", "$browser": "Firefox", "$os": "Mac"},
)
_create_event(
team=self.team,
event="sign up",
distinct_id="blabla",
properties={"$current_url": "first url", "$browser": "Chrome", "$os": "Windows"},
)
with freeze_time("2020-01-04T13:01:01Z"):
_create_event(
team=self.team,
event="sign up",
distinct_id="blabla",
properties={"$current_url": "second url", "$browser": "Firefox", "$os": "Mac"},
)
_create_event(
team=self.team,
event="sign up",
distinct_id="blabla",
properties={"$current_url": "second url", "$browser": "Chrome", "$os": "Windows"},
)
with freeze_time("2020-01-05T13:01:01Z"):
response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "$current_url",
"events": [
{
"id": "sign up",
"name": "sign up",
"type": "events",
"order": 0,
"properties": [{"key": "$os", "value": "Mac"}],
},
],
"properties": [{"key": "$browser", "value": "Firefox"}],
}
),
self.team,
)
response = sorted(response, key=lambda x: x["label"])
self.assertEqual(response[0]["label"], "sign up - first url")
self.assertEqual(response[1]["label"], "sign up - second url")
self.assertEqual(sum(response[0]["data"]), 1)
self.assertEqual(response[0]["breakdown_value"], "first url")
self.assertEqual(sum(response[1]["data"]), 1)
self.assertEqual(response[1]["breakdown_value"], "second url")
def track_trends_dau(self):
filter = Filter(data={
"events": [{
"id": "$pageview",
"math": "dau"
}],
**DATE_RANGE,
})
ClickhouseTrends().run(filter, self.team)
def test_dau_with_breakdown_filtering(self):
sign_up_action, _ = self._create_events()
with freeze_time("2020-01-02T13:01:01Z"):
_create_event(
team=self.team,
event="sign up",
distinct_id="blabla",
properties={"$some_property": "other_value"},
)
with freeze_time("2020-01-04T13:01:01Z"):
action_response = ClickhouseTrends().run(
Filter(
data={
"breakdown": "$some_property",
"actions": [{
"id": sign_up_action.id,
"math": "dau"
}]
}),
self.team,
)
event_response = ClickhouseTrends().run(
Filter(
data={
"breakdown": "$some_property",
"events": [{
"id": "sign up",
"math": "dau"
}]
}),
self.team,
)
self.assertEqual(event_response[0]["label"], "sign up - value")
self.assertEqual(event_response[1]["label"], "sign up - other_value")
self.assertEqual(sum(event_response[0]["data"]), 1)
self.assertEqual(event_response[0]["data"][4],
1) # property not defined
self.assertEqual(sum(event_response[1]["data"]), 1)
self.assertEqual(event_response[1]["data"][5], 1)
self.assertTrue(
self._compare_entity_response(action_response, event_response))
def test_breakdown_by_person_property(self):
person1, person2, person3, person4 = self._create_multiple_people()
action = _create_action(name="watched movie", team=self.team)
with freeze_time("2020-01-04T13:01:01Z"):
action_response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "name",
"breakdown_type": "person",
"actions": [{"id": action.pk, "type": "actions", "order": 0}],
}
),
self.team,
)
event_response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "name",
"breakdown_type": "person",
"events": [{"id": "watched movie", "name": "watched movie", "type": "events", "order": 0,}],
}
),
self.team,
)
self.assertListEqual(
[res["breakdown_value"] for res in event_response], ["none", "person1", "person2", "person3"]
)
for response in event_response:
if response["breakdown_value"] == "person1":
self.assertEqual(response["count"], 1)
self.assertEqual(response["label"], "watched movie - person1")
if response["breakdown_value"] == "person2":
self.assertEqual(response["count"], 3)
if response["breakdown_value"] == "person3":
self.assertEqual(response["count"], 3)
self.assertEntityResponseEqual(
event_response, action_response,
)
def track_trends_filter_by_action_with_person_filters_materialized(self):
action = Action.objects.create(team=self.team, name=".com-users page views")
ActionStep.objects.create(
action=action,
event="$pageview",
properties=[{"key": "email", "operator": "icontains", "value": ".com", "type": "person"}],
)
filter = Filter(data={"actions": [{"id": action.id}], **DATE_RANGE}, team=self.team)
ClickhouseTrends().run(filter, self.team)
def track_trends_event_property_breakdown_materialized(self):
filter = Filter(data={
"events": [{
"id": "$pageview"
}],
"breakdown": "$host",
**DATE_RANGE,
})
ClickhouseTrends().run(filter, self.team)
def track_trends_person_property_filter_materialized(self):
filter = Filter(
data={
"events": [{"id": "$pageview"}],
"properties": [{"key": "email", "operator": "icontains", "value": ".com", "type": "person"}],
**DATE_RANGE,
}
)
ClickhouseTrends().run(filter, self.team)
def test_breakdown_multiple_cohorts(self):
p1 = Person.objects.create(team_id=self.team.pk, distinct_ids=["p1"], properties={"key": "value"})
_create_event(
team=self.team,
event="$pageview",
distinct_id="p1",
timestamp="2020-01-02T12:00:00Z",
properties={"key": "val"},
)
p2 = Person.objects.create(team_id=self.team.pk, distinct_ids=["p2"], properties={"key_2": "value_2"})
_create_event(
team=self.team,
event="$pageview",
distinct_id="p2",
timestamp="2020-01-02T12:00:00Z",
properties={"key": "val"},
)
p3 = Person.objects.create(team_id=self.team.pk, distinct_ids=["p3"], properties={"key_2": "value_2"})
_create_event(
team=self.team,
event="$pageview",
distinct_id="p3",
timestamp="2020-01-02T12:00:00Z",
properties={"key": "val"},
)
cohort1 = _create_cohort(team=self.team, name="cohort_1", groups=[{"properties": {"key": "value"}}])
cohort2 = _create_cohort(team=self.team, name="cohort_2", groups=[{"properties": {"key_2": "value_2"}}])
cohort1.calculate_people()
cohort1.calculate_people_ch()
cohort2.calculate_people()
cohort2.calculate_people_ch()
with self.settings(USE_PRECALCULATED_CH_COHORT_PEOPLE=True): # Normally this is False in tests
with freeze_time("2020-01-04T13:01:01Z"):
res = ClickhouseTrends().run(
Filter(
data={
"date_from": "-7d",
"events": [{"id": "$pageview"}],
"properties": [],
"breakdown": [cohort1.pk, cohort2.pk],
"breakdown_type": "cohort",
}
),
self.team,
)
self.assertEqual(res[0]["count"], 1)
self.assertEqual(res[1]["count"], 2)
def test_active_user_math(self):
self._create_active_user_events()
data = {
"date_from": "2020-01-09T00:00:00Z",
"date_to": "2020-01-16T00:00:00Z",
"events": [{"id": "$pageview", "type": "events", "order": 0, "math": "weekly_active"}],
}
filter = Filter(data=data)
result = ClickhouseTrends().run(filter, self.team,)
self.assertEqual(result[0]["data"], [3.0, 2.0, 2.0, 0.0, 0.0, 0.0, 0.0, 0.0])
def test_test_account_filters_with_groups(self):
self.team.test_account_filters = [
{"key": "key", "type": "group", "value": "value", "group_type_index": 0},
]
self.team.save()
GroupTypeMapping.objects.create(team=self.team, group_type="organization", group_type_index=0)
create_group(self.team.pk, group_type_index=0, group_key="in", properties={"key": "value"})
create_group(self.team.pk, group_type_index=0, group_key="out", properties={"key": "othervalue"})
with freeze_time("2020-01-11T12:00:00Z"):
Person.objects.create(distinct_ids=["person1"], team_id=self.team.pk)
with freeze_time("2020-01-09T12:00:00Z"):
Person.objects.create(distinct_ids=["person2"], team_id=self.team.pk)
journeys_for(
{
"person1": [
{"event": "$pageview", "timestamp": datetime(2020, 1, 11, 12), "properties": {"$group_0": "out"},},
],
"person2": [
{"event": "$pageview", "timestamp": datetime(2020, 1, 9, 12), "properties": {"$group_0": "in"},},
{"event": "$pageview", "timestamp": datetime(2020, 1, 12, 12), "properties": {"$group_0": "in"},},
{"event": "$pageview", "timestamp": datetime(2020, 1, 15, 12), "properties": {"$group_0": "in"},},
],
},
self.team,
)
result = ClickhouseTrends().run(
Filter(
data={
"date_from": "2020-01-12T00:00:00Z",
"date_to": "2020-01-19T00:00:00Z",
"events": [{"id": "$pageview", "type": "events", "order": 0}],
"shown_as": TRENDS_LIFECYCLE,
FILTER_TEST_ACCOUNTS: True,
},
team=self.team,
),
self.team,
)
self.assertLifecycleResults(
result,
[
{"status": "dormant", "data": [0, -1, 0, 0, -1, 0, 0, 0]},
{"status": "new", "data": [0, 0, 0, 0, 0, 0, 0, 0]},
{"status": "resurrecting", "data": [1, 0, 0, 1, 0, 0, 0, 0]},
{"status": "returning", "data": [0, 0, 0, 0, 0, 0, 0, 0]},
],
)
def track_trends_filter_by_cohort_precalculated(self):
self.cohort.last_calculation = now()
self.cohort.save()
filter = Filter(
data={
"events": [{"id": "$pageview"}],
"properties": [{"key": "id", "value": self.cohort.pk, "type": "cohort"}],
**DATE_RANGE,
},
team=self.team,
)
ClickhouseTrends().run(filter, self.team)
def trend(self, request: Request, *args: Any, **kwargs: Any) -> Response:
team = request.user.team
filter = Filter(request=request)
if filter.shown_as == TRENDS_STICKINESS:
result = ClickhouseStickiness().run(filter, team)
else:
result = ClickhouseTrends().run(filter, team)
self._refresh_dashboard(request=request)
return Response(result)
def track_trends_person_property_breakdown(self):
filter = Filter(
data={
"events": [{
"id": "$pageview"
}],
"breakdown": "$browser",
"breakdown_type": "person",
**DATE_RANGE,
})
with no_materialized_columns():
ClickhouseTrends().run(filter, self.team)
def test_breakdown_active_user_math(self):
p1 = Person.objects.create(team_id=self.team.pk, distinct_ids=["p1"], properties={"name": "p1"})
_create_event(
team=self.team,
event="$pageview",
distinct_id="p1",
timestamp="2020-01-09T12:00:00Z",
properties={"key": "val"},
)
_create_event(
team=self.team,
event="$pageview",
distinct_id="p1",
timestamp="2020-01-10T12:00:00Z",
properties={"key": "val"},
)
_create_event(
team=self.team,
event="$pageview",
distinct_id="p1",
timestamp="2020-01-11T12:00:00Z",
properties={"key": "val"},
)
p2 = Person.objects.create(team_id=self.team.pk, distinct_ids=["p2"], properties={"name": "p2"})
_create_event(
team=self.team,
event="$pageview",
distinct_id="p2",
timestamp="2020-01-09T12:00:00Z",
properties={"key": "val"},
)
_create_event(
team=self.team,
event="$pageview",
distinct_id="p2",
timestamp="2020-01-11T12:00:00Z",
properties={"key": "val"},
)
data = {
"date_from": "2020-01-01T00:00:00Z",
"date_to": "2020-01-12T00:00:00Z",
"breakdown": "key",
"events": [{"id": "$pageview", "type": "events", "order": 0, "math": "weekly_active"}],
}
filter = Filter(data=data)
result = ClickhouseTrends().run(filter, self.team,)
self.assertEqual(result[1]["data"], [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2.0, 2.0, 2.0, 0.0])
def test_breakdown_filtering_limit(self):
self._create_breakdown_events()
with freeze_time("2020-01-04T13:01:01Z"):
response = ClickhouseTrends().run(
Filter(
data={
"date_from": "-14d",
"breakdown": "$some_property",
"events": [{"id": "sign up", "name": "sign up", "type": "events", "order": 0}],
}
),
self.team,
)
self.assertEqual(len(response), 26) # We fetch 25 to see if there are more ethan 20 values
def track_trends_filter_by_cohort(self):
self.cohort.last_calculation = None
self.cohort.save()
filter = Filter(
data={
"events": [{"id": "$pageview"}],
"properties": [{"key": "id", "value": self.cohort.pk, "type": "cohort"}],
**DATE_RANGE,
},
team=self.team,
)
with no_materialized_columns():
ClickhouseTrends().run(filter, self.team)
def calculate_trends(self, request: Request) -> List[Dict[str, Any]]:
team = self.team
filter = Filter(request=request)
if filter.shown_as == TRENDS_STICKINESS:
earliest_timestamp_func = lambda team_id: get_earliest_timestamp(team_id)
stickiness_filter = StickinessFilter(
request=request, team=team, get_earliest_timestamp=earliest_timestamp_func
)
result = ClickhouseStickiness().run(stickiness_filter, team)
else:
result = ClickhouseTrends().run(filter, team)
self._refresh_dashboard(request=request)
return result
def _run(self, extra: Dict = {}, run_at: Optional[str] = None):
with freeze_time(run_at or "2020-01-04T13:01:01Z"):
action_response = ClickhouseTrends().run(
Filter(
data={
"events": [
{"id": "session start", "math": "sum", "math_property": "session duration"},
{"id": "session start", "math": "avg", "math_property": "session duration"},
],
"formula": "A + B",
**extra,
}
),
self.team,
)
return action_response
def track_lifecycle(self):
filter = Filter(
data={
"insight": "LIFECYCLE",
"events": [{
"id": "$pageview",
"type": "events"
}],
"interval": "week",
"shown_as": "Lifecycle",
"date_from": "-14d",
**DATE_RANGE,
},
team=self.team,
)
ClickhouseTrends().run(filter, self.team)
def get_results(self):
if self.query_filter.insight == INSIGHT_TRENDS:
trend_results = ClickhouseTrends().run(self.query_filter,
self.team)
variants = self.get_trend_count_data_for_variants(trend_results)
elif self.query_filter.insight == INSIGHT_FUNNELS:
funnel_results = ClickhouseFunnel(self.query_filter,
self.team).run()
variants = self.get_funnel_conversion_rate_for_variants(
funnel_results)
else:
raise ValidationError(
"Secondary metrics need to be funnel or trend insights")
return {"result": variants}
