Mensagens

Decodificar a coluna "Content"

Abaixo mostraremos como decodificar a conteúdo da coluna, utilizando a linguagem SQL.

SELECT
  decode(unbase64(Contect), 'UTF-16LE') as mensagem,
  *
FROM tabela.messages

SELECT 
  OwnerIdentity,
  StorageDateDayBR as Data,
  COUNT(case when fromIdentity rlike '@msging.net' and toDomain != 'broadcast.msging.net' then fromIdentity else null end) as MensagensBot,
  COUNT(case when fromIdentity rlike '@wa.gw.msging.net' then fromIdentity else NULL end) as MensagensUsario,
  COUNT(distinct case when ToIdentity rlike '@wa.gw.msging.net' then ToIdentity else NULL end) as UsuárioUnico,
  COUNT(distinct case when FromIdentity rlike '@wa.gw.msging.net' then FromIdentity else NULL end) as UsuárioEngajado,
  count(*) as totalMensagens
  -- count(FromDomain) as qtde
FROM tabela.messages
WHERE TenantId = 'idContrato'
AND StorageDateDayBR >= '2024-09-19' 
GROUP BY OwnerIdentity, Data

select
  StorageDateDayBR,
  StorageDateBR,
  case when FromDomain = 'desk.msging.net' then "Atendente"
    when FromDomain != 'desk.msging.net' then "Usuario"
    end as MensagemDe,
  case when toDomain = 'desk.msging.net' then "Atendente"
    when ToDomain != 'desk.msging.net' then "Usuario"
    end as MensagemPara,
  FromIdentity,
  ToIdentity,
  decode(unbase64(Content), 'UTF-16LE') as Mensagem
  from Database.messages
    where TenantId = 'TenandId'
    and StorageDateDayBR = '2025-03-19'
    and (ToDomain = 'desk.msging.net'
      or FromDomain = 'desk.msging.net')

VOLUME DE MENSAGENS TRAFEGADAS (Usuário x Atendente)

Objetivo: Essa query calcula o volume total de mensagens trafegadas entre atendentes e usuários dentro da plataforma, permitindo analisar a distribuição das interações.

Métrica Calculada:

Total de Mensagens por Tipo de Remetente: Contabiliza quantas mensagens foram enviadas por atendentes e quantas por usuários.

Explicação da Query:

Classificação das Mensagens:

Se a coluna FromDomain for desk.msging.net, a mensagem é classificada como "Atendente".

Caso contrário, a mensagem é classificada como "Usuário".

Agrupamento e Contagem:

A query agrupa os registros pela classificação do remetente (MensagemDe).

Conta o número total de mensagens para cada grupo.

Query de Exemplo:

SELECT

    MensagemDe,

    COUNT(*) AS TotalMensagens

FROM (

    SELECT

        StorageDateDayBR,

        StorageDateBR,

        CASE

            WHEN FromDomain = 'desk.msging.net' THEN 'Atendente'

            ELSE 'Usuario'

        END AS MensagemDe,

        CASE

            WHEN ToDomain = 'desk.msging.net' THEN 'Atendente'

            ELSE 'Usuario'

        END AS MensagemPara,

        FromIdentity,

        ToIdentity,

        DECODE(UNBASE64(Content), 'UTF-16LE') AS Mensagem

    FROM clients_trustedzone.deltashare_core.messages

    WHERE TenantId = 'tenant' -- Cliente

    AND StorageDateDayBR = '2025-03-25' -- Data de referência

    AND (ToDomain = 'desk.msging.net' OR FromDomain = 'desk.msging.net')

) AS Subquery

GROUP BY MensagemDe;

Filtros Aplicados:

Considera apenas mensagens da empresa tenant.

Filtra mensagens enviadas na data 2025-03-25.

Considera apenas mensagens que tenham envolvimento com o domínio desk.msging.net (interações com atendentes).

Exemplo de Saída:

Análises no roadmap da documentação:

Volume de acessos e usuários por data

Volume de usuários novos e recorrentes

Taxa de Retorno de Usuários

Respostas a Notificações Ativas

Segmentando as mensagens associadas a um ticket do Blip Desk

Segmentando a conversa de um único usuário para fins regulatórios a partir do telefone

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