Pular para o conteúdo principal

Tickets – Novos

  • Atualizado

Essa tabela contém registros dos eventos de abertura e fechamento dos tickets.

Latência nominal: 5 minutos

Latência máxima: 15 minutos 

Objeto: clients_trustedzone.deltashare_core.tickets_new

Retenção: 3 dias (D-0, D-1, D-2)

Carga histórica inicial (em objeto separado): a combinar

A carga histórica deve ser combinada com a Blip para disponibilização, o objeto fica disponível por 7 dias para ingestão.

 

Conceitos importantes:

TicketId e SequentialId

A chave única para tratar tickets é sempre o TicketId, em alguns casos a plataforma pode reutilizar o SequentialId e encontramos a mesma numeração para tickets diferentes no mesmo bot. 

 

Possibilidades de análise:

Usuários Únicos Transbordados

Contagem distinta do CustomerIdentity.

Tickets Abertos

Contagem distinta do TicketId, agrupando pela coluna StorageDate

Tickets Atendidos

Contagem da coluna OpenDate

Tickets Fechados

Contagem da coluna ClosedDate.


 

Métricas de atendimento

Tempo de atendimento: Diferença entre data de abertura (openDate) e data de fechamento (closeDate).

Tempo de espera na fila: Diferença entre a data de criação do ticket (storageDate) e da data de abertura (openDate).

Tempo de primeira resposta: Diferença entre a data de abertura (openDate) e a primeira resposta (firstResponseDate).

 
 

Associar tabela de tickets com a tabela de Contatos

Quando o usuário sai do router e entra no bot de atendimento é gerado um novo contato, agora com o ID do usuário gerado dentro do bot de atendimento, portanto, só conseguimos fazer o relacionamento pelo critério de:

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

 

Exemplo de query com relacionamento entre tabelas

 

Query simples de como realizar o relacionamento entre tabelas.

with tickets(
select * from database.tickets_new
where StorageDateDayBR >= '2025-01-09'
),
contatos(
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_Origem, RouterBotId_Origem),
  t2.Extras as ExtrasContato,
  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

 

Importante: O contato referente ao atendimento, é criado quando o ticket é aberto, ou seja, quando o usuário sai do contexto do router e entra no bot de atendimento, portanto, caso existam casos em que um ticket foi criado em uma data (ex.: 01/01/2025) e o fechamento realizado em uma data mais a frente (ex.: 31/01/2025), as informações do contato, estarão disponíveis próxima a data de criação do ticket, no nosso exemplo, 01/01/2025.

 

Possibilidade de obter informações detalhadas após relacionar as tabelas

Após o relacionamento, é possível obter, por exemplo as informações do contexto do usuário dentro do roteador do bot, como, por exemplo informações adicionais de campanha.

Importante: O nome dos campos e as informações salvas, não são salvas de maneira automática, demandam desenvolvimento para indicar quais campos devem ser salvos nos extras.

 

Abaixo JSON de exemplo dos extras de um contato:

{
    "tunnel.owner": "botIdroteador@msging.net",
    "tunnel.originator": "55319999_telefone@wa.gw.msging.net",
    "campaignId": "IDCampanha",
    "campaignMessageTemplate": "nomeDoTemplate",
    "segmento": "segmento",
    "elegivelcartao": "false",
    "dataexpiracao": "2025-2-12",
    "telefone_employee": "",
    "activeClient": "true",
    "AdvisorCode": "null",
    "contactid": "asdasd",
    "filaAtendimentoBlip": "",
    "pldfinancialapplications": "null",
    "cpf": "00000000000",
    "contasMesmoCelular": "null",
    "usuarioCampanha": "false",
    "assessorOnline": "false"
}

 

Como chegar nas métricas do Blip Desk

Métricas temporais de monitoramento do Blip Desk

 Como chegar nas métricas do Blip Desk

 

 

Introdução

Esta documentação descreve o processo de transformação e extração de métricas a partir da tabela tickets_new no Delta Share. O objetivo é filtrar os dados corretamente e calcular as métricas de tickets Abertos, Perdidos, Abandonados, Finalizados e Fechados.

 

Calculo Abertos, Perdidos e Abandonados

A filtragem dos dados deve ser realizada com base na coluna storageDate, que representa o momento em que o cliente entra no transbordo. Caso o Blip esteja configurado no horário de Brasília, a conversão para esse fuso horário deve ser aplicada ao filtro.

 

