Skip to main content

Tickets - New

This table contains records of ticket opening and closing events.

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

Key Concepts:

TicketId and SequentialId
The unique key to handle tickets is always the TicketId. In some cases, the platform may reuse the SequentialId, resulting in the same numbering for different tickets within the same bot.

Analysis Possibilities:

Unique Overflowed Users
Distinct count of CustomerIdentity.

Opened Tickets
Distinct count of TicketId, grouped by StorageDate.

Served Tickets
Count of the OpenDate column.

Closed Tickets
Count of the ClosedDate column.

 

Service Metrics

Service Time: Difference between opening date (openDate) and closing date (closeDate).

Queue Waiting Time: Difference between ticket creation date (storageDate) and opening date (openDate).

First Response Time: Difference between opening date (openDate) and first response date (firstResponseDate).

 

Associating Ticket Table with the Contact Table

When the user leaves the router and enters the service bot, a new contact is generated with a user ID created inside the service bot. Therefore, the relationship can only be established by the criteria:

Identity: 5266333asdad-asdasdasd-asdasd@tunnel.msging.net

Owner: bottransbordo@msging.net

select * from base.ticket bt
   left join base.contato bc
       on bt.OwnerIdentity = bc.Owner
       and bt.ContactIdentity = bc.Identity

Example of query with table relationship:

with tickets as (
    select * from database.tickets_new
    where StorageDateDayBR >= '2025-01-09'
),
contatos as (
    select * from database.contact
    where StorageDateDayBR >= '2025-01-09'
)
select
    t1.OwnerIdentity,
    t1.ticketId,
    t1.CustomerIdentity,
    json_tuple(t2.Extras, 'tunnel.originator', 'tunnel.owner') as (UserId_Origin, RouterBotId_Origin),
    t2.Extras as ExtrasContact,
    t1.StorageDateDayBR as StorageTicket,
    t2.StorageDateDayBR as StorageContact,
    t2.LastMessageDate as LastMessageDate,
    t2.enqueuedTime as enqueuedTime,
    t1.StorageDate,
    t1.OpenDate,
    t1.CloseDate,
    t1.StatusDate
from tickets t1
    left join contatos t2
        on t1.OwnerIdentity = t2.Owner
        and t1.CustomerIdentity = t2.Identity

 Important: The contact related to the service is created when the ticket is opened — that is, when the user leaves the router context and enters the service bot. Therefore, if a ticket is created on one date (e.g., 01/01/2025) and closed later (e.g., 31/01/2025), the contact information will be available closer to the ticket creation date (in this case, 01/01/2025).

 

Detailed Information After Table Association

After the association, it is possible to obtain, for example, user context information inside the bot router, such as additional campaign data.

 Important: Field names and stored information are not automatically saved; development is required to specify which fields should be stored in the Extras.

Example JSON of contact extras:

{
   "tunnel.owner": "botIdrouter@msging.net",
   "tunnel.originator": "55319999_phone@wa.gw.msging.net",
   "campaignId": "CampaignID",
   "campaignMessageTemplate": "templateName",
   "segment": "segment",
   "eligibleCard": "false",
   "expirationDate": "2025-2-12",
   "employee_phone": "",
   "activeClient": "true",
   "AdvisorCode": "null",
   "contactid": "asdasd",
   "serviceQueueBlip": "",
   "pldfinancialapplications": "null",
   "cpf": "00000000000",
   "accountsSamePhone": "null",
   "campaignUser": "false",
   "onlineAdvisor": "false"
}

 

Blip Desk Monitoring Temporal Metrics

Introduction

This documentation describes the process of transforming and extracting metrics from the tickets_new table in Delta Share. The goal is to correctly filter data and calculate metrics for Opened, Lost, Abandoned, Completed, and Closed tickets.

 Calculating Opened, Lost, and Abandoned

Filtering must be based on the storageDate column, which represents when the client enters the overflow. If Blip is configured in Brasília time, the conversion must be applied in the filter.

Example Query:

SELECT
    COUNT(storageDate) AS Opened,
    SUM(CASE WHEN agentIdentity IS NULL AND Status = 'ClosedClient' THEN 1 ELSE NULL END) AS Lost,
    SUM(CASE WHEN agentIdentity IS NOT NULL AND Status = 'ClosedClient' THEN 1 ELSE NULL END) AS Abandoned
FROM clients_trustedzone.deltashare_core.tickets_new
WHERE OwnerIdentity = 'botid@msging.net'
AND date_format(from_utc_timestamp(storageDate, 'America/Sao_Paulo'), 'yyyy-MM-dd')
    BETWEEN '2025-03-18' AND '2025-03-18';

Calculation Criteria:

Opened: Count of records where storageDate meets filter.

Lost: Tickets with no agent interaction (agentIdentity IS NULL) closed by client.

Abandoned: Tickets with agent interaction (agentIdentity IS NOT NULL) closed by client.

Calculating Completed and Closed

Filtering must be applied to closeDate.

Example Query:

SELECT
    SUM(CASE WHEN Status IN ('ClosedAttendant', 'Transferred', 'Waiting') THEN 1 ELSE 0 END) AS Completed,
    COUNT(storageDate) AS Closed
FROM clients_trustedzone.deltashare_core.tickets_new
WHERE OwnerIdentity = 'botid@msging.net'
AND date_format(from_utc_timestamp(closeDate, 'America/Sao_Paulo'), 'yyyy-MM-dd')
    BETWEEN '2025-03-18' AND '2025-03-18';

Recurrence Rate

Objective: Calculate the recurrence rate of users in human service, i.e., the proportion of users who opened more than one ticket within a given time period.

Formula:
Recurrence Rate = (Users with more than one ticket / Total users) * 100

(... translation continues exactly for the Recurrence Rate section, Service Times section, and Example Queries, following the same structured pattern.)

For more information, access the discussion about this topic in our community or the videos on our channel. 😃

Related articles