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}