Contacts

This table contains information about the contacts that pass through the bots.

Nominal latency: 5 minutes
Maximum latency: 15 minutes
Object: clients_trustedzone.deltashare_core.contact
Retention: 3 days (D-0, D-1, D-2)
Initial historical load (in separate object): 360 days

The historical load must be combined with Blip for availability; the object is available for 7 days for ingestion.

Important concepts:

Identity and Owner

Identity is the user ID, and Owner will be the bot name in the format "botid@msging.net". To search for information about the user, it is necessary to use the identity and Owner keys to retrieve the user’s context within the desired bot.

This table is usually used as a catalog of user information. It is recommended for storing fixed information related to the user, such as CPF, customer internal code, customer type. It will be used when, during the analysis of another table, it becomes necessary to associate information that is recorded under the user’s ID.

It is worth noting that global extras are extras associated with events/tracking, while in the contact table we will have extras associated with the user ID.

The recording of information associated with contacts is directly related to the bot’s development.

Analysis possibilities (TBD)

Campaign sender

When a campaign is triggered from the Growth screen, the sender can be identified via the Contact table. This information is not available in the Messages table.

First step: build the table to retrieve the ID of the sent message(s).

If necessary, a filter can be applied by template name or campaign name, as shown in the image below:

Query suggestion:

WITH disparosRealizados AS (
    SELECT
        decode(unbase64(Content), 'UTF-16LE') AS Message,
        Id AS SendId,
        FromIdentity AS BotId,
        ToIdentity AS UserId,
        json_tuple(Metadata, '#activecampaign.name') AS CampaignName,
        get_json_object(
            get_json_object(decode(unbase64(Content), 'UTF-16LE'), '$.template'),
            '$.name'
        ) AS templateName,
        Metadata
    FROM table.messages
    WHERE tenantId = 'tenantId'
      AND OwnerIdentity = 'BOTID@msging.net' -- Bot ID
      AND PpDomain = 'broadcast.msging.net'
      AND StorageDateDayBR = '2025-01-21'
)

SELECT *
FROM disparosRealizados
WHERE CampaignName RLIKE 'CAMPAIGN NAME';

Then, the campaign sender can be retrieved directly from the Contact table.

In this table, the send ID (campaignId) and the sender (campaignOriginator) are available inside the JSON in the Extras column.

campaignId and campaignOriginator

Below is an example query with only the necessary information to fetch these fields from the Contact table:

SELECT
    get_json_object(t2.Extras, '$.campaignId') AS campaignId,
    get_json_object(t2.Extras, '$.campaignMessageTemplate') AS MessageTemplate,
    get_json_object(t2.Extras, '$.campaignOriginator') AS campaignSender
FROM table.contact t2
WHERE get_json_object(t2.Extras, '$.campaignId') = 'xxxxxx-yyyyy-bbbb-b6a1354eac85'
  AND t2.Owner = 'BOTID@msging.net'
  AND t2.StorageDateDayBR = '2025-01-21';

Send information available in the Contact table

Important:

The campaignId field in contacts does not have the "activecampaign:" prefix that the ID in the Message table has, so it is necessary to perform a split in the message column to match it with the contact table ID.

Example:

Message table ID: activecampaign:123456

Contact table ID: 123456

Join example:

SELECT *
FROM disparosRealizados t1
INNER JOIN base.contact t2
    ON split(t1.SendId, '[:]')[1] = get_json_object(t2.Extras, '$.campaignId')
    AND t1.BotId = t2.Owner;

After cross-referencing the information from both tables, it is possible to correctly obtain the matched data:

Example view after joining the Messages and Contact tables to retrieve information about the send.

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