Repository: PostHog/posthog
Pull request: Allow Excluding Multiple Events + events in unordered funnels
~~Don't merge until SQL injection issue is resolved~~

~~To go after #5104 ~~

Fixes #5074 


## Changes

Event exclusion in unordered events
Support for multiple exclusions in the same step in ordered + unordered funnels

## Checklist

- [ ] All querysets/queries filter by Organization, by Team, and by User
- [ ] Django backend tests
- [ ] Jest frontend tests
- [ ] Cypress end-to-end tests
- [ ] Migrations are safe to run at scale (e.g. PostHog Cloud) – present proof if not obvious
- [ ] New/changed UI is decent on smartphones (viewport width around 360px)


Problem statement:
Excluding Events in Funnels
## Is your feature request related to a problem?

Say you have a funnel: Sign Up -> Discover Learning. And you're interested in a specific kind of user: one that, say, didn't invite teammates to the project.

It's hard to represent this using segmentation / breakdowns, since the users you're interested in are defined by what _events_ they didn't trigger.

So, we'd like to support excluding events (and in turn, disqualifying users) who did a certain event.

More example use cases:

1. (1) Search -> (2) Watch Movie. And in this case, I want to exclude extra search events happening inside the funnel. (So, focusing on users who watched a movie after just one search, instead of multiple searches).
2. Looking at behaviour funnel of users who did NOT click the call to action at any point.
3. ???

## Describe the solution you'd like

The first and third examples are qualitatively different from the second example (search->movie).

Let's tackle the former first: In this case, given a funnel date range, I want to exclude all users who did the event within the date range. This implies an exclusion filter over the date range, i.e. exclude all people who did this specific event between `date_from` and `date_to`.

This doesn't work with the latter case, because the funnel step, and the exclusion event are the same: "search". For this case, we introduce the concept of "exclusion within conversion time".

### What is Conversion Window?

Given a funnel `A->B`, and a date range from, say, `-7d` to `now`, the conversion window is the maximum allowable time within which a user should do events A and B. (our code controls this parameter using the `funnel_window_days` parameter).

---

Exclusion within conversion time means that if the user does events `A->A->B` within the conversion window, this user is disqualified from the funnel.

However, if this user did `A->B->A` within 20 minutes (which is within the funnel date range, and within conversion window) - this user is **not** disqualified, because they completed the funnel before the second A event.

This also implies that when dealing with exclusion within conversion time with duplicates, we choose the first event that we see as the start of the conversion window.

Do we count a user where we see two As, but no B? If the second A happened before the conversion window ends, then they're disqualified. Otherwise, no.

## Additional context

### What we're deciding not to do(?)

An alternative way to deal with (search->movie, excluding multiple search) case is to allow exclusion between steps. Such that, say you have a funnel, `X->A->B`, you can have exclusion events within every two steps of the funnel (X->A, A->B).

This introduces complications, both in the UI, and what the exclusions mean. For example: consider `X->A-B` and exclusion of A between A and B: Is the 'first' A the A corresponding to the first X, or the first A corresponding to all `X->A` half-funnels? 

#### Is there a way to work around this using what we have?

Yes! Given an exclusion funnel result, we could create a static cohort out of all the people entering the funnel, and now you can create all sorts of funnels out of this cohort. 

## Alternative solutions

It's worth thinking about other ways of solving this, specially the "excluding event which is also a part of the funnel step" case. I haven't done this yet, opting to preserve the context so far for now.

#### *Thank you* for your feature request – we love each and every one!

Hints from issue discussion:
Note that this is not a funnel-specific abstraction/search thing but rather a general analytics filtering capability, often associated with a time window.

He@p has this functionality:

