Repository: PostHog/posthog
Pull request: feat(funnels): allow seconds for funnel conversion window
## Problem

A user would like to set their conversion window for a matter of seconds. While probably not _that_ common of a request, adding it seemed easy enough? And I can see how it would be useful.

Fixes https://github.com/PostHog/posthog/issues/14380

![image](https://user-images.githubusercontent.com/18598166/221223855-f8de93c3-bb24-4544-be31-217ecc172239.png)


<!-- Who are we building for, what are their needs, why is this important? -->

## Changes

Adds seconds as a funnel conversion interval. 

<!-- If there are frontend changes, please include screenshots. -->
<!-- If a reference design was involved, include a link to the relevant Figma frame! -->

👉 *Stay up-to-date with [PostHog coding conventions](https://posthog.com/docs/contribute/coding-conventions) for a smoother review.*

## How did you test this code?

Manually, and I updated some tests. But I have not worked on this area of the code before so it's likely I did this entirely wrong or missed something 😄 

<!-- Briefly describe the steps you took. -->
<!-- Include automated tests if possible, otherwise describe the manual testing routine. -->


Problem statement:
Allow setting conversion window limit in seconds
## Is your feature request related to a problem?

We would like to create an insight that identifies users returning to the same page very quickly. So far the best option I have identified to do this is to create a three step funnel with a conversion window limit. However the minimum window limit currently supported is one minute, and we are looking to capture behaviours that happen in seconds.

## Describe the solution you'd like

Allow setting a conversion window limit in seconds.

## Describe alternatives you've considered

Setting fractional values of minutes does not appear to work. Maybe a different insight model could solve the problem above?

## Additional context

N/A

#### *Thank you* for your feature request – we love each and every one!

Hints from issue discussion:

Commit message: Update UI snapshots for `chromium` (1)

Patch to review:
diff --git a/frontend/__snapshots__/components-integrations-slack--slack-integration-added.png b/frontend/__snapshots__/components-integrations-slack--slack-integration-added.png
index d55b52a440079..1feda2dcd15a7 100644
Binary files a/frontend/__snapshots__/components-integrations-slack--slack-integration-added.png and b/frontend/__snapshots__/components-integrations-slack--slack-integration-added.png differ
diff --git a/frontend/src/queries/schema.json b/frontend/src/queries/schema.json
index 3c9508dfa7c21..766948b90eaad 100644
--- a/frontend/src/queries/schema.json
+++ b/frontend/src/queries/schema.json
@@ -1745,7 +1745,7 @@
             "type": "string"
         },
         "FunnelConversionWindowTimeUnit": {
-            "enum": ["minute", "hour", "day", "week", "month"],
+            "enum": ["second", "minute", "hour", "day", "week", "month"],
             "type": "string"
         },
         "FunnelLayout": {
diff --git a/frontend/src/scenes/funnels/funnelUtils.test.ts b/frontend/src/scenes/funnels/funnelUtils.test.ts
index 65933d5c82427..e58bf31db4fb9 100644
--- a/frontend/src/scenes/funnels/funnelUtils.test.ts
+++ b/frontend/src/scenes/funnels/funnelUtils.test.ts
@@ -110,6 +110,11 @@ describe('getBreakdownStepValues()', () => {
 
 describe('getIncompleteConversionWindowStartDate()', () => {
     const windows = [
+        {
+            funnel_window_interval: 10,
+            funnel_window_interval_unit: FunnelConversionWindowTimeUnit.Second,
+            expected: '2018-04-04T15:59:50.000Z',
+        },
         {
             funnel_window_interval: 60,
             funnel_window_interval_unit: FunnelConversionWindowTimeUnit.Minute,
diff --git a/frontend/src/scenes/insights/views/Funnels/FunnelConversionWindowFilter.tsx b/frontend/src/scenes/insights/views/Funnels/FunnelConversionWindowFilter.tsx
index cc52463c2984b..e121a180118a9 100644
--- a/frontend/src/scenes/insights/views/Funnels/FunnelConversionWindowFilter.tsx
+++ b/frontend/src/scenes/insights/views/Funnels/FunnelConversionWindowFilter.tsx
@@ -41,6 +41,7 @@ export function FunnelConversionWindowFilter({ insightProps }: Pick<EditorFilter
 }
 
 const TIME_INTERVAL_BOUNDS: Record<FunnelConversionWindowTimeUnit, number[]> = {
+    [FunnelConversionWindowTimeUnit.Second]: [1, 3600],
     [FunnelConversionWindowTimeUnit.Minute]: [1, 1440],
     [FunnelConversionWindowTimeUnit.Hour]: [1, 24],
     [FunnelConversionWindowTimeUnit.Day]: [1, 365],
diff --git a/frontend/src/types.ts b/frontend/src/types.ts
index 4ed45607dab78..00d4dcee79316 100644
--- a/frontend/src/types.ts
+++ b/frontend/src/types.ts
@@ -1821,6 +1821,7 @@ export interface FunnelConversionWindow {
 
 // https://github.com/PostHog/posthog/blob/master/posthog/models/filters/mixins/funnel.py#L100
 export enum FunnelConversionWindowTimeUnit {
+    Second = 'second',
     Minute = 'minute',
     Hour = 'hour',
     Day = 'day',
diff --git a/posthog/constants.py b/posthog/constants.py
index 8727e946104b4..8c08d7e122d0a 100644
--- a/posthog/constants.py
+++ b/posthog/constants.py
@@ -157,7 +157,7 @@ class AvailableFeature(str, Enum):
 FUNNEL_ORDER_TYPE = "funnel_order_type"
 FUNNEL_VIZ_TYPE = "funnel_viz_type"
 FUNNEL_CORRELATION_TYPE = "funnel_correlation_type"
-FUNNEL_WINDOW_INTERVAL_TYPES = Literal["DAY", "MINUTE", "HOUR", "WEEK", "MONTH"]
+FUNNEL_WINDOW_INTERVAL_TYPES = Literal["DAY", "SECOND", "MINUTE", "HOUR", "WEEK", "MONTH"]
 # Funnel Correlation Properties
 FUNNEL_CORRELATION_NAMES = "funnel_correlation_names"
 FUNNEL_CORRELATION_EXCLUDE_NAMES = "funnel_correlation_exclude_names"
diff --git a/posthog/models/filters/mixins/base.py b/posthog/models/filters/mixins/base.py
index b2562a2082241..b0c79566f72d5 100644
--- a/posthog/models/filters/mixins/base.py
+++ b/posthog/models/filters/mixins/base.py
@@ -2,7 +2,7 @@
 
 BreakdownType = Literal["event", "person", "cohort", "group", "session", "hogql"]
 IntervalType = Literal["hour", "day", "week", "month"]
-FunnelWindowIntervalType = Literal["minute", "hour", "day", "week", "month"]
+FunnelWindowIntervalType = Literal["second", "minute", "hour", "day", "week", "month"]
 
 
 class BaseParamMixin:
diff --git a/posthog/models/filters/mixins/funnel.py b/posthog/models/filters/mixins/funnel.py
index 130bf7248442d..534f236b6524b 100644
--- a/posthog/models/filters/mixins/funnel.py
+++ b/posthog/models/filters/mixins/funnel.py
@@ -121,6 +121,8 @@ def funnel_window_interval_unit_ch(self) -> FUNNEL_WINDOW_INTERVAL_TYPES:
         if self.funnel_window_interval_unit is None:
             return "DAY"
 
+        if self.funnel_window_interval_unit == "second":
+            return "SECOND"
         if self.funnel_window_interval_unit == "minute":
             return "MINUTE"
         elif self.funnel_window_interval_unit == "hour":
diff --git a/posthog/queries/funnels/test/__snapshots__/test_funnel.ambr b/posthog/queries/funnels/test/__snapshots__/test_funnel.ambr
index 490a5eae4e862..e5e2a4df90296 100644
--- a/posthog/queries/funnels/test/__snapshots__/test_funnel.ambr
+++ b/posthog/queries/funnels/test/__snapshots__/test_funnel.ambr
@@ -1,3 +1,191 @@
+# name: TestFOSSFunnel.test_funnel_conversion_window_seconds
+  '
+  
+  SELECT countIf(steps = 1) step_1,
+         countIf(steps = 2) step_2,
+         countIf(steps = 3) step_3,
+         avg(step_1_average_conversion_time_inner) step_1_average_conversion_time,
+         avg(step_2_average_conversion_time_inner) step_2_average_conversion_time,
+         median(step_1_median_conversion_time_inner) step_1_median_conversion_time,
+         median(step_2_median_conversion_time_inner) step_2_median_conversion_time
+  FROM
+    (SELECT aggregation_target,
+            steps,
+            avg(step_1_conversion_time) step_1_average_conversion_time_inner,
+            avg(step_2_conversion_time) step_2_average_conversion_time_inner,
+            median(step_1_conversion_time) step_1_median_conversion_time_inner,
+            median(step_2_conversion_time) step_2_median_conversion_time_inner
+     FROM
+       (SELECT aggregation_target,
+               steps,
+               max(steps) over (PARTITION BY aggregation_target) as max_steps,
+                               step_1_conversion_time,
+                               step_2_conversion_time
+        FROM
+          (SELECT *,
+                  if(latest_0 <= latest_1
+                     AND latest_1 <= latest_0 + INTERVAL 15 SECOND
+                     AND latest_1 <= latest_2
+                     AND latest_2 <= latest_0 + INTERVAL 15 SECOND, 3, if(latest_0 <= latest_1
+                                                                          AND latest_1 <= latest_0 + INTERVAL 15 SECOND, 2, 1)) AS steps ,
+                  if(isNotNull(latest_1)
+                     AND latest_1 <= latest_0 + INTERVAL 15 SECOND, dateDiff('second', toDateTime(latest_0), toDateTime(latest_1)), NULL) step_1_conversion_time,
+                  if(isNotNull(latest_2)
+                     AND latest_2 <= latest_1 + INTERVAL 15 SECOND, dateDiff('second', toDateTime(latest_1), toDateTime(latest_2)), NULL) step_2_conversion_time
+           FROM
+             (SELECT aggregation_target,
+                     timestamp,
+                     step_0,
+                     latest_0,
+                     step_1,
+                     latest_1,
+                     step_2,
+                     min(latest_2) over (PARTITION by aggregation_target
+                                         ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) latest_2
+              FROM
+                (SELECT aggregation_target,
+                        timestamp,
+                        step_0,
+                        latest_0,
+                        step_1,
+                        latest_1,
+                        step_2,
+                        if(latest_2 < latest_1, NULL, latest_2) as latest_2
+                 FROM
+                   (SELECT aggregation_target,
+                           timestamp,
+                           step_0,
+                           latest_0,
+                           step_1,
+                           min(latest_1) over (PARTITION by aggregation_target
+                                               ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) latest_1,
+                                              step_2,
+                                              min(latest_2) over (PARTITION by aggregation_target
+                                                                  ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) latest_2
+                    FROM
+                      (SELECT e.timestamp as timestamp,
+                              pdi.person_id as aggregation_target,
+                              pdi.person_id as person_id ,
+                              if(event = 'step one', 1, 0) as step_0,
+                              if(step_0 = 1, timestamp, null) as latest_0,
+                              if(event = 'step two', 1, 0) as step_1,
+                              if(step_1 = 1, timestamp, null) as latest_1,
+                              if(event = 'step three', 1, 0) as step_2,
+                              if(step_2 = 1, timestamp, null) as latest_2
+                       FROM events e
+                       INNER JOIN
+                         (SELECT distinct_id,
+                                 argMax(person_id, version) as person_id
+                          FROM person_distinct_id2
+                          WHERE team_id = 2
+                          GROUP BY distinct_id
+                          HAVING argMax(is_deleted, version) = 0) AS pdi ON e.distinct_id = pdi.distinct_id
+                       WHERE team_id = 2
+                         AND event IN ['step one', 'step three', 'step two']
+                         AND toTimeZone(timestamp, 'UTC') >= toDateTime('2021-05-01 00:00:00', 'UTC')
+                         AND toTimeZone(timestamp, 'UTC') <= toDateTime('2021-05-14 23:59:59', 'UTC')
+                         AND (step_0 = 1
+                              OR step_1 = 1
+                              OR step_2 = 1) ))))
+           WHERE step_0 = 1 ))
+     GROUP BY aggregation_target,
+              steps
+     HAVING steps = max_steps)
+  '
+---
+# name: TestFOSSFunnel.test_funnel_conversion_window_seconds.1
+  '
+  
+  SELECT aggregation_target AS actor_id
+  FROM
+    (SELECT aggregation_target,
+            steps,
+            avg(step_1_conversion_time) step_1_average_conversion_time_inner,
+            avg(step_2_conversion_time) step_2_average_conversion_time_inner,
+            median(step_1_conversion_time) step_1_median_conversion_time_inner,
+            median(step_2_conversion_time) step_2_median_conversion_time_inner
+     FROM
+       (SELECT aggregation_target,
+               steps,
+               max(steps) over (PARTITION BY aggregation_target) as max_steps,
+                               step_1_conversion_time,
+                               step_2_conversion_time
+        FROM
+          (SELECT *,
+                  if(latest_0 <= latest_1
+                     AND latest_1 <= latest_0 + INTERVAL 15 SECOND
+                     AND latest_1 <= latest_2
+                     AND latest_2 <= latest_0 + INTERVAL 15 SECOND, 3, if(latest_0 <= latest_1
+                                                                          AND latest_1 <= latest_0 + INTERVAL 15 SECOND, 2, 1)) AS steps ,
+                  if(isNotNull(latest_1)
+                     AND latest_1 <= latest_0 + INTERVAL 15 SECOND, dateDiff('second', toDateTime(latest_0), toDateTime(latest_1)), NULL) step_1_conversion_time,
+                  if(isNotNull(latest_2)
+                     AND latest_2 <= latest_1 + INTERVAL 15 SECOND, dateDiff('second', toDateTime(latest_1), toDateTime(latest_2)), NULL) step_2_conversion_time
+           FROM
+             (SELECT aggregation_target,
+                     timestamp,
+                     step_0,
+                     latest_0,
+                     step_1,
+                     latest_1,
+                     step_2,
+                     min(latest_2) over (PARTITION by aggregation_target
+                                         ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) latest_2
+              FROM
+                (SELECT aggregation_target,
+                        timestamp,
+                        step_0,
+                        latest_0,
+                        step_1,
+                        latest_1,
+                        step_2,
+                        if(latest_2 < latest_1, NULL, latest_2) as latest_2
+                 FROM
+                   (SELECT aggregation_target,
+                           timestamp,
+                           step_0,
+                           latest_0,
+                           step_1,
+                           min(latest_1) over (PARTITION by aggregation_target
+                                               ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) latest_1,
+                                              step_2,
+                                              min(latest_2) over (PARTITION by aggregation_target
+                                                                  ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) latest_2
+                    FROM
+                      (SELECT e.timestamp as timestamp,
+                              pdi.person_id as aggregation_target,
+                              pdi.person_id as person_id ,
+                              if(event = 'step one', 1, 0) as step_0,
+                              if(step_0 = 1, timestamp, null) as latest_0,
+                              if(event = 'step two', 1, 0) as step_1,
+                              if(step_1 = 1, timestamp, null) as latest_1,
+                              if(event = 'step three', 1, 0) as step_2,
+                              if(step_2 = 1, timestamp, null) as latest_2
+                       FROM events e
+                       INNER JOIN
+                         (SELECT distinct_id,
+                                 argMax(person_id, version) as person_id
+                          FROM person_distinct_id2
+                          WHERE team_id = 2
+                          GROUP BY distinct_id
+                          HAVING argMax(is_deleted, version) = 0) AS pdi ON e.distinct_id = pdi.distinct_id
+                       WHERE team_id = 2
+                         AND event IN ['step one', 'step three', 'step two']
+                         AND toTimeZone(timestamp, 'UTC') >= toDateTime('2021-05-01 00:00:00', 'UTC')
+                         AND toTimeZone(timestamp, 'UTC') <= toDateTime('2021-05-14 23:59:59', 'UTC')
+                         AND (step_0 = 1
+                              OR step_1 = 1
+                              OR step_2 = 1) ))))
+           WHERE step_0 = 1 ))
+     GROUP BY aggregation_target,
+              steps
+     HAVING steps = max_steps)
+  WHERE steps IN [2, 3]
+  ORDER BY aggregation_target
+  LIMIT 100
+  OFFSET 0
+  '
+---
 # name: TestFOSSFunnel.test_funnel_with_cohorts_step_filter
   '
   
diff --git a/posthog/queries/funnels/test/test_funnel.py b/posthog/queries/funnels/test/test_funnel.py
index 79bdc915b8d23..03a95e795e09f 100644
--- a/posthog/queries/funnels/test/test_funnel.py
+++ b/posthog/queries/funnels/test/test_funnel.py
@@ -1581,6 +1581,50 @@ def test_funnel_conversion_window(self):
 
             self.assertCountEqual([str(id) for id in self._get_actor_ids_at_step(filter, 2)], ids_to_compare)
 
+        @snapshot_clickhouse_queries
+        def test_funnel_conversion_window_seconds(self):
+            ids_to_compare = []
+            for i in range(10):
+                person = _create_person(distinct_ids=[f"user_{i}"], team=self.team)
+                ids_to_compare.append(str(person.uuid))
+                _create_event(
+                    event="step one", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:00"
+                )
+                _create_event(
+                    event="step two", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:10"
+                )
+
+            for i in range(10, 25):
+                _create_person(distinct_ids=[f"user_{i}"], team=self.team)
+                _create_event(
+                    event="step one", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:00"
+                )
+                _create_event(
+                    event="step two", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-10 00:00:00"
+                )
+
+            data = {
+                "insight": INSIGHT_FUNNELS,
+                "date_from": "2021-05-01 00:00:00",
+                "date_to": "2021-05-14 00:00:00",
+                "funnel_window_interval": 15,
+                "funnel_window_interval_unit": "second",
+                "events": [
+                    {"id": "step one", "order": 0},
+                    {"id": "step two", "order": 1},
+                    {"id": "step three", "order": 2},
+                ],
+            }
+
+            filter = Filter(data={**data})
+            results = Funnel(filter, self.team).run()
+
+            self.assertEqual(results[0]["count"], 25)
+            self.assertEqual(results[1]["count"], 10)
+            self.assertEqual(results[2]["count"], 0)
+
+            self.assertCountEqual([str(id) for id in self._get_actor_ids_at_step(filter, 2)], ids_to_compare)
+
         def test_funnel_exclusions_invalid_params(self):
             filters = {
                 "events": [
diff --git a/posthog/schema.py b/posthog/schema.py
index fa322d6da3a28..5ccacf3652033 100644
--- a/posthog/schema.py
+++ b/posthog/schema.py
@@ -192,6 +192,7 @@ class FilterLogicalOperator(str, Enum):
 
 
 class FunnelConversionWindowTimeUnit(str, Enum):
+    second = "second"
     minute = "minute"
     hour = "hour"
     day = "day"


Code review comments:
diff_hunk: @@ -1581,6 +1581,50 @@ def test_funnel_conversion_window(self):
 
             self.assertCountEqual([str(id) for id in self._get_actor_ids_at_step(filter, 2)], ids_to_compare)
 
+        @snapshot_clickhouse_queries
+        def test_funnel_conversion_window_seconds(self):
+            ids_to_compare = []
+            for i in range(10):
+                person = _create_person(distinct_ids=[f"user_{i}"], team=self.team)
+                ids_to_compare.append(str(person.uuid))
+                _create_event(
+                    event="step one", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:00"
+                )
+                _create_event(
+                    event="step two", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:10"
+                )
+
+            for i in range(10, 25):
+                _create_person(distinct_ids=[f"user_{i}"], team=self.team)
+                _create_event(
+                    event="step one", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:00"
+                )
+                _create_event(
+                    event="step two", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-10 00:00:00"
path: posthog/queries/funnels/test/test_funnel.py
text: @user1:
```suggestion
                    event="step two", distinct_id=f"user_{i}", team=self.team, timestamp="2021-05-01 00:00:20"
```