Advance pre defined filters


(Ran Davidovitz) #1

We have a events table which holds few columns (created_at, name, kind, info1, info2, info3, ab_test_variant)

We have features in the system that are constructed from events (advanced)
For example feature 1 can be (name=“column_change” and kind=“date”) [This is simple]
another feature might be (name="open_column and kind=“initially”) OR (name=“close_column” and info1=“1”) [This is more advance]

I want to allow my team to choose a feature by name and that it will filter relevant events in the table, without them need to remember above rules
How can i make this happen?

Above is high level issue, the problem is deeper since a feature is consist of

  1. Discovery query - identified who saw the feature
  2. Retention query - Identified who used the feature (different events)

(Izzy) #2

The way that I’d first think to do this would be a parameter that feeds a sql_always_where clause in your model.

parameter: fancyfilter {
   type: string
   allowed_value: {
      label: "Filter on change + Date"
      value:"feature1"
   }
   allowed_value: {
      label: "Filter on closed or opened"
      value:"feature2"
   }
   etc
   etc
   

You could have it be a CASE statement, so like

sql_always_where:
CASE 
WHEN {% parameter fancyfilter %} = "feature1" THEN table.name = "column_change" AND table.kind = "date"
WHEN {% parameter fancyfilter %} = "feature2" THEN (table.name = "open_column" AND table.kind = "initially") OR (table.name = "close_column" AND table.info1="1")
add as many as you like
ELSE:
1=1

The 1=1 is necessary so that there is not an empty where clause in all queries where the parameter isn’t selected.

That way you can have a user-friendly dropdown (the parameter) select potentially HIGHLY complex sql filtering logic that’ll get applied in the WHERE clause of every query.

Would love to hear any other ideas people have on how to do this!