Marketing Campaigns V2 Segmentation Query Reference

1
SELECT contact_id, updated_at
2
FROM <table_reference> [ AS <table_alias> ]
3
[ [ INNER ] JOIN <table_reference> ON contact_id ]
4
[ WHERE <predicate> ]

1
{
2
  "payload": {
3
      "unique_args": {
4
        "mc_auto_id": "02bc41ca-07f9-11eb-9c1f-422f9e0f60d1",
5
        "mc_auto_step_id": "0478e207-07f9-11eb-9c1f-422f9e0f60d1",
6
        "singlesend_id": "b1ff0108-0994-11eb-915a-7ab0feb0ce95",
7
      },
8
   },
9
}

1
SELECT contact_id, updated_at
2
FROM contact_data
3
WHERE created_at < CURRENT_TIMESTAMP

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE created_at > timestampadd(day, 1, '2020-10-15T18:00:12Z')

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE array_contains(email_domains, ['gmail.com', 'yahoo.com'])

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE contains_word('bob', first_name)

1
SELECT contact_id, updated_at 
2
FROM contact_data

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE first_name = 'Dave'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE state_province_region = 'CO'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE email like '%gmail.com%'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE my_text_custom_field = 'abc'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE email like '%gmail.com' and my_custom_field = 'abc'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE my_number_custom_field = 12

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE my_date_custom_field = '2021-01-01T12:46:24Z'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE array_contains(alternate_emails, ['alternate@gmail.com'])

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE array_contains(alternate_emails, ['alternate@gmail.com','alternate2@gmail.com'])

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE array_contains(list_ids, ['02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'])

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE array_contains(list_ids, ['02bc41ca-07f9-11eb-9c1f-422f9e0f60d1', '02bc41ca-07f9-11eb-9c1f-422f9e0f62e4', '042a8e48-6e31-11eb-a8bc-7656c249c550'])

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE array_contains(email_domains, ['gmail.com'])

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE created_at > '2021-01-01T12:00:00Z'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE created_at = '2021-01-01T12:00:12Z'

1
SELECT contact_id, updated_at
2
FROM contact_data
3
WHERE external_id like '123%'

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE first_name != ‘Dave' OR first_name is null

1
SELECT contact_id, updated_at 
2
FROM contact_data 
3
WHERE my_custom_field like ‘%abc%' OR my_custom_field is null

1
SELECT c.contact_id, c.updated_at 
2
FROM contact_data c
3
LEFT JOIN event_data e 
4
ON c.contact_id = e.contact_id
5
WHERE e.event_source = 'mail'
6
AND e.event_type = 'delivered' 
7
AND e.DATA:payload.unique_args.singlesend_id = '042a8e48-6e31-11eb-a8bc-7656c249c550'
8
OR c.state_province_region = 'CO'

1
SELECT c.contact_id, c.updated_at 
2
FROM contact_data c
3
JOIN event_data e 
4
ON c.contact_id = e.contact_id
5
WHERE e.event_source = 'mail'
6
AND e.event_type = 'delivered'
7
AND e.DATA:payload.unique_args.singlesend_id = '042a8e48-6e31-11eb-a8bc-7656c249c550'
8
UNION 
9
SELECT contact_id, updated_at 
10
FROM contact_data 
11
WHERE c.state_province_region = 'CO'

1
SELECT c.contact_id, c.updated_at 
2
FROM contact_data c 
3
JOIN event_data e ON c.contact_id = e.contact_id

1
SELECT e2.contact_id, c2.updated_at
2
FROM event_data e2
3
INNER JOIN (
4
 SELECT c1.contact_id, c1.updated_at
5
 FROM contact_data AS c1
6
 INNER JOIN event_data e1 ON c1.contact_id = e1.contact_id
7
 WHERE e1.event_source = 'mail'
8
   AND e1.event_type = 'open'
9
   AND e1.DATA:payload.unique_args.singlesend_id = '02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'
10
) AS c2 ON c2.contact_id = e2.contact_id
11
WHERE e2.event_source = 'mail' 
12
 AND e2.event_type = 'open'
13
 AND e2.DATA:payload.unique_args.singlesend_id = '00163f67-7211-4363-ab4e-12dd6f313b3a'

1
SELECT c.contact_id, c.updated_at
2
FROM contact_data as c
3
JOIN event_data as e on c.contact_id = e.contact_id
4
WHERE e.event_source = 'mail'
5
    AND e.event_type = 'open'
6
    AND e.DATA:payload.unique_args.singlesend_id is not null
7
    AND e.timestamp >= timestampadd(day,  -3,  current_timestamp())

1
SELECT e2.contact_id, c2.updated_at
2
FROM event_data e2
3
INNER JOIN (
4
 SELECT c1.contact_id, c1.updated_at
5
 FROM contact_data AS c1
6
 INNER JOIN event_data e1 ON c1.contact_id = e1.contact_id
7
 WHERE e1.event_source = 'mail' AND e1.event_type = 'open'
8
) AS c2 ON c2.contact_id = e2.contact_id
9
WHERE e2.event_source = 'mail' AND e2.event_type = 'click'

1
SELECT e2.contact_id, c2.updated_at
2
FROM event_data e2
3
INNER JOIN (
4
 SELECT c1.contact_id, c1.updated_at
5
 FROM contact_data AS c1
6
 INNER JOIN event_data e1 ON c1.contact_id = e1.contact_id
7
 WHERE e1.event_source = 'mail' 
8
  AND e1.event_type = 'bounce'
9
  AND e1.DATA:payload.unique_args.mc_auto_id = '02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'
10
) AS c2 ON c2.contact_id = e2.contact_id
11
WHERE e2.event_source = 'mail' 
12
 AND e2.event_type = 'deferred'
13
 AND e2.DATA:payload.unique_args.mc_auto_id = '02bc41ca-07f9-11eb-9c1f-422f9e0f60d1'

1
SELECT c.contact_id, c.updated_at
2
FROM contact_data c
3
WHERE c.contact_id NOT IN (
4
 SELECT e.contact_id
5
 FROM event_data e
6
)

1
SELECT c.contact_id, c.updated_at
2
FROM contact_data c
3
WHERE c.contact_id NOT IN (
4
 SELECT e.contact_id
5
 FROM event_data e
6
 WHERE e.event_source = 'mail' 
7
   AND e.event_type = 'open'
8
   AND e.timestamp < timestampadd(MONTH, -2, CURRENT_TIMESTAMP)
9
)
10

1
SELECT c.contact_id, c.updated_at
2
FROM contact_data c
3
JOIN event_data AS e1 ON c.contact_id = e1.contact_id
4
 WHERE e1.event_source = 'mail' 
5
 AND e1.event_type = 'delivered'
6
 AND e1.DATA:payload.unique_args.singlesend_id is not null
7
 AND c.contact_id NOT IN (
8
   SELECT e2.contact_id
9
   FROM event_data AS e2
10
   WHERE e2.event_source = 'mail' 
11
     AND e2.event_type = 'open'
12
     AND e2.DATA:payload.unique_args.singlesend_id is not null
13
     AND e2.timestamp >= timestampadd(month, -1, current_timestamp()))
14
 )

1
SELECT c.contact_id, c.updated_at
2
FROM contact_data c
3
JOIN event_data AS e1 ON c.contact_id = e1.contact_id
4
 WHERE e1.event_source = 'mail' 
5
 AND e1.event_type = 'delivered'
6
 AND e1.DATA:payload.unique_args.mc_auto_id = 'fa3e0f6e-d397-11eb-87ce-22ffc6ed50f5'
7
 AND e1.DATA:payload.unique_args.mc_auto_step_id = 'fc9ead9f-d397-11eb-92fe-02aa17a6534e'
8
 AND c.contact_id NOT IN (
9
   SELECT e2.contact_id
10
   FROM event_data AS e2
11
   WHERE e2.event_source = 'mail' 
12
     AND e2.event_type = 'open'
13
     AND e2.DATA:payload.unique_args.mc_auto_id = 'fa3e0f6e-d397-11eb-87ce-22ffc6ed50f5'
14
     AND e2.DATA:payload.unique_args.mc_auto_step_id = 'fc9ead9f-d397-11eb-92fe-02aa17a6534e'
15
 )

1
SELECT c1.contact_id, c1.updated_at 
2
  FROM contact_data as c1 
3
  JOIN (
4
    SELECT e1.contact_id, e1.max_timestamp 
5
    FROM (
6
      SELECT e.contact_id, MAX(e.timestamp) max_timestamp 
7
      FROM event_data e 
8
      WHERE e.event_source = 'mail' and e.event_type = 'open' 
9
      GROUP BY e.contact_id
10
    ) as e1
11
    WHERE e1.max_timestamp < '2021-01-01T00:00:00.000Z'
12
  ) as e2 on c1.contact_id = e2.contact_id

1
SELECT c1.contact_id, c1.updated_at 
2
  FROM contact_data as c1 
3
  JOIN (
4
    SELECT e1.contact_id, e1.max_timestamp 
5
    FROM (
6
      SELECT e.contact_id, MAX(e.timestamp) max_timestamp 
7
      FROM event_data e 
8
      WHERE e.event_source = 'mail' and e.event_type = 'click' 
9
      GROUP BY e.contact_id
10
    ) as e1
11
    WHERE e1.max_timestamp >= timestampadd(day, -7, current_timestamp())
12
  ) as e2 on c1.contact_id = e2.contact_id

1
SELECT c1.contact_id, c1.updated_at 
2
FROM (
3
    SELECT c.contact_id, c.updated_at 
4
    FROM contact_data as c 
5
    JOIN (
6
        SELECT e1.contact_id, e1.max_timestamp 
7
        FROM (
8
            SELECT e.contact_id, MAX(e.timestamp) as max_timestamp 
9
            FROM event_data e 
10
            WHERE e.event_source = 'mail' 
11
             AND e.event_type = 'delivered' group by e.contact_id
12
        ) as e1 
13
    WHERE e1.max_timestamp >= timestampadd(month, -3, current_timestamp())
14
    ) as e2 on c.contact_id = e2.contact_id
15
) as c1 
16
JOIN (
17
    SELECT c.contact_id, c.updated_at 
18
    FROM contact_data as c 
19
    JOIN (
20
         SELECT e1.contact_id, e1.max_timestamp 
21
         FROM (
22
              SELECT e.contact_id, MAX(e.timestamp) as max_timestamp 
23
              FROM event_data e 
24
              WHERE e.event_source = 'mail' 
25
               AND e.event_type = 'open' group by e.contact_id
26
         ) as e1 
27
    WHERE e1.max_timestamp >= timestampadd(month, -1, current_timestamp())
28
    ) as e2 on c.contact_id = e2.contact_id
29
) as c11 on c1.contact_id = c11.contact_id
30

1
SELECT c1.contact_id, c1.updated_at FROM event_data AS e2
2
JOIN contact_data AS c1 ON e2.contact_id = c1.contact_id
3
JOIN event_data as e1 ON e1.contact_id = e2.contact_id
4
WHERE e1.event_source = 'mail' AND e1.event_type = 'delivered' AND e2.event_source = 'mail' AND e2.event_type = 'processed'

1
SELECT e2.contact_id, c2.updated_at
2
FROM event_data AS e2 JOIN (
3
    SELECT c1.contact_id, c1.updated_at
4
    FROM contact_data AS c1 JOIN event_data AS e1
5
    ON c1.contact_id = e1.contact_id
6
    WHERE e1.event_source = 'mail' AND e1.event_type = 'delivered') AS c2
7
ON c2.contact_id = e2.contact_id
8
WHERE  e2.event_source = 'mail' AND e2.event_type = 'processed'

1
SELECT contact_id, updated_at
2
FROM contact_data 
3
WHERE first_name = '\''Dave'\''