Filter Groups

Filter groups are configured in the segment builder as follows:

Property filter groups

Property filter groups allow filtering contacts on one-to-one property tables.

Property filter group query template

   SELECT
    "d[id]"."SIMON_ID",
    NULL AS "EVENT_GROUP_INDICES",
    NULL AS "EVENT_GROUPS"
  FROM "[database]"."[schema]"."[table]" AS "d[id]"
  LEFT JOIN "[database]"."[schema]"."[table]" AS "d[id]"
    ON "d[id]"."SIMON_ID" = "d[id]"."[join key]"
  WHERE
    -- Property filters here
    -- Segment filters here, of the form:
    --   "d[id]"."SIMON_ID" IN (SELECT "SIMON_ID" FROM "[segment view]")

Event filter groups

Event filter groups allow filtering contacts on aggregates of one-to-many event tables.

Event filter group query template

  SELECT
    "d[id]"."SIMON_ID",
    NULL AS "EVENT_GROUP_INDICES",
    NULL AS "EVENT_GROUPS"
  FROM "[database]"."[schema]"."[identity table]" AS "d[id]"
  INNER JOIN (
    SELECT
      "d[id]"."[joinkey]"
    FROM "[database]"."[schema]"."[event table]" AS "d[id]"
    WHERE
      -- Event filters here
      -- Event timestamp filter here
  ) AS "d[id]"
    ON "d[id]"."SIMON_ID" = "d[id]"."[joinkey]"
  GROUP BY
    "d[id]"."SIMON_ID"
  HAVING
    -- Aggregate filters here

Filter group composition

The Segment Builder allows you to compose multiple filter groups, via the AND or OR operators.

Filter group composition query template

WITH "g0" AS (
  -- Group 0 query here
), "g1" AS (
  -- Group 1 query here
)
SELECT
  "[database]"."[schema]"."[table]"."SIMON_ID",
  NULL AS "EVENT_GROUP_INDICES",
  NULL AS "EVENT_GROUPS"
FROM "[database]"."[schema]"."[table]"
LEFT JOIN "g0"
  ON "[database]"."[schema]"."[table]"."SIMON_ID" = "g0"."SIMON_ID"
LEFT JOIN "g1"
  ON "[database]"."[schema]"."[table]"."SIMON_ID" = "g1"."SIMON_ID"
WHERE
  -- Group operator (AND or OR) reflected here
  NOT "g0"."SIMON_ID" IS NULL AND NOT "g1"."SIMON_ID" IS NULL;