Visão Geral da Arquitetura CRM
Documentação técnica completa da arquitetura de dados, fluxo do lead e operação de CRM da UNIBF. Esta documentação serve como referência para engenharia de dados, modelagem BigQuery, operação de CRM e marketing.
Evento → Lead → Enriquecido → Lead Real → Venda
O dado nasce como um evento comportamental no site, é capturado como lead, enriquecido com dados de CRM e mídia, filtrado por regras de negócio e transformado em base elegível para disparo — o Lead_Real. A conversão é registrada em unibf_vendas_v2, fechando o ciclo.
Papel de Cada Camada
Ferramentas e Infraestrutura
Armazém principal de dados: gtm-5zhzjzt-njk1n / performance_v4. Hospeda f_eventos, f_leads e f_vendas_v2.
Banco operacional para leads e vendas. Sincronizado com BigQuery. Origem dos dados para leads_enriquecidos_v2 e unibf_vendas_v2.
Headless BI para tabelas de campanha (report_tables_campaign), templates (dim_templates) e atendimento (dim_customer_support_request).
Automação de fluxos: criação de lead no CRM, atualização de status de aluno, enriquecimento via API OmniChat, sincronização com Active Campaign.
Automação e CRMMapa das Bases de Dados
Descrição completa de cada tabela, seu propósito, momento de entrada no fluxo e plataforma de hospedagem.
f_eventos
Tabela de fatos de eventos comportamentais. Registra toda ação do usuário no site: pageview, form_submit, generate_lead, whatsapp_click. É o dado mais granular e mais bruto do ecossistema. Entra no fluxo no momento em que o usuário acessa qualquer propriedade da UNIBF.
unibf_leads_v2
Captura inicial do lead vindo de formulários das landing pages, site, quiz, ENEM, polos e orgânico. Contém dados informados diretamente pelo usuário, sem cruzamento com vendas ou normalização completa. Entra no fluxo no momento do submit do formulário.
leads_enriquecidos_v2
Versão tratada dos leads: telefone normalizado, e-mail em lowercase, modalidade e curso corrigidos para IDs canônicos, recorrência identificada, consent LGPD registrado, campos de status e fechamento do CRM (RD Station). Entra no fluxo após o processo de enriquecimento e padronização.
Lead_Real
~241.016 linhas × 53 colunas. Resultado de quatro etapas: (1) exportação do Supabase, (2) deduplicação contra vendas e filtro de desqualificados, (3) governança de contatos recentes, (4) validação pré-disparo. É a fonte de verdade para qualquer campanha de WhatsApp, e-mail ou SMS.
unibf_vendas_v2
Registros de matrículas confirmadas. Atualizada automaticamente via Unimestre → Supabase → BigQuery. É a base usada para deduplicação: qualquer telefone ou e-mail presente aqui é excluído do Lead_Real. Entra no fluxo no momento da confirmação de matrícula.
report_tables_campaign
Dados de performance de disparos (WhatsApp, e-mail, SMS): volume entregue, lidas, respondidas, CPL, bounce. Alimenta o Power BI. Não entra diretamente no fluxo de leads — é consumido para análise e otimização da operação.
dim_templates
Catálogo de templates aprovados para disparo via OmniChat/WhatsApp. Associado a campanhas e fluxos automatizados. Dimensão de referência para os disparos registrados em report_tables_campaign.
dim_customer_support_request
Registro de conversas do OmniChat: tickets de suporte, qualificação comercial, encerramento. Vincula interações de atendimento ao lead e ao template disparado. Fonte de dados para cálculo de TMA, TMPR e taxa de resposta.
Relacionamentos e Chaves
Como as tabelas se conectam, quais são as chaves de join e como a resolução de identidade funciona na prática.
Chaves Principais de Identificação
| Chave | Tabelas | Tipo | Descrição |
|---|---|---|---|
| external_id | f_eventos ↔ leads_enriquecidos_v2 | STRING | ID do CRM/banco de leads. Chave primária de ligação entre evento no site e registro de lead. Pode ser hash do usuário. |
| whatsapp_user_id | f_eventos ↔ dim_customer_support_request | STRING | ID da plataforma OmniChat. Liga eventos de navegação com conversas de atendimento WhatsApp. |
| Todas as tabelas | STRING | Chave de unificação de identidade entre ferramentas. Usado na deduplicação leads × vendas. Deve estar em lowercase. | |
| telefone / telefone_celular | leads_enriquecidos_v2 ↔ unibf_vendas_v2 | STRING | Chave operacional primária de deduplicação. O campo fullNumber é usado no disparo. Problemas de normalização existem — ver Seção 9. |
| cd_pessoa | unibf_vendas_v2 | STRING | Identificador interno único do aluno/cliente no sistema ERP (Unimestre). Não existe nas tabelas de lead — é gerado somente após matrícula. |
| id | unibf_leads_v2, leads_enriquecidos_v2, Lead_Real | STRING | Identificador sequencial do lead no sistema de origem (Supabase). Não é a mesma chave entre as três tabelas. |
| id_recorrencia_lead | leads_enriquecidos_v2, Lead_Real | NULLABLE | Liga leads recorrentes ao mesmo indivíduo quando ele retorna ao funil com outro formulário. |
Ordem de Prioridade de Matching
fullNumber antes do join.Problemas de Identidade Conhecidos
Um mesmo indivíduo pode estar em múltiplas tabelas com IDs diferentes, telefones em formatos distintos e e-mails com variações de capitalização. A resolução de identidade depende de normalização prévia dos campos antes de qualquer join.
Regras de Negócio Críticas
As regras que definem quais leads entram na base de disparo e quais são excluídos. Estas regras são aplicadas no processo de geração do Lead_Real.
Definição de Lead_Real
Lead_Real é o resultado de quatro filtros sequenciais aplicados sobre leads_enriquecidos_v2. Não contém alunos matriculados, não contém desqualificados, não contém leads contatados recentemente. É a única base autorizada para disparo de campanhas.
Regra 1 — Deduplicação contra Vendas
Todo lead cujo telefone_celular ou e-mail já existe em unibf_vendas_v2 é removido da base de disparo. Isso garante que alunos matriculados não recebam comunicações de captação.
# Remove leads já matriculados (match por telefone ou e-mail)
leads_elegíveis = leads_enriquecidos[
~leads_enriquecidos['telefone'].isin(vendas['telefone_celular']) &
~leads_enriquecidos['email'].isin(vendas['email'])
]
Regra 2 — Exclusão de Desqualificados (closing_reason)
Leads com os seguintes closing_reason são excluídos de todas as bases de disparo. A lista deve ser atualizada periodicamente conforme o time comercial altera as tags.
Regra 3 — Governança de Contatos Recentes (Blocklist)
Leads contatados nos últimos 7 dias (em alguns fluxos, 15 dias) são incluídos em uma blocklist temporária e não recebem novo disparo. Esta regra é verificada no início do pipeline de preparação da base.
Regra 4 — Rotina de Identificação de Aluno (n8n)
Fluxo automatizado via n8n que verifica periodicamente se leads na base RD Station converteram em matrícula. Ao confirmar conversão, aplica a tag aluno n8n, atualiza campos de matrícula e remove o contato das réguas de captação.
# Exclusões para pipe de pré-inscritos (variante mais restritiva)
EXCLUIR_CLOSING_NORM = {
"negocio ganho", "negocio perdido",
"ja e aluno", "ja e aluno - atendimento cra",
"ja e aluno - recem matriculado", "ja e aluno - renegociacao",
"sou aluno", "sou_aluno",
"fechou com o concorrente", "desqualificado",
"nao tem interesse", "sem interesse",
"queria curso gratis", "nao tem os requisitos necessarios",
"ensino medio incompleto"
}
Segmentação de Leads
Critérios operacionais de classificação dos leads válidos para otimização de campanhas e jornadas.
Segmentação Temporal (Janela de Decisão)
Leads criados nos últimos 6 meses (180 dias) ainda não convertidos são considerados ativos de venda. Lead ativo = data_criacao >= hoje - 180 dias E status ∉ {Matriculado, Descartado, Concorrente, Não tem interesse, Opt-out}.
Segmentação por Nível de Interesse
| Nível | Critério | Abordagem Recomendada |
|---|---|---|
| quente | Interagiu nos últimos 15 dias | Conversão direta. Abordagem comercial imediata. |
| morno | Interagiu entre 15 e 45 dias | Reforço de valor + oferta intermediária. |
| frio | Nunca interagiu | Nutrição de autoridade. Não forçar conversão imediata. |
Segmentação Temporal Detalhada (Last N Days)
Segmentação por Modalidade
Pipeline de Preparação e Disparo
Fluxo técnico completo da geração da base final até o disparo OmniChat/WhatsApp.
Pipeline de Preparação da Base
leads_enriquecidos_v2 com todos os campos normalizados.unibf_vendas_v2 por telefone e e-mail. Remove alunos matriculados.EXCLUIR_CLOSING_NORM: remove desqualificados, concorrentes, alunos, sem interesse.telefone normalizado para fullNumber.(Nome + fullNumber).csv. Resultado: Base Final para disparo.Tipos de Disparo OmniChat
Estados do Fluxo de Atendimento
f_eventos
Tabela de fatos de eventos comportamentais. BigQuery · gtm-5zhzjzt-njk1n / performance_v4 / f_eventos. 36 campos.
Identificação do Evento
| Campo | Tipo | Descrição |
|---|---|---|
| event_id | STRING · REQUIRED | Identificador único do evento gerado pelo site/tag (UUID ou ID do GA/Tag Manager). Garante unicidade e rastreio do mesmo evento entre sistemas. |
| nome_evento | STRING · REQUIRED | Nome lógico do evento coletado: page_view, generate_lead, form_submit, whatsapp_click. Chave de segmentação por tipo de ação. |
| data_hora_primeiro_acesso | Data e hora do primeiro evento registrado para este usuário/sessão (primeiro pageview). Usado em análises de janela de decisão, coortes e origem inicial. | |
| data_hora_atual | Data e hora em que este evento específico foi disparado. Timestamp principal em filtros por período. | |
| dominio | STRING · REQUIRED | Domínio onde o evento ocorreu: unibf.com.br, inscricao.unibf.com.br. Diferencia propriedades e ambientes. |
| url_inteira | STRING · REQUIRED | URL completa no momento do evento (caminho + query string). Análise de funil por página e parâmetros adicionais. |
| x-ga-request_count | INTEGER · REQUIRED | Contador incremental de requisições na sessão (originado do x-ga- do GA4). Útil para entender ordem e cadeia dos eventos. |
| ga_session_number | INTEGER · REQUIRED | Número da sessão GA para o usuário (1ª, 2ª, 3ª sessão). Análise de comportamento por frequência de retorno ao site. |
Identificação do Usuário / Lead
| Campo | Tipo | Descrição |
|---|---|---|
| external_id | STRING · REQUIRED | Identificador externo do usuário/lead (ID do CRM, banco de leads ou hash). Chave de join com tabelas de leads, matrículas e CRM. |
| whatsapp_user_id | STRING · REQUIRED | ID do usuário na plataforma de WhatsApp/OmniChat. Cruza eventos de navegação com eventos de chat. |
| nome | STRING · REQUIRED | Nome do usuário capturado em formulários. Pode estar vazio em eventos puramente de navegação. |
| STRING · REQUIRED | E-mail do usuário quando informado. Chave importante para unificação de identidades entre ferramentas. | |
| telefone | STRING · REQUIRED | Telefone informado no formato original do formulário. Em outro estágio do pipeline é normalizado para fullNumber, DDD etc. |
| existe_whatsapp | STRING · NULLABLE | Flag textual indicando se o número possui WhatsApp detectado/validado: sim, nao, desconhecido. Decide acionamento do canal WhatsApp. |
| escolaridade | STRING · NULLABLE | Nível de escolaridade informado: ensino medio completo, graduacao completa. Relevante para segmentações de campanha. |
Dados de Navegação / Dispositivo
| Campo | Tipo | Descrição |
|---|---|---|
| client_user_agent | STRING · REQUIRED | User Agent do navegador/dispositivo. Identifica browser, SO e tipo de device (mobile/desktop). |
| ip_address | STRING · REQUIRED | IP público do usuário no momento do evento. Geolocalização e detecção de fraude/robô. Requer tratamento LGPD. |
| mobile | STRING · REQUIRED | Indicador de acesso móvel: true/false ou mobile/desktop. Segmentação de desempenho por tipo de device. |
| pais | STRING · REQUIRED | País detectado ou informado (ex.: Brasil). |
| estado | STRING · REQUIRED | Estado/UF associado ao IP ou campo de formulário (ex.: RJ, SP). |
| cidade | STRING · REQUIRED | Cidade do usuário (formulário ou geolocalização por IP). |
Dados de Interesse / Produto
| Campo | Tipo | Descrição |
|---|---|---|
| curso | STRING · REQUIRED | Curso/produto de interesse no momento do evento: Pedagogia EAD, Pós em Psicopedagogia. Chave para cruzar eventos com matrículas por curso. |
| modalidade | STRING · REQUIRED | Modalidade do curso: EAD, Semipresencial, Tecnólogo. Análise de performance por tipo de oferta. |
Parâmetros de Mídia — Primeira Entrada (_entrada)
| Campo | Tipo | Descrição |
|---|---|---|
| utm_source_entrada | STRING · REQUIRED | utm_source da primeira sessão conhecida do usuário: google, facebook, instagram, afiliado_x. |
| utm_medium_entrada | STRING · REQUIRED | utm_medium da primeira sessão: cpc, paid_social, email, organic. |
| utm_campaign_entrada | STRING · REQUIRED | utm_campaign da primeira sessão. Define a campanha de entrada do lead (coorte original). |
| utm_content_entrada | STRING · REQUIRED | utm_content da primeira sessão: variações de criativo, teste A/B. |
| utm_term_entrada | STRING · REQUIRED | utm_term da primeira sessão: termo de busca em campanhas de pesquisa. |
Parâmetros de Mídia — Campanha Atual (_atual)
| Campo | Tipo | Descrição |
|---|---|---|
| utm_source_atual | STRING · REQUIRED | utm_source no momento do evento. Pode diferir da entrada se o usuário voltou por outro canal. |
| utm_medium_atual | STRING · REQUIRED | utm_medium atual. |
| utm_campaign_atual | STRING · REQUIRED | utm_campaign atual: campanha associada ao clique/sessão onde o evento ocorreu. |
| utm_content_atual | STRING · REQUIRED | utm_content atual: criativo/variação que trouxe o clique. |
| utm_term_atual | STRING · REQUIRED | utm_term atual: termo de busca na sessão corrente. |
Identificadores de Mídia — Facebook / Google
| Campo | Tipo | Descrição |
|---|---|---|
| fbp | STRING · REQUIRED | Facebook Browser ID (cookie fbp). Identifica o navegador para matching de eventos via pixel/Meta e CAPI. |
| fbc | STRING · REQUIRED | Facebook Click ID (cookie fbc). Identificador de clique da Meta Ads (campanha/conjunto/anúncio). Usado na atribuição via CAPI. |
| gclid | STRING · REQUIRED | Google Click ID associado à origem do evento. Integração e atribuição de campanhas Google Ads. |
unibf_vendas_v2
Tabela de conversões/matrículas. Supabase + BigQuery. Fonte de verdade para receita e deduplicação de leads.
| Campo | Descrição | Exemplos |
|---|---|---|
| cd_pessoa | Identificador interno único do aluno/cliente (ERP Unimestre) | 12345, 98765 |
| E-mail usado na matrícula. Chave de deduplicação contra leads. | [email protected] | |
| telefone_celular | Telefone celular do aluno convertido. Chave de deduplicação primária. | 21999998888 |
| modalidade | Modalidade do curso contratado | EAD, Presencial, Híbrido, Semipresencial |
| grau | Grau acadêmico da matrícula | Bacharelado, Pós-Graduação, Mestrado, Técnico, Licenciatura |
| curso | Nome do curso contratado | Administração, Engenharia Civil, Pedagogia, Direito, Psicologia |
| matriculou | Status de matrícula (confirmação booleana) | Sim, Não |
| origem | Origem da captação do aluno | Google Ads, Facebook Ads, Indicação, Orgânico |
| origem2 | Origem secundária/detalhe do canal | Landing Page A, Formulário B, Chatbot, WhatsApp |
| cd_coligada | Código interno da coligada/unidade | 1, 2, 10, 20, 35 |
| data | Data de matrícula | 2025-08-10, 2025-07-15 |
| valor_parc_1 | Valor da primeira parcela | 150.00, 200.00, 300.00 |
| valor_parc_2 | Valor da segunda parcela | 150.00, 200.00 |
| valor_total | Valor total do curso/matrícula | 3000.00, 4500.00, 1200.00 |
| quantidade_parcelas | Número de parcelas acordadas | 12, 6, 24, 18, 3 |
| ticket_medio_global | Ticket médio de compras globais do aluno | 2500.00, 4000.00 |
| qtd_parcelas | Quantidade de parcelas (campo complementar/sinônimo) | 12, 6, 24 |
| estado | Estado de residência do aluno | RJ, SP, MG, BA, DF |
| sexo | Sexo do aluno | Masculino, Feminino, Outro |
| idade | Idade do aluno | 18, 22, 30, 40, 55 |
| forma_pagamento_limpa | Forma de pagamento padronizada | Cartão de Crédito, Boleto, PIX, Débito Automático |
| forma_pagamento_bruta | Forma de pagamento como recebida no sistema de origem | CC, BOL, PIX, Débito, Transf |
leads_enriquecidos_v2
Base de leads tratada e enriquecida. Supabase + BigQuery. Resultado do processo de normalização sobre unibf_leads_v2.
| Campo | Descrição | Exemplos |
|---|---|---|
| id | Identificador único do lead no sistema Supabase | 10001, 10002 |
| date | Data/hora da captura do lead | 2025-08-20, 2025-07-10 |
| nome | Nome do lead | Maria, João, Pedro |
| sobrenome | Sobrenome do lead | Silva, Souza, Oliveira |
| E-mail do lead | [email protected] | |
| telefone | Telefone do lead (pode ter DDD e/ou +55 — requer normalização) | 21999998888, +552199998877 |
| modalidade | Modalidade de interesse declarada pelo lead | EAD, Presencial, Híbrido |
| curso | Curso de interesse do lead | Administração, Direito, Pedagogia |
| investimento | Valor informado ou estimado do curso pelo lead | 500.00, 1000.00, 2000.00 |
| cupom | Cupom de desconto informado pelo lead | BOLSA55, DESCONTO20, CAMPANHAJULHO |
| consent | Indica se o lead deu consentimento LGPD (opt-in). Campo crítico para governança. | true, false, TRUE, FALSE, 1 |
| utm_term_id | ID de termo de campanha digital | 101, 202, term_003 |
| utm_content_id | ID de conteúdo da campanha | 301, 404, cont_abc |
| utm_campaign_id | ID interno da campanha digital | 501, 777, camp_2025 |
| utm_source | Origem digital da campanha | google, facebook, instagram |
| utm_campaign | Nome da campanha digital | vestibular2025, desconto55, graduacao_online |
| status | Status do lead no CRM RD Station | novo, em_andamento, qualificado, perdido, convertido |
| contacted_user | Responsável pelo contato | Victor, Maria, João, Sistema |
| finished_user | Usuário que finalizou atendimento | Victor, CRM_bot, Ana |
| origin_channel | Canal de origem do lead | Facebook Ads, Google Ads, Orgânico, WhatsApp |
| origin_name | Nome do canal/campanha de origem | Campanha Vestibular, Formulário Site, LP_2025 |
| origin_url | URL de origem da captura | https://unibf.com/vestibular, https://lp.unibf.com/ead |
| origin_token | Token único do canal/origem | tok123, abc999, 456xyz |
| closing_reason | Motivo de fechamento do lead no CRM | desqualificado, desistência, matrícula concluída |
| modalidade_limpa_original | Modalidade original antes da normalização | EAD - Online, Presencial (Campus RJ) |
| normalized | Flag indicando se os dados foram normalizados | sim, não, true, false |
| modalidade_limpa | Modalidade padronizada após normalização | EAD, PRESENCIAL, HÍBRIDO, SEMIPRESENCIAL |
| curso_corrigido | Curso normalizado para nomenclatura canônica | Adm, Eng Civil, Pedagogia, Psicologia |
| id_curso | ID único do curso no sistema | 1, 2, 3, 4, 5 |
| id_area | ID da área do curso | 10, 20, 30, 40, 50 |
| id_modalidade | ID da modalidade no sistema | 100, 200, 300, 400 |
| lead_recorrencia | Indica se o lead já ocorreu antes (lead retornou ao funil) | 0, 1, false, true |
| id_recorrencia_lead | ID de recorrência do lead para ligação entre ocorrências | rec123, rec456, rec789 |
unibf_leads_v2
Base de leads brutos capturados nos formulários das landing pages, site, quiz e canais orgânicos. Supabase + BigQuery.
| Campo | Descrição | Exemplos |
|---|---|---|
| id | Identificador único do lead no Supabase | 1001, 1002, 1003 |
| date | Data/hora da captura do lead | 2025-08-20, 2025-07-10 |
| nome | Nome do lead informado no formulário | Maria, João, Pedro |
| sobrenome | Sobrenome do lead | Silva, Souza, Oliveira |
| E-mail informado pelo lead | [email protected] | |
| telefone | Telefone informado pelo lead (formato original do formulário) | 21999998888, 11988887777 |
| modalidade | Modalidade de interesse declarada | EAD, Presencial, Híbrido |
| curso | Curso de interesse | Administração, Direito, Engenharia |
| investimento | Valor estimado do curso pelo lead | 500.00, 1000.00, 2000.00 |
| utm_source | Origem da campanha digital | google, facebook, instagram |
| utm_campaign | Nome da campanha de marketing | vestibular2025, desconto55, graduacao_online |
| utm_medium | Meio da campanha digital | cpc, email, social, referral, display |
| utm_content | Conteúdo do anúncio/campanha | anuncioA, banner1, video_lp |
| utm_term | Palavra-chave capturada | curso_online, bolsa_55, graduação |
| interaction_date | Data da última interação do lead | 2025-08-21, 2025-08-19 |
| interaction_id | ID da interação no sistema | int123, int456, int789 |
| status | Status atual do lead no CRM | novo, em_andamento, qualificado, perdido, convertido |
| contacted_user | Usuário que fez o contato | Victor, Ana, João, CRM_Bot |
| finished_user | Usuário que finalizou a jornada | Victor, CRM_Bot, Pedro |
| origin_channel | Canal principal de origem | Google Ads, Facebook Ads, Orgânico, WhatsApp |
| origin_name | Nome do canal/campanha | Campanha Vestibular, LP_Engenharia |
| origin_type | Tipo de origem do lead | digital, físico, indicação, orgânico, parceiro |
| origin_url | URL da origem | https://unibf.com/vestibular, https://lp.unibf.com/ead |
| origin_token | Token único da origem | tok123, abc999, jwt_abc |
| closing_reason | Motivo de fechamento do lead | desqualificado, desistência, matrícula concluída, duplicado |
Lead_Real
Base final elegível para disparo. ~241.016 linhas × 53 colunas. Mesma estrutura do enriquecido, mas já filtrada. Não contém alunos matriculados. Não contém desqualificados.
Lead_Real.csv não contém leads cujo telefone_celular ou email já estejam em unibf_vendas_v2. Também não contém leads com closing_reason = desqualificado ou qualquer dos motivos de exclusão listados na Seção 4.
| Campo | Descrição | Exemplos |
|---|---|---|
| id | Identificador único do lead (herdado de leads_enriquecidos_v2) | 20001, 20002, 20003 |
| date | Data/hora da captura do lead | 2025-08-20, 2025-07-10 |
| nome | Nome do lead (válido para disparo) | Carla, Bruno, Fernanda |
| sobrenome | Sobrenome do lead | Almeida, Ribeiro, Costa |
| E-mail válido — não duplicado com vendas | [email protected], [email protected] | |
| telefone | Telefone válido — não duplicado com vendas. Usado como fullNumber no disparo. | 21999998888, 11988887777 |
| modalidade | Modalidade de interesse do lead | EAD, Presencial, Híbrido |
| curso | Curso de interesse do lead | Administração, Direito, Pedagogia |
| investimento | Valor estimado informado pelo lead | 500.00, 1000.00, 2000.00 |
| cupom | Cupom de desconto quando informado | BOLSA55, VESTIBULAR2025 |
| consent | Consentimento LGPD (opt-in). Deve ser true para disparo autorizado. | true, false, TRUE, 1 |
| utm_term_id | ID de termo da campanha de origem | 101, 202, term_003 |
| utm_content_id | ID do conteúdo da campanha de origem | cont001, contXYZ |
| utm_campaign_id | ID interno da campanha de origem | camp001, vestibular_2025 |
| utm_source | Origem da campanha digital | google, facebook, instagram |
| utm_campaign | Nome da campanha de marketing | vestibular2025, desconto55 |
| status | Status do lead no CRM (apenas status não excluídos) | novo, em_andamento, qualificado |
| contacted_user | Usuário que fez contato | Victor, Ana, CRM_Bot |
| finished_user | Usuário que finalizou atendimento | Victor, Sistema, CRM_Bot |
| origin_channel | Canal de origem | Facebook Ads, Google Ads, Orgânico |
| origin_name | Nome do canal/campanha de origem | Campanha Vestibular, LP_Engenharia |
| origin_url | URL de origem do lead | https://unibf.com/vestibular |
| origin_token | Token único do canal/origem | tok123, abc999 |
| closing_reason | Motivo de fechamento — somente valores ≠ "desqualificado" (pós-filtro) | desistência, matrícula concluída, não respondeu |
| modalidade_limpa_original | Modalidade original antes da normalização | EAD - Online, Presencial (Campus SP) |
| normalized | Flag de normalização dos dados | sim, não, true, false |
| modalidade_limpa | Modalidade padronizada (pós-normalização) | EAD, PRESENCIAL, HÍBRIDO, SEMIPRESENCIAL |
| curso_corrigido | Curso normalizado para nomenclatura canônica | Adm, Eng Civil, Psicologia, Pedagogia |
| id_curso | ID do curso no sistema | 1, 2, 3, 4, 5 |
| id_area | ID da área acadêmica | 10, 20, 30, 40, 50 |
| id_modalidade | ID da modalidade no sistema | 100, 200, 300, 400 |
| lead_recorrencia | Indica se o lead é recorrente (retornou ao funil) | 0, 1, false, true |
| id_recorrencia_lead | ID de recorrência para ligação de ocorrências do mesmo indivíduo | rec123, rec456, rec789 |
Atribuição de Mídia
Como funcionam os campos _entrada vs _atual e seu impacto nas análises de marketing.
Primeira Origem vs. Última Origem
Capturados na primeira sessão do usuário. Representam a coorte original — o canal que originou o lead pela primeira vez. Usados para análise de origem de lead.
Capturados no momento do evento específico. Podem diferir da entrada se o usuário voltou por outro canal. Usados para análise de origem de conversão.
Impacto nas Análises de Marketing
| Análise | Campo Correto | Motivo |
|---|---|---|
| Qual canal originou o lead? | utm_source_entrada | Representa a primeira interação conhecida — o canal que "descobriu" o lead. |
| Qual canal gerou a conversão? | utm_source_atual | Representa o canal da sessão onde ocorreu o evento de conversão (last-click). |
| Análise de coorte por campanha | utm_campaign_entrada | Agrupa leads pela campanha original de captação, independente de re-entradas. |
| Performance de criativo | utm_content_atual | Identifica qual variação/criativo gerou o clique que levou ao evento. |
| CPL por canal | utm_source_entrada + data_hora_primeiro_acesso | Custo por lead calculado sobre a origem inicial, não sobre re-engajamentos. |
Os campos UTM frequentemente chegam nulos ou com valores genéricos (direct, (none)) quando o usuário acessa diretamente ou via link não tagueado. Isso distorce análises de atribuição. Ver Seção 9 para detalhes.
Governança e LGPD
Regras de privacidade, consentimento, exclusão e anonimização de dados de leads conforme a Lei Geral de Proteção de Dados.
Consentimento (Opt-in)
O campo consent em leads_enriquecidos_v2 e Lead_Real registra o opt-in do lead para comunicações. Valores aceitos: true, TRUE, 1. Leads com consent = false não devem entrar em réguas de marketing.
Lead continua na base mas não pode ser contatado. Gatilhos: clique em descadastro, pedido explícito, reclamação formal (Procon). Aplica tag optout_email_global ou bloqueado_email. Remove de todas as listas e fluxos automáticos. Mantém e-mail + data + motivo para auditoria LGPD.
Lead sem engajamento por muito tempo. Critério: 12 meses sem abrir e-mail → move para segmento "inativo". Após campanha de reativação final (2–3 tentativas): manter como registro frio ou seguir para anonimização.
Remove dados que identifiquem a pessoa, mas mantém dados agregados para análise histórica. Quando: pedido de eliminação, inatividade 3–5 anos, fim de obrigação contratual.
Como Anonimizar
| Campo | Ação | Exemplo Pós-Anonimização |
|---|---|---|
| Substituir por hash irreversível | [email protected] | |
| nome | Substituir por valor genérico | ANONIMIZADO |
| telefone | Zerar ou substituir por padrão | NULL ou 00000000000 |
| cidade | Manter apenas UF/macro-região | RJ (manter estado, remover cidade) |
| ip_address | Zerar completamente | NULL |
| datas | Manter (sem identificação pessoal) | Manter date, data_criacao |
| IDs internos | Manter se não reversíveis | Manter id, id_curso |
| utm_*, origin_* | Manter para análise de performance histórica | Manter todos os campos de campanha |
Riscos e Considerações
ip_address em f_eventos é considerado dado pessoal pela LGPD. Seu uso para geolocalização e detecção de fraude é legítimo, mas deve estar coberto pela política de privacidade. Não deve ser exposto em relatórios ou dashboards sem pseudonimização.Fonte de Verdade por Domínio
Mapeamento definitivo de qual base deve ser consultada para cada domínio de dados do CRM UNIBF.
Observações de Qualidade de Dados
Problemas reais identificados nas bases que impactam a confiabilidade de análises e a eficiência de campanhas.
telefone existe em diferentes formatos: número puro (21999998888), com DDD separado, com prefixo +55 (+552199998877). A normalização para fullNumber é feita no pipeline de disparo, mas joins entre tabelas sem normalização prévia geram falsos negativos na deduplicação — alunos matriculados podem escapar do filtro.email é REQUIRED mas frequentemente vazio em eventos de page_view onde nenhum formulário foi submetido. Isso limita a capacidade de join diretamente entre eventos e leads sem passar pelo external_id.utm_source = direct, (none) ou null, especialmente em tráfego orgânico, acessos diretos e links não tagueados de WhatsApp. Em 2025, o mês de abril concentrou alto volume de desengajamento correlacionado com base de UTMs fracos — indicando que a qualidade da base é mais determinante que o volume.id em unibf_leads_v2, (2) um id diferente em leads_enriquecidos_v2, (3) um external_id no GA/GTM, (4) um whatsapp_user_id no OmniChat e (5) um cd_pessoa no Unimestre se converteu. A ausência de um ID unificado de pessoa torna a análise de jornada completa dependente de cruzamentos por telefone/e-mail, com os riscos de qualidade associados.closing_reason apresenta duplicidades semânticas: Não tem interesse, nao tem interesse, sem interesse, Sem Interesse - Não Tem Interesse existem como valores separados. A normalização por EXCLUIR_CLOSING_NORM resolve parcialmente, mas deve ser atualizada toda vez que o time comercial criar novas tags.lead_recorrencia aceita valores boolean (true/false) e numéricos (0/1) sem padronização entre tabelas. Análises de coorte por recorrência exigem normalização prévia deste campo.Diagnóstico Operacional 2025
Visão Relacional Final
Representação da estrutura de dados do CRM UNIBF: fatos, dimensões e relacionamentos entre tabelas.
Tabelas de Fato
Dimensões Implícitas
report_tables_campaign pelo ID do template usado na campanha.whatsapp_user_id e com campanhas via template + data.id_curso, id_area, id_modalidade em leads_enriquecidos_v2 e Lead_Real.utm_*_entrada e utm_*_atual em f_eventos. Origem, meio, campanha e criativo.Mapa de Fluxo Completo
Nenhuma campanha de captação deve ser disparada sem validação prévia contra Lead_Real. Nenhuma análise de receita deve ser feita sem consultar unibf_vendas_v2. Nenhuma atribuição de lead deve ser feita sem distinguir utm_*_entrada de utm_*_atual.