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. 😃