Notifications

This table contains records of the statuses/events associated with messages

Nominal latency: 5 minutes
Maximum latency: 15 minutes
Object: clients_trustedzone.deltashare_core.notifications
Retention: 3 days (D-0, D-1, D-2)
Initial historical load (in a separate object): 360 days
The historical load must be combined with Blip for availability; the object is available for 7 days for ingestion.

[Exclusive WhatsApp Channel] Rules for statuses associated with Meta template sends

 New message statuses may be updated in the tables with information up to 30 days later.

Ex.: if the user receives a send today but only reads it 28 days later, the message status will be updated next month.

The consumption/reading status will only be available when the user has this configuration enabled on their WhatsApp.

Notifications

Within the notifications table, you can view the send information for all active notifications, and the status of these sends will be recorded in the "Event" column.

The statuses can be:

accepted → the message was accepted

received → message received by the user

consumed → message read by the user

failed → a failure occurred in sending

To obtain more detailed send information, such as the template name, responsible party, etc., it is necessary to relate the notifications table with the messages table.

Which keys are needed to relate one table to the other?

Within the "Metadata" column, there are unique objects that can be used to relate to the messages table.

Example JSON in this column:

{
  "$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"
}

It is necessary to save the #message.uniqueId object, as it will be the main key for the relationship with the messages table.

Another important column is Id, which is in the messages table. This refers to the send Id. If the send was mass, all messages in the "messages" column will have this Id. If it was individual, the Id will only appear in that record.

Example query to list information needed to relate with the messages table:

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

In the messages table, we relate the message_uniqueID column generated in the previous query with the InternalId of the messages table.

Example query:

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

After performing the relationship, you can obtain send information directly from the Content column and additional info from Metadata.

Blip Desk Send

If the notification was sent via Blip Desk, you can get information about the responsible sender (#activecampaign.attendanceRedirect) and whether it was actually sent by Blip Desk (#activecampaign.stateId, always starting with desk:), as well as the campaign name (#activecampaign.name) from the Metadata column.

Example JSON from a Blip Desk send:

{
  "$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"
}

The template used can be obtained directly from the Content column.

Example of a test template called "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": "Hello! This is {{1}}, from XXXXX {{2}}.", "example": {
          "bodyText": [["name", "location"]]
        }
      }
    ]
  }
}

Mass Send

For mass sends, the relationship of tables uses the same columns. The difference is that the Id in notifications will be repeated for all recipients. However, when related with InternalId in messages, the relationship is handled individually without problems.

Example query to relate Messages and 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 'Yes' ELSE 'No' END AS DisparoDesk,
  CASE WHEN t1.Metadata RLIKE 'desk:' THEN t1.Metadata['#activecampaign.attendanceRedirect'] ELSE 'MassSend' 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';

Volume of templates sent

This section describes how to obtain the number of templates sent by a chatbot, relating messages with notification events, grouped by template name.

templateName: template name

QuantitySent: total sends of the template

OwnerIdentity: bot identifier

StorageDate: message storage date

toIdentity: recipient

campaignName: associated campaign name

DisparoDesk: indicates if sent manually

SentBy: origin of send

Status: notification status

UTF-16LE decoding is used, and manual sends (Desk) are differentiated from mass sends.

Status of templates sent

This obtains the number of messages per template and status:

templateName

Event → notification status

Quantity → total events per status

Same filters and decoding as before apply.

Duplicate templates sent

Measures:

Total template messages by bot and status

Users who received these notifications

Unique users per template

Option to segment by day or other time interval

Example SQL for deduplication and counting users:

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;

Objective:
Count how many templates each user received in a single day via active notifications sent by a specific bot.

Documentation roadmap analyses:

Status of templates sent

Failures and reasons

Time to receive and read/consume

Correlation with messages for user response

Correlation with eventtracks for journey view

For more information, visit the discussion on the subject in our community or the videos on our channel. 😊