Notificaciones

Esta tabla contiene registros de los estados/eventos asociados a los mensajes

Latencia nominal: 5 minutos
Latencia máxima: 15 minutos
Objeto: clients_trustedzone.deltashare_core.notifications
Retención: 3 días (D-0, D-1, D-2)
Carga histórica inicial (en objeto separado): 360 días
La carga histórica debe combinarse con Blip para su disponibilidad; el objeto estará disponible durante 7 días para ingestión.

[Exclusivo Canal WhatsApp] Reglas de estado asociadas al envío de templates de Meta

 Los nuevos estados de mensajes pueden actualizarse en las tablas con información hasta 30 días después.

Ej.: si el usuario recibe un envío hoy, pero solo lo lee dentro de 28 días, el próximo mes se actualizará el estado del mensaje.

 El estado de consumo/lectura solo estará disponible cuando el usuario tenga activada esta configuración en su WhatsApp.

Notifications

Dentro de la tabla notifications, es posible visualizar la información de envío de todas las notificaciones activas y el estado de estos envíos estará registrado en la columna "Event".

Los estados pueden ser:

accepted → el mensaje fue aceptado

received → mensaje recibido por el usuario

consumed → mensaje leído por el usuario

failed → ocurrió un fallo en el envío

Para obtener más información del envío, como el nombre del template enviado, responsable del envío, etc., es necesario relacionar la tabla notifications con la tabla de messages.

¿Qué claves son necesarias para relacionar una tabla con la otra?

Dentro de la columna "Metadata" existen algunos objetos únicos que se pueden usar para relacionar con la tabla de mensajes.

Ejemplo del JSON en esta columna:

{
  "$elapsedTimeToStorage": "00:00:00.0082890",
  "#wa.timestamp": "1727274704",
  "#message.from": "botid@msging.net",
  "#wa.conversation.id": "00a9b99abad234c7b29c13626",
  "#wa.conversation.origin.type": "marketing",
  "#message.uniqueId": "46e1887a-e3d6-4696-bc4c-000000000",
  "traceparent": "00-a3f5436db296fbd9478f768208ea7ae3-530703ddd0000000-01",
  "$internalId": "58ea1971-40c4-4832-9cf2-d925ce336081",
  "$originatorSessionRemoteNode": "postmaster@broadcast.msging.net/!msging-server-ss000-ll2ss6kkk"
}

Será necesario guardar el objeto #message.uniqueId, ya que será la clave principal para relacionar con la tabla de mensajes.

Otra columna importante es Id, que se encuentra en la tabla messages. Esta columna hace referencia al Id del envío. Si el envío fue masivo, todos los mensajes de la columna "messages" tendrán el mismo Id. Si fue un envío individual, el Id estará solo en ese registro.

Ejemplo de query para listar la información necesaria para relacionar con la tabla messages:

SELECT 
  OwnerIdentity,
  regexp_replace(split(From, '[/]')[1], '%40', '@') AS From_,
  json_tuple(Metadata, '#message.uniqueId') AS message_uniqueID,
  Event,
  Id,
  StorageDate,
  StorageDateBR,
  StorageDateDayBR
FROM database.notification;

Messages

En la tabla messages realizaremos la relación de la columna message_uniqueID generada en la consulta anterior con InternalId de la tabla de mensajes.

Ejemplo de query:

SELECT *
FROM database.message t1
INNER JOIN database.notification t2
  ON t1.InternalId = t2.messageID
  AND t1.id = t2.id;

Después de realizar la relación, podrás obtener información del envío en la columna Content y en Metadata.

Envío desde Blip Desk