![image](https://user-images.githubusercontent.com/148820/125284124-c0065b00-e321-11eb-9533-45f8c22ca737.png)

Related issue: https://github.com/PostHog/posthog/issues/2594
Hmm, now getting into problem solving mode, and it sounds like maybe separating out the two use cases into two different abstractions is better: make no-duplicates a new type of ordering, such that event exclusion definition becomes cleaner: just means excluding that event from the funnel range.

Thinking from a users point of view, this really helps resolve confusion between: Select "search" while excluding "search" (huh?) vs "select search, disqualifying duplicates" (aha!) ? 
Also, another challenge to the problem: When would you want to exclude events & disqualify persons in the funnel date range, but not globally? 

Or more specifically, when would you want to disqualify persons outside the funnel conversion time, but inside the funnel date range?

That is, do we need the date range exclusion at all, given we have a "within conversion time" exclusion, and a global exclusion?
Hey @neilkakkar great discussion this AM – summarizing a few points here:
- No separate concept of "exclusions within the conversion window". Setting a conversion window is a top level requirements to create a funnel. Every funnel must have a conversion window set, and event exclusions have no impact on the conversion window. 

- Event ordering semantics (i.e. specifying a strict funnel) are a separate concept from exclusions and should not impact the options we provide in terms of exclusions.

- We want support for two kinds of exclusions:
  - Across all steps
  - Between steps (the user must specify the steps the event could not occur between)
  - General way this should work on a high level:
     - Make a funnel: define a chart date range, the funnel events, and a conversion window
     - For each day, (x-axis val on the chart), pull users that perform the first event of the funnel
       - For each user, look conversion window ahead from funnel entry time to see if they performed the necessary events needed to count as a conversion. 
         - If all steps were not complete or exclusion cases are detected, mark as drop-off.
 
### Example
Let's say we have an example funnel:
1. View ad
2. Play song
3. Download song
4. Purchase tickets


If we excluded the event "View email" (not present in the funnel at all):
- Across all steps: For someone to count as a conversion, they cannot perform "View email" between the time they perform the first event and before they perform the 4th event.
- Between steps: Let's say we chose steps 2 and 3. For a conversion to count, this person could not have performed a "View email" event between those steps. Performing the event before or after has no bearing on the result.

If we excluded the "View ad" event:
- Across all steps: For someone to count as a conversion, they cannot perform a second "View ad" event anytime between the sequence of above 4 events. We do not exclude this event completely because this funnel includes it.
- Between steps:  Let's say we choose to exclude between Steps 3 and 4. Conversions here could perform "View ad" as many times as they want, as long as it hasn't occurred between steps 3 and 4. 
Cheers, thanks! It came out during the call that I don't really have a good reason for exclusion between specific steps, except that I can't imagine how it would be useful. (That's a failure of imagination, not of utility). 

I'm still not 100% convinced it will be useful, but happy to defer to people who're actually going to use this (Kunal)+ user studies done by competitors. (We had a look at how competitors indeed support this).

So, I'm moving forward with implementing this.

---

Further, the date range exclusion doesn't make sense with this, so we're discarding that instead. And global exclusions happen using cohorts (users who never did event X), so we're good there as well.

Thanks!
Further, to refine exclusions over orderings: this doesn't make sense for strict funnels, and between specific steps doesn't make sense for unordered funnels.

Between any 2 steps makes sense for ordered funnels.
Some great feedback from @macobo : Looks like I got too hung up on the specifics / edge cases - I jumped too quickly, without thinking from first principles.

Concretely, I started with thinking about why users would / wouldn't want to exclude events in a part of the funnel, without asking the deeper question: What question are users trying to answer when they exclude events in a part of the funnel?

And then, I could follow from there to whether funnel exclusion steps is the way to go, or a more general approach, like the one he@p does, as mentioned by Karl above^.

This is probably also why I wrote: `I'm still not 100% convinced it will be useful, but happy to defer to people who're actually going to use this.`

--- 

While the final result may not change, it's important to me that I take the right path to the decision. 

## Challenging the decision

#1: Comparing different flows, one where users didn't do X in their journey, vs where users did X in their journey, is more general that just funnels. (this is what exclusions help us do)

I agree with this. However our focus right now is funnels, so I'd restrict scope to funnels. Plus, trends/retention/whatever would have to implement their own logic for filtering like so, since the queries are different. Perhaps it's worth abstracting common bits out into `ClickhouseEventQuery`, but that only happens _after_ we've seen the pattern.

#2 So, what question exactly are users answering with excluding events in a part of the funnel? More importantly, is that the right question for users to be asking, and the right way for us to be presenting that information?

The first one is above: Comparing across flows - Users who do an event X in between their conversion, vs, all users who convert. You might have a multivariate A/B test, where you want more granular level analysis on the A side: Users on a certain flow may or may not trigger event X, and you want to see their conversion to determine whether the A side is good or not.

Err, but, can't you do this using two funnels as well: `event 1 -> event 2 -> X -> event 3` and `event 1 -> event 2 -> event 3.` ? This gives you the same answer for funnels I think, where instead of excluding those users who did X, you compare across two funnels. This is essentially the reverse comparison. However, **visualising this is harder**, since you need two funnels for the same information you would've got with exclusions, which is a good enough reason to implement exclusions, imo.

I think this use case is enough to warrant having exclusions, but for the sake of completeness, what other questions are users trying to answer? I don't know. I need your help, @kpthatsme , @paolodamico to help answer this! :)

Also, welcoming other challenges. This was a bit messy, since it's happening _after_ I've even implemented exclusions, sorry about all this questioning!

Hey @neilkakkar, let me give this some further thought and get back to you! I'm sure there's other questions we're missing. Also tagging @marcushyett-ph here for input who probably has some useful insights too.
@neilkakkar thanks for this great discussion - it's a little complex and hard to follow this entire thread so please correct me if I've misunderstood anything crucial.

I agree with @macobo that this is probably a standard filtering capability we'll want across the board at some point, but I'll focus my feedback specifically on the funnels use case.

I want to probe at whether there is a higher level problem people are trying to solve and whether or not we can solve that problem directly rather than something lower level.

I'll take this example funnel:

1. View ad
2. Play song
3. Download song
4. Purchase tickets

Lets say there are a 100+ other events a user can trigger in our product, we're likely to have 3 jobs we want to do using this information (linked to exclusions):
1) Understand which other events might have "caused" a user to be successful or unsuccessful (since there's 100+ I could do this by creating 100 funnels - but if we could automate this - that'd be ideal): https://github.com/PostHog/product-internal/issues/92# <--- This issue talks about this use case in more detail
2) I already have a "hunch" that a specific event might be "causing" a user to be unsuccessful, so I want to backtest this hypothesis by excluding it from the funnel and seeing if conversion is improved
3) Understand short-cuts in the funnel (and potentially how we can encourage them), e.g. understand who managed to go straight from view ad to purchase tickets (and what other events they did along the way)

