Do envio ao read: relacionando Marketing Cloud, templates, contatos e eventos de tracking

Quando trabalhamos com campanhas de WhatsApp em escala, saber apenas que uma mensagem foi disparada não é suficiente.

Em uma operação real, rapidamente surgem perguntas como:

Qual template foi utilizado? Para qual cliente a mensagem foi enviada? Qual linha oficial realizou o disparo?

Foi a partir dessa necessidade que comecei a estruturar uma visão de rastreabilidade dos disparos de WhatsApp utilizando Salesforce Data Cloud e SQL. O resultado foi uma consulta capaz de relacionar informações do envio realizado pelo Marketing Cloud com os eventos posteriores de tracking.

O problema

Os dados necessários existiam, porém não estavam todos no mesmo registro.

De um lado, temos informações relacionadas ao envio:

cliente;
contato;
telefone;
CPF/CNPJ;
operação;
template;
Journey Activity;
data do disparo.

Do outro lado, temos os eventos gerados pelo WhatsApp:

sent
delivered
read
failed
error

Além deles, também podemos ter informações como:

Message ID;
número destinatário;
Sender ID;
código de erro;
descrição do erro;
data de cada evento.

Visão da solução

A lógica criada ficou aproximadamente assim:

Marketing Cloud
    ↓
Message Engagement
    ↓
Dados do envio
    ↓
Normalização do telefone
    ↓
Localização do evento SENT mais próximo
    ↓
Message ID
    ↓
Eventos de Tracking
    ↓
SENT → DELIVERED → READ → FAILED

O ponto mais importante dessa estratégia foi encontrar um identificador capaz de conectar toda a jornada da mensagem.

Nesse caso, chegamos ao:

message_id

Porém, antes de obter esse ID, havia outro desafio.

Precisávamos descobrir qual evento sent pertencia a determinado disparo.

1. Identificando os disparos

A primeira CTE da consulta foi chamada de:

WITH envios AS (...)

Ela busca os dados relacionados aos disparos.

Entre as informações recuperadas estão:

s.ssot__Id__c AS envio_id,
s.ssot__EngagementDateTm__c AS data_envio,
s.ssot__IndividualId__c AS contact_id,
c.Name__c AS nome_cliente,
c.MobilePhone__c AS telefone_contact,
c.CPF_CNPJ_c__c AS cpf_cnpj,
c.Operacao_c__c AS operacao

Também relacionamos o disparo ao template:

s.ssot__EngagementAssetId__c AS template_id,
t.ssot__Name__c AS template_nome,
t.ssot__EngagementAssetTypeId__c AS tipo_template

Isso já permite responder uma pergunta importante:

Qual cliente recebeu qual template?

Também conseguimos obter a atividade da jornada:

s.ssot__MarketJourneyActivityId__c AS journey_activity_id

Criando a base para futuramente expandir a rastreabilidade até Journey Builder e campanhas.

2. Um problema clássico: telefone

Essa foi uma das partes mais interessantes do desenvolvimento.

O telefone do contato poderia aparecer em formatos diferentes.

Por exemplo:

5514999999999
14999999999
1499999999
+55 (14) 99999-9999

Para relacionar corretamente envio e tracking, foi necessário normalizar esses números.

A consulta remove primeiro todos os caracteres que não sejam números:

REGEXP_REPLACE(
  c.MobilePhone__c,
  '[^0-9]',
  '',
  'g'
)

Depois avaliamos o tamanho do telefone.

Por exemplo, caso recebamos:

551499999999

e seja identificado que está faltando o nono dígito, realizamos o tratamento:

SUBSTRING(
  REGEXP_REPLACE(c.MobilePhone__c, '[^0-9]', '', 'g'),
  3,
  2
)
|| '9' ||
RIGHT(
  REGEXP_REPLACE(c.MobilePhone__c, '[^0-9]', '', 'g'),
  8
)

Assim conseguimos criar:

telefone_normalizado

Essa coluna passa a funcionar como uma chave auxiliar para relacionar diferentes fontes.

3. Capturando os eventos do WhatsApp

Depois criamos outra CTE:

tracking AS (...)

Ela representa os eventos retornados pela camada de tracking.

Entre os campos recuperados:

x.ssot__EngagementDateTm__c AS data_evento,
x.ssot__EngagementChannelActionId__c AS evento,
x.Mobile_Number__c AS telefone_cliente,
x.Sender_ID__c AS linha_oficial,
x.ssot__EngagementAssetId__c AS message_id

Aqui começam a aparecer eventos como:

sent
delivered
read
failed
error