Si la notificación se envió desde Blip Desk, podrás obtener información del responsable del envío (#activecampaign.attendanceRedirect) y si efectivamente fue realizado por Blip Desk (#activecampaign.stateId, siempre inicia con desk:), así como el nombre de la campaña (#activecampaign.name) desde la columna Metadata.

Ejemplo de JSON de un envío desde Blip Desk:

{
  "$elapsedTimeToStorage": "00:00:00.0227413",
  "#activecampaign.stateId": "desk:66ab9488-ee9f-4c68-808007-cd4d7166a2cb",
  "#activecampaign.flowId": "0000000-6c69-4498-9f75-3c3d6852e556",
  "#activecampaign.masterState": "botid@msging.net",
  "#activecampaign.name": "Desk-Active-Campaing-d203351d-dbdc-45b8-a902-000000",
  "#activecampaign.contactNotMerged": "True",
  "#activecampaign.isToUseLiteApi": "False",
  "#activecampaign.attendanceRedirect": "atendente01%40teste.com.br@blip.ai",
  "traceparent": "00-6b767540d737e57b366b88cca0376820-000000000-01",
  "$internalId": "2c19855b-587c-48d1-80c1-00000000",
  "$originatorSessionRemoteNode": "postmaster@storage-activecampaign.msging.net/!msging-server-w6jss-00000",
  "#uniqueId": "2c19855b-587c-48d1-80c1-00000000",
  "#date_processed": "1727274700000",
  "date_created": "00000000000"
}

El template utilizado puede obtenerse directamente desde la columna Content.

Ejemplo de código de un template de prueba llamado "saudacao_inicial":

{
  "type": "template",
  "template": {
    "language": { "policy": "deterministic", "code": "pt_BR" },
    "name": "saudacao_inicial",
    "components": [
      { "type": "body", "parameters": [
          { "text": "Usuario", "type": "text" },
          { "text": "Localidade", "type": "text" }
        ]
      }
    ]
  },
  "templateContent": {
    "name": "saudacao_inicial",
    "language": "pt_BR",
    "components": [
      { "type": "BODY", "text": "¡Hola! Aquí es {{1}}, de XXXXX {{2}}.", "example": {
          "bodyText": [["nombre", "localidad"]]
        }
      }
    ]
  }
}

Envío masivo

Para envíos masivos, la relación de tablas se realiza con las mismas columnas. La diferencia es que el Id en notifications se repetirá para todos los destinatarios, pero al relacionar con InternalId de messages, se realizará de manera individualizada.

Ejemplo de query para relacionar Messages y Notifications:

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 'Sí' ELSE 'No' END AS DisparoDesk,
  CASE WHEN t1.Metadata RLIKE 'desk:' THEN t1.Metadata['#activecampaign.attendanceRedirect'] ELSE 'EnvioMasa' END AS SentBy,
  t2.Event AS Status
FROM database.message t1
INNER JOIN database.notification t2
  ON t1.InternalId = t2.Metadata['#message.uniqueId']
  AND t1.id = t2.id
WHERE t1.ppDomain = 'broadcast.msging.net';

Volumen de templates enviados

Esta sección describe cómo obtener la cantidad de templates enviados por un chatbot, relacionando mensajes con eventos de notificación, agrupados por nombre del template.

templateName: nombre del template

CantidadDisparada: total de envíos del template

OwnerIdentity: identificación del bot

StorageDate: fecha de almacenamiento

toIdentity: destinatario

campaignName: nombre de la campaña

DisparoDesk: indica si fue envío manual

SentBy: origen del envío

Status: estado de la notificación

Se utiliza decodificación UTF-16LE y se diferencia envío manual (Desk) de masivo.

Status de templates enviados

Se obtiene la cantidad de mensajes por template y estado:

templateName

Event → estado del envío

Cantidad → total de eventos por estado

Se aplican los mismos filtros y decodificación mencionados anteriormente.

Templates enviados duplicados

Se contabiliza:

Total de mensajes de template por bot y estado

Usuarios que recibieron estas notificaciones

Usuarios únicos por template

Posibilidad de segmentación por día u otro intervalo

Ejemplo SQL para deduplicar y contar usuarios:

WITH messages_filtered AS (
  SELECT * FROM clients_trustedzone.deltashare_core.messages
  WHERE StorageDateDayBR = "" AND OwnerIdentity = ""
),
notifications_filtered AS (
  SELECT * FROM clients_trustedzone.deltashare_core.notifications
  WHERE StorageDateDayBR = "" AND OwnerIdentity = ""
),
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
  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"
)
SELECT DISTINCT
  OwnerIdentity,
  StorageDateDayBR,
  toIdentity,
  templateName,
  COUNT(*) OVER (PARTITION BY OwnerIdentity, toIdentity, StorageDateDayBR, templateName) AS totalTemplate
FROM joined_data
WHERE rn = 1
ORDER BY totalTemplate DESC, toIdentity;

Objetivo:
Contabilizar cuántos templates recibió cada usuario en un día mediante notificaciones activas enviadas por un bot específico.

Análisis en roadmap de documentación:

Estado de templates enviados

Fallos y motivos

Tiempos hasta recepción y lectura/consumo

Correlación con messages para respuesta de usuario

Correlación con eventtracks para visión de jornada

Para obtener más información, acceda a la discusión sobre el tema en nuestra comunidad o los videos en nuestro canal. 😃