I think it would be great to see if we have any user feedback around this, I've heard some around 1 and 2, but I don't think I've heard a customers talk much about 3 so far? @paolodamico 
Thanks @marcushyett-ph 

For usecases 1-2, the natural way for me would be think about it is breakdowns. Being able to break down (or filter) by a virtual property like "User has done $action in past X days" or "User has done X at any time" would help answer similar questions I've had in the past, minus the automation part.

Commit message: rm extra imports

Patch to review:
diff --git a/ee/clickhouse/queries/funnels/base.py b/ee/clickhouse/queries/funnels/base.py
index e85bd86c6e2a4..5dd80fb0d0000 100644
--- a/ee/clickhouse/queries/funnels/base.py
+++ b/ee/clickhouse/queries/funnels/base.py
@@ -128,9 +128,9 @@ def _get_partition_cols(self, level_index: int, max_steps: int):
             cols.append(f"step_{i}")
             if i < level_index:
                 cols.append(f"latest_{i}")
-                for exclusion in self._filter.exclusions:
+                for exclusion_id, exclusion in enumerate(self._filter.exclusions):
                     if exclusion.funnel_from_step + 1 == i:
-                        cols.append(f"exclusion_latest_{exclusion.funnel_from_step}")
+                        cols.append(f"exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}")
             else:
                 duplicate_event = 0
                 if i > 0 and self._filter.entities[i].equals(self._filter.entities[i - 1]):
@@ -138,11 +138,11 @@ def _get_partition_cols(self, level_index: int, max_steps: int):
                 cols.append(
                     f"min(latest_{i}) over (PARTITION by person_id {self._get_breakdown_prop()} ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND {duplicate_event} PRECEDING) latest_{i}"
                 )
-                for exclusion in self._filter.exclusions:
+                for exclusion_id, exclusion in enumerate(self._filter.exclusions):
                     # exclusion starting at step i follows semantics of step i+1 in the query (since we're looking for exclusions after step i)
                     if exclusion.funnel_from_step + 1 == i:
                         cols.append(
-                            f"min(exclusion_latest_{exclusion.funnel_from_step}) over (PARTITION by person_id {self._get_breakdown_prop()} ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) exclusion_latest_{exclusion.funnel_from_step}"
+                            f"min(exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}) over (PARTITION by person_id {self._get_breakdown_prop()} ORDER BY timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}"
                         )
         return ", ".join(cols)
 
@@ -151,10 +151,10 @@ def _get_exclusion_condition(self):
             return ""
 
         conditions = []
-        for exclusion in self._filter.exclusions:
+        for exclusion_id, exclusion in enumerate(self._filter.exclusions):
             from_time = f"latest_{exclusion.funnel_from_step}"
             to_time = f"latest_{exclusion.funnel_to_step}"
