SQL Segment Extensions use cases
Use the search bar or the checkboxes below to browse sample SQL Segment Extension queries.
Seleccionar usuarios por la cantidad de veces que ha ocurrido un evento
Selecciona usuarios que abrieron una determinada Campaign de correo electrónico más de una vez en el pasado.
Esto también funciona para limitar mensajes dentro de la aplicación por número de impresiones, como seleccionar usuarios con más de tres impresiones como exclusión de Segment en la misma Campaign.
1
2
3
4
SELECT user_id FROM "USERS_MESSAGES_EMAIL_OPEN_SHARED"
WHERE campaign_api_id='8f7026dc-e9b7-40e6-bdc7-96cf58e80faa'
GROUP BY user_id
HAVING count(*) > 1
Seleccionar usuarios que realizaron una acción y sumar el valor de una propiedad
Selecciona usuarios que hicieron una apuesta deportiva cuya suma total de todas sus apuestas sea mayor que una cantidad determinada.
1
2
3
select user_id from "USERS_BEHAVIORS_CUSTOMEVENT_SHARED"
where name='Bet On Sports'
group by 1 having sum(get_path(parse_json(properties), 'amount')) > 150
Seleccionar usuarios en función de cuántas veces ocurrió un evento en un rango de tiempo
Selecciona usuarios con más de tres aperturas de correo electrónico en los últimos 30 días.
Esto también funciona para determinar los niveles de interacción de los usuarios, como usuarios altamente receptivos en diferentes canales.
1
2
3
4
5
SELECT user_id, COUNT(DISTINCT id) AS num_emails_opened
FROM USERS_MESSAGES_EMAIL_OPEN_SHARED
WHERE to_timestamp_ntz(time) >= DATEADD(day, -30, CURRENT_TIMESTAMP()) AND to_timestamp_ntz(time) <= CURRENT_TIMESTAMP()
GROUP BY user_id;
HAVING COUNT(DISTINCT id) > 3
Seleccionar usuarios que registraron al menos un evento en múltiples rangos de tiempo
Selecciona usuarios que realizaron una compra en cada uno de los últimos cuatro trimestres. Este segmento de usuarios se puede utilizar con audience sync para identificar clientes similares de alto valor para la adquisición.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= DATEADD(day, -90, CURRENT_TIMESTAMP()) AND to_timestamp_ntz(time) <= CURRENT_TIMESTAMP()
INTERSECT
SELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= DATEADD(day, -180, CURRENT_TIMESTAMP()) AND to_timestamp_ntz(time) <= DATEADD(day, -91, CURRENT_TIMESTAMP())
INTERSECT
SELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= DATEADD(day, -270, CURRENT_TIMESTAMP()) AND to_timestamp_ntz(time) <= DATEADD(day, -181, CURRENT_TIMESTAMP())
INTERSECT
SELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= DATEADD(day, -365, CURRENT_TIMESTAMP()) AND to_timestamp_ntz(time) <= DATEADD(day, -271, CURRENT_TIMESTAMP());
Seleccionar cualquier compra con determinadas propiedades
Selecciona clientes que realizaron cualquier compra que contenga la propiedad "type = shops" en 14 días.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
user_id
FROM
USERS_BEHAVIORS_PURCHASE_SHARED
WHERE
product_id IS NOT NULL
AND
get_path(
parse_json(properties),
'propertyname'
) = 'propertyvalue'
AND
to_timestamp_ntz(time) >= DATEADD(day, -14, CURRENT_TIMESTAMP())
AND
to_timestamp_ntz(time) <= CURRENT_TIMESTAMP()
GROUP BY 1
HAVING COUNT(id) > 0;
Seleccionar usuarios a los que se les envió un mensaje que no fue entregado
Selecciona usuarios a los que se les envió una campaña o Canvas de SMS, pero el mensaje no llegó al operador. Por ejemplo, el mensaje podría haber sido detenido por un desbordamiento de cola.
1
2
3
4
5
6
7
8
9
SELECT
user_id
FROM
USERS_MESSAGES_SMS_SEND_SHARED
WHERE
CANVAS_ID='63067c50740cc3377f8200d5'
AND TO_PHONE_NUMBER NOT IN (SELECT TO_PHONE_NUMBER FROM USERS_MESSAGES_SMS_CARRIERSEND_SHARED WHERE CANVAS_ID='63067c50740cc3377f8200d5')
GROUP BY 1
HAVING COUNT(id) > 0;
Encontrar todos los mensajes SMS que se enviaron pero no llegaron al operador debido a un desbordamiento de cola
Esto se puede reutilizar para otros tipos de mensajes enviados desde un Canvas en particular que no fueron entregados.
1
2
3
4
5
6
7
8
9
SELECT
user_id
FROM
USERS_MESSAGES_SMS_SEND_SHARED
WHERE
CANVAS_ID='id pulled from URL'
AND TO_PHONE_NUMBER NOT IN (SELECT TO_PHONE_NUMBER FROM USERS_MESSAGES_SMS_CARRIERSEND_SHARED WHERE CANVAS_ID='id pulled from URL')
GROUP BY 1
HAVING COUNT(id) > 0;
CANVAS_ID es el número que aparece después de /canvas/ en la URL de tu Canvas.
Seleccionar usuarios que realizaron cualquier compra con un array de propiedades que contenga un valor específico
1
2
3
4
SELECT DISTINCT EXTERNAL_USER_ID
FROM "USERS_BEHAVIORS_PURCHASE_SHARED",
LATERAL FLATTEN(input=>parse_json(properties):modifiers) as f
WHERE f.VALUE::STRING = 'Bacon'
Encontrar todos los usuarios que tuvieron múltiples errores 30003 y 0 entregas
Esto es útil para resolver situaciones en las que quieres dejar de enviar a usuarios que no están recibiendo mensajes pero que no se marcan como no válidos porque no tienen el código de error requerido. Puedes reorientar a estos usuarios para que actualicen su número de teléfono o cancelar su suscripción.
Esta consulta utiliza el editor incremental y busca usuarios con tres o más envíos rechazados en los últimos 90 días y cero entregas.
1
2
3
4
5
6
7
8
9
10
SELECT
$date(time), user_id, COUNT(id)
FROM
USERS_MESSAGES_SMS_REJECTION_SHARED
WHERE
provider_error_code = '30003'
AND
time > $start_date
AND TO_PHONE_NUMBER NOT IN (SELECT TO_PHONE_NUMBER FROM USERS_MESSAGES_SMS_DELIVERY_SHARED)
GROUP BY 1, 2;
Encontrar usuarios con propiedades de evento específicas y recuentos de eventos en un rango de tiempo
Encuentra usuarios que cumplan las siguientes condiciones simultáneamente:
- Realizaron transacciones por un valor total superior a $500 (la suma de múltiples eventos
Transact) - Realizaron transacciones en el centro comercial
Funan - Realizaron transacciones más de tres veces en los últimos 90 días
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
USER_ID
FROM
USERS_BEHAVIORS_CUSTOMEVENT_SHARED
WHERE
TIME > $start_date
AND NAME = 'Transact'
AND get_path(parse_json(properties), 'mall') = 'Funan'
GROUP BY
USER_ID
HAVING
SUM(get_path(parse_json(properties), 'total_value')) > 500
AND COUNT(*) > 3
Seleccionar usuarios cuya sesión más reciente fue en un modelo de dispositivo específico
1
2
3
4
5
6
select user_id, external_user_id, device_id, platform, os_version, device_model, to_timestamp(max(time)) last_session
from users_behaviors_app_sessionstart
where app_group_id = ''
and date_trunc(day, to_timestamp(time)) <= to_timestamp('2023-08-07')
and device_model = ''
group by user_id, external_user_id, device_id, platform, os_version, device_model
Encontrar usuarios que seleccionaron el segundo botón de un mensaje dentro de la aplicación en un rango de tiempo específico
1
2
3
4
5
6
SELECT DISTINCT USER_ID, to_timestamp_ntz(time)
FROM USERS_MESSAGES_INAPPMESSAGE_CLICK_SHARED
WHERE to_timestamp_ntz(time) >= '2023-08-03'::timestamp_ntz
AND to_timestamp_ntz(time) <= '2023-08-09'::timestamp_ntz
AND BUTTON_ID = '1'
AND CAMPAIGN_ID = '64c8cd9c4d38d13091957b1c'
Encontrar usuarios que compraron en cada uno de los últimos tres meses calendario
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= '2023-09-01'::timestamp_ntz
AND to_timestamp_ntz(time) <= '2023-09-30'::timestamp_ntz
INTERSECT
SELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= '2023-10-01'::timestamp_ntz
AND to_timestamp_ntz(time) <= '2023-10-31'::timestamp_ntz
INTERSECT
SELECT DISTINCT user_id
FROM USERS_BEHAVIORS_PURCHASE_SHARED
WHERE to_timestamp_ntz(time) >= '2023-11-01'::timestamp_ntz
AND to_timestamp_ntz(time) <= '2023-11-30'::timestamp_ntz;
Seleccionar usuarios que completaron un evento personalizado con una propiedad específica cuando la propiedad es un entero
Enviar un mensaje a usuarios que vieron una serie en los últimos seis meses y están a punto de abandonar la plataforma.
La propiedad es el ID del título; de lo contrario, necesitarías incluir más de 100 ID de título en un filtro. La extensión de segmento incremental se puede optimizar en cuanto a costos y puedes especificar el rango de fechas en el encabezado.
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
$date(time),
USER_ID,
COUNT(*)
FROM
USERS_BEHAVIORS_CUSTOMEVENT_SHARED
WHERE
TIME > $start_date
AND NAME = 'event name'
AND (PARSE_JSON(PROPERTIES):property_name::INT) IN (1, 2)
GROUP BY
1, 2;
Encontrar el número promedio de correos electrónicos que un usuario recibe diariamente
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH user_email_counts AS (
SELECT
USER_ID,
COUNT(*) AS total_emails,
DATEDIFF(day, MIN(TO_DATE(DATE_TRUNC('day', TO_TIMESTAMP_NTZ(TIME)))), MAX(TO_DATE(DATE_TRUNC('day', TO_TIMESTAMP_NTZ(TIME))))) AS days
FROM USERS_MESSAGES_EMAIL_SEND_SHARED
GROUP BY USER_ID
HAVING COUNT(USER_ID) > 1
),
-- Then, calculate the average number of emails received by each user daily
user_daily_average AS (
SELECT
USER_ID,
days,
CASE
WHEN days = 0 THEN total_emails -- If the user received all emails in one day, the average for that user is the total number of emails
ELSE total_emails / days -- Otherwise, it's the total number of emails divided by the number of days
END AS daily_average
FROM user_email_counts
)
-- The total daily average is the average of all users
SELECT
AVG(daily_average)
FROM user_daily_average;
Para mensajes SMS, reemplaza USERS_MESSAGES_EMAIL_SEND_SHARED con USERS_MESSAGES_SMS_SEND_SHARED en la consulta. Para notificaciones push, reemplaza USERS_MESSAGES_EMAIL_SEND_SHARED con USERS_MESSAGES_SMS_SEND_SHARED en la consulta.
Encontrar el número promedio de correos electrónicos que un usuario recibe semanalmente
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH user_email_counts AS (
SELECT
USER_ID,
COUNT(*) AS total_emails,
DATEDIFF(week, MIN(TO_DATE(DATE_TRUNC('week', TO_TIMESTAMP_NTZ(TIME)))), MAX(TO_DATE(DATE_TRUNC('week', TO_TIMESTAMP_NTZ(TIME))))) AS weeks
FROM USERS_MESSAGES_EMAIL_SEND_SHARED
GROUP BY USER_ID
HAVING COUNT(USER_ID) > 1
),
-- Then, calculate the average number of emails received by each user weekly
user_weekly_average AS (
SELECT
USER_ID,
CASE
WHEN weeks = 0 THEN total_emails -- If the user received all emails in the same week, the average is the total number of emails
ELSE total_emails / weeks -- Otherwise, it's the total number of emails divided by the number of weeks
END AS weekly_average
FROM user_email_counts
)
-- The total weekly average is the average of all users
SELECT
AVG(weekly_average) AS average_weekly_emails
FROM user_weekly_average;
Para mensajes SMS, reemplaza USERS_MESSAGES_EMAIL_SEND_SHARED con USERS_MESSAGES_SMS_SEND_SHARED en la consulta. Para notificaciones push, reemplaza USERS_MESSAGES_EMAIL_SEND_SHARED con USERS_MESSAGES_SMS_SEND_SHARED en la consulta.