Skip to content

SQL Segment Extensions use cases

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

Selecione os usuários pela quantidade de vezes que um evento ocorreu

Selecione os usuários que abriram uma certa campanha de e-mail mais de uma vez no passado.

Isso também funciona para o limite de mensagens no app pelo número de impressões, como selecionar usuários com mais de três impressões como uma exclusão de segmento na mesma campanha.

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

Selecione os usuários que realizaram uma ação e some um valor de propriedade

Selecione usuários que fizeram uma aposta em esportes com a soma de todas as suas apostas sendo maior que um certo valor.

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

Selecione usuários com base em quantas vezes um evento ocorreu em um intervalo de tempo

Selecione usuários com mais de três aberturas de e-mail nos últimos 30 dias.

Isso também funciona para determinar os níveis de engajamento dos usuários, como usuários altamente responsivos em diferentes canais.

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

Selecione usuários que registraram pelo menos um evento em vários intervalos de tempo

Selecione os usuários que fizeram uma compra em cada um dos últimos quatro trimestres. Este segmento de usuário pode ser usado com público sincronizado para identificar clientes semelhantes de alto valor para aquisição.

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());

Selecione qualquer compra com certas propriedades

Selecione clientes que fizeram qualquer compra que contenha a propriedade “type = shops” em 14 dias.

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;

Selecione usuários que receberam uma mensagem que não foi entregue

Selecione os usuários que receberam uma campanha de SMS ou canva, mas a mensagem não chegou à operadora. Por exemplo, a mensagem pode ter sido interrompida por um estouro de fila.

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;

Encontre todas as mensagens SMS que foram enviadas, mas não chegaram à operadora devido ao estouro da fila

Isso pode ser reutilizado para outros tipos de mensagens enviadas de uma canva específica que não foram entregues.

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 é o número após /canvas/ na sua URL do canva.

Selecione usuários que fizeram qualquer compra com uma matriz de propriedades contendo um 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'

Encontre todos os usuários que tiveram vários erros 30003 e 0 entregas

Isso é útil para resolver situações em que você deseja parar de enviar para usuários que não estão recebendo mensagens, mas não estão sendo marcados como inválidos porque não possuem o código de erro necessário. Você pode redirecionar esses usuários para atualizar o número de telefone deles ou cancelar inscrição.

Esta consulta usa o editor incremental e procura usuários com três ou mais envios rejeitados nos últimos 90 dias e zero 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;

Encontre usuários com propriedades de eventos específicas e contagens de eventos em um intervalo de tempo

Encontre usuários que atendam às seguintes condições simultaneamente:

  • Transacionou um valor total superior a $500 (a soma de vários Transact eventos)
  • Transação no shopping Funan
  • Transacionado mais de três vezes nos últimos 90 dias
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

Selecione usuários cuja sessão mais recente foi em um 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

Encontre usuários que selecionaram o segundo botão de uma mensagem no app em um intervalo de tempo 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'

Encontre usuários que compraram em cada um dos últimos três meses do calendário

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;

Selecione usuários que completaram um evento personalizado com uma propriedade específica quando a propriedade é um número inteiro

Enviando uma mensagem para usuários que assistiram a uma série nos últimos seis meses e estão prestes a deixar a plataforma.

A propriedade é o ID do título; caso contrário, você precisaria incluir mais de 100 IDs de título em um filtro. A extensão de segmento incremental pode ser otimizada para custo e você pode especificar o intervalo de datas no cabeçalho.

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;

Encontre o número médio de e-mails que um usuário recebe 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;

Encontre o número médio de e-mails que um usuário recebe 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 no GitHub
QUÃO ÚTIL FOI ESTA PÁGINA?
New Stuff!