Exemplo de Query para Filtragem

SELECT

    COUNT(storageDate) AS Abertos,

    SUM(CASE WHEN agentIdentity IS NULL AND Status = 'ClosedClient' THEN 1 ELSE NULL END) AS Perdidos,

    SUM(CASE WHEN agentIdentity IS NOT NULL AND Status = 'ClosedClient' THEN 1 ELSE NULL END) AS Abandonados

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';

 

Cálculo das Métricas

As métricas são calculadas conforme os seguintes critérios:

Abertos: Contagem total de registros onde storageDate atende ao critério do filtro.

Perdidos: Tickets que não tiveram interação com um agente (agentIdentity IS NULL) e foram encerrados pelo cliente (Status = 'ClosedClient').

Abandonados: Tickets que tiveram interação com um agente (agentIdentity IS NOT NULL) e foram encerrados pelo cliente (Status = 'ClosedClient').

 

Cálculo de Finalizados e Fechados

Para obter os tickets Finalizados e Fechados, o filtro deve ser aplicado na coluna closeDate, garantindo que a conversão para o horário de Brasília seja feita corretamente.

 

Exemplo de Query para Finalizados e Fechados

SELECT

    SUM(CASE WHEN Status IN ('ClosedAttendant', 'Transferred', 'Waiting') THEN 1 ELSE 0 END) AS Finalizados,

    COUNT(storageDate) AS Fechados

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';

 

Critérios para Cálculo:

Finalizados: Tickets que tiveram o status ClosedAttendant, Transferred ou Waiting na data filtrada.

Fechados: Contagem total de tickets com closeDate dentro do período especificado.

 

Considerações

O fuso horário de Brasília (‘America/Sao_Paulo’) deve ser aplicado no closeDate antes da filtragem.

O OwnerIdentity deve ser especificado corretamente para garantir que apenas tickets relevantes sejam incluídos na análise.

A filtragem por data deve ser ajustada conforme a necessidade da análise.

 

Conclusão

Este procedimento garante que os dados sejam filtrados corretamente, assegurando que as métricas sejam extraídas de maneira consistente e confiável. Caso haja necessidade de ajustes na consulta ou na conversão de fuso horário, verifique os requisitos do ambiente e do Blip antes da execução

 

TAXA DE RECORRÊNCIA

Objetivo: Essa query calcula a Taxa de Recorrência dos usuários no atendimento humano, ou seja, a proporção de usuários que abriram mais de um ticket dentro de um intervalo de datas especificado.


 

Fórmula Utilizada: A taxa de recorrência é calculada com a seguinte fórmula:

Taxa de Recorrência = (Usuários com mais de um ticket / Total de usuários) * 100


 

Explicação da Query:

Filtragem de Tickets (ContagemTickets) A primeira parte da query agrupa os tickets por customerIdentity (usuário) e conta quantos tickets cada um abriu dentro do intervalo de datas escolhido.


 

Filtros aplicados:

TenantId = 'tenantid': Considera apenas tickets da empresa "Empresa".

StorageDateDayBR BETWEEN '2025-03-25' AND '2025-03-25': Considera apenas tickets criados dentro do intervalo de datas escolhido.


 

Resultado esperado:

customerIdentity: user_123, qtd_tickets: 1

customerIdentity: user_456, qtd_tickets: 3

customerIdentity: user_789, qtd_tickets: 2


 

Contagem de Usuários Recorrentes (UsuariosRecorrentes) Essa parte da query conta quantos usuários abriram mais de um ticket dentro do intervalo selecionado.

Filtro aplicado:

qtd_tickets > 1

Resultado esperado:

usuarios_recorrentes = 2 (caso user_456 e user_789 tenham aberto mais de um ticket).


 

  1. Contagem Total de Usuários (TotalUsuarios) Essa parte da query conta o total de usuários distintos que abriram pelo menos um ticket dentro do intervalo de datas.

Resultado esperado:

total_usuarios = 3 (se tivermos os três users do exemplo anterior).


 

  1. Cálculo da Taxa de Recorrência A query final divide usuarios_recorrentes pelo total_usuarios e multiplica por 100 para obter a porcentagem:

Exemplo de resultado:

(2 / 3) * 100 = 66.67%

O ROUND(..., 2) garante que o resultado tenha apenas duas casas decimais.

Exemplo de Saída: TaxaRecorrencia: 66.67%


 

Essa query é útil para avaliar a eficácia do suporte, pois uma taxa de recorrência alta pode indicar problemas na resolução de tickets.


 

 

TEMPOS DE ATENDIMENTO

Objetivo: Essa query calcula os tempos médios de atendimento em diferentes fases do processo, permitindo avaliar a eficiência do suporte ao cliente.


 

Métricas Calculadas:

Tempo Médio de Espera na Fila: Tempo desde a criação do ticket até a abertura do atendimento.

Tempo Médio até a Primeira Resposta: Tempo entre a abertura do atendimento e a primeira resposta do atendente.

Tempo Médio de Espera Total: Tempo entre a criação do ticket e a primeira resposta do atendente.

Tempo Médio de Atendimento: Tempo entre a primeira resposta e o fechamento do ticket.


 

Explicação da Query:

Tempo Médio de Espera na Fila:

Cálculo: AVG(UNIX_TIMESTAMP(openDate) - UNIX_TIMESTAMP(storageDate))

Representa o tempo que um ticket passa aguardando antes de ser atendido.


 

Tempo Médio até a Primeira Resposta:

Cálculo: AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(openDate))

Mede quanto tempo um atendente demora para responder o ticket pela primeira vez.


 

Tempo Médio de Espera Total:

Cálculo: AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(storageDate))

Indica o tempo total que o cliente esperou desde a criação do ticket até a primeira resposta.


 

Tempo Médio de Atendimento:

Cálculo: AVG(UNIX_TIMESTAMP(closeDate) - UNIX_TIMESTAMP(firstResponseDate))

Mede o tempo decorrido entre a primeira resposta e o fechamento do ticket.


 

Formatando os Resultados:

Os tempos são convertidos para o formato HH:MM:SS para melhor compreensão.

O LPAD() é utilizado para garantir que os valores tenham dois dígitos.


 

 

Query de Exemplo:

SELECT 

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(openDate) - UNIX_TIMESTAMP(storageDate)) / 3600), 2, '0') || ':' ||

    LPAD(FLOOR((AVG(UNIX_TIMESTAMP(openDate) - UNIX_TIMESTAMP(storageDate)) % 3600) / 60), 2, '0') || ':' ||

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(openDate) - UNIX_TIMESTAMP(storageDate)) % 60), 2, '0') AS Tempo_medio_de_espera_na_fila,


 

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(openDate)) / 3600), 2, '0') || ':' ||

    LPAD(FLOOR((AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(openDate)) % 3600) / 60), 2, '0') || ':' ||

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(openDate)) % 60), 2, '0') AS Tempo_medio_ate_primeira_resposta,


 

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(storageDate)) / 3600), 2, '0') || ':' ||

    LPAD(FLOOR((AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(storageDate)) % 3600) / 60), 2, '0') || ':' ||

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(firstResponseDate) - UNIX_TIMESTAMP(storageDate)) % 60), 2, '0') AS Tempo_medio_de_espera_total,  


 

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(closeDate) - UNIX_TIMESTAMP(firstResponseDate)) / 3600), 2, '0') || ':' ||

    LPAD(FLOOR((AVG(UNIX_TIMESTAMP(closeDate) - UNIX_TIMESTAMP(firstResponseDate)) % 3600) / 60), 2, '0') || ':' ||

    LPAD(FLOOR(AVG(UNIX_TIMESTAMP(closeDate) - UNIX_TIMESTAMP(firstResponseDate)) % 60), 2, '0') AS Tempo_medio_de_atendimento

 

FROM clients_trustedzone.deltashare_core.tickets_new

WHERE OwnerIdentity = 'botid@msging.net'

AND DATE(FROM_UTC_TIMESTAMP(closeDate, 'America/Sao_Paulo')) BETWEEN '2025-03-25' AND '2025-03-25';


 Filtros Aplicados:

Considera apenas tickets da empresa botid@msging.net.

Filtra tickets fechados entre 2025-03-25 e 2025-03-25.

 

Exemplo de Saída:

Tempo_medio_de_espera_na_fila: 00:05:30

Tempo_medio_ate_primeira_resposta: 00:02:45

Tempo_medio_de_espera_total: 00:08:15

Tempo_medio_de_atendimento: 00:20:10


 

Essa query é essencial para monitorar e otimizar o tempo de resposta e resolução dos chamados, garantindo um melhor atendimento ao cliente.

Para mais informações, acesse a discussão sobre o assunto em nossa comunidade ou os vídeos no nosso canal. 😃

Artigos relacionados