Como modelar conversões corretamente: lógica, deduplicação, janelas e atribuição multi-origem

A modelagem de conversões é uma das áreas mais sensíveis e críticas da mensuração moderna.

Fazer isso errado significa relatórios distorcidos, decisões erradas e campanhas sub ou superavaliadas.

Neste post, vamos construir um framework técnico completo para modelar conversões usando GA4 + BigQuery, considerando:

• deduplicação

• validação de eventos

• janelas de conversão

• lógica de negócio

• reconciliação client-side × server-side

• atribuição multi-origem

• fallback para sessões sem UTM/referrer

1. O que é modelagem de conversão?

Modelar conversões significa:

Transformar eventos capturados pelo GA4 (ou APIs) em dados confiáveis de negócio, aplicando regras para:

1. Remover duplicidades

2. Validar campos essenciais

3. Definir janelas (ex.: 7, 28 ou 90 dias)

4. Classificar origem válida

5. Calcular métricas derivadas

6. Conciliar várias fontes (GA4, CRM, CAPI, SGTM)

Sem esse processo, “purchase”, “generate_lead” ou “subscribe” são apenas eventos crus — não métricas reais.

2. As quatro fontes de duplicação que precisam ser tratadas

2.1. Client-side + Server-side enviando o mesmo evento

O caso mais comum:

• GA4 Web envia purchase

• CAPI envia purchase

• SGTM server-side envia novamente

• Webhooks do CRM enviam outro registro

Regra: A dedupe deve usar o campo event_id, com fallback para transaction_id.

Exemplo SQL:

CREATE OR REPLACE TABLE model.purchase_deduped AS
SELECT
  event_id,
  ANY_VALUE(transaction_id) AS transaction_id,
  ANY_VALUE(value) AS value,
  ANY_VALUE(currency) AS currency,
  MIN(event_timestamp) AS canonical_timestamp
FROM clean.events
WHERE event_name = "purchase"
GROUP BY event_id;

CREATE OR REPLACE TABLE model.purchase_deduped AS
SELECT
  event_id,
  ANY_VALUE(transaction_id) AS transaction_id,
  ANY_VALUE(value) AS value,
  ANY_VALUE(currency) AS currency,
  MIN(event_timestamp) AS canonical_timestamp
FROM clean.events
WHERE event_name = "purchase"
GROUP BY event_id;

CREATE OR REPLACE TABLE model.purchase_deduped AS
SELECT
  event_id,
  ANY_VALUE(transaction_id) AS transaction_id,
  ANY_VALUE(value) AS value,
  ANY_VALUE(currency) AS currency,
  MIN(event_timestamp) AS canonical_timestamp
FROM clean.events
WHERE event_name = "purchase"
GROUP BY event_id;

2.2. Eventos disparados mais de uma vez por erro de front-end

SPA mal implementadas duplicam eventos em:

• navegação interna

• re-render do componente

• race conditions

Detectamos duplicações analisando “intervalos muito pequenos”.

SELECT
  user_pseudo_id,
  event_name,
  COUNT(*) AS qtd,
  MIN(event_timestamp),
  MAX(event_timestamp)
FROM clean.events
GROUP BY user_pseudo_id, event_name
HAVING COUNT(*) > 3 
   AND TIMESTAMP_DIFF(MAX(event_timestamp), MIN(event_timestamp), SECOND) < 2;

SELECT
  user_pseudo_id,
  event_name,
  COUNT(*) AS qtd,
  MIN(event_timestamp),
  MAX(event_timestamp)
FROM clean.events
GROUP BY user_pseudo_id, event_name
HAVING COUNT(*) > 3 
   AND TIMESTAMP_DIFF(MAX(event_timestamp), MIN(event_timestamp), SECOND) < 2;

SELECT
  user_pseudo_id,
  event_name,
  COUNT(*) AS qtd,
  MIN(event_timestamp),
  MAX(event_timestamp)
FROM clean.events
GROUP BY user_pseudo_id, event_name
HAVING COUNT(*) > 3 
   AND TIMESTAMP_DIFF(MAX(event_timestamp), MIN(event_timestamp), SECOND) < 2;

2.3. Conversões de CRM sem validação de captura

Exemplo:

• leads incompletos

• emails inválidos

• phone sem DDI

• registros de teste internos

Validação:

WHERE 
  email NOT LIKE '%@test%' 
  AND email NOT LIKE '%internal%'
  AND phone IS NOT NULL 
  AND LENGTH(phone) >= 10

WHERE 
  email NOT LIKE '%@test%' 
  AND email NOT LIKE '%internal%'
  AND phone IS NOT NULL 
  AND LENGTH(phone) >= 10

WHERE 
  email NOT LIKE '%@test%' 
  AND email NOT LIKE '%internal%'
  AND phone IS NOT NULL 
  AND LENGTH(phone) >= 10

2.4. Conversões retroativas / atualizações de CRM

Um lead se torna MQL após 2 dias.

Precisamos registrar versão:

• lead_raw

• lead_valid

• lead_enriched

• lead_mql

• lead_sql

Cada uma é uma camada temporal — não é overwrite.

3. Construindo a janela de conversão correta

Modelagem real exige:

• janela de 1 dia para micro conversões

• janela de 7–30 dias para leads

