Introduction
In contrast to segments, routing via a query does not automatically handle filters on consent and contactability. It must therefore be added directly to the query.
Consent
To manage consent, use the proc_contactability_aggregate table and the consent_type field.
Example use of consent
WITH filtered_cu AS (
SELECT *
FROM client_user
WHERE is_master = 1
),
filtered_pca AS (
SELECT DISTINCT customer_id, business_unit_brand_id, business_unit_country_code
FROM proc_contactability_aggregate
WHERE is_master = 1 AND consent_type["consent_email"]
)
SELECT cu.email
FROM filtered_cu cu
JOIN filtered_pca pca
on cu.customer_id = pca.customer_id
AND cu.business_unit_brand_id = pca.business_unit_brand_id
AND cu.business_unit_country_code = pca.business_unit_country_code
Contactability
To manage contactability, use the proc_contactability_aggregate table and the email_contactability and/or mobile_contactability and/or customer_contactability
These fields are set to false for non-contactability. They are therefore to be used in exclusion.
Example use of contactability
To target only email contactables, I exclude email non-contactables.
WITH filtered_cu AS (
SELECT *
FROM client_user
WHERE is_master = 1
),
filtered_pca AS (
SELECT DISTINCT customer_id, business_unit_brand_id, business_unit_country_code
FROM proc_contactability_aggregate
WHERE is_master = 1 AND email_contactability
)
SELECT cu.email
FROM filtered_cu cu
JOIN filtered_pca pca
on cu.customer_id = pca.customer_id
AND cu.business_unit_brand_id = pca.business_unit_brand_id
AND cu.business_unit_country_code = pca.business_unit_country_code