Notificações

Tabela gerada relacionando informações Messages e Notification

Volume de templates disparados

Este documento descreve a consulta SQL utilizada para obter o volume de templates disparados por um chatbot no ambiente Databricks/Azure. A consulta analisa mensagens enviadas e correlaciona com eventos de notificação.

Objetivo

Obter a quantidade de disparos de templates a partir dos dados armazenados na tabela clients_trustedzone.deltashare_core.messages, agrupando-os pelo nome do template.

Estrutura da Consulta

A consulta executa as seguintes etapas:

Extração dos Dados:
 

Filtra mensagens enviadas pelo bot especificado (botid@msging.net).

Decodifica o conteúdo da mensagem para obter o nome do template.

Extraí informações da metadata, como nome da campanha e identificação de disparos via Desk.

Define se a mensagem foi enviada via atendimento humano (#activecampaign.attendanceRedirect) ou envio em massa.

 

Junção com Notificações:
 

As mensagens são unidas com os eventos de notificação através dos campos InternalId e id.
 

Agregação:
 

Conta o total de disparos para cada templateName.
 

Consulta SQL

SELECT
templateName,
count(*) as QuantidadeDisparada
FROM
(
  SELECT
    t1.OwnerIdentity,
    t1.StorageDate,
    t1.toIdentity,
    DECODE(UNBASE64(t1.Content), 'UTF-16LE')::template.name AS templateName,
    JSON_TUPLE(t1.Metadata, '#activecampaign.name') AS campaignName,
    CASE
      WHEN t1.Metadata RLIKE 'desk:' THEN 'Sim'
      ELSE 'Não'
    END AS DisparoDesk,
    CASE
      WHEN t1.Metadata RLIKE 'desk:' THEN t1.Metadata['#activecampaign.attendanceRedirect']
      ELSE 'EnvioMassa'
    END AS SentBy,
    t2.Event AS Status
  FROM clients_trustedzone.deltashare_core.messages t1
  INNER JOIN clients_trustedzone.deltashare_core.notifications t2
    ON t1.InternalId = t2.Metadata['#message.uniqueId']
    AND t1.id = t2.id
  WHERE t1.ppDomain = 'broadcast.msging.net'
    AND t1.OwnerIdentity = 'botid@msging.net' -- Seu bot
)
GROUP BY templateName;

Campos Utilizados

Campo e Descrição

templateName: Nome do template extraído do conteúdo da mensagem

QuantidadeDisparada: Total de disparos do template

OwnerIdentity: Identificação do bot responsável pelo disparo

StorageDate: Data de armazenamento da mensagem

toIdentity: Destinatário da mensagem

campaignName: Nome da campanha associada ao disparo

DisparoDesk: Indica se o disparo ocorreu via atendimento humano

SentBy: Origem do disparo (atendimento humano ou envio em massa)

Status: Status da notificação associada

Considerações

A consulta filtra mensagens enviadas apenas pelo bot especificado (botid@msging.net).

Apenas mensagens enviadas via broadcast.msging.net são consideradas.

A decodificação do conteúdo está em UTF-16LE para extrair corretamente o nome do template.

O campo SentBy diferencia disparos manuais (via Desk) de disparos em massa.

 

Status dos templates disparados

Este documento detalha a consulta SQL utilizada para obter o status dos templates disparados por um chatbot no ambiente Databricks/Azure. A consulta analisa mensagens enviadas e correlaciona com eventos de notificação, permitindo visualizar a distribuição dos status por template.

Objetivo

Identificar a quantidade de mensagens enviadas para um template específico, categorizadas pelo status da notificação.

Estrutura da Consulta

A consulta executa as seguintes etapas:

Extração dos Dados:
Filtra mensagens enviadas pelo bot especificado (botid@msging.net).

Decodifica o conteúdo da mensagem para obter o nome do template.

Extraí informações da metadata, como nome da campanha e identificação de disparos via Desk.

Define se a mensagem foi enviada via atendimento humano (#activecampaign.attendanceRedirect) ou envio em massa.

 

Junção com Notificações:
 

As mensagens são unidas com os eventos de notificação através dos campos InternalId e id.
 

Filtragem por Template:
 

Apenas mensagens relacionadas ao template templateName são consideradas.
 

Agregação:
 

Conta o total de disparos para cada combinação de templateName e Event.

 

Consulta SQL

SELECT
templateName,
Event,
count(*) as Quantidade
FROM
(
  SELECT
    t1.OwnerIdentity,
    t1.StorageDate,
    t1.toIdentity,
    t2.Event,
    DECODE(UNBASE64(t1.Content), 'UTF-16LE')::template.name AS templateName,
    JSON_TUPLE(t1.Metadata, '#activecampaign.name') AS campaignName,
    CASE
      WHEN t1.Metadata RLIKE 'desk:' THEN 'Sim'
      ELSE 'Não'
    END AS DisparoDesk,
    CASE
      WHEN t1.Metadata RLIKE 'desk:' THEN t1.Metadata['#activecampaign.attendanceRedirect']
      ELSE 'EnvioMassa'
    END AS SentBy,
    t2.Event AS Status
  FROM clients_trustedzone.deltashare_core.messages t1
  INNER JOIN clients_trustedzone.deltashare_core.notifications t2
    ON t1.InternalId = t2.Metadata['#message.uniqueId']
    AND t1.id = t2.id
  WHERE t1.ppDomain = 'broadcast.msging.net'
    AND t1.OwnerIdentity = 'botid@msging.net' -- Seu bot
)
WHERE templateName = 'templateName' -- Nome do template
GROUP BY templateName, Event;

Campos Utilizados

CampoDescrição

templateName

Nome do template extraído do conteúdo da mensagem

Event

Status da notificação associada ao disparo

Quantidade

Total de eventos para cada status

OwnerIdentity

Identificação do bot responsável pelo disparo

StorageDate

Data de armazenamento da mensagem

toIdentity

Destinatário da mensagem

campaignName

Nome da campanha associada ao disparo

DisparoDesk

Indica se o disparo ocorreu via atendimento humano

SentBy

Origem do disparo (atendimento humano ou envio em massa)

Considerações

A consulta filtra mensagens enviadas apenas pelo bot especificado (botid@msging.net).

Apenas mensagens enviadas via broadcast.msging.net são consideradas.

A decodificação do conteúdo está em UTF-16LE para extrair corretamente o nome do template.

O campo SentBy diferencia disparos manuais (via Desk) de disparos em massa.

A consulta considera apenas o template templateName. Para analisar outros templates, basta modificar esse filtro.

Templates enviados em duplicidade

Realizar um levantamento detalhado do volume total de templates enviados por um bot específico, quantificando tanto o volume de notificações ativas quanto a quantidade de usuários únicos que os receberam em um mesmo período. Essa mensuração deverá englobar:

O total de mensagens de template, dado um status, pelo bot em questão;

A volumetria agregada de usuários que receberam essas notificações;

A contagem de usuários distintos atingidos por cada template;

A possibilidade de segmentação por dia ou outro intervalo temporal definido, possibilitando uma visão do alcance e da penetração das notificações ativas.

Objetivo

Quantificar o volume de templates entregues aos usuários por meio de notificações ativas enviadas por um bot específico. 

WITH messages_filtered AS (
  SELECT
    *
  FROM
    clients_trustedzone.deltashare_core.messages
  WHERE
    1=1
    AND StorageDateDayBR = "" -- filtre o período
    AND OwnerIdentity = "" -- filtre seu bot
),
notifications_filtered AS (
  SELECT
    *
  FROM
    clients_trustedzone.deltashare_core.notifications
  WHERE
    1=1
    AND StorageDateDayBR = "" -- filtre o período
    AND OwnerIdentity = "" -- filtre seu bot
),
joined_data AS (
  SELECT
    t1.OwnerIdentity,
    t1.StorageDateDayBR,
    t1.StorageDateBR,
    t1.toIdentity,
    decode(unbase64(t1.Content), 'UTF-16LE'):template . name AS templateName,
    t2.Event AS Status,
    ROW_NUMBER() OVER (
        PARTITION BY
          CONCAT(
            CAST(t1.Content AS STRING),
            CAST(t1.Id AS STRING),
            CAST(t1.InternalId AS STRING),
            CAST(t1.Metadata AS STRING)
          )
        ORDER BY t1.StorageDateBR DESC
      ) AS rn --chave artificial para deduplicacao de dados
  FROM
    messages_filtered t1
      INNER JOIN notifications_filtered t2
        ON t1.InternalId = t2.Metadata:[ '#message.uniqueId' ]
        AND t1.id = t2.id
  WHERE
    t1.ppDomain = 'broadcast.msging.net'
    AND Event = "received" -- filte pelo evento relavante para análise
)
SELECT
  OwnerIdentity,
  StorageDateDayBR,
  StorageDateBR,
  toIdentity,
  templateName
FROM
  joined_data
WHERE
  1 = 1
  and rn = 1

Comentários

messages_filtered e notifications_filtered isolam primeiro as tabelas de mensagens e notificações no schema clients_trustedzone.deltashare_core, restringindo por StorageDateDayBR e OwnerIdentity. Isso minimiza o volume de dados que será unido e processado nas etapas seguintes.

A junção (INNER JOIN) relaciona cada mensagem à sua notificação via InternalId e id e filtra ainda por ppDomain = 'broadcast.msging.net', garantindo que sejam consideradas apenas mensagens do domínio.

A função de janela ROW_NUMBER() OVER (PARTITION BY … ORDER BY StorageDateBR DESC) cria uma “chave artificial” concatenando todo o conteúdo da mensagem com seus identificadores. Só o registro mais recente (rn = 1) é mantido, evitando duplicatas.

Objetivo

Contabilizar quantos templates cada usuário recebeu em um mesmo dia por meio de notificações ativas enviadas por um bot específico. 

WITH messages_filtered AS (
  SELECT
    *
  FROM
    clients_trustedzone.deltashare_core.messages
  WHERE
    1=1
    AND StorageDateDayBR = "" -- filtre o período
    AND OwnerIdentity = "" -- filtre seu bot
),
notifications_filtered AS (
  SELECT
    *
  FROM
    clients_trustedzone.deltashare_core.notifications
  WHERE
    1=1
    AND StorageDateDayBR = "" -- filtre o período
    AND OwnerIdentity = "" -- filtre seu bot
),
joined_data AS (
  SELECT
    t1.OwnerIdentity,
    t1.StorageDateDayBR,
    t1.StorageDateBR,
    t1.toIdentity,
    decode(unbase64(t1.Content), 'UTF-16LE'):template . name AS templateName,
    t2.Event AS Status,
    ROW_NUMBER() OVER (
        PARTITION BY
          CONCAT(
            CAST(t1.Content AS STRING),
            CAST(t1.Id AS STRING),
            CAST(t1.InternalId AS STRING),
            CAST(t1.Metadata AS STRING)
          )
        ORDER BY t1.StorageDateBR DESC
      ) AS rn --chave artificial para deduplicacao de dados
  FROM
    messages_filtered t1
      INNER JOIN notifications_filtered t2
        ON t1.InternalId = t2.Metadata:[ '#message.uniqueId' ]
        AND t1.id = t2.id
  WHERE
    t1.ppDomain = 'broadcast.msging.net'
    AND Event = "received" -- filte pelo evento relavante para análise
)
SELECT DISTINCT
  OwnerIdentity,
  StorageDateDayBR,
  toIdentity,
  templateName,
  COUNT(*) OVER (PARTITION BY OwnerIdentity, toIdentity, StorageDateDayBR, templateName) AS totalTemplate
FROM
  joined_data
WHERE
  1 = 1
  and rn = 1
ORDER BY
  totalTemplate DESC,
  toIdentity;

Comentários

Mantém a mesma estratégia de ROW_NUMBER() OVER (PARTITION … ORDER BY StorageDateBR DESC) para assegurar que apenas o registro mais recente de cada mensagem única seja considerado.

COUNT(*) OVER (PARTITION BY OwnerIdentity, toIdentity, StorageDateDayBR, templateName) garantir a contabilização da volumetria de templates repetidos enviados a um mesmo usuário em um mesmo período.

Objetivo

Contabilizar, para um template, o total de notificações enviadas e usuários únicos que as receberam em um dia por um bot específico. 

WITH messages_filtered AS (
  SELECT
    *
  FROM
    clients_trustedzone.deltashare_core.messages
  WHERE
    1=1
    AND StorageDateDayBR = "" -- filtre o período
    AND OwnerIdentity = "" -- filtre seu bot
),
notifications_filtered AS (
  SELECT
    *
  FROM
    clients_trustedzone.deltashare_core.notifications
  WHERE
    1=1
    AND StorageDateDayBR -- filtre o período
    AND OwnerIdentity -- filtre seu bot
),
joined_data AS (
  SELECT
    t1.OwnerIdentity,
    t1.StorageDateDayBR,
    t1.StorageDateBR,
    t1.toIdentity,
    decode(unbase64(t1.Content), 'UTF-16LE'):template . name AS templateName,
    t2.Event AS Status,
    ROW_NUMBER() OVER (
        PARTITION BY
          CONCAT(
            CAST(t1.Content AS STRING),
            CAST(t1.Id AS STRING),
            CAST(t1.InternalId AS STRING),
            CAST(t1.Metadata AS STRING)
          )
        ORDER BY t1.StorageDateBR DESC
      ) AS rn --chave artificial para deduplicacao de dados
  FROM
    messages_filtered t1
      INNER JOIN notifications_filtered t2
        ON t1.InternalId = t2.Metadata:[ '#message.uniqueId' ]
        AND t1.id = t2.id
  WHERE
    t1.ppDomain = 'broadcast.msging.net'
    AND Event = "received" -- filte pelo evento relavante para análise
)
SELECT
  OwnerIdentity,
  StorageDateDayBR,
  templateName,
  COUNT(toIdentity) AS qtyUsersReceived,
  COUNT(DISTINCT toIdentity) AS qtyUniqueUsersReceived,
  qtyUsersReceived - qtyUniqueUsersReceived as Diff
FROM
  joined_data
WHERE
  1 = 1
  and rn = 1
group by 1,2,3

Análises no roadmap da documentação:

Status dos templates disparados

Falhas e motivos

Tempos até o recebimento e leitura/consumo

Correlação com a messages para resposta do usuário

Correlação com a eventtracks para visão de jornada