Também capturamos informações importantes para troubleshooting:

x.ssot__MessagingErrorCode__c AS codigo_erro,
x.ssot__ErrorMessageText__c AS descricao_erro

Isso significa que a mesma estrutura utilizada para acompanhamento de campanhas pode ajudar também na investigação de falhas.

4. Como descobrir qual sent pertence ao envio?

Esse foi um dos pontos centrais da solução.

Apenas relacionar pelo telefone não seria suficiente.

Um mesmo cliente poderia receber diversas mensagens ao longo do dia.

Por isso, além do telefone normalizado, utilizamos uma janela temporal.

AND tr.data_evento >=
  e.data_envio - INTERVAL '5 minutes'

AND tr.data_evento <=
  e.data_envio + INTERVAL '5 minutes'

Ou seja:

5 minutos antes do envio
      ↓
   DISPARO
      ↓
5 minutos depois do envio

Dentro desses candidatos, selecionamos o evento sent cuja data está mais próxima da data original do disparo.

Para isso utilizamos:

ROW_NUMBER() OVER (
  PARTITION BY e.envio_id
  ORDER BY
      ABS(
          EXTRACT(
              EPOCH FROM (
                  tr.data_evento - e.data_envio
              )
          )
      )
) AS ordem

Depois:

WHERE ordem = 1

Dessa maneira, cada envio fica relacionado ao evento sent temporalmente mais próximo.

5. Encontrando o Message ID

Depois dessa associação, obtemos uma informação extremamente importante:

message_id

A partir dele, não precisamos mais depender do telefone ou da proximidade de horário.

Podemos relacionar diretamente todos os eventos daquela mensagem:

INNER JOIN tracking tr
  ON e.message_id = tr.message_id

Agora conseguimos reconstruir algo parecido com:

Cliente: João

Template:
cobranca_whatsapp_01

Disparo:
10:01:03

SENT:
10:01:05

DELIVERED:
10:01:07

READ:
10:03:21

Ou, em um cenário de erro:

SENT
↓
FAILED
↓
Código do erro
↓
Descrição do erro

Essa é a parte que transforma os dados técnicos em rastreabilidade operacional.

Resultado final

A consulta consegue retornar informações como:

data_envio
data_evento
evento
telefone_cliente
linha_oficial
template_nome
nome_cliente
cpf_cnpj
operacao
template_id
contact_id
tipo_template
journey_activity_id
message_id
codigo_erro
descricao_erro
origem_envio
origem_tracking

Próximo passo

Hoje essa visão já permite consultar essas informações dentro do ecossistema Salesforce.

O próximo objetivo é levar essa rastreabilidade para nossa estrutura interna de dados.

A arquitetura começa a evoluir para algo como:

Salesforce Marketing Cloud
        ↓
Salesforce Data Cloud
        ↓
SQL / Tracking
        ↓
Integração
        ↓
Banco de Dados interno
        ↓
Dashboard / Indicadores / Auditoria

A ideia é reduzir a necessidade de acessar diferentes ferramentas para investigar um disparo.

Em uma única fonte poderemos acompanhar toda a jornada da mensagem.

O que aprendi

Essa implementação me mostrou que um dos maiores desafios de dados nem sempre é simplesmente consultar uma tabela.

Muitas vezes o trabalho está em descobrir como diferentes eventos se relacionam.

Nesse projeto utilizamos:

CTEs → organização da consulta.

REGEXP_REPLACE → tratamento dos telefones.

CASE → normalização.

ROW_NUMBER() → seleção do melhor candidato.

EXTRACT(EPOCH) → comparação temporal.

JOIN → relacionamento entre diferentes informações.

Message ID → reconstrução do ciclo completo da mensagem.

E o resultado deixou de ser somente uma consulta SQL.

Passou a ser uma forma de acompanhar o ciclo de vida de uma comunicação enviada ao cliente.

Conclusão

Criar rastreabilidade para WhatsApp dentro do Salesforce Data Cloud abriu novas possibilidades para nossa operação.

Saímos de:

“A campanha disparou.”

para:

“Esse cliente recebeu esse template, por essa linha, nesse horário, a mensagem foi entregue, posteriormente lida e esse é o identificador que acompanha todo o processo.”

É uma mudança relativamente simples de explicar, mas extremamente relevante quando pensamos em dados, governança, atendimento, campanhas e tomada de decisão.

E esse projeto ainda está evoluindo. 🚀

Tecnologias utilizadas: Salesforce Data Cloud • Marketing Cloud • WhatsApp • SQL • Data Model Objects • Journey Builder • Integração de Dados