Messages

This table contains records of messages exchanged between users and the intelligent contact, as well as system messages between subbots.

Nominal latency: 5 minutes
Maximum latency: 15 minutes
Object: clients_trustedzone.deltashare_core.messages
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.

The message content is available in the "Content" column of the table.

IMPORTANT: Messages in this table are masked in base64, meaning decoding is required to view their content.

"Content" Column Encoded in Base64

Decoding the "Content" Column

Below is an example of how to decode the column content using SQL: "Content" Column Encoded in Base64

SELECT
  decode(unbase64(Content), 'UTF-16LE') AS message,
  *
FROM table.messages

"Content" Column Decoded with unbase64

Column List

The column list, dictionary, and metadata available for each table can be found in an external document. 

Example Queries for Data Analysis

Volume of Messages Exchanged (by Bot and User)

For the example below, we will use the following columns from the "Messages" table to generate views of messages sent by the bot and the user, grouped by day:

OwnerIdentity → Bot ID (format: id@msging.net). Example: botid@msging.net

StorageDateDayBR → Message date in UTC-3 (Brasília Official Time)

FromIdentity → Message sender. Example: botid@msging.net, 5531999999999@wa.gw.msging.net, etc.

ToIdentity → Message recipient, stored in id@domain format. Example: botid@msging.net, 5531999999999@wa.gw.msging.net, etc.

ToDomain → Domain of the recipient’s channel. Example: broadcast.msging.net, wa.gw.msging.net, 0mn.io, googlersc.gw.msging.net, etc.

SELECT 
  OwnerIdentity,
  StorageDateDayBR AS Date,
  COUNT(CASE WHEN FromIdentity RLIKE '@msging.net' AND ToDomain != 'broadcast.msging.net' THEN FromIdentity ELSE NULL END) AS BotMessages,
  COUNT(CASE WHEN FromIdentity RLIKE '@wa.gw.msging.net' THEN FromIdentity ELSE NULL END) AS UserMessages,
  COUNT(DISTINCT CASE WHEN ToIdentity RLIKE '@wa.gw.msging.net' THEN ToIdentity ELSE NULL END) AS UniqueUser,
  COUNT(DISTINCT CASE WHEN FromIdentity RLIKE '@wa.gw.msging.net' THEN FromIdentity ELSE NULL END) AS EngagedUser,
  COUNT(*) AS TotalMessages
FROM table.messages
WHERE TenantId = 'contractId'
  AND StorageDateDayBR >= '2024-09-19' 
GROUP BY OwnerIdentity, Date

Calculating Time Between Messages

The StorageDateBR column contains the date and time of messages; it can be used to sort and calculate the difference between message times.

It is important to group by user and bot ID to segment conversations.

Analysis options:

Time between all messages to understand user behavior throughout the conversation.

Time from the start to the end of a conversation, whether within the bot or support.

Response time for a NA or other critical communication, such as a field waiting for a document.

When performing time-based analyses, we recommend building a boxplot, median, and quartiles. It is common for the mean to be skewed by outlier users, which may impact decision-making.

Separating Conversations within the Desk

Example query to obtain conversations in Blip Desk between Agent and User:

SELECT
  StorageDateDayBR,
  StorageDateBR,
  CASE WHEN FromDomain = 'desk.msging.net' THEN 'Agent'
       ELSE 'User'
  END AS MessageFrom,
  CASE WHEN ToDomain = 'desk.msging.net' THEN 'Agent'
       ELSE 'User'
  END AS MessageTo,
  FromIdentity,
  ToIdentity,
  decode(unbase64(Content), 'UTF-16LE') AS Message
FROM Database.messages
WHERE TenantId = 'TenantId'
  AND StorageDateDayBR = '2025-03-19'
  AND (ToDomain = 'desk.msging.net' OR FromDomain = 'desk.msging.net')

VOLUME OF MESSAGES EXCHANGED (User x Agent)

Objective: This query calculates the total volume of messages exchanged between agents and users within the platform, allowing analysis of interaction distribution.

Calculated Metric:

Total messages by sender type: counts how many messages were sent by agents versus users.

Query Explanation:

Message Classification:

If FromDomain = desk.msging.net, the message is classified as "Agent".

Otherwise, it is classified as "User".

Grouping and Counting:

The query groups records by sender classification (MessageFrom) and counts total messages per group.

SELECT
    MessageFrom,
    COUNT(*) AS TotalMessages
FROM (
    SELECT
        StorageDateDayBR,
        StorageDateBR,
        CASE
            WHEN FromDomain = 'desk.msging.net' THEN 'Agent'
            ELSE 'User'
        END AS MessageFrom,
        CASE
            WHEN ToDomain = 'desk.msging.net' THEN 'Agent'
            ELSE 'User'
        END AS MessageTo,
        FromIdentity,
        ToIdentity,
        DECODE(UNBASE64(Content), 'UTF-16LE') AS Message
    FROM clients_trustedzone.deltashare_core.messages
    WHERE TenantId = 'tenant'
      AND StorageDateDayBR = '2025-03-25'
      AND (ToDomain = 'desk.msging.net' OR FromDomain = 'desk.msging.net')
) AS Subquery
GROUP BY MessageFrom;

Applied Filters:

Only considers messages from the company tenant.

Filters messages sent on 2025-03-25.

Only considers messages involving the desk.msging.net domain (interactions with agents).

Example Output:

Analyses in the documentation roadmap:

Access volume and users by date

Volume of new and recurring users

User return rate

Responses to active notifications

Segmenting messages associated with a Blip Desk ticket

Segmenting a single user’s conversation for regulatory purposes based on phone number

If you want, I can also create a concise, formatted version in English highlighting just the key points and example queries, which is easier for documentation or training purposes. Do you want me to do that?