• janela de até 90 dias para compras repetidas

• regra de “last-touch válido”

Exemplo:

SELECT *
FROM model.purchase_deduped
WHERE event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) 
                          AND CURRENT_TIMESTAMP();

SELECT *
FROM model.purchase_deduped
WHERE event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) 
                          AND CURRENT_TIMESTAMP();

SELECT *
FROM model.purchase_deduped
WHERE event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) 
                          AND CURRENT_TIMESTAMP();

4. Como aplicar regras de origem (Source/Medium/Channel)

Origem correta = métrica confiável.

GA4 frequentemente perde UTMs/referrers → vira Direct.

Regras de fallback:

CASE
  WHEN utm_medium IS NOT NULL THEN utm_medium
  WHEN page_referrer LIKE '%facebook.com%' THEN 'paid_social'
  WHEN page_referrer LIKE '%google.com%' THEN 'organic_search'
  ELSE 'direct'
END AS medium_fixed

CASE
  WHEN utm_medium IS NOT NULL THEN utm_medium
  WHEN page_referrer LIKE '%facebook.com%' THEN 'paid_social'
  WHEN page_referrer LIKE '%google.com%' THEN 'organic_search'
  ELSE 'direct'
END AS medium_fixed

CASE
  WHEN utm_medium IS NOT NULL THEN utm_medium
  WHEN page_referrer LIKE '%facebook.com%' THEN 'paid_social'
  WHEN page_referrer LIKE '%google.com%' THEN 'organic_search'
  ELSE 'direct'
END AS medium_fixed

5. Como fazer atribuição multi-toque (último clique + primeiro clique)

A empresa precisa ver:

• canal que trouxe o usuário pela primeira vez

• canal que converteu

Exemplo: atribuição dual-touch:

SELECT
  user_pseudo_id,
  FIRST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) 
    AS first_medium,
  LAST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    AS last_medium
FROM clean.events;

SELECT
  user_pseudo_id,
  FIRST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) 
    AS first_medium,
  LAST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    AS last_medium
FROM clean.events;

SELECT
  user_pseudo_id,
  FIRST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) 
    AS first_medium,
  LAST_VALUE(traffic_source.medium IGNORE NULLS) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    AS last_medium
FROM clean.events;

6. Como fazer reconciliação GA4 × CRM × CAPI

A regra moderna:

CAPI > CRM > GA4

CAPI > CRM > GA4

CAPI > CRM > GA4

Por quê?

• CAPI é mais confiável para Ads

• CRM é verdade absoluta de receita

• GA4 é melhor para jornada e atribuição

Exemplo de merge:

SELECT
  COALESCE(capi.event_id, crm.event_id, ga4.event_id) AS event_id,
  COALESCE(crm.value, capi.value, ga4.value) AS value,
  COALESCE(crm.status, 'converted') AS status
FROM capi.purchase capi
FULL JOIN crm.orders crm ON crm.event_id = capi.event_id
FULL JOIN ga4.purchase ga4 ON ga4.event_id = capi.event_id;

SELECT
  COALESCE(capi.event_id, crm.event_id, ga4.event_id) AS event_id,
  COALESCE(crm.value, capi.value, ga4.value) AS value,
  COALESCE(crm.status, 'converted') AS status
FROM capi.purchase capi
FULL JOIN crm.orders crm ON crm.event_id = capi.event_id
FULL JOIN ga4.purchase ga4 ON ga4.event_id = capi.event_id;

SELECT
  COALESCE(capi.event_id, crm.event_id, ga4.event_id) AS event_id,
  COALESCE(crm.value, capi.value, ga4.value) AS value,
  COALESCE(crm.status, 'converted') AS status
FROM capi.purchase capi
FULL JOIN crm.orders crm ON crm.event_id = capi.event_id
FULL JOIN ga4.purchase ga4 ON ga4.event_id = capi.event_id;

7. Validações finais antes de publicar conversões oficiais

Checklist:

• Todas as conversões deduplicadas por event_id

• Valores validados (currency, nulls, zeros)

• Origem corrigida com fallback

• Janelas aplicadas

• Marcações de teste removidas

• Reconciliação Cross-Source completa

• Métrica versionada: conversion_v1, conversion_v2…

8. Como conectar este post à série

👉 Post 1 - Como construir dashboards que refletem a “verdade de negócio” (GA4 + BigQuery)
👉 Post 2 - Como padronizar métricas entre equipes e ferramentas (GA4, CRM, Ads e BigQuery)
👉 Post 3 - Como alinhar definições de conversão entre GA4, Ads e CRM (e finalmente parar com divergências)
👉 Post 4 - Como limpar conversões no GA4: deduplicação, parâmetros obrigatórios e validação técnica
👉 Post 5 — Como construir pipelines confiáveis com BigQuery: coleta, limpeza e versionamento de métricas
👉 Post 6 — Como modelar conversões corretamente: lógica, deduplicação, janelas e atribuição multi-origem
👉 Post 7 — Auditoria técnica de métricas: como validar dados, detectar erros e garantir integridade no GA4 + BigQuery
👉 Post 8 - Como criar um Sistema Oficial de Métricas para sua empresa (ou ONG): governança, padronização e ciclo de melhoria contínua)

Referências técnicas

👉 GA4 Conversions guide