SQL para Analytics: avaliar consultas que juntam Analytics, loja e CRM

Quando a equipe entrega uma consulta sobre a exportação do Analytics, o executivo raramente vai ler o código. Ele precisa saber três coisas: que pergunta a consulta responde, em que unidade ela conta (evento, sessão, pedido, cliente) e que prova mostra que nenhum total foi inflado no caminho. Este capítulo trata dessas três coisas, com o código da Verde Vivo, a loja fictícia do curso, mostrado para quem quiser acompanhar.

Cada linha da exportação é um evento, e o resto se reconstrói

Na tabela exportada, cada linha é um evento: uma página vista, um item no carrinho, uma compra. A sessão não existe como linha; ela é montada juntando os eventos que têm o mesmo identificador de navegador e o mesmo número de sessão. Os detalhes de cada evento ficam guardados em listas dentro da linha, como gavetas numa caixa: a lista de parâmetros (endereço da página, número da sessão) e a lista de itens comprados. Para usar um parâmetro, a consulta abre a gaveta com a operação UNNEST, que transforma cada elemento da lista numa linha; a equipe precisa controlar essa expansão para não repetir valores na soma.

Um cuidado aparece logo na primeira consulta. A exportação tem três registros com origem de tráfego: a origem coletada em cada evento, a origem da sessão e a origem da primeira visita. Os dois últimos são os que mais confundem. O registro traffic_source guarda a origem que trouxe a pessoa pela primeira vez e não muda depois. O registro session_traffic_source_last_click guarda a origem da sessão, pela regra do último clique. Uma consulta que pretende mostrar “de onde vieram as compras desta semana” e usa traffic_source está contando a primeira visita de cada cliente, talvez de anos atrás.

O exemplo técnico abaixo lista, para uma semana, cada página vista e cada compra, com o número da sessão e a origem da sessão. O filtro pelo final do nome da tabela (_TABLE_SUFFIX) limita a leitura aos sete dias pedidos; sem ele, o BigQuery leria todo o histórico e cobraria por isso.

Itens e pedidos repetidos inflam a receita sem aviso

A multiplicação de linhas numa junção já apareceu em Data Studio (antigo Looker Studio): o relatório mensal que junta fontes; aqui os dois riscos novos são a expansão dos itens e a repetição do mesmo pedido. Pense em três pedidos da semana. O pedido 101 teve um item; o 102, três itens; o 103 foi registrado duas vezes porque o servidor reenviou a compra depois de uma resposta perdida. Se a consulta abre a lista de itens antes de somar a receita do pedido, o 102 aparece três vezes. Se não trata a repetição, o 103 aparece duas. A receita sai inflada, e a consulta roda sem nenhum aviso.

Contar pedidos distintos protege a contagem e não protege a soma. A versão anterior da consulta da Verde Vivo usava COUNT(DISTINCT), a operação que conta cada número de pedido uma única vez, para os pedidos e somava a receita de todas as linhas, e o pedido repetido entrava duas vezes no dinheiro. A versão corrigida escolhe primeiro uma linha por pedido (a primeira compra registrada com aquele número) e só depois soma. Antes disso, uma consulta de apoio lista os números repetidos com valores ou itens diferentes entre si, que vão para investigação em vez de serem resolvidos pela regra, e as compras sem número válido são contadas à parte.

A mesma lógica vale para o CRM. Um lead gera dezenas de eventos no Analytics e uma linha no HubSpot. Juntar as duas tabelas evento a evento multiplica cada contrato por dezenas. A consulta certa resume o Analytics em uma linha por lead e só depois junta uma linha com uma linha. A defesa contra o erro é contar as linhas antes e depois de cada junção: se o total mudou, investigue registros duplicados ou excluídos pela junção, e a equipe explica toda diferença.

A conciliação com a loja classifica cada pedido

A consulta mais útil da Verde Vivo junta as compras do Analytics com os pedidos pagos da loja pelo número do pedido e coloca cada pedido em um de três grupos: está nos dois, só na loja, só no Analytics. Em agosto de 2026, ela encontrou 1.390 compras no Analytics para 1.650 pedidos pagos na loja. Os 260 pedidos só na loja vinham sobretudo de quem recusou a medição no banner. Foi uma conciliação igual, em maio, que revelou os pagamentos por Pix confirmados depois que a pessoa fechava a aba, caso resolvido em Server-side e Measurement Protocol: medir pagamentos fora do site.

Com a compra enviada pelo servidor, os grupos separam lacuna esperada de defeito. “Só na loja” tem uma causa esperada, a recusa de medição no banner, e causas de defeito, como pedido parado na fila ou sem identificador. “Só no Analytics” pede investigação de compras de teste, identificadores incorretos, diferenças de período ou registros ausentes na extração da loja; reenvios com o mesmo número são detectados antes da junção. A consulta separa os grupos; a explicação vem de olhar alguns pedidos de cada um, pela forma de pagamento e pela data.

O custo da consulta depende de quantos dados ela lê

No modelo sob demanda, o BigQuery cobra pelos bytes lidos; no modelo por capacidade, pelos slots (unidades de processamento) contratados. O volume estimado aparece antes da execução, e a consulta gera cobrança conforme o modelo contratado. Quatro hábitos mantêm a conta baixa: filtrar sempre pelo final do nome da tabela, pedir só as colunas necessárias, criar tabelas intermediárias com os parâmetros já abertos e olhar a estimativa de volume antes de rodar. Na Verde Vivo, a tabela preparada diária, com uma linha por evento e os parâmetros necessários em colunas, reduziu a leitura de cada atualização do relatório de cerca de 38 GB para 2 GB.

A coorte é a pergunta que a exportação responde melhor que a interface, porque o prazo de armazenamento é definido no BigQuery e pode passar de 14 meses, desde que a política de expiração e o orçamento permitam. Até janeiro de 2026, as compras da loja iam para outra propriedade, criada pelo plugin da Shopify, e não estão na exportação. Por isso a consulta da Verde Vivo juntou o histórico de pedidos da Shopify, carregado no BigQuery, à exportação do site: 31% de quem fez a primeira compra em setembro de 2025, segundo o cadastro da loja, voltou a comprar nos 12 meses seguintes. A exportação entra para ler o que esses clientes fizeram no site.

Consulta no BigQuery e consulta pela API respondem de jeitos diferentes

Às vezes a equipe compara o resultado do SQL com um número tirado da Data API, a porta pela qual programas pedem relatórios ao Analytics. Os dois diferem pelos mesmos motivos que separam interface e exportação, e a ajuda oficial tem uma tabela que lista essas diferenças. Desde 14 de setembro de 2026, as respostas da Data API trazem um campo que avisa quando o relatório foi cortado por limite (dataTruncationReasons), com o tipo de corte e as datas afetadas. A exportação não traz esse campo; os limites dela são outros, como o teto de 1 milhão de eventos por dia na exportação diária da propriedade padrão.

Para a próxima consulta que chegar à sua mesa, peça três coisas junto do resultado: a frase da pergunta que ela responde, a unidade de cada linha e a comparação entre o total antes e depois de cada junção. Se a consulta concilia Analytics e loja, peça também a contagem dos três grupos e cinco exemplos de cada um. Essas verificações permitem avaliar o resultado sem revisar todo o código.