Tickets - New October 15, 2025 13:32 This table contains records of ticket opening and closing events.Nominal latency: 5 minutesMaximum latency: 15 minutesObject: clients_trustedzone.deltashare_core.tickets_newRetention: 3 days (D-0, D-1, D-2)Initial historical load (in a separate object): to be agreedThe historical load must be integrated with Blip for availability; the object remains available for 7 days for ingestion.Key Concepts:TicketId and SequentialIdThe 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 UsersDistinct count of CustomerIdentity.Opened TicketsDistinct count of TicketId, grouped by StorageDate.Served TicketsCount of the OpenDate column.Closed TicketsCount of the ClosedDate column. Service MetricsService 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 TableWhen 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.netOwner: bottransbordo@msging.netselect * 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 AssociationAfter 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 MetricsIntroductionThis 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 AbandonedFiltering 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 ClosedFiltering 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 RateObjective: 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 Messages Notifications Hubspot Extension (Lanum) FAQ - Processes Untitled