-            exclusion_time = f"exclusion_latest_{exclusion.funnel_from_step}"
+            exclusion_time = f"exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}"
             condition = (
                 f"if( {exclusion_time} > {from_time} AND {exclusion_time} < "
                 f"if(isNull({to_time}), {from_time} + INTERVAL {self._filter.funnel_window_days} DAY, {to_time}), 1, 0)"
@@ -199,9 +199,9 @@ def _get_inner_event_query(
             step_cols = self._get_step_col(entity, index, entity_name)
             all_step_cols.extend(step_cols)
 
-        for entity in self._filter.exclusions:
-            step_cols = self._get_step_col(entity, entity.funnel_from_step, entity_name, "exclusion_")
-            # every exclusion entity has the form: exclusion_step_i & timestamp exclusion_latest_i
+        for exclusion_id, entity in enumerate(self._filter.exclusions):
+            step_cols = self._get_step_col(entity, entity.funnel_from_step, entity_name, f"exclusion_{exclusion_id}_")
+            # every exclusion entity has the form: exclusion_<id>_step_i & timestamp exclusion_<id>_latest_i
             # where i is the starting step for exclusion on that entity
             all_step_cols.extend(step_cols)
 
@@ -235,8 +235,8 @@ def _get_steps_conditions(self, length: int) -> str:
         for index in range(length):
             step_conditions.append(f"step_{index} = 1")
 
-        for entity in self._filter.exclusions:
-            step_conditions.append(f"exclusion_step_{entity.funnel_from_step} = 1")
+        for exclusion_id, entity in enumerate(self._filter.exclusions):
+            step_conditions.append(f"exclusion_{exclusion_id}_step_{entity.funnel_from_step} = 1")
 
         return " OR ".join(step_conditions)
 
diff --git a/ee/clickhouse/queries/funnels/funnel.py b/ee/clickhouse/queries/funnels/funnel.py
index 1188adab7987c..5243c1724b8f3 100644
--- a/ee/clickhouse/queries/funnels/funnel.py
+++ b/ee/clickhouse/queries/funnels/funnel.py
@@ -132,17 +132,18 @@ def get_comparison_cols(self, level_index: int, max_steps: int):
             cols.append(f"step_{i}")
             if i < level_index:
                 cols.append(f"latest_{i}")
-                for exclusion in self._filter.exclusions:
+                for exclusion_id, exclusion in enumerate(self._filter.exclusions):
                     if exclusion.funnel_from_step + 1 == i:
-                        cols.append(f"exclusion_latest_{exclusion.funnel_from_step}")
+                        cols.append(f"exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}")
             else:
                 comparison = self._get_comparison_at_step(i, level_index)
                 cols.append(f"if({comparison}, NULL, latest_{i}) as latest_{i}")
 
-                for exclusion in self._filter.exclusions:
+                for exclusion_id, exclusion in enumerate(self._filter.exclusions):
                     if exclusion.funnel_from_step + 1 == i:
+                        exclusion_identifier = f"exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}"
                         cols.append(
-                            f"if(exclusion_latest_{exclusion.funnel_from_step} < latest_{exclusion.funnel_from_step}, NULL, exclusion_latest_{exclusion.funnel_from_step}) as exclusion_latest_{exclusion.funnel_from_step}"
+                            f"if({exclusion_identifier} < latest_{exclusion.funnel_from_step}, NULL, {exclusion_identifier}) as {exclusion_identifier}"
                         )
 
         return ", ".join(cols)
diff --git a/ee/clickhouse/queries/funnels/funnel_unordered.py b/ee/clickhouse/queries/funnels/funnel_unordered.py
index dddc7910ba0bb..53da736fcf85a 100644
--- a/ee/clickhouse/queries/funnels/funnel_unordered.py
+++ b/ee/clickhouse/queries/funnels/funnel_unordered.py
@@ -1,4 +1,6 @@
-from typing import List, Union
+from typing import List
+
+from rest_framework.exceptions import ValidationError
 
 from ee.clickhouse.queries.funnels.base import ClickhouseFunnelBase
 
@@ -21,12 +23,22 @@ class ClickhouseFunnelUnordered(ClickhouseFunnelBase):
 
     The result format is the same as the basic funnel, i.e. [step, count].
     Here, `step_i` (0 indexed) signifies the number of people that did at least `i+1` steps.
+
+    ## Exclusion Semantics
+    For unordered funnels, exclusion is a bit weird. It means, given all ordering of the steps,
+    how far can you go without seeing an exclusion event.
+    If you see an exclusion event => you're discarded.
+    See test_advanced_funnel_multiple_exclusions_between_steps for details.
     """
 
     def get_query(self):
 
         max_steps = len(self._filter.entities)
 
+        for exclusion in self._filter.exclusions:
+            if exclusion.funnel_from_step != 0 or exclusion.funnel_to_step != max_steps - 1:
+                raise ValidationError("Partial Exclusions not allowed in unordered funnels")
+
         breakdown_clause = self._get_breakdown_prop()
 
         return f"""
@@ -59,6 +71,7 @@ def get_step_counts_without_aggregation_query(self):
         partition_select = self._get_partition_cols(1, max_steps)
         sorting_condition = self.get_sorting_condition(max_steps)
         breakdown_clause = self._get_breakdown_prop()
+        exclusion_clause = self._get_exclusion_condition()
 
         for i in range(max_steps):
             inner_query = f"""
@@ -71,9 +84,11 @@ def get_step_counts_without_aggregation_query(self):
             """
 
             formatted_query = f"""
-                SELECT *, {sorting_condition} AS steps {self._get_step_times(max_steps)} FROM (
+                SELECT *, {sorting_condition} AS steps {exclusion_clause} {self._get_step_times(max_steps)} FROM (
                         {inner_query}
-                    ) WHERE step_0 = 1"""
+                    ) WHERE step_0 = 1
+                    {'AND exclusion = 0' if exclusion_clause else ''}
+                    """
 
             #  rotate entities by 1 to get new first event
             entities_to_use.append(entities_to_use.pop(0))
@@ -109,13 +124,44 @@ def _get_step_times(self, max_steps: int):
 
     def get_sorting_condition(self, max_steps: int):
 
+        conditions = []
+
+        event_times_elements = []
+        for i in range(max_steps):
+            event_times_elements.append(f"latest_{i}")
+
+        conditions.append(f"arraySort([{','.join(event_times_elements)}]) as event_times")
+        # replacement of latest_i for whatever query part requires it, just like conversion_times
+
         basic_conditions: List[str] = []
         for i in range(1, max_steps):
             basic_conditions.append(
                 f"if(latest_0 < latest_{i} AND latest_{i} <= latest_0 + INTERVAL {self._filter.funnel_window_days} DAY, 1, 0)"
             )
 
+        conditions.append(f"arraySum([{','.join(basic_conditions)}, 1])")
+
         if basic_conditions:
-            return f"arraySum([{','.join(basic_conditions)}, 1])"
+            return ",".join(conditions)
         else:
             return "1"
+
+    def _get_exclusion_condition(self):
+        if not self._filter.exclusions:
+            return ""
+
+        conditions = []
+        for exclusion_id, exclusion in enumerate(self._filter.exclusions):
+            from_time = f"latest_{exclusion.funnel_from_step}"
+            to_time = f"event_times[{exclusion.funnel_to_step + 1}]"
+            exclusion_time = f"exclusion_{exclusion_id}_latest_{exclusion.funnel_from_step}"
+            condition = (
+                f"if( {exclusion_time} > {from_time} AND {exclusion_time} < "
+                f"if(isNull({to_time}), {from_time} + INTERVAL {self._filter.funnel_window_days} DAY, {to_time}), 1, 0)"
+            )
+            conditions.append(condition)
+
+        if conditions:
+            return f", arraySum([{','.join(conditions)}]) as exclusion"
+        else:
+            return ""
diff --git a/ee/clickhouse/queries/funnels/test/test_funnel.py b/ee/clickhouse/queries/funnels/test/test_funnel.py
index fe6e449a4f6f6..1569142a0a7ef 100644
--- a/ee/clickhouse/queries/funnels/test/test_funnel.py
+++ b/ee/clickhouse/queries/funnels/test/test_funnel.py
@@ -137,7 +137,7 @@ def test_funnel_exclusions_full_window(self):
             "funnel_window_days": 14,
             "date_from": "2021-05-01 00:00:00",
             "date_to": "2021-05-14 00:00:00",
-            "exclusions": [{"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},],
+            "exclusions": [{"id": "x 1 2", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},],
         }
         filter = Filter(data=filters)
         funnel = ClickhouseFunnel(filter, self.team)
@@ -150,7 +150,7 @@ def test_funnel_exclusions_full_window(self):
         # event 2
         person2 = _create_person(distinct_ids=["person2"], team_id=self.team.pk)
         _create_event(team=self.team, event="user signed up", distinct_id="person2", timestamp="2021-05-01 03:00:00")
-        _create_event(team=self.team, event="x", distinct_id="person2", timestamp="2021-05-01 03:30:00")
+        _create_event(team=self.team, event="x 1 2", distinct_id="person2", timestamp="2021-05-01 03:30:00")
         _create_event(team=self.team, event="paid", distinct_id="person2", timestamp="2021-05-01 04:00:00")
 
         # event 3
@@ -1120,3 +1120,141 @@ def test_funnel_with_denormalised_properties(self):
 
             self.assertEqual(result[0]["name"], "user signed up")
             self.assertEqual(result[0]["count"], 1)
+
+    def test_advanced_funnel_multiple_exclusions_between_steps(self):
+        filters = {
+            "events": [
+                {"id": "user signed up", "type": "events", "order": 0},
+                {"id": "$pageview", "type": "events", "order": 1},
+                {"id": "insight viewed", "type": "events", "order": 2},
+                {"id": "invite teammate", "type": "events", "order": 3},
+                {"id": "pageview2", "type": "events", "order": 4},
+            ],
+            "date_from": "2021-05-01 00:00:00",
+            "date_to": "2021-05-14 00:00:00",
+            "insight": INSIGHT_FUNNELS,
+            "exclusions": [
+                {"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},
+                {"id": "y", "type": "events", "funnel_from_step": 2, "funnel_to_step": 3},
+            ],
+        }
+
+        person1 = _create_person(distinct_ids=["person1"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person1", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person1", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person1", timestamp="2021-05-01 03:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person1", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person1", timestamp="2021-05-01 04:30:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person1", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person1", timestamp="2021-05-01 06:00:00")
+
+        person2 = _create_person(distinct_ids=["person2"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person2", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person2", timestamp="2021-05-01 01:30:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person2", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person2", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person2", timestamp="2021-05-01 04:30:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person2", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person2", timestamp="2021-05-01 05:30:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person2", timestamp="2021-05-01 06:00:00")
+
+        person3 = _create_person(distinct_ids=["person3"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person3", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person3", timestamp="2021-05-01 01:30:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person3", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person3", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person3", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person3", timestamp="2021-05-01 05:30:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person3", timestamp="2021-05-01 06:00:00")
+
+        person4 = _create_person(distinct_ids=["person4"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person4", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person4", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person4", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person4", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person4", timestamp="2021-05-01 06:00:00")
+
+        filter = Filter(data=filters)
+        funnel = ClickhouseFunnel(filter, self.team)
+
+        result = funnel.run()
+
+        self.assertEqual(result[0]["name"], "user signed up")
+        self.assertEqual(result[0]["count"], 1)
+        self.assertEqual(len(result[0]["people"]), 1)
+
+        self.assertEqual(result[4]["count"], 1)
+        self.assertEqual(len(result[4]["people"]), 1)
+
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 1), [person4.uuid],
+        )
+
+        filter = filter.with_data(
+            {
+                "exclusions": [
+                    {"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},
+                    {"id": "y", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},
+                ],
+            }
+        )
+        funnel = ClickhouseFunnel(filter, self.team)
+
+        result = funnel.run()
+
+        self.assertEqual(result[0]["name"], "user signed up")
+        self.assertEqual(result[0]["count"], 1)
+        self.assertEqual(len(result[0]["people"]), 1)
+
+        self.assertEqual(result[4]["count"], 1)
+        self.assertEqual(len(result[4]["people"]), 1)
+
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 1), [person4.uuid],
+        )
+
+        filter = filter.with_data(
+            {
+                "exclusions": [
+                    {"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},
+                    {"id": "y", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},
+                ],
+            }
+        )
+        funnel = ClickhouseFunnel(filter, self.team)
+
+        result = funnel.run()
+
+        self.assertEqual(result[0]["name"], "user signed up")
+        self.assertEqual(result[0]["count"], 1)
+        self.assertEqual(len(result[0]["people"]), 1)
+
+        self.assertEqual(result[4]["count"], 1)
+        self.assertEqual(len(result[4]["people"]), 1)
+
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 1), [person4.uuid],
+        )
+
+        filter = filter.with_data(
+            {
+                "exclusions": [
+                    {"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 4},
+                    {"id": "y", "type": "events", "funnel_from_step": 0, "funnel_to_step": 4},
+                ],
+            }
+        )
+        funnel = ClickhouseFunnel(filter, self.team)
+
+        result = funnel.run()
+
+        self.assertEqual(result[0]["name"], "user signed up")
+        self.assertEqual(result[0]["count"], 1)
+        self.assertEqual(len(result[0]["people"]), 1)
+
+        self.assertEqual(result[4]["count"], 1)
+        self.assertEqual(len(result[4]["people"]), 1)
+
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 1), [person4.uuid],
+        )
diff --git a/ee/clickhouse/queries/funnels/test/test_funnel_unordered.py b/ee/clickhouse/queries/funnels/test/test_funnel_unordered.py
index 43f83b351f47e..b47e32879cb02 100644
--- a/ee/clickhouse/queries/funnels/test/test_funnel_unordered.py
+++ b/ee/clickhouse/queries/funnels/test/test_funnel_unordered.py
@@ -1,5 +1,7 @@
 from uuid import uuid4
 
+from rest_framework.exceptions import ValidationError
+
 from ee.clickhouse.models.event import create_event
 from ee.clickhouse.queries.funnels.funnel_unordered import ClickhouseFunnelUnordered
 from ee.clickhouse.queries.funnels.funnel_unordered_persons import ClickhouseFunnelUnorderedPersons
@@ -491,3 +493,163 @@ def test_single_event_unordered_funnel(self):
 
         self.assertEqual(result[0]["name"], "user signed up")
         self.assertEqual(result[0]["count"], 2)
+
+    def test_funnel_exclusions_invalid_params(self):
+        filters = {
+            "events": [
+                {"id": "user signed up", "type": "events", "order": 0},
+                {"id": "paid", "type": "events", "order": 1},
+                {"id": "blah", "type": "events", "order": 2},
+            ],
+            "insight": INSIGHT_FUNNELS,
+            "funnel_window_days": 14,
+            "exclusions": [{"id": "x", "type": "events", "funnel_from_step": 1, "funnel_to_step": 1},],
+        }
+        filter = Filter(data=filters)
+        funnel = ClickhouseFunnelUnordered(filter, self.team)
+        self.assertRaises(ValidationError, funnel.run)
+
+        # partial windows not allowed for unordered
+        filter = filter.with_data(
+            {"exclusions": [{"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1}]}
+        )
+        funnel = ClickhouseFunnelUnordered(filter, self.team)
+        self.assertRaises(ValidationError, funnel.run)
+
+    def test_funnel_exclusions_full_window(self):
+        filters = {
+            "events": [
+                {"id": "user signed up", "type": "events", "order": 0},
+                {"id": "paid", "type": "events", "order": 1},
+            ],
+            "insight": INSIGHT_FUNNELS,
+            "funnel_window_days": 14,
+            "date_from": "2021-05-01 00:00:00",
+            "date_to": "2021-05-14 00:00:00",
+            "exclusions": [{"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},],
+        }
+        filter = Filter(data=filters)
+        funnel = ClickhouseFunnelUnordered(filter, self.team)
+
+        # event 1
+        person1 = _create_person(distinct_ids=["person1"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person1", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="paid", distinct_id="person1", timestamp="2021-05-01 02:00:00")
+
+        # event 2
+        person2 = _create_person(distinct_ids=["person2"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person2", timestamp="2021-05-01 03:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person2", timestamp="2021-05-01 03:30:00")
+        _create_event(team=self.team, event="paid", distinct_id="person2", timestamp="2021-05-01 04:00:00")
+
+        # event 3
+        person3 = _create_person(distinct_ids=["person3"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person3", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="paid", distinct_id="person3", timestamp="2021-05-01 06:00:00")
+
+        result = funnel.run()
+
+        self.assertEqual(len(result), 2)
+        self.assertEqual(result[0]["name"], "user signed up")
+        self.assertEqual(result[0]["count"], 3)
+        self.assertEqual(result[1]["name"], "paid")
+        self.assertEqual(result[1]["count"], 2)
+
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 1), [person1.uuid, person2.uuid, person3.uuid],
+        )
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 2), [person1.uuid, person3.uuid],
+        )
+
+    def test_advanced_funnel_multiple_exclusions_between_steps(self):
+        filters = {
+            "events": [
+                {"id": "user signed up", "type": "events", "order": 0},
+                {"id": "$pageview", "type": "events", "order": 1},
+                {"id": "insight viewed", "type": "events", "order": 2},
+                {"id": "invite teammate", "type": "events", "order": 3},
+                {"id": "pageview2", "type": "events", "order": 4},
+            ],
+            "date_from": "2021-05-01 00:00:00",
+            "date_to": "2021-05-14 00:00:00",
+            "insight": INSIGHT_FUNNELS,
+            "exclusions": [
+                {"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 4},
+                {"id": "y", "type": "events", "funnel_from_step": 0, "funnel_to_step": 4},
+            ],
+        }
+
+        person1 = _create_person(distinct_ids=["person1"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person1", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person1", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person1", timestamp="2021-05-01 03:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person1", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person1", timestamp="2021-05-01 04:30:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person1", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person1", timestamp="2021-05-01 06:00:00")
+
+        person2 = _create_person(distinct_ids=["person2"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person2", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person2", timestamp="2021-05-01 01:30:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person2", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person2", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person2", timestamp="2021-05-01 04:30:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person2", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person2", timestamp="2021-05-01 05:30:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person2", timestamp="2021-05-01 06:00:00")
+
+        person3 = _create_person(distinct_ids=["person3"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person3", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person3", timestamp="2021-05-01 01:30:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person3", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person3", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person3", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person3", timestamp="2021-05-01 05:30:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person3", timestamp="2021-05-01 06:00:00")
+
+        person4 = _create_person(distinct_ids=["person4"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person4", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person4", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person4", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person4", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person4", timestamp="2021-05-01 06:00:00")
+
+        person5 = _create_person(distinct_ids=["person5"], team_id=self.team.pk)
+        _create_event(team=self.team, event="user signed up", distinct_id="person5", timestamp="2021-05-01 01:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person5", timestamp="2021-05-01 01:30:00")
+        _create_event(team=self.team, event="$pageview", distinct_id="person5", timestamp="2021-05-01 02:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person5", timestamp="2021-05-01 02:30:00")
+        _create_event(team=self.team, event="insight viewed", distinct_id="person5", timestamp="2021-05-01 04:00:00")
+        _create_event(team=self.team, event="y", distinct_id="person5", timestamp="2021-05-01 04:30:00")
+        _create_event(team=self.team, event="invite teammate", distinct_id="person5", timestamp="2021-05-01 05:00:00")
+        _create_event(team=self.team, event="x", distinct_id="person5", timestamp="2021-05-01 05:30:00")
+        _create_event(team=self.team, event="pageview2", distinct_id="person5", timestamp="2021-05-01 06:00:00")
+
+        filter = Filter(data=filters)
+        funnel = ClickhouseFunnelUnordered(filter, self.team)
+
+        result = funnel.run()
+
+        self.assertEqual(result[0]["name"], "user signed up")
+        self.assertEqual(result[0]["count"], 5)
+        self.assertEqual(result[1]["count"], 2)
+        self.assertEqual(result[2]["count"], 1)
+        self.assertEqual(result[3]["count"], 1)
+        self.assertEqual(result[4]["count"], 1)
+
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 1), [person1.uuid, person2.uuid, person3.uuid, person4.uuid, person5.uuid],
+        )
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 2), [person1.uuid, person4.uuid],
+        )
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 3), [person4.uuid],
+        )
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 4), [person4.uuid],
+        )
+        self.assertCountEqual(
+            self._get_people_at_step(filter, 5), [person4.uuid],
+        )


Code review comments:
diff_hunk: @@ -137,7 +137,7 @@ def test_funnel_exclusions_full_window(self):
             "funnel_window_days": 14,
             "date_from": "2021-05-01 00:00:00",
             "date_to": "2021-05-14 00:00:00",
-            "exclusions": [{"id": "x", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},],
+            "exclusions": [{"id": "x 1 2", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},],
path: ee/clickhouse/queries/funnels/test/test_funnel.py
text: @user1:
NIT: this id is confusing because it looks like extra notation 😅

@author:
I was just going for a space in the name, will change

@author:
```suggestion
            "exclusions": [{"id": "x 1 name with numbers 2", "type": "events", "funnel_from_step": 0, "funnel_to_step": 1},],
```

diff_hunk: @@ -150,7 +150,7 @@ def test_funnel_exclusions_full_window(self):
         # event 2
         person2 = _create_person(distinct_ids=["person2"], team_id=self.team.pk)
         _create_event(team=self.team, event="user signed up", distinct_id="person2", timestamp="2021-05-01 03:00:00")
-        _create_event(team=self.team, event="x", distinct_id="person2", timestamp="2021-05-01 03:30:00")
+        _create_event(team=self.team, event="x 1 2", distinct_id="person2", timestamp="2021-05-01 03:30:00")
path: ee/clickhouse/queries/funnels/test/test_funnel.py
text: @author:
```suggestion
        _create_event(team=self.team, event="x 1 name with numbers 2", distinct_id="person2", timestamp="2021-05-01 03:30:00")
```