Skip to content

SQL Segment Extensions use cases

Use the search bar or the checkboxes below to browse sample SQL Segment Extension queries.

Seleccionar usuarios por el número de veces que se ha producido un evento

Seleccione los usuarios que abrieron una determinada campaña de correo electrónico más de una vez en el pasado.

Esto también funciona para la limitación de mensajes in-app por el número de impresiones, como la selección de usuarios con más de tres impresiones como exclusión de segmento en la misma campaña.

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 los usuarios que han realizado una acción y sumar el valor de una propiedad

Seleccionar usuarios que hayan realizado una apuesta deportiva cuya suma de todas sus apuestas sea superior a 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 del número de veces que se ha producido un evento en un intervalo de tiempo.

Seleccione usuarios con más de tres aperturas de correo electrónico en los últimos 30 días.

Esto también sirve para determinar los niveles de compromiso de los usuarios, como los usuarios muy 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 hayan registrado al menos un evento en varios intervalos de tiempo

Seleccione los usuarios que hayan realizado una compra en cada uno de los cuatro últimos trimestres. Este segmento de usuarios puede utilizarse con la sincronización de audiencias 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());

Seleccione cualquier compra con determinadas propiedades

Seleccione los clientes que hayan realizado alguna 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 envió un mensaje que no fue entregado

Seleccione los usuarios a los que se ha enviado una campaña SMS o Canvas, pero el mensaje no ha llegado al operador. Por ejemplo, el mensaje podría haberse detenido por un desbordamiento de la 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;

Buscar todos los mensajes SMS que se enviaron pero no llegaron al transportista por desbordamiento de la cola.

Esto se puede reutilizar para otros tipos de mensajes enviados desde un Canvas concreto que no se hayan entregado.

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 su URL de Canvas.

Seleccionar usuarios que hayan realizado alguna compra con una matriz 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'

Buscar todos los usuarios que tuvieron múltiples errores 30003 y 0 entregas

Esto es útil para resolver situaciones en las que se desea detener el envío a usuarios que no reciben mensajes pero que no se marcan como no válidos porque no tienen el código de error requerido. Puede volver a dirigirse a estos usuarios para que actualicen su número de teléfono o darles de baja.

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;

Buscar usuarios con propiedades de eventos específicas y recuentos de eventos en un intervalo de tiempo

Busca usuarios que cumplan simultáneamente las siguientes condiciones:

  • Transacciones por un valor total superior a 500 $ (la suma de varios eventos Transact )
  • Transacciones en el centro comercial Funan
  • Transacciones realizadas 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 se haya realizado 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

Buscar usuarios que hayan seleccionado el segundo botón de un mensaje in-app en un intervalo 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'

Buscar usuarios que hayan comprado en cada uno de los tres últimos meses naturales

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 número entero

Envío de un mensaje a los 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, tendría que incluir más de 100 ID de títulos en un filtro. La extensión incremental de segmentos puede optimizarse en función de los costes y puede especificar el intervalo de fechas en la cabecera.

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;

Averiguar el número medio de correos electrónicos que recibe un usuario al día

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;

Encuentre el número medio de correos electrónicos que recibe un usuario 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;
Github   Editar esta página en GitHub
¿QUÉ TAN ÚTIL FUE ESTA PÁGINA?
New